You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@drill.apache.org by "Abhishek Girish (JIRA)" <ji...@apache.org> on 2018/03/16 16:19:00 UTC
[jira] [Created] (DRILL-6260) Query fails with
"UNSUPPORTED_OPERATION ERROR: Non-scalar sub-query used in an expression"
when it contains a cast expression around a scalar sub-query
Abhishek Girish created DRILL-6260:
--------------------------------------
Summary: Query fails with "UNSUPPORTED_OPERATION ERROR: Non-scalar sub-query used in an expression" when it contains a cast expression around a scalar sub-query
Key: DRILL-6260
URL: https://issues.apache.org/jira/browse/DRILL-6260
Project: Apache Drill
Issue Type: Bug
Components: Query Planning & Optimization
Affects Versions: 1.13.0
Reporter: Abhishek Girish
{code}
> explain plan for SELECT T1.b FROM `t1.json` T1 WHERE T1.a = (SELECT cast(max(T2.a) as varchar) FROM `t2.json` T2);
Error: UNSUPPORTED_OPERATION ERROR: Non-scalar sub-query used in an expression
See Apache Drill JIRA: DRILL-1937
{code}
Slightly different variants of the query work fine.
{code}
> explain plan for SELECT T1.b FROM `t1.json` T1 WHERE T1.a = (SELECT max(cast(T2.a as varchar)) FROM `t2.json` T2);
+------+------+
| text | json |
+------+------+
| 00-00 Screen
00-01 Project(b=[$0])
00-02 Project(b=[$1])
00-03 SelectionVectorRemover
00-04 Filter(condition=[=($0, $2)])
00-05 NestedLoopJoin(condition=[true], joinType=[left])
00-07 Scan(table=[[si, tmp, t1.json]], groupscan=[EasyGroupScan [selectionRoot=maprfs:/tmp/t1.json, numFiles=1, columns=[`a`, `b`], files=[maprfs:///tmp/t1.json]]])
00-06 StreamAgg(group=[{}], EXPR$0=[MAX($0)])
00-08 Project($f0=[CAST($0):VARCHAR(65535) CHARACTER SET "UTF-16LE" COLLATE "UTF-16LE$en_US$primary"])
00-09 Scan(table=[[si, tmp, t2.json]], groupscan=[EasyGroupScan [selectionRoot=maprfs:/tmp/t2.json, numFiles=1, columns=[`a`], files=[maprfs:///tmp/t2.json]]]){code}
{code}
> explain plan for SELECT T1.b FROM `t1.json` T1 WHERE T1.a = (SELECT max(T2.a) FROM `t2.json` T2);
+------+------+
| text | json |
+------+------+
| 00-00 Screen
00-01 Project(b=[$0])
00-02 Project(b=[$1])
00-03 SelectionVectorRemover
00-04 Filter(condition=[=($0, $2)])
00-05 NestedLoopJoin(condition=[true], joinType=[left])
00-07 Scan(table=[[si, tmp, t1.json]], groupscan=[EasyGroupScan [selectionRoot=maprfs:/tmp/t1.json, numFiles=1, columns=[`a`, `b`], files=[maprfs:///tmp/t1.json]]])
00-06 StreamAgg(group=[{}], EXPR$0=[MAX($0)])
00-08 Scan(table=[[si, tmp, t2.json]], groupscan=[EasyGroupScan [selectionRoot=maprfs:/tmp/t2.json, numFiles=1, columns=[`a`], files=[maprfs:///tmp/t2.json]]])
{code}
File contents:
{code}
# cat t1.json
{"a":1, "b":"V"}
{"a":2, "b":"W"}
{"a":3, "b":"X"}
{"a":4, "b":"Y"}
{"a":5, "b":"Z"}
# # cat t2.json
{"a":1, "b":"A"}
{"a":2, "b":"B"}
{"a":3, "b":"C"}
{"a":4, "b":"D"}
{"a":5, "b":"E"}
{code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)