Bug #53648 Where clause with IS TRUE generates different plan
Submitted: 14 May 2010 14:00 Modified: 18 May 2010 6:22
Reporter: Roy Lyseng Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.1.48-bzr OS:Any
Assigned to: CPU Architecture:Any

[14 May 2010 14:00] Roy Lyseng
Description:
In the SQL standard ISO 9075-2:2008, the explanation of a <where clause>
contains the following general rule:

The <search condition> is effectively evaluated for each row of T.
The result of the <where clause> is a table of those rows of T for which the result of the <search condition> is True.

Hence, I would imagine that the plan for a query containing a where clause with
the truth value test IS TRUE is the same as for a query without that clause.
However, this is not the case, at least for some join operations.

How to repeat:
CREATE TABLE t1n(
  id INTEGER PRIMARY KEY,
  u  INTEGER UNIQUE,
  g  INTEGER,
  v  INTEGER,
  vi INTEGER, INDEX(vi),
  s  CHAR(1));

CREATE TABLE t2n(
  id INTEGER PRIMARY KEY,
  u  INTEGER UNIQUE,
  g  INTEGER,
  v  INTEGER,
  vi INTEGER, INDEX(vi),
  s  CHAR(1));

INSERT INTO t1n VALUES(10, 10, 10, 10,   10,   'l');
INSERT INTO t2n VALUES(10, 10, 10, 10,   10,   'r');
INSERT INTO t1n VALUES(20, 20, 20, 20,   20,   'l');
INSERT INTO t2n VALUES(30, 30, 30, 30,   30,   'r');
INSERT INTO t1n VALUES(40, 40, 40, 40,   40,   'l');
INSERT INTO t1n VALUES(41, 41, 40, NULL, NULL, 'l');
INSERT INTO t1n VALUES(50, 50, 50, 50,   50,   'l');
INSERT INTO t1n VALUES(51, 51, 50, NULL, NULL, 'l');
INSERT INTO t2n VALUES(50, 50, 50, 50,   50,   'r');
INSERT INTO t1n VALUES(60, 60, 60, 60,   60,   'l');
INSERT INTO t1n VALUES(61, 61, 60, NULL, NULL, 'l');
INSERT INTO t2n VALUES(60, 60, 60, 60,   60,   'r');
INSERT INTO t2n VALUES(61, 61, 60, NULL, NULL, 'r');
INSERT INTO t1n VALUES(70, 70, 70, 70,   70,   'l');
INSERT INTO t2n VALUES(70, 70, 70, 70,   70,   'r');
INSERT INTO t2n VALUES(71, 71, 70, NULL, NULL, 'r');
INSERT INTO t2n VALUES(80, 80, 80, 80,   80,   'r');
INSERT INTO t2n VALUES(81, 81, 80, NULL, NULL, 'r');
INSERT INTO t1n VALUES(90, 90, 90, NULL, NULL, 'l');
INSERT INTO t2n VALUES(100,100,100,NULL, NULL, 'r');

EXPLAIN
SELECT t1n.* FROM t1n, t2n
WHERE t1n.v=t2n.u;

EXPLAIN
SELECT t1n.* FROM t1n, t2n
WHERE t1n.v=t2n.u IS TRUE;

Suggested fix:
Embed truth value test information into items that describe predicates and
boolean value expressions.
[14 May 2010 14:13] Valeriy Kravchuk
Thank you for the problem report. Verified just as described with recent 5.1.48 from bzr:

...
mysql> EXPLAIN
    -> SELECT t1n.* FROM t1n, t2n
    -> WHERE t1n.v=t2n.u;
+----+-------------+-------+------+---------------+------+---------+------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref        | rows | Extra                    |
+----+-------------+-------+------+---------------+------+---------+------------+------+--------------------------+
|  1 | SIMPLE      | t1n   | ALL  | NULL          | NULL | NULL    | NULL       |   10 |                          |
|  1 | SIMPLE      | t2n   | ref  | u             | u    | 5       | test.t1n.v |    2 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+------------+------+--------------------------+
2 rows in set (0.38 sec)

mysql> EXPLAIN
    -> SELECT t1n.* FROM t1n, t2n
    -> WHERE t1n.v=t2n.u IS TRUE;
+----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                       |
+----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------------+
|  1 | SIMPLE      | t1n   | ALL   | NULL          | NULL | NULL    | NULL |   10 |                                             |
|  1 | SIMPLE      | t2n   | index | NULL          | u    | 5       | NULL |   10 | Using where; Using index; Using join buffer |
+----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT t1n.* FROM t1n, t2n WHERE (t1n.v=t2n.u) IS TRUE;
+----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                       |
+----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------------+
|  1 | SIMPLE      | t1n   | ALL   | NULL          | NULL | NULL    | NULL |   10 |                                             |
|  1 | SIMPLE      | t2n   | index | NULL          | u    | 5       | NULL |   10 | Using where; Using index; Using join buffer |
+----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------------+
2 rows in set (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.1.48-debug |
+--------------+
1 row in set (0.00 sec)
[17 May 2010 21:58] Omer Barnir
This is not a bug - "IS TRUE" converts a LOGICAL evaluation into a FUNCTION RESULT evaluation.
[18 May 2010 6:22] Roy Lyseng
It is a bug because the optimizer treats two logically equivalent SQL queries differently. Even though the bug does not manifest in wrong results, it will simplify the optimizer if we consolidated the handling of the two queries into one. In particular, the optimization taken for the two queries should be the same.

Notice that IS TRUE is part of a search condition in SQL - it is applied to any logical predicate result (true, false or unknown) and returns another truth value (true or false, but not unknown).