Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-4786

Query with IN and BETWEEN with overlapping literal ranges prefers IN ranges if IN goes last in the query

    XMLWordPrintable

    Details

    • Sprint:
      2021-9

      Description

      Consider the example:

      CREATE TABLE `bd_dwhs_private_ex_pub_services_ins` (
        `age` int(11) DEFAULT NULL,
      ) ENGINE=Columnstore DEFAULT CHARSET=latin1;
       
      insert into bd_dwhs_private_ex_pub_services_ins select * from seq_1_to_120;
       
      CREATE OR REPLACE VIEW `dwhs_ins_call_detail_vw` AS select age, case when `bd_dwhs_private_ex_pub_services_ins`.`age` between 16 and 24 then '16-24' when `bd_dwhs_private_ex_pub_services_ins`.`age` between 25 and 34 then '25-34' when `bd_dwhs_private_ex_pub_services_ins`.`age` between 35 and 44 then '35-44' when `bd_dwhs_private_ex_pub_services_ins`.`age` between 45 and 54 then '45-54' when `bd_dwhs_private_ex_pub_services_ins`.`age` between 55 and 64 then '55-64' when `bd_dwhs_private_ex_pub_services_ins`.`age` between 65 and 74 then '65-74' when `bd_dwhs_private_ex_pub_services_ins`.`age` >= 75 then '75+' end AS `age_group` from `bd_dwhs_private_ex_pub_services_ins`;
       
      select age_group,age,count(*) from dwhs_ins_call_detail_vw where age between 16 and 34 and age_group IN ('35-44') group by age_group,age order by age_group,age;
      

      The query returns records with age > 34 but it mustn't.
      CSEP analyze has no filter for BETWEEN predicate if BETWEEN goes in front of IN.

        Attachments

          Activity

            People

            Assignee:
            dleeyh Daniel Lee
            Reporter:
            drrtuy Roman
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration