[
https://issues.apache.org/jira/browse/CALCITE-3408?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Wang Yanlin updated CALCITE-3408:
---------------------------------
Description:
When running sql with calcite driver
{code:java}
final String sql = "select * from\n"
+ "(select x, y from (values (1, 'a'), (2, 'b'), (2, 'b'), (3, 'c')) as
t(x, y))\n"
+ "except all\n"
+ "(select x, y from (values (1, 'a'), (2, 'c'), (4, 'x')) as t2(x,
y))\n";
{code}
got
{code:java}
java.sql.SQLException: Error while executing SQL "explain plan for select * from
(select x, y from (values (1, 'a'), (2, 'b'), (2, 'b'), (3, 'c')) as t(x, y))
except all
(select x, y from (values (1, 'a'), (2, 'c'), (4, 'x')) as t2(x, y))
": There are not enough rules to produce a node with desired properties:
convention=ENUMERABLE, sort=[].
Missing conversion is LogicalMinus[convention: NONE -> ENUMERABLE]
There is 1 empty subset: rel#27:Subset#4.ENUMERABLE.[], the relevant part of
the original plan is as follows
22:LogicalMinus(all=[true])
1:LogicalValues(subset=[rel#16:Subset#0.NONE.[]], tuples=[[{ 1, 'a' }, { 2,
'b' }, { 2, 'b' }, { 3, 'c' }]])
5:LogicalValues(subset=[rel#19:Subset#2.NONE.[]], tuples=[[{ 1, 'a' }, { 2,
'c' }, { 4, 'x' }]])
Root: rel#27:Subset#4.ENUMERABLE.[]
Original rel:
LogicalMinus(all=[true]): rowcount = 3.0, cumulative cost = {17.0 rows, 19.0
cpu, 0.0 io}, id = 14
LogicalProject(X=[$0], Y=[$1]): rowcount = 4.0, cumulative cost = {8.0 rows,
9.0 cpu, 0.0 io}, id = 9
LogicalValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 2, 'b' }, { 3, 'c' }]]):
rowcount = 4.0, cumulative cost = {4.0 rows, 1.0 cpu, 0.0 io}, id = 1
LogicalProject(X=[$0], Y=[$1]): rowcount = 3.0, cumulative cost = {6.0 rows,
7.0 cpu, 0.0 io}, id = 12
LogicalValues(tuples=[[{ 1, 'a' }, { 2, 'c' }, { 4, 'x' }]]): rowcount =
3.0, cumulative cost = {3.0 rows, 1.0 cpu, 0.0 io}, id = 5
{code}
Add these cases in JdbcTest to reproduce
{code:java}
@Test public void testExceptAll() {
final String sql = "select * from\n"
+ "(select x, y from (values (1, 'a'), (2, 'b'), (2, 'b'), (3, 'c')) as
t(x, y))\n"
+ "except all\n"
+ "(select x, y from (values (1, 'a'), (2, 'c'), (4, 'x')) as t2(x,
y))\n";
CalciteAssert.hr()
.query(sql)
.explainContains(""
+ "PLAN=EnumerableMinus(all=[true])")
.returnsUnordered(
"X=2, Y=b",
"X=2, Y=b",
"X=3, Y=c");
}
@Test public void testIntersectAll() {
final String sql = "select * from\n"
+ "(select x, y from (values (1, 'a'), (1, 'a'), (2, 'b'), (3, 'c')) as
t(x, y))\n"
+ "intersect all\n"
+ "(select x, y from (values (1, 'a'), (2, 'c'), (4, 'x')) as t2(x,
y))\n";
CalciteAssert.hr()
.query(sql)
.withHook(Hook.PLANNER, (Consumer<RelOptPlanner>) planner ->
planner.removeRule(IntersectToDistinctRule.INSTANCE))
.explainContains(""
+ "PLAN=EnumerableIntersect(all=[true])")
.returnsUnordered(
"X=1; Y=a",
"X=1; Y=a");
}
{code}
was:
When running sql with calcite driver
{code:java}
final String sql = "select * from\n"
+ "(select x, y from (values (1, 'a'), (2, 'b'), (2, 'b'), (3, 'c')) as
t(x, y))\n"
+ "except all\n"
+ "(select x, y from (values (1, 'a'), (2, 'c'), (4, 'x')) as t2(x,
y))\n";
{code}
got
{code:java}
java.sql.SQLException: Error while executing SQL "explain plan for select * from
(select x, y from (values (1, 'a'), (2, 'b'), (2, 'b'), (3, 'c')) as t(x, y))
except all
(select x, y from (values (1, 'a'), (2, 'c'), (4, 'x')) as t2(x, y))
": There are not enough rules to produce a node with desired properties:
convention=ENUMERABLE, sort=[].
Missing conversion is LogicalMinus[convention: NONE -> ENUMERABLE]
There is 1 empty subset: rel#27:Subset#4.ENUMERABLE.[], the relevant part of
the original plan is as follows
22:LogicalMinus(all=[true])
1:LogicalValues(subset=[rel#16:Subset#0.NONE.[]], tuples=[[{ 1, 'a' }, { 2,
'b' }, { 2, 'b' }, { 3, 'c' }]])
5:LogicalValues(subset=[rel#19:Subset#2.NONE.[]], tuples=[[{ 1, 'a' }, { 2,
'c' }, { 4, 'x' }]])
Root: rel#27:Subset#4.ENUMERABLE.[]
Original rel:
LogicalMinus(all=[true]): rowcount = 3.0, cumulative cost = {17.0 rows, 19.0
cpu, 0.0 io}, id = 14
LogicalProject(X=[$0], Y=[$1]): rowcount = 4.0, cumulative cost = {8.0 rows,
9.0 cpu, 0.0 io}, id = 9
LogicalValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 2, 'b' }, { 3, 'c' }]]):
rowcount = 4.0, cumulative cost = {4.0 rows, 1.0 cpu, 0.0 io}, id = 1
LogicalProject(X=[$0], Y=[$1]): rowcount = 3.0, cumulative cost = {6.0 rows,
7.0 cpu, 0.0 io}, id = 12
LogicalValues(tuples=[[{ 1, 'a' }, { 2, 'c' }, { 4, 'x' }]]): rowcount =
3.0, cumulative cost = {3.0 rows, 1.0 cpu, 0.0 io}, id = 5
{code}
> Add support for Enumerable Intersect/Minus all
> ----------------------------------------------
>
> Key: CALCITE-3408
> URL: https://issues.apache.org/jira/browse/CALCITE-3408
> Project: Calcite
> Issue Type: Improvement
> Reporter: Wang Yanlin
> Priority: Major
>
> When running sql with calcite driver
> {code:java}
> final String sql = "select * from\n"
> + "(select x, y from (values (1, 'a'), (2, 'b'), (2, 'b'), (3, 'c'))
> as t(x, y))\n"
> + "except all\n"
> + "(select x, y from (values (1, 'a'), (2, 'c'), (4, 'x')) as t2(x,
> y))\n";
> {code}
> got
> {code:java}
> java.sql.SQLException: Error while executing SQL "explain plan for select *
> from
> (select x, y from (values (1, 'a'), (2, 'b'), (2, 'b'), (3, 'c')) as t(x, y))
> except all
> (select x, y from (values (1, 'a'), (2, 'c'), (4, 'x')) as t2(x, y))
> ": There are not enough rules to produce a node with desired properties:
> convention=ENUMERABLE, sort=[].
> Missing conversion is LogicalMinus[convention: NONE -> ENUMERABLE]
> There is 1 empty subset: rel#27:Subset#4.ENUMERABLE.[], the relevant part of
> the original plan is as follows
> 22:LogicalMinus(all=[true])
> 1:LogicalValues(subset=[rel#16:Subset#0.NONE.[]], tuples=[[{ 1, 'a' }, { 2,
> 'b' }, { 2, 'b' }, { 3, 'c' }]])
> 5:LogicalValues(subset=[rel#19:Subset#2.NONE.[]], tuples=[[{ 1, 'a' }, { 2,
> 'c' }, { 4, 'x' }]])
> Root: rel#27:Subset#4.ENUMERABLE.[]
> Original rel:
> LogicalMinus(all=[true]): rowcount = 3.0, cumulative cost = {17.0 rows, 19.0
> cpu, 0.0 io}, id = 14
> LogicalProject(X=[$0], Y=[$1]): rowcount = 4.0, cumulative cost = {8.0
> rows, 9.0 cpu, 0.0 io}, id = 9
> LogicalValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 2, 'b' }, { 3, 'c' }]]):
> rowcount = 4.0, cumulative cost = {4.0 rows, 1.0 cpu, 0.0 io}, id = 1
> LogicalProject(X=[$0], Y=[$1]): rowcount = 3.0, cumulative cost = {6.0
> rows, 7.0 cpu, 0.0 io}, id = 12
> LogicalValues(tuples=[[{ 1, 'a' }, { 2, 'c' }, { 4, 'x' }]]): rowcount =
> 3.0, cumulative cost = {3.0 rows, 1.0 cpu, 0.0 io}, id = 5
> {code}
> Add these cases in JdbcTest to reproduce
> {code:java}
> @Test public void testExceptAll() {
> final String sql = "select * from\n"
> + "(select x, y from (values (1, 'a'), (2, 'b'), (2, 'b'), (3, 'c'))
> as t(x, y))\n"
> + "except all\n"
> + "(select x, y from (values (1, 'a'), (2, 'c'), (4, 'x')) as t2(x,
> y))\n";
> CalciteAssert.hr()
> .query(sql)
> .explainContains(""
> + "PLAN=EnumerableMinus(all=[true])")
> .returnsUnordered(
> "X=2, Y=b",
> "X=2, Y=b",
> "X=3, Y=c");
> }
> @Test public void testIntersectAll() {
> final String sql = "select * from\n"
> + "(select x, y from (values (1, 'a'), (1, 'a'), (2, 'b'), (3, 'c'))
> as t(x, y))\n"
> + "intersect all\n"
> + "(select x, y from (values (1, 'a'), (2, 'c'), (4, 'x')) as t2(x,
> y))\n";
> CalciteAssert.hr()
> .query(sql)
> .withHook(Hook.PLANNER, (Consumer<RelOptPlanner>) planner ->
> planner.removeRule(IntersectToDistinctRule.INSTANCE))
> .explainContains(""
> + "PLAN=EnumerableIntersect(all=[true])")
> .returnsUnordered(
> "X=1; Y=a",
> "X=1; Y=a");
> }
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
