Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 Search Hadoop and all its subprojects:

Switch to Threaded View
Drill >> mail # dev >> SQL question


Copy link to this message
-
Re: SQL question
Basically, yes.

The SQL standard does not use 'SELECT <expression>' for table-less queries (I know that MySQL and Postgres support this syntax); accordingly, Optiq uses 'VALUES <expression>', or you can write 'SELECT <expression> FROM (VALUES (1))' if you prefer. So, if you add 'FROM (VALUES (1))' to your query, it should be valid, and yes, Drill will support it.

A few extra comments:

1. The SQL standard says that you can use single-row queries provided that they return a single column. If it returns 0 rows, the answer is null; if it returns more than 1 row, there is a runtime error. That error is tricky to implement; previously I have used a special aggregate function -- introduced via an internal rewrite -- that threw if it had more than 1 row. The Drill implementation would need to do something similar.

2. You can use scalar subqueries in queries that have one or more tables in the FROM clause. In that case, you will usually want to use correlating variables. Correlations are hard work, especially if don't want to implement by restarting the sub-query each time the value changes. Optiq can handle correlations via a rewrite.

3. If you want subqueries in the SELECT clause to return multiple records, wrap them in MULTISET ( ... ) rather than ( ... ). These queries can return multiple columns.

Julian

On Mar 27, 2013, at 10:05 AM, Ted Dunning <[EMAIL PROTECTED]> wrote:

> Is this legal SQL?
>
> select (153 div 31) as v1
>
> And if so, is it legal to substitute sub-queries that return a single row
> and column result as in this next query?  (final question, will Drill
> support this)
>
> select   (
>  (
>    (
>      SELECT COUNT(*) AS maxvalue
>      FROM bidemo.Sales s
>      inner join
>      bidemo.customer c on c.CustomerSId = s.CustomerSId
>      inner join
>      bidemo.orders  o on o.OrderSId = s.OrderSId
>      WHERE c.CustomerId =1001
>      GROUP BY c.CustomerId order by maxvalue  desc limit 1
>    )*100) div
>    (
>      SELECT  COUNT(*) AS maxvalue
>      FROM
>          bidemo.Sales s
>          inner join
>          bidemo.customer c on c.CustomerSId = s.CustomerSId
>          inner join
>          bidemo.orders  o on o.OrderSId = s.OrderSId
>      GROUP BY c.CustomerId order by maxvalue  desc limit 1 )
>     ) as v1
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB