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

Wrong result (missing rows) on LEFT JOINs and <non-nullable datetime field> IS NULL

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0.4, 5.5.32
    • 10.0.5, 5.5.33, 5.3.13
    • None
    • None

    Description

      CREATE TABLE t1 (i1 INT) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (1),(2);
       
      CREATE TABLE t2 (i2 INT) ENGINE=MyISAM;
      INSERT INTO t2 VALUES (10),(20);
       
      CREATE TABLE t3 (i3 INT, d3 DATETIME NOT NULL) ENGINE=MyISAM;
      INSERT INTO t3 VALUES (8,'2008-12-04 17:53:42'),(9,'2012-12-21 12:12:12');
       
      SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3 WHERE d3 IS NULL;
      EXPLAIN EXTENDED SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3 WHERE d3 IS NULL;

      Expected result:

      SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3 WHERE d3 IS NULL;
      i1	i2	i3	d3
      1	NULL	NULL	NULL
      2	NULL	NULL	NULL

      Actual result:

      SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3 WHERE d3 IS NULL;
      i1	i2	i3	d3

      On 5.3, the problem appeared with the revision below; but on 5.5 it had apparently existed long before that, and still does.

      revno: 3654
      committer: Igor Babaev <igor@askmonty.org>
      branch nick: maria-5.3-bugs
      timestamp: Fri 2013-05-03 18:45:20 -0700
      message:
        Made consistent handling of the predicates of the form
        <non-nullable datatime field> IS NULL in outer joins with
        that in inner joins.
        Previously such condition was transformed into the condition
        <non-nullable datatime field> = 0 unless the field belonged
        to an inner table of an outer join. In this case the predicate
        was interpreted as for any other field.
        Now if the field in the predicate <non-nullable datatime field> IS NULL
        belongs to an inner table of an outer join the predicate is
        transformed into the disjunction
        <non-nullable datatime field> = 0 OR <non-nullable datatime field> IS NULL.
        This is fully compatible with the semantics of such predicates in 5.5.

      MariaDB 5.2, MySQL 5.1, MySQL 5.6 return 2 rows.

      Attachments

        Issue Links

          Activity

            People

              igor Igor Babaev
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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