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 >
