[ https://issues.apache.org/jira/browse/DRILL-6260?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Hanumath Rao Maduri reassigned DRILL-6260: ------------------------------------------ Assignee: Hanumath Rao Maduri > Query fails with "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, 1.14.0 > Environment: git Commit ID: dd4a46a6c57425284a2b8c68676357f947e01988 > git Commit Message: Update version to 1.14.0-SNAPSHOT > Reporter: Abhishek Girish > Assignee: Hanumath Rao Maduri > Priority: Major > > {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); > 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); > 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)