[jira] [Commented] (CALCITE-2466) RexSimplify incorrectly simplify conjunction statement with null literal
[
https://issues.apache.org/jira/browse/CALCITE-2466?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16582683#comment-16582683
]
Shuyi Chen commented on CALCITE-2466:
-
[~julianhyde], [~vladimirsitnikov], thanks for the info. This is actually valid
SQL. I'll close the JIRA.
> RexSimplify incorrectly simplify conjunction statement with null literal
>
>
> Key: CALCITE-2466
> URL: https://issues.apache.org/jira/browse/CALCITE-2466
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Shuyi Chen
>Assignee: Julian Hyde
>Priority: Major
> Fix For: 1.18.0
>
>
> This can be reproduced using the following test:
> {code:java}
> @Test public void testFilterReduction() throws Exception {
> HepProgramBuilder programBuilder = HepProgram.builder();
> programBuilder.addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE);
> HepPlanner planner = new HepPlanner(programBuilder.build());
> final String sql = "select deptno from dept_nested where NAME <> '' AND
> employees <> null";
> planner.setRoot(tester.convertSqlToRel(sql).rel);
> RelNode bestRel = planner.findBestExp();
> assertEquals(
> "LogicalProject(DEPTNO=[$0])\n" +
> " LogicalFilter(condition=[AND(<>($1, ''), <>($3, null))])\n" +
> "LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])\n",
> RelOptUtil.toString(bestRel));
> }
> {code}
> In this test,
> {code:java}
> NAME <> '' AND employees <> null
> {code} is always evaluated to false incorrectly, so the optimized plan is
> {code:java}
> LogicalProject(DEPTNO=[$0])
> LogicalValues(tuples=[[]])
> {code}.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2466) RexSimplify incorrectly simplify conjunction statement with null literal
[
https://issues.apache.org/jira/browse/CALCITE-2466?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16580915#comment-16580915
]
Vladimir Sitnikov commented on CALCITE-2466:
[~suez1224] , there are {{... is distinct from ...}} and {{... is not distinct
from ...}} which can compare NULL values and treat NULLs as equal.
> RexSimplify incorrectly simplify conjunction statement with null literal
>
>
> Key: CALCITE-2466
> URL: https://issues.apache.org/jira/browse/CALCITE-2466
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Shuyi Chen
>Assignee: Julian Hyde
>Priority: Major
> Fix For: 1.18.0
>
>
> This can be reproduced using the following test:
> {code:java}
> @Test public void testFilterReduction() throws Exception {
> HepProgramBuilder programBuilder = HepProgram.builder();
> programBuilder.addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE);
> HepPlanner planner = new HepPlanner(programBuilder.build());
> final String sql = "select deptno from dept_nested where NAME <> '' AND
> employees <> null";
> planner.setRoot(tester.convertSqlToRel(sql).rel);
> RelNode bestRel = planner.findBestExp();
> assertEquals(
> "LogicalProject(DEPTNO=[$0])\n" +
> " LogicalFilter(condition=[AND(<>($1, ''), <>($3, null))])\n" +
> "LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])\n",
> RelOptUtil.toString(bestRel));
> }
> {code}
> In this test,
> {code:java}
> NAME <> '' AND employees <> null
> {code} is always evaluated to false incorrectly, so the optimized plan is
> {code:java}
> LogicalProject(DEPTNO=[$0])
> LogicalValues(tuples=[[]])
> {code}.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2466) RexSimplify incorrectly simplify conjunction statement with null literal
[
https://issues.apache.org/jira/browse/CALCITE-2466?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16580461#comment-16580461
]
Julian Hyde commented on CALCITE-2466:
--
"NULL <> NULL" is valid SQL. It just returns UNKNOWN.
> RexSimplify incorrectly simplify conjunction statement with null literal
>
>
> Key: CALCITE-2466
> URL: https://issues.apache.org/jira/browse/CALCITE-2466
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Shuyi Chen
>Assignee: Julian Hyde
>Priority: Major
> Fix For: 1.18.0
>
>
> This can be reproduced using the following test:
> {code:java}
> @Test public void testFilterReduction() throws Exception {
> HepProgramBuilder programBuilder = HepProgram.builder();
> programBuilder.addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE);
> HepPlanner planner = new HepPlanner(programBuilder.build());
> final String sql = "select deptno from dept_nested where NAME <> '' AND
> employees <> null";
> planner.setRoot(tester.convertSqlToRel(sql).rel);
> RelNode bestRel = planner.findBestExp();
> assertEquals(
> "LogicalProject(DEPTNO=[$0])\n" +
> " LogicalFilter(condition=[AND(<>($1, ''), <>($3, null))])\n" +
> "LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])\n",
> RelOptUtil.toString(bestRel));
> }
> {code}
> In this test,
> {code:java}
> NAME <> '' AND employees <> null
> {code} is always evaluated to false incorrectly, so the optimized plan is
> {code:java}
> LogicalProject(DEPTNO=[$0])
> LogicalValues(tuples=[[]])
> {code}.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2466) RexSimplify incorrectly simplify conjunction statement with null literal
[
https://issues.apache.org/jira/browse/CALCITE-2466?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16580456#comment-16580456
]
Shuyi Chen commented on CALCITE-2466:
-
i agree, I am thinking of adding or extending some logic in
SqlBinaryOperator.validateCall() to reject <>, = and other range binary
operators with null value. What do you think, [~julianhyde]?
> RexSimplify incorrectly simplify conjunction statement with null literal
>
>
> Key: CALCITE-2466
> URL: https://issues.apache.org/jira/browse/CALCITE-2466
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Shuyi Chen
>Assignee: Julian Hyde
>Priority: Major
> Fix For: 1.18.0
>
>
> This can be reproduced using the following test:
> {code:java}
> @Test public void testFilterReduction() throws Exception {
> HepProgramBuilder programBuilder = HepProgram.builder();
> programBuilder.addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE);
> HepPlanner planner = new HepPlanner(programBuilder.build());
> final String sql = "select deptno from dept_nested where NAME <> '' AND
> employees <> null";
> planner.setRoot(tester.convertSqlToRel(sql).rel);
> RelNode bestRel = planner.findBestExp();
> assertEquals(
> "LogicalProject(DEPTNO=[$0])\n" +
> " LogicalFilter(condition=[AND(<>($1, ''), <>($3, null))])\n" +
> "LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])\n",
> RelOptUtil.toString(bestRel));
> }
> {code}
> In this test,
> {code:java}
> NAME <> '' AND employees <> null
> {code} is always evaluated to false incorrectly, so the optimized plan is
> {code:java}
> LogicalProject(DEPTNO=[$0])
> LogicalValues(tuples=[[]])
> {code}.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2466) RexSimplify incorrectly simplify conjunction statement with null literal
[
https://issues.apache.org/jira/browse/CALCITE-2466?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16580261#comment-16580261
]
Julian Hyde commented on CALCITE-2466:
--
I think supporting standard SQL semantics is much the best option.
> RexSimplify incorrectly simplify conjunction statement with null literal
>
>
> Key: CALCITE-2466
> URL: https://issues.apache.org/jira/browse/CALCITE-2466
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Shuyi Chen
>Assignee: Julian Hyde
>Priority: Major
> Fix For: 1.18.0
>
>
> This can be reproduced using the following test:
> {code:java}
> @Test public void testFilterReduction() throws Exception {
> HepProgramBuilder programBuilder = HepProgram.builder();
> programBuilder.addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE);
> HepPlanner planner = new HepPlanner(programBuilder.build());
> final String sql = "select deptno from dept_nested where NAME <> '' AND
> employees <> null";
> planner.setRoot(tester.convertSqlToRel(sql).rel);
> RelNode bestRel = planner.findBestExp();
> assertEquals(
> "LogicalProject(DEPTNO=[$0])\n" +
> " LogicalFilter(condition=[AND(<>($1, ''), <>($3, null))])\n" +
> "LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])\n",
> RelOptUtil.toString(bestRel));
> }
> {code}
> In this test,
> {code:java}
> NAME <> '' AND employees <> null
> {code} is always evaluated to false incorrectly, so the optimized plan is
> {code:java}
> LogicalProject(DEPTNO=[$0])
> LogicalValues(tuples=[[]])
> {code}.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2466) RexSimplify incorrectly simplify conjunction statement with null literal
[
https://issues.apache.org/jira/browse/CALCITE-2466?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16580246#comment-16580246
]
Shuyi Chen commented on CALCITE-2466:
-
so does it mean we only support is NULL or IS NOT NULL for null comparison? I
thought *=* and *<>* has the same meaning of *IS NULL* or *IS NOT NULL*.I think
we can either allow *=* and *<>* with null, or in validation phase, throw an
exception for this kind of statement. What do you think, [~julianhyde]?
> RexSimplify incorrectly simplify conjunction statement with null literal
>
>
> Key: CALCITE-2466
> URL: https://issues.apache.org/jira/browse/CALCITE-2466
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Shuyi Chen
>Assignee: Julian Hyde
>Priority: Major
> Fix For: 1.18.0
>
>
> This can be reproduced using the following test:
> {code:java}
> @Test public void testFilterReduction() throws Exception {
> HepProgramBuilder programBuilder = HepProgram.builder();
> programBuilder.addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE);
> HepPlanner planner = new HepPlanner(programBuilder.build());
> final String sql = "select deptno from dept_nested where NAME <> '' AND
> employees <> null";
> planner.setRoot(tester.convertSqlToRel(sql).rel);
> RelNode bestRel = planner.findBestExp();
> assertEquals(
> "LogicalProject(DEPTNO=[$0])\n" +
> " LogicalFilter(condition=[AND(<>($1, ''), <>($3, null))])\n" +
> "LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])\n",
> RelOptUtil.toString(bestRel));
> }
> {code}
> In this test,
> {code:java}
> NAME <> '' AND employees <> null
> {code} is always evaluated to false incorrectly, so the optimized plan is
> {code:java}
> LogicalProject(DEPTNO=[$0])
> LogicalValues(tuples=[[]])
> {code}.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2466) RexSimplify incorrectly simplify conjunction statement with null literal
[
https://issues.apache.org/jira/browse/CALCITE-2466?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16580233#comment-16580233
]
Julian Hyde commented on CALCITE-2466:
--
No. "x <> NULL" always returns UNKNOWN. And UNKNOWN in the WHERE clause has the
same effect as FALSE.
For example, try "select * from emp where comm <> null" on
[oracle|http://rextester.com/l/oracle_online_compiler].
> RexSimplify incorrectly simplify conjunction statement with null literal
>
>
> Key: CALCITE-2466
> URL: https://issues.apache.org/jira/browse/CALCITE-2466
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Shuyi Chen
>Assignee: Julian Hyde
>Priority: Major
> Fix For: 1.18.0
>
>
> This can be reproduced using the following test:
> {code:java}
> @Test public void testFilterReduction() throws Exception {
> HepProgramBuilder programBuilder = HepProgram.builder();
> programBuilder.addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE);
> HepPlanner planner = new HepPlanner(programBuilder.build());
> final String sql = "select deptno from dept_nested where NAME <> '' AND
> employees <> null";
> planner.setRoot(tester.convertSqlToRel(sql).rel);
> RelNode bestRel = planner.findBestExp();
> assertEquals(
> "LogicalProject(DEPTNO=[$0])\n" +
> " LogicalFilter(condition=[AND(<>($1, ''), <>($3, null))])\n" +
> "LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])\n",
> RelOptUtil.toString(bestRel));
> }
> {code}
> In this test,
> {code:java}
> NAME <> '' AND employees <> null
> {code} is always evaluated to false incorrectly, so the optimized plan is
> {code:java}
> LogicalProject(DEPTNO=[$0])
> LogicalValues(tuples=[[]])
> {code}.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2466) RexSimplify incorrectly simplify conjunction statement with null literal
[
https://issues.apache.org/jira/browse/CALCITE-2466?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16580184#comment-16580184
]
Shuyi Chen commented on CALCITE-2466:
-
Also, I've created a PR [here|https://github.com/apache/calcite/pull/792], you
can take a quick look on the test cases I added and see if it makes sense.
> RexSimplify incorrectly simplify conjunction statement with null literal
>
>
> Key: CALCITE-2466
> URL: https://issues.apache.org/jira/browse/CALCITE-2466
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Shuyi Chen
>Assignee: Julian Hyde
>Priority: Major
> Fix For: 1.18.0
>
>
> This can be reproduced using the following test:
> {code:java}
> @Test public void testFilterReduction() throws Exception {
> HepProgramBuilder programBuilder = HepProgram.builder();
> programBuilder.addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE);
> HepPlanner planner = new HepPlanner(programBuilder.build());
> final String sql = "select deptno from dept_nested where NAME <> '' AND
> employees <> null";
> planner.setRoot(tester.convertSqlToRel(sql).rel);
> RelNode bestRel = planner.findBestExp();
> assertEquals(
> "LogicalProject(DEPTNO=[$0])\n" +
> " LogicalFilter(condition=[AND(<>($1, ''), <>($3, null))])\n" +
> "LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])\n",
> RelOptUtil.toString(bestRel));
> }
> {code}
> In this test,
> {code:java}
> NAME <> '' AND employees <> null
> {code} is always evaluated to false incorrectly, so the optimized plan is
> {code:java}
> LogicalProject(DEPTNO=[$0])
> LogicalValues(tuples=[[]])
> {code}.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2466) RexSimplify incorrectly simplify conjunction statement with null literal
[
https://issues.apache.org/jira/browse/CALCITE-2466?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16580180#comment-16580180
]
Shuyi Chen commented on CALCITE-2466:
-
Thanks a lot, [~julianhyde], in
{code:java}
select deptno from dept_nested where NAME <> '' AND employees <> null
{code},
if NAME is empty string and employees is a valid array (non null), then it
should not return empty row, right?
> RexSimplify incorrectly simplify conjunction statement with null literal
>
>
> Key: CALCITE-2466
> URL: https://issues.apache.org/jira/browse/CALCITE-2466
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Shuyi Chen
>Assignee: Julian Hyde
>Priority: Major
> Fix For: 1.18.0
>
>
> This can be reproduced using the following test:
> {code:java}
> @Test public void testFilterReduction() throws Exception {
> HepProgramBuilder programBuilder = HepProgram.builder();
> programBuilder.addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE);
> HepPlanner planner = new HepPlanner(programBuilder.build());
> final String sql = "select deptno from dept_nested where NAME <> '' AND
> employees <> null";
> planner.setRoot(tester.convertSqlToRel(sql).rel);
> RelNode bestRel = planner.findBestExp();
> assertEquals(
> "LogicalProject(DEPTNO=[$0])\n" +
> " LogicalFilter(condition=[AND(<>($1, ''), <>($3, null))])\n" +
> "LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])\n",
> RelOptUtil.toString(bestRel));
> }
> {code}
> In this test,
> {code:java}
> NAME <> '' AND employees <> null
> {code} is always evaluated to false incorrectly, so the optimized plan is
> {code:java}
> LogicalProject(DEPTNO=[$0])
> LogicalValues(tuples=[[]])
> {code}.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2466) RexSimplify incorrectly simplify conjunction statement with null literal
[
https://issues.apache.org/jira/browse/CALCITE-2466?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16580119#comment-16580119
]
Julian Hyde commented on CALCITE-2466:
--
It seems correct that that query returns 0 rows. What do you think it should
return?
> RexSimplify incorrectly simplify conjunction statement with null literal
>
>
> Key: CALCITE-2466
> URL: https://issues.apache.org/jira/browse/CALCITE-2466
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Shuyi Chen
>Assignee: Julian Hyde
>Priority: Major
> Fix For: 1.18.0
>
>
> This can be reproduced using the following test:
> {code:java}
> @Test public void testFilterReduction() throws Exception {
> HepProgramBuilder programBuilder = HepProgram.builder();
> programBuilder.addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE);
> HepPlanner planner = new HepPlanner(programBuilder.build());
> final String sql = "select deptno from dept_nested where NAME <> '' AND
> employees <> null";
> planner.setRoot(tester.convertSqlToRel(sql).rel);
> RelNode bestRel = planner.findBestExp();
> assertEquals(
> "LogicalProject(DEPTNO=[$0])\n" +
> " LogicalFilter(condition=[AND(<>($1, ''), <>($3, null))])\n" +
> "LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])\n",
> RelOptUtil.toString(bestRel));
> }
> {code}
> In this test,
> {code:java}
> NAME <> '' AND employees <> null
> {code} is always evaluated to false incorrectly, so the optimized plan is
> {code:java}
> LogicalProject(DEPTNO=[$0])
> LogicalValues(tuples=[[]])
> {code}.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
