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

Wrong result on RIGHT JOIN with constant derived table column in WHERE IS NULL clause

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Critical
    • Resolution: Unresolved
    • 12.2.2
    • None
    • 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.

      Expected results:

      +------------+
      | c0         |
      +------------+
      |       NULL |
      |       NULL |
      |  371923972 |
      |          0 |
      |       NULL |
      | -260642322 |
      |  782140878 |
      |       NULL |
      |       NULL |
      |          0 |
      |       NULL |
      | -431721820 |
      |          1 |
      |          0 |
      |          1 |
      +------------+
      

      Actual Results:

      Empty set (0.001 sec)
      

      How to repeat:

      -- create table
      DROP TABLE IF EXISTS `t0`;
      CREATE TABLE `t0` ( `c0` decimal(10,0) DEFAULT NULL, KEY `i0` (`c0`), KEY `i1` (`c0`) );
      INSERT INTO `t0` VALUES (NULL),(NULL),(0),(1),(1),(1),(36944682),(573744917),(723675432),(723675432);
      DROP TABLE IF EXISTS `t2`;
      CREATE TABLE `t2` ( `c0` decimal(10,0) DEFAULT NULL );
      INSERT INTO `t2` VALUES (0),(NULL),(NULL),(782140878),(-431721820),(1),(0),(NULL),(NULL),(NULL),(1),(NULL),(371923972),(-260642322),(0);
       
      -- query
      SELECT t2.c0 AS c0 FROM (SELECT 45 AS c0 FROM t2 AS t3) AS t1 RIGHT JOIN t2 AS t2 ON (t1.c0 = t2.c0) WHERE EXISTS(SELECT t2.c0 AS c0 FROM t0 AS t4 WHERE t1.c0 IS NULL);
      
      

      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.