drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Abhishek Girish (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DRILL-6260) Query fails with "ERROR: Non-scalar sub-query used in an expression" when it contains a cast expression around a scalar sub-query
Date Fri, 16 Mar 2018 16:24:00 GMT

     [ https://issues.apache.org/jira/browse/DRILL-6260?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Abhishek Girish updated DRILL-6260:
-----------------------------------
    Environment: 
git Commit ID: dd4a46a6c57425284a2b8c68676357f947e01988 
git Commit Message: Update version to 1.14.0-SNAPSHOT

  was:
git Commit ID: dd4a46a6c57425284a2b8c68676357f947e01988 
git Commit message: Update version to 1.14.0-SNAPSHOT


> 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 &amp; 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
>            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);
> +------+------+
> | 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)

Mime
View raw message