[
https://issues.apache.org/jira/browse/IGNITE-16443?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17487066#comment-17487066
]
Aleksey Plekhanov commented on IGNITE-16443:
--------------------------------------------
[~ivandasch], [~tledkov-gridgain], thanks for the review!
Merged to sql-calcite branch.
> Calcite engine. Incorrect nulls in search row processing by hash/sorted spools
> ------------------------------------------------------------------------------
>
> Key: IGNITE-16443
> URL: https://issues.apache.org/jira/browse/IGNITE-16443
> Project: Ignite
> Issue Type: Bug
> Reporter: Aleksey Plekhanov
> Assignee: Aleksey Plekhanov
> Priority: Major
> Labels: calcite2-required, calcite3-required
> Time Spent: 0.5h
> Remaining Estimate: 0h
>
> Currently, the sorted spool comparator treats nulls in the search row as
> regular values and applies nulls ordering to them, but these nulls have
> another meaning: it means that any value matches the bound.
> For example, if we have the condition for spool {{a > 0}} it will be
> transformed to bounds: {{lower [0]}}, {{upper [null]}}, after comparing rows
> with lower bound we will get the correct result, but comparing with upper
> bound always give us {{1}} (value > null) with {{nulls first}} collation and
> no rows will be returned by index spool.
> For hash spool there is the reverse problem, we should not find rows if nulls
> are present in the search row, since condition NULL=NULL should not satisfy
> the filter in SQL.
> For index scan, there are no problems that lead to data inconsistency, but in
> the case of nulls in the search row, any value matches the bound and index
> scan becomes very ineffective. For example, if we have the index by field
> {{a}} and filter {{a = $cor0.b}}, for {{$cor0.b = null}} there will be the
> full index scan, all rows will be passed to the predicate and 0 rows will be
> produced after the predicate.
> Another problem with comparators: there is a dead code in
> {{ExpressionFactoryImpl#comparator(RelFieldCollation)}} which compare object
> by hash or bytes:
> * This code never executes since we support now only comparable types by
> Calcite-based SQL engine
> * Depends on H2
> * Serialize object by standard java serialization
> * Works only for ASC ordering
> * Works for sort/spool/sort aggregate nodes, but doesn't work for merge join
> I think we can get rid of this code at least until we don't support Object
> types and revert corresponding changes to ignite-indexing to avoid merge
> conflicts.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
