[
https://issues.apache.org/jira/browse/CALCITE-5632?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17710118#comment-17710118
]
Kirill Kim commented on CALCITE-5632:
-------------------------------------
I compared query execution in my project with test query in CsvTable example. I
noticed that query plan in Csvtable has difference with my plan after
optimization.
My project plan before optimization is:
{code:java}
LogicalSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC]): rowcount = 10.0,
cumulative cost = {220.0 rows, 761.5170185988093 cpu, 0.0 io}, id = 3562
LogicalAggregate(group=[{0, 1}]): rowcount = 10.0, cumulative cost = {210.0
rows, 301.0 cpu, 0.0 io}, id = 3561
LogicalProject(COL1=[$11], COL2=[$3]): rowcount = 100.0, cumulative cost =
{200.0 rows, 301.0 cpu, 0.0 io}, id = 3560
LogicalTableScan(table=[[INFOSET, SUBEKT]]): rowcount = 100.0, cumulative
cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 3552 {code}
My project plan after optimization is:
{code:java}
EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC]): rowcount =
10.0, cumulative cost = {130.0 rows, 601.5170185988093 cpu, 0.0 io}, id = 3547
EnumerableCalc(expr#0..1=[{inputs}], INN=[$t1], NAIMENOVANIEFIO=[$t0]):
rowcount = 10.0, cumulative cost = {120.0 rows, 141.0 cpu, 0.0 io}, id = 3549
EnumerableAggregate(group=[{3, 11}]): rowcount = 10.0, cumulative cost =
{110.0 rows, 101.0 cpu, 0.0 io}, id = 3543
EnumerableTableScan(table=[[INFOSET, SUBEKT]]): rowcount = 100.0,
cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 3508
{code}
CsvTable plan before optimization is:
{code:java}
LogicalSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC]): rowcount = 10.0,
cumulative cost = {220.0 rows, 761.5170185988093 cpu, 0.0 io}, id = 11
LogicalAggregate(group=[{0, 1}]): rowcount = 10.0, cumulative cost = {210.0
rows, 301.0 cpu, 0.0 io}, id = 10
LogicalProject(COL1=[$3], COL2=[$4]): rowcount = 100.0, cumulative cost =
{200.0 rows, 301.0 cpu, 0.0 io}, id = 9
LogicalTableScan(table=[[BUG, LONG_EMPS]]): rowcount = 100.0, cumulative
cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 1 {code}
CsvTable plan after optimization is:
{code:java}
EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC]): rowcount =
10.0, cumulative cost = {120.0 rows, 561.5170185988093 cpu, 0.0 io}, id = 58
EnumerableAggregate(group=[{3, 4}]): rowcount = 10.0, cumulative cost =
{110.0 rows, 101.0 cpu, 0.0 io}, id = 56
EnumerableTableScan(table=[[BUG, LONG_EMPS]]): rowcount = 100.0, cumulative
cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 37
{code}
> Query with ORDER BY and null values NullPointer exception
> ---------------------------------------------------------
>
> Key: CALCITE-5632
> URL: https://issues.apache.org/jira/browse/CALCITE-5632
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.34.0
> Reporter: Kirill Kim
> Priority: Major
> Attachments: image-2023-04-09-11-49-05-991.png,
> image-2023-04-09-11-50-18-796.png
>
>
> I try to perform query over over some table data in Java using Calcite. I try
> to execute query:
> {code:java}
> select distinct tbl.col1 , tbl.col2 from infoset.SUBEKT tbl order by col1 asc
> nulls last , col2 asc nulls last{code}
> And the following exception occurs
> {code:java}
> java.lang.NullPointerException at
> org.apache.calcite.runtime.Utilities.compare(Utilities.java:202)
> at Baz$2.compare(ANONYMOUS.java:15)
> at Baz$2.compare(ANONYMOUS.java:27)
> at java.util.TreeMap.getEntryUsingComparator(TreeMap.java:376)
> at java.util.TreeMap.getEntry(TreeMap.java:345)
> at java.util.TreeMap.get(TreeMap.java:278)
> at
> org.apache.calcite.linq4j.EnumerableDefaults.toLookup_(EnumerableDefaults.java:3608)
>
> at
> org.apache.calcite.linq4j.EnumerableDefaults$14.enumerator(EnumerableDefaults.java:2660)
>
> at
> org.apache.calcite.linq4j.AbstractEnumerable.iterator(AbstractEnumerable.java:33)
>
> at org.apache.calcite.avatica.MetaImpl.createCursor(MetaImpl.java:90)
> at
> org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:186)
>
> at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:64)
> at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:43)
> at
> org.apache.calcite.avatica.AvaticaConnection$1.execute(AvaticaConnection.java:669)
>
> at
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:636)
>
> at
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:677)
>
> at
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:157)
>
> at
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:228)
>
> at
> ru.spi2.modules.infosetquery.InfosetQueryPlugin.executeQuery(InfosetQueryPlugin.java:101)
> {code}
> The error occurs because col1 has some rows with null values. If I order only
> by col1 or col2, all works well.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
