Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-39117

Incorrect column mapping in EXCEPT query involving UNION ALL with a false constant condition in a derived table

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Critical
    • Resolution: Unresolved
    • 12.2.2
    • None
    • Optimizer, Server
    • None
    • Docker: mariadb:12.2.2
      Server version: 12.2.2-MariaDB-ubu2404
      Source revision: d26a6f44c1f2119377e79a9540886c6d8c01472f

    Description

      Hi, I found a logic bug in MariaDB 12.2.2.

      A query that should have returned an empty set actually returned (22105307, 0).

      How to repeat:

      -- create table
      DROP TABLE IF EXISTS t0;
      CREATE TABLE t0 (
          c0 TEXT,
          c2 BIGINT UNSIGNED ZEROFILL
      );
      INSERT INTO t0 VALUES (NULL, 22105307), (NULL, 0);
       
      -- query, expect: empty set, actual: {22105307, 0}
      SELECT COALESCE(a, b) AS c0 FROM (
          SELECT t_sub0.c0 AS a, t_sub0.c2 AS b 
          FROM (SELECT * FROM t0 AS t_sub0 WHERE CAST('b' AS DECIMAL(65, 30))) AS t_sub0
          UNION ALL
          SELECT t_sub1.c0 AS a, t_sub1.c2 AS b 
          FROM (SELECT * FROM t0 AS t_sub1 WHERE NOT (CAST('b' AS DECIMAL(65, 30)))) AS t_sub1
      ) AS t_branch
      EXCEPT
      SELECT COALESCE(`t3`.`c0`, `t3`.`c2`) AS `c0` FROM `t0` AS `t3`;
      
      

      Attachments

        Activity

          People

            Unassigned Unassigned
            Yuxiao Guo Yuxiao Guo
            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.