| # testInnerJoinQueryOfLookupRemovable@default case-crc:56875820 |
| # quidem testcase reason: DEFINETLY_WORSE_PLAN |
| !set debug true |
| !set defaultTimeout 300000 |
| !set maxScatterGatherBytes 9223372036854775807 |
| !set plannerStrategy DECOUPLED |
| !set sqlCurrentTimestamp 2000-01-01T00:00:00Z |
| !set sqlQueryId dummy |
| !set outputformat mysql |
| !use druidtest:/// |
| SELECT dim1, dim2, t1.sk |
| FROM foo |
| INNER JOIN |
| (SELECT k, SUBSTRING(v, 1, 3) sk FROM lookup.lookyloo) t1 |
| ON foo.dim2 = t1.k; |
| +------+------+-----+ |
| | dim1 | dim2 | sk | |
| +------+------+-----+ |
| | | a | xa | |
| | 1 | a | xa | |
| | def | abc | xab | |
| +------+------+-----+ |
| (3 rows) |
| |
| !ok |
| LogicalProject(dim1=[$0], dim2=[$1], sk=[$3]) |
| LogicalJoin(condition=[=($1, $2)], joinType=[inner]) |
| LogicalProject(dim1=[$1], dim2=[$2]) |
| LogicalTableScan(table=[[druid, foo]]) |
| LogicalProject(k=[$0], sk=[SUBSTRING($1, 1, 3)]) |
| LogicalTableScan(table=[[lookup, lookyloo]]) |
| |
| !logicalPlan |
| DruidProject(dim1=[$0], dim2=[$1], sk=[$3], druid=[logical]) |
| DruidJoin(condition=[=($1, $2)], joinType=[inner]) |
| DruidProject(dim1=[$1], dim2=[$2], druid=[logical]) |
| DruidTableScan(table=[[druid, foo]], druid=[logical]) |
| DruidProject(k=[$0], sk=[SUBSTRING($1, 1, 3)], druid=[logical]) |
| DruidTableScan(table=[[lookup, lookyloo]], druid=[logical]) |
| |
| !druidPlan |
| { |
| "queryType" : "scan", |
| "dataSource" : { |
| "type" : "join", |
| "left" : { |
| "type" : "table", |
| "name" : "foo" |
| }, |
| "right" : { |
| "type" : "query", |
| "query" : { |
| "queryType" : "scan", |
| "dataSource" : { |
| "type" : "lookup", |
| "lookup" : "lookyloo" |
| }, |
| "intervals" : { |
| "type" : "intervals", |
| "intervals" : [ "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z" ] |
| }, |
| "virtualColumns" : [ { |
| "type" : "expression", |
| "name" : "v0", |
| "expression" : "substring(\"v\", 0, 3)", |
| "outputType" : "STRING" |
| } ], |
| "resultFormat" : "compactedList", |
| "columns" : [ "k", "v0" ], |
| "columnTypes" : [ "STRING", "STRING" ], |
| "granularity" : { |
| "type" : "all" |
| }, |
| "legacy" : false |
| } |
| }, |
| "rightPrefix" : "j0.", |
| "condition" : "(\"dim2\" == \"j0.k\")", |
| "joinType" : "INNER" |
| }, |
| "intervals" : { |
| "type" : "intervals", |
| "intervals" : [ "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z" ] |
| }, |
| "resultFormat" : "compactedList", |
| "columns" : [ "dim1", "dim2", "j0.v0" ], |
| "columnTypes" : [ "STRING", "STRING", "STRING" ], |
| "granularity" : { |
| "type" : "all" |
| }, |
| "legacy" : false |
| } |
| !nativePlan |
