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

Different results compared to innodb with union all and subselect with null in select and impossible where condition

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 23.10.1
    • Icebox
    • None
    • None

    Description

      Columnstore throws rows with union all and subselect with null in select and impossible where condition

      To reproduce:

      drop table if exists t1col;
      drop table if exists t1inno;
       
      create table t1col (id int) engine=columnstore;
      create table t1inno (id int) engine=innodb;
       
      select * from
      (
      Select ID
      FROM
      (
      SELECT 1 ID
      FROM 
      t1col
      ) V
      UNION ALL
      SELECT ID
      FROM
      (
      SELECT NULL ID WHERE 1=0
      ) V
      ) U;
       
       
       
      select * from
      (
      Select ID
      FROM
      (
      SELECT 1 ID
      FROM 
      t1inno
      ) V
      UNION ALL
      SELECT ID
      FROM
      (
      SELECT NULL ID WHERE 1=0
      ) V
      ) U;
       
      
      

      Result with innodb table:

      Empty set (0.001 sec)
      

      Result with Columnstore table:

      ------

      ID

      ------

      NULL

      ------
      1 row in set (0.043 sec)

      Columnstore throws the same result like innodb, if

      • change "where 1= 0" to "where 1=1" (or without where condition)
      • change SELECT NULL ID to SELECT 1 ID
      • Remove the outer wrapper "select * from (...."

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              Richard Richard Stracke
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.