[ https://issues.apache.org/jira/browse/CALCITE-3408?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Wang Yanlin reassigned CALCITE-3408: ------------------------------------ Assignee: Wang Yanlin > 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 > Assignee: Wang Yanlin > Priority: Major > Labels: pull-request-available > Time Spent: 1h 10m > Remaining Estimate: 0h > > 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)