It sounds like you’re running into problems because of the mapping between date/time types and integers/longs. Date/time types are internally represented as integers/longs but that should be hidden from you by the code generation (RexToLixTranslator). If you’re hitting a problem in SqlFunctions.toLong you’re probably doing something wrong - in https://issues.apache.org/jira/browse/CALCITE-1569 <https://issues.apache.org/jira/browse/CALCITE-1569> and related bugs my hunches that proved correct.
Note that Java months are one off — eg. java.util.Calendar.JANUARY = 0. The real solution to this is to ensure that when you do RelBuilder.call(EXTRACT, …) it goes through the same code as if you’d written SQL “EXTRACT(…)”, and ends up with the same RelNode/RexNode expression. Then the generated code will be identical. Easier said than done, I know. But if you’re changing SqlFunctions or RexToLixTranslator you’ve probably gone off track. Julian > On May 16, 2017, at 9:44 AM, Chris Baynes <[email protected]> wrote: > > I've created an issue for this: > https://issues.apache.org/jira/browse/CALCITE-1793 > > While supplying a return type does fix the UnsupportedOperationException, > there's a couple of further problems: > > 1. SqlFunctions.toLong(Object o) does not support Timestamp, so throws the > "Cannot convert 2017-01-01... to long " > > If I fix that by adding a Timestamp case to that method making a call to > toLong(Timestamp v), then... > > 2. The result set returned doesn't make much sense, the months seem to be > off by 2 (ish), and the year is just totally wrong. > original col ;; year extract ;; month extract > 2015-12-21 15:35:28 ;; -2689988 ;; -1 > 2016-09-07 05:07:53 ;; 161565 ;; 11 > 2015-05-19 14:00:09 ;; 5004399 ;; 7 > > Is INTEGER a sensible returnTypeInference? Where does the date extract > actually get executed? > > Thanks! > > On Fri, May 12, 2017 at 8:01 AM, Julian Hyde <[email protected]> wrote: > >> While >> >>> builder.getRexBuilder().makeFlag(TimeUnitRange.YEAR), >> >> is fine, but we should change RelBuilder to allow >> >>> builder.literal(TimeUnitRange.YEAR), >> >> A SqlOperator doesn't need a returnTypeInference if each RexCall to >> the function always has it is always an explicit return type. The >> underlying problem here is that EXTRACT_DATE was originally intended >> to be an internal function, always created by translating a call to >> EXTRACT. >> >> You can probably get it working by supplying an explicit type. >> >> I think the ideal thing would be for people to create calls EXTRACT >> using RelBuilder (analogous to the SQL parse tree) and then have >> RelBuilder invoke a convertlet or something to translate into calls to >> EXTRACT_DATE or date-time arithmetic. But that's a fair amount of >> work. >> >> Please log a JIRA case so we can capture all of this. >> >> >> On Thu, May 11, 2017 at 3:59 AM, Chris Baynes <[email protected]> wrote: >>> I'm a bit closer with this, but it's still not quite working. >>> Couple of things I've noticed: >>> * literal() cannot be called with an enum >>> * the time unit is the first operand to extract when looking at >> testExtract >>> through the debugger >>> >>> This seems a bit closer: >>> >>> builder.call(SqlStdOperatorTable.EXTRACT_DATE, >>> builder.getRexBuilder().makeFlag(TimeUnitRange.YEAR), >>> builder.field(1, 0, "my_date")); >>> >>> But this still fails with: >>> >>> java.lang.UnsupportedOperationException: class >>> org.apache.calcite.sql.SqlSpecialOperator: EXTRACT_DATE >>> at org.apache.calcite.util.Util.needToImplement(Util.java:925) >>> at org.apache.calcite.sql.SqlOperator.inferReturnType( >> SqlOperator.java:475) >>> at org.apache.calcite.rex.RexBuilder.deriveReturnType( >> RexBuilder.java:272) >>> >>> This seems strange as the returnTypeInference on the EXTRACT_DATE op is >>> null in the testExtract method too. >>> >>> On Wed, May 10, 2017 at 8:26 PM, Julian Hyde <[email protected]> wrote: >>> >>>> You can wrap an enum constant as if it were a literal. Using lisp >>>> terminology, I usually refer to such a thing as a "symbol". Try >>>> >>>> builder.call(SqlStdOperatorTable.EXTRACT_DATE, >>>> builder.field(1, 0, "my_date"), >>>> builder.literal(TimeUnitRange.YEAR)); >>>> >>>> Run JdbcTest.testExtract and put a break point in the constructor of >>>> RexCall and you should see what's going on. >>>> >>>> Extracting time values (e.g. HOUR) is a little more complicated >>>> because there is no EXTRACT_TIME function. These operations are turned >>>> into millisecond arithmetic at sql-to-rel time. >>>> >>>> Julian >>>> >>>> >>>> On Wed, May 10, 2017 at 3:09 AM, Chris Baynes <[email protected]> >> wrote: >>>>> We're trying to use the date extract & floor functions via the >>>> relbuilder, >>>>> but can't figure out the right syntax. >>>>> >>>>> 1. Trying YEAR(field) >>>>> ...Relbuilder builder; >>>>> // could use SqlStdOperatorTable.EXTRACT, but then how do we pass >> YEAR as >>>>> an argument? >>>>> builder.call(SqlStdOperatorTable.YEAR, >>>>> builder.field(1, 0, "my_date") >>>>> ); >>>>> >>>>> Cause: java.lang.RuntimeException: cannot translate call YEAR($t2) >>>>> at >>>>> org.apache.calcite.adapter.enumerable.RexToLixTranslator. >> translateCall( >>>> RexToLixTranslator.java:563) >>>>> at >>>>> org.apache.calcite.adapter.enumerable.RexToLixTranslator. >>>> translate0(RexToLixTranslator.java:537) >>>>> at >>>>> org.apache.calcite.adapter.enumerable.RexToLixTranslator. >>>> translate(RexToLixTranslator.java:223) >>>>> >>>>> 2. Using floor >>>>> builder.call(SqlStdOperatorTable.FLOOR, >>>>> // -> need to specify `YEAR` here somehow as a RexNode >>>>> builder.field(1, 0, "my_date") >>>>> ); >>>>> >>>>> Thanks in advance! >>>> >>> >>> >>> >>> -- >>> >>> *Christopher Baynes* >>> CTO >>> >>> *Contiamo – all your data in one place* >>> >>> Winterfeldtstrasse 21 | 10781 Berlin | Germany >>> >>> E-mail: [email protected] >>> >>> Web: www.contiamo.com >>> <http://t.sidekickopen65.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nM >> JW7t5XZs4X9YtjW8q-fZW65jv3RW2zhrDH56dLV8f5DKhvM0 >> 2?t=http%3A%2F%2Fwww.contiamo.com%2F&si=5165279625740288&pi= >> bff9f6a3-d8a4-4bf6-87d5-a5464041547d> >>> >>> Contiamo GmbH, Sitz der Gesellschaft: Berlin >>> HR Berlin-Charlottenburg, HRB Nr. 156569 >>> Geschäftsführer: Dr. Tilmann Doll, Michael Franzkowiak >>
