Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-6481

Optimize 'VALUES...UNION ALL...VALUES' to a single 'VALUES' the IN-list contains CAST and it is converted to VALUES

    XMLWordPrintableJSON

Details

    • Improvement
    • Status: Closed
    • Major
    • Resolution: Fixed
    • None
    • 1.38.0
    • None

    Description

      The SQL:

      with
      t1(a,y) as (select * from (values (1, 2), (3, null),(7369,null),(7499,30),(null, 20),(null, 5)) as t1)
      select *
      from t1
      where (t1.a,t1.y) in ((1, 2), (3, null),(7369,null),(7499,30),(null, 20),(null, 5));
      
      EnumerableHashJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[semi])
        EnumerableValues(tuples=[[{ 1, 2 }, { 3, null }, { 7369, null }, { 7499, 30 }, { null, 20 }, { null, 5 }]])
        EnumerableUnion(all=[true])
          EnumerableValues(tuples=[[{ 3, null }]])
          EnumerableValues(tuples=[[{ 7369, null }]])
          EnumerableValues(tuples=[[{ null, 20 }]])
          EnumerableValues(tuples=[[{ null, 5 }]])
          EnumerableValues(tuples=[[{ 1, 2 }, { 7499, 30 }]])
      !plan
      
      with
      t1(a,y) as (select * from (values (1, 2), (3, 5),(7369,6),(7499,30),(2, 20),(3, 5)) as t1)
      select *
      from t1
      where (t1.a,t1.y) in ((1, 2), (3, 3),(7369,3),(7499,30),(1, 20),(3, 5));
      
      EnumerableHashJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[semi])
        EnumerableValues(tuples=[[{ 1, 2 }, { 3, 5 }, { 7369, 6 }, { 7499, 30 }, { 2, 20 }, { 3, 5 }]])
        EnumerableValues(tuples=[[{ 1, 2 }, { 3, 3 }, { 7369, 3 }, { 7499, 30 }, { 1, 20 }, { 3, 5 }]])
      !plan 

      If the IN-list includes NULL, Calcite will convert VALUES to UNION ALL.

      Attachments

        Issue Links

          Activity

            People

              nobigo xiong duan
              nobigo xiong duan
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: