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

Query returns wrong results (while using CHARACTER SET utf8)

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Confirmed (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3
    • Fix Version/s: 5.5, 10.0, 10.1, 10.2, 10.3
    • Component/s: None
    • Labels:
      None

      Description

      Query returns an empty set, but the same query with additional strict conditions (adding WHERE ..) returns some results.

      CREATE TABLE t1 (i1 int);
      INSERT INTO t1 VALUES (NULL),(3),(8),(0),(2);
       
      CREATE TABLE t2 (v1 varchar(10) CHARACTER SET utf8, KEY v1 (v1(3)));
      INSERT INTO t2 VALUES  ('k'),('rid'),('f'),('x');
       
      CREATE TABLE t3 (v1 varchar(10) CHARACTER SET utf8, l1 varchar(10), pk int NOT NULL PRIMARY KEY, i1 int , i2 int, KEY v1 (v1(3)));
      INSERT INTO t3 VALUES ('measure','m',2,NULL,2),('o','k',3,1970208768,NULL);
       
      CREATE TABLE t4 (v1 varchar(10) CHARACTER SET utf8, l1 varchar(10), pk int NOT NULL PRIMARY KEY, i1 int , i2 int, KEY v1 (v1(3)));
      INSERT INTO t4 VALUES ('e','we',1,-1,1),('n','o',2,4,4),('r','o',3,3,2),('o','secure',4,NULL,NULL),('t','e',8,-1,6);
       
      SELECT 1 FROM t3 JOIN (t1 LEFT JOIN t4 ON t4.pk = t1.i1) ON ( t4.l1 < ANY (SELECT MAX(t2.v1) FROM t2));
      SELECT 1 FROM t3 JOIN (t1 LEFT JOIN t4 ON t4.pk = t1.i1) ON ( t4.l1 < ANY (SELECT MAX(t2.v1) FROM t2)) WHERE (t4.i2 <=2);
      

      MariaDB [test2]> SELECT 1 FROM t3 JOIN (t1 LEFT JOIN t4 ON t4.pk = t1.i1) ON ( t4.l1 < ANY (SELECT MAX(t2.v1) FROM t2));
      Empty set (0.001 sec)
       
      MariaDB [test2]> SELECT 1 FROM t3 JOIN (t1 LEFT JOIN t4 ON t4.pk = t1.i1) ON ( t4.l1 < ANY (SELECT MAX(t2.v1) FROM t2)) WHERE (t4.i2 <=2);
      +---+
      | 1 |
      +---+
      | 1 |
      | 1 |
      +---+
      2 rows in set (0.001 sec)
       
      MariaDB [test2]> explain extended SELECT 1 FROM t3 JOIN (t1 LEFT JOIN t4 ON t4.pk = t1.i1) ON ( t4.l1 < ANY (SELECT MAX(t2.v1) FROM t2));
      +------+-------------+-------+--------+---------------+---------+---------+-------------+------+----------+------------------------------------+
      | id   | select_type | table | type   | possible_keys | key     | key_len | ref         | rows | filtered | Extra                              |
      +------+-------------+-------+--------+---------------+---------+---------+-------------+------+----------+------------------------------------+
      |    1 | PRIMARY     | t3    | index  | NULL          | PRIMARY | 4       | NULL        |    2 |   100.00 | Using index                        |
      |    1 | PRIMARY     | t1    | ALL    | NULL          | NULL    | NULL    | NULL        |    5 |   100.00 | Using join buffer (flat, BNL join) |
      |    1 | PRIMARY     | t4    | eq_ref | PRIMARY       | PRIMARY | 4       | test2.t1.i1 |    1 |   100.00 | Using where                        |
      |    2 | SUBQUERY    | t2    | ALL    | NULL          | NULL    | NULL    | NULL        |    4 |   100.00 |                                    |
      +------+-------------+-------+--------+---------------+---------+---------+-------------+------+----------+------------------------------------+
      4 rows in set, 1 warning (0.001 sec)
       
      Note (Code 1003): /* select#1 */ select 1 AS `1` from `test2`.`t3` join `test2`.`t1` left join `test2`.`t4` on(`test2`.`t4`.`pk` = `test2`.`t1`.`i1` and `test2`.`t1`.`i1` is not null) where <nop>(<in_optimizer>(`test2`.`t4`.`l1`,<max>(/* select#2 */ select max(`test2`.`t2`.`v1`) from `test2`.`t2`) > <cache>(convert(`test2`.`t4`.`l1` using utf8))))
       
      MariaDB [test2]> explain extended SELECT 1 FROM t3 JOIN (t1 LEFT JOIN t4 ON t4.pk = t1.i1) ON ( t4.l1 < ANY (SELECT MAX(t2.v1) FROM t2)) WHERE (t4.i2 <=2);
      +------+-------------+-------+--------+---------------+---------+---------+-------------+------+----------+-------------------------------------------------+
      | id   | select_type | table | type   | possible_keys | key     | key_len | ref         | rows | filtered | Extra                                           |
      +------+-------------+-------+--------+---------------+---------+---------+-------------+------+----------+-------------------------------------------------+
      |    1 | PRIMARY     | t3    | index  | NULL          | PRIMARY | 4       | NULL        |    2 |   100.00 | Using index                                     |
      |    1 | PRIMARY     | t1    | ALL    | NULL          | NULL    | NULL    | NULL        |    5 |   100.00 | Using where; Using join buffer (flat, BNL join) |
      |    1 | PRIMARY     | t4    | eq_ref | PRIMARY       | PRIMARY | 4       | test2.t1.i1 |    1 |   100.00 | Using where                                     |
      |    2 | SUBQUERY    | t2    | ALL    | NULL          | NULL    | NULL    | NULL        |    4 |   100.00 |                                                 |
      +------+-------------+-------+--------+---------------+---------+---------+-------------+------+----------+-------------------------------------------------+
      4 rows in set, 1 warning (0.000 sec)
       
      Note (Code 1003): /* select#1 */ select 1 AS `1` from `test2`.`t3` join `test2`.`t1` join `test2`.`t4` where `test2`.`t4`.`pk` = `test2`.`t1`.`i1` and `test2`.`t4`.`i2` <= 2 and <nop>(<in_optimizer>(`test2`.`t4`.`l1`,<max>(/* select#2 */ select max(`test2`.`t2`.`v1`) from `test2`.`t2`) > <cache>(convert(`test2`.`t4`.`l1` using utf8))))
      

        Attachments

          Activity

            People

            • Assignee:
              varun Varun Gupta
              Reporter:
              alice Alice Sherepa
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: