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

Join with const table produces incorrect query result

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5.33
    • 5.5.33
    • None
    • None

    Description

      In 5.5, revision 3589, we've had:

      CREATE TABLE t1 (i1 INT) ENGINE=MyISAM;
      		 
      INSERT INTO t1 VALUES (NULL);
      		 
      CREATE TABLE t2 (i2 INT, a INT, b INT) ENGINE=MyISAM;
      		 
      CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2;
      		 
      INSERT INTO t2 VALUES (NULL,1,2),(NULL,2,3);
      		 
      SELECT * FROM t1 JOIN v2 ON i1 = i2 WHERE a < b;
      		 
      i1	i2	a	b
      		 
      # CHECK:
      		 
      SELECT * FROM t1 JOIN t2 ON i1 = i2 WHERE a < b;
      		 
      i1	i2	a	b
      		 
      DROP VIEW v2;
      		 
      DROP TABLE t1,t2;

      in revision 3860, we get

      CREATE TABLE t1 (i1 INT) ENGINE=MyISAM;
      		 
      INSERT INTO t1 VALUES (NULL);
      		 
      CREATE TABLE t2 (i2 INT, a INT, b INT) ENGINE=MyISAM;
      		 
      CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2;
      		 
      INSERT INTO t2 VALUES (NULL,1,2),(NULL,2,3);
      		 
      SELECT * FROM t1 JOIN v2 ON i1 = i2 WHERE a < b;
      		 
      i1	i2	a	b
      		 
      NULL	NULL	2	3
      		 
      NULL	NULL	1	2
      		 
      # CHECK:
      		 
      SELECT * FROM t1 JOIN t2 ON i1 = i2 WHERE a < b;
      		 
      i1	i2	a	b
      		 
      DROP VIEW v2;
      		 
      DROP TABLE t1,t2;

      Note that the query that used to return zero records now returns two records. This is incorrect.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          psergei Sergei Petrunia created issue -
          psergei Sergei Petrunia added a comment -

          The difference in make_join_select/add_not_null_conds. The optimizer used to add equality, but doesn't do it anymore.

          psergei Sergei Petrunia added a comment - The difference in make_join_select/add_not_null_conds. The optimizer used to add equality, but doesn't do it anymore.
          psergei Sergei Petrunia added a comment -
          1. EXPLAIN for the empty-set (correct) result:
            MariaDB [j12]> explain SELECT * FROM t1 JOIN v2 ON i1 = i2 WHERE a < b;
            -------------------------------------------------------------------------------------------------------------+
            id select_type table type possible_keys key key_len ref rows Extra

            -------------------------------------------------------------------------------------------------------------+

            1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
            2 DERIVED t2 ALL NULL NULL NULL NULL 2  

            -------------------------------------------------------------------------------------------------------------+
            2 rows in set (0.00 sec)

          1. EXPLAIN for the incorrect result:

          -----------------------------------------------------------------------------+

          id select_type table type possible_keys key key_len ref rows Extra

          -----------------------------------------------------------------------------+

          1 PRIMARY t1 system NULL NULL NULL NULL 1  
          1 PRIMARY <derived2> ref key0 key0 5 const 0 Using where
          2 DERIVED t2 ALL NULL NULL NULL NULL 2  

          -----------------------------------------------------------------------------+

          psergei Sergei Petrunia added a comment - EXPLAIN for the empty-set (correct) result: MariaDB [j12] > explain SELECT * FROM t1 JOIN v2 ON i1 = i2 WHERE a < b; ----- ----------- ----- ---- ------------- ---- ------- ---- ---- ----------------------------------------------------+ id select_type table type possible_keys key key_len ref rows Extra ----- ----------- ----- ---- ------------- ---- ------- ---- ---- ----------------------------------------------------+ 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 2 DERIVED t2 ALL NULL NULL NULL NULL 2   ----- ----------- ----- ---- ------------- ---- ------- ---- ---- ----------------------------------------------------+ 2 rows in set (0.00 sec) EXPLAIN for the incorrect result: ----- ----------- ---------- ------ ------------- ---- ------- ----- ---- ------------+ id select_type table type possible_keys key key_len ref rows Extra ----- ----------- ---------- ------ ------------- ---- ------- ----- ---- ------------+ 1 PRIMARY t1 system NULL NULL NULL NULL 1   1 PRIMARY <derived2> ref key0 key0 5 const 0 Using where 2 DERIVED t2 ALL NULL NULL NULL NULL 2   ----- ----------- ---------- ------ ------------- ---- ------- ----- ---- ------------+
          psergei Sergei Petrunia made changes -
          Field Original Value New Value
          Assignee Igor Babaev [ igor ]
          psergei Sergei Petrunia added a comment -

          The problem started to occur after this push:
          ------------------------------------------------------------
          revno: 3860 [merge]
          committer: Igor Babaev <igor@askmonty.org>
          branch nick: maria-5.5-trunk
          timestamp: Fri 2013-08-23 08:34:35 -0700
          message:
          Merge
          ------------------------------------------------------------
          revno: 3857.1.1
          committer: Igor Babaev <igor@askmonty.org>
          branch nick: maria-5.5
          timestamp: Fri 2013-08-23 07:25:45 -0700
          message:
          Fixed bug mdev-4420.
          The code of JOIN::optimize that performed substitutions for the best equal
          field in all ref items did not take into account that a multiple equality
          could contain the result of the single-value subquery if the subquery is
          inexpensive. This code was corrected.
          Also made necessary corresponding corrections in the code of make_join_select().

          psergei Sergei Petrunia added a comment - The problem started to occur after this push: ------------------------------------------------------------ revno: 3860 [merge] committer: Igor Babaev <igor@askmonty.org> branch nick: maria-5.5-trunk timestamp: Fri 2013-08-23 08:34:35 -0700 message: Merge ------------------------------------------------------------ revno: 3857.1.1 committer: Igor Babaev <igor@askmonty.org> branch nick: maria-5.5 timestamp: Fri 2013-08-23 07:25:45 -0700 message: Fixed bug mdev-4420. The code of JOIN::optimize that performed substitutions for the best equal field in all ref items did not take into account that a multiple equality could contain the result of the single-value subquery if the subquery is inexpensive. This code was corrected. Also made necessary corresponding corrections in the code of make_join_select().
          psergei Sergei Petrunia made changes -
          Resolution Fixed [ 1 ]
          Status Open [ 1 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow defaullt [ 28712 ] MariaDB v2 [ 44499 ]
          ratzpo Rasmus Johansson (Inactive) made changes -
          Workflow MariaDB v2 [ 44499 ] MariaDB v3 [ 63854 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 63854 ] MariaDB v4 [ 146989 ]

          People

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