You can find out a lot by searching for uses of SqlStdOperatorTable.SINGLE_VALUE.
But clearly since SINGLE_VALUE is Calcite-specific we should not push it down. We would need an expression that will throw if it returns more than one row. Something like this: CASE COUNT(*) WHEN 0 THEN null WHEN 1 THEN … ELSE THROW(‘query returned more than one row’) END except that isn’t valid SQL. You’re right, this is basically https://issues.apache.org/jira/browse/CALCITE-259 <https://issues.apache.org/jira/browse/CALCITE-259>. Julian > On Apr 7, 2015, at 2:13 AM, Yuri Au Yong <[email protected]> wrote: > > Hi, > > When using JdbcAdapter to execute query “select RNUM, C1, C2 from TJOIN2 > where C1 = ( select C1 from TJOIN1 WHERE RNUM=0)”, the generated sql: > > SELECT "TABLE1"."RNUM", "TABLE1"."C1", "TABLE1"."C2" > FROM (SELECT SINGLE_VALUE("C1") AS "$f0" > FROM (SELECT "C1" > FROM "TABLE2" > WHERE "RNUM" = 0) AS "t0") AS "t1" > INNER JOIN "TABLE1" ON "t1"."$f0" = "TABLE1"."C1" > > The issue is similar to that which caused > https://issues.apache.org/jira/browse/CALCITE-259 > <https://issues.apache.org/jira/browse/CALCITE-259> to reopen. > > Any pointers on where the internal aggregate function SINGLE_VALUE function > gets resolved? > > > Thanks & Regards, > > Yuri Au Yong > Software Engineer - NPM | [email protected] > <mailto:[email protected]> | Desk: +60 3 7663 8372 > Persistent Systems Ltd. | Partners in Innovation | www.persistentsys.com > <http://www.persistentsys.com/> > <https://tnpmsupport.persistentsys.com/training> > > DISCLAIMER ========== This e-mail may contain privileged and confidential > information which is the property of Persistent Systems Ltd. It is intended > only for the use of the individual or entity to which it is addressed. If you > are not the intended recipient, you are not authorized to read, retain, copy, > print, distribute or use this message. If you have received this > communication in error, please notify the sender and delete all copies of > this message. Persistent Systems Ltd. does not accept any liability for virus > infected mails. >
