[jira] [Created] (IGNITE-24707) Sql. Fix type coercion for quantify operators

["Konstantin Orlov (Jira)" <ji...@apache.org>]

Konstantin Orlov created IGNITE-24707:
-----------------------------------------

             Summary: Sql. Fix type coercion for quantify operators
                 Key: IGNITE-24707
                 URL: https://issues.apache.org/jira/browse/IGNITE-24707
             Project: Ignite
          Issue Type: Bug
          Components: sql
            Reporter: Konstantin Orlov
            Assignee: Konstantin Orlov


Currently, type coercion is not applied to quantify operators (ALL, SOME), resulting in a wrong plan.

For example, let's take a look at output for following queries:
{code}
CREATE TABLE test (id INT PRIMARY KEY, int_c INT, byte_c TINYINT);
EXPLAIN PLAN FOR SELECT byte_c = SOME(SELECT int_c FROM test) FROM test;
===============

Project
    fields: [EXPR$0]
    exprs: [OR(AND(IS NULL(BYTE_C), null, <>(c, 0)), AND(IS NOT NULL(i), <>(c, 0), IS NOT NULL(BYTE_C)), AND(<(ck, c), null, <>(c, 0), IS NOT NULL(BYTE_C), IS NULL(i)))]
    est. row count: 3008500
  HashJoin
      condition: =(BYTE_C, INT_C)
      joinType: left
      est. row count: 3008500
    NestedLoopJoin
        condition: true
        joinType: inner
        est. row count: 10000
      Exchange
          distribution: single
          est. row count: 10000
        TableScan
            table: [PUBLIC, TEST]
            fields: [BYTE_C]
            est. row count: 10000
      Project
          fields: [c, ck]
          exprs: [CAST(f0_0):BIGINT NOT NULL, CAST(f0_1):BIGINT NOT NULL]
          est. row count: 1
        ReduceSortAggregate
            rowType: RecordType(BIGINT f0_0, BIGINT f0_1)
            group: []
            aggs: [$SUM0(_ACC0), $SUM0(_ACC1)]
            collation: []
            est. row count: 1
          Exchange
              distribution: single
              est. row count: 1
            MapSortAggregate
                group: []
                aggs: [COUNT(), COUNT(INT_C)]
                collation: []
                est. row count: 1
              TableScan
                  table: [PUBLIC, TEST]
                  fields: [INT_C]
                  est. row count: 10000
    ColocatedHashAggregate
        group: [INT_C]
        aggs: [LITERAL_AGG(true)]
        est. row count: 2000
      Exchange
          distribution: single
          est. row count: 10000
        TableScan
            table: [PUBLIC, TEST]
            fields: [INT_C]
            est. row count: 10000

{code}

The problem is missed cast of {{byte_c}} to {{INTEGER}} before {{HashJoin}} operation resulting in a wrong outcome.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)