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

lp:994392: Wrong result with RIGHT JOIN and ALL subquery predicate in WHERE condition

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 5.2.12
    • 5.2.13
    • None
    • None

    Description

      fix https://bugs.launchpad.net/maria/+bug/994392


      The following sequence of commands gives us a wrong result set in mariadb-5.2:

      CREATE TABLE t1(a INT);
      INSERT INTO t1 VALUES(9);
      CREATE TABLE t2(b INT);
      INSERT INTO t2 VALUES(8);
      CREATE TABLE t3(c INT);
      INSERT INTO t3 VALUES(3);
      SELECT * FROM t2 RIGHT JOIN t3 ON(c = b) WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7);

      MariaDB [test]> SELECT * FROM t2 RIGHT JOIN t3 ON(c = b) WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7);
      Empty set (0.00 sec)
       
      MariaDB [test]> EXPLAIN EXTENDED SELECT * FROM t2 RIGHT JOIN t3 ON(c = b) WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7);
      +----+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
      | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                               |
      +----+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
      |  1 | PRIMARY     | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL     | Impossible WHERE noticed after reading const tables |
      |  2 | SUBQUERY    | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL     | Impossible WHERE noticed after reading const tables |
      +----+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
      2 rows in set, 1 warning (0.00 sec)
      MariaDB [test]> show warnings;
      +-------+------+------------------------------------------------------------------------+
      | Level | Code | Message                                                                |
      +-------+------+------------------------------------------------------------------------+
      | Note  | 1003 | select '8' AS `b`,'3' AS `c` from `test`.`t3` join `test`.`t2` where 0 |
      +-------+------+------------------------------------------------------------------------+

      The bug is not reproducible in mariadb-5.3:

      MariaDB [test]> select version();
      +---------------------+
      | version()           |
      +---------------------+
      | 5.3.6-MariaDB-debug |
      +---------------------+
       
      MariaDB [test]> SELECT * FROM t2 RIGHT JOIN t3 ON(c = b) WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7);
      +------+------+
      | b    | c    |
      +------+------+
      | NULL | 3    |
      +------+------+
       
      MariaDB [test]> EXPLAIN EXTENDED SELECT * FROM t2 RIGHT JOIN t3 ON(c = b) WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7);
      +----+-------------+-------+--------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
      | id | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                               |
      +----+-------------+-------+--------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
      | 1 | PRIMARY      | t3    | system | NULL          | NULL | NULL    | NULL | 1    | 100.00   |                                                     |
      | 1 | PRIMARY      | t2    | system | NULL          | NULL | NULL    | NULL | 1    | 100.00   |                                                     |
      | 2 | SUBQUERY     | NULL  | NULL   | NULL          | NULL | NULL    | NULL | NULL | NULL     | Impossible WHERE noticed after reading const tables |
      +----+-------------+-------+--------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
       
      MariaDB [test]> show warnings;
      +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Level | Code | Message                                                                                                                                                                       |
      +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Note  | 1003 | select NULL AS `b`,3 AS `c` from `test`.`t3` left join `test`.`t2` on(0) where <not>(<in_optimizer>(NULL,(<min>(select 9 from `test`.`t1` where (9 <= 7)) <= <cache>(NULL)))) |
      +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

      (See also lp:13735712 for mysql code line)

      Attachments

        Activity

          People

            sanja Oleksandr Byelkin
            sanja Oleksandr Byelkin
            Votes:
            0 Vote for this issue
            Watchers:
            1 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.