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

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

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-14911 zero_date is considered as NULL, depending on optimizer_switch

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova created issue -
            elenst Elena Stepanova made changes -
            Field Original Value New Value
            igor Igor Babaev (Inactive) added a comment -

            Elena,
            I see the correct result in mysql-5.6, but don't see it in mysql-5.5 (I built both versions from the launchpad trees).

            igor Igor Babaev (Inactive) added a comment - Elena, I see the correct result in mysql-5.6, but don't see it in mysql-5.5 (I built both versions from the launchpad trees).
            elenst Elena Stepanova added a comment - - edited

            You are right, typo. I updated the description to remove MySQL 5.5 from the list of the good ones. It was meant to be MySQL 5.1, MySQL 5.6, MySQL 5.7.

            elenst Elena Stepanova added a comment - - edited You are right, typo. I updated the description to remove MySQL 5.5 from the list of the good ones. It was meant to be MySQL 5.1, MySQL 5.6, MySQL 5.7.
            elenst Elena Stepanova made changes -
            Description {code:sql}
            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;
            {code}

            Expected result:
            {noformat}
            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
            {noformat}

            Actual result:
            {noformat}
            SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3 WHERE d3 IS NULL;
            i1 i2 i3 d3
            {noformat}

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

            {noformat}
            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.
            {noformat}

            MariaDB 5.2, MySQL 5.5, MySQL 5.6 all return 2 rows.
            		{code:sql}
            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;
            {code}

            Expected result:
            {noformat}
            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
            {noformat}

            Actual result:
            {noformat}
            SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3 WHERE d3 IS NULL;
            i1 i2 i3 d3
            {noformat}

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

            {noformat}
            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.
            {noformat}

            MariaDB 5.2, MySQL 5.6 return 2 rows.
            elenst Elena Stepanova made changes -
            Description {code:sql}
            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;
            {code}

            Expected result:
            {noformat}
            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
            {noformat}

            Actual result:
            {noformat}
            SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3 WHERE d3 IS NULL;
            i1 i2 i3 d3
            {noformat}

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

            {noformat}
            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.
            {noformat}

            MariaDB 5.2, MySQL 5.6 return 2 rows.
            		{code:sql}
            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;
            {code}

            Expected result:
            {noformat}
            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
            {noformat}

            Actual result:
            {noformat}
            SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3 WHERE d3 IS NULL;
            i1 i2 i3 d3
            {noformat}

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

            {noformat}
            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.
            {noformat}

            MariaDB 5.2, MySQL 5.1, MySQL 5.6 return 2 rows.
            igor Igor Babaev (Inactive) added a comment -

            The fix was pushed into the 5.3 tree and merged into the 5.5 tree.

            igor Igor Babaev (Inactive) added a comment - The fix was pushed into the 5.3 tree and merged into the 5.5 tree.
            igor Igor Babaev (Inactive) made changes -
            Resolution Fixed [ 1 ]
            Status Open [ 1 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow defaullt [ 28715 ] MariaDB v2 [ 42452 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Workflow MariaDB v2 [ 42452 ] MariaDB v3 [ 62147 ]
            alice Alice Sherepa made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 62147 ] MariaDB v4 [ 146992 ]

            People

              igor Igor Babaev (Inactive)
              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.