Re: SINGLE_VALUE internal aggregate function in generated sql

[Julian Hyde <ju...@hydromatic.net>]

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 <yu...@persistent.com> 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 | yuri.auyong@persistent.my <ma...@persistent.my> | 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.
>