[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

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.13.0, 1.14.0
• Fix Version/s: 1.16.0
• Component/s: Query Planning & Optimization

Environment

git Commit ID: dd4a46a6c57425284a2b8c68676357f947e01988
git Commit Message: Update version to 1.14.0-SNAPSHOT

• Assignee: Hanumath Rao Maduri
• Reporter: Abhishek Girish
• Created: 16/Mar/18 16:18
• Updated: 02/Mar/19 13:09
• Resolved: 02/Mar/19 13:09

Description

> 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

Slightly different variants of the query work fine.

> 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]]])

> 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]]])

File contents:

# 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"}