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/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJW7t5XZs4X9YtjW8q-fZW65jv3RW2zhrDH56dLV8f5DKhvM02?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
