Bug #6042 constants propogation works only once (prepared statements)
Submitted: 12 Oct 2004 13:07 Modified: 12 Oct 2004 13:37
Reporter: Konstantin Osipov (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.* OS:Any (All)
Assigned to: Konstantin Osipov

[12 Oct 2004 13:07] Konstantin Osipov
Description:
From the log below you can see that constants propogation for prepared 
statements is done only once:

mysql> create table t1 (a int, b int);
mysql> insert into t1 (a, b) values (1,1), (1,2), (2,1), (2,2);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> prepare stmt from "explain select * from t1 where t1.a=2 and t1.a=t1.b and t1.b > 1 + ?";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> set @v=0; execute stmt using @v;
Query OK, 0 rows affected (0.00 sec)

+----+-------------+-------+------+- -+------+-------------+
| id | select_type | table | type |   | rows | Extra       |
+----+-------------+-------+------+- -+------+-------------+
|  1 | SIMPLE      | t1    | ALL  |   |    4 | Using where |
+----+-------------+-------+------+- -+------+-------------+
1 row in set (0.01 sec)

mysql> set @v=5; execute stmt using @v;
Query OK, 0 rows affected (0.00 sec)

+----+-------------+-------+------+- -+------+-------------+
| id | select_type | table | type |   | rows | Extra       |
+----+-------------+-------+------+- -+------+-------------+
|  1 | SIMPLE      | t1    | ALL  |   |    4 | Using where |
+----+-------------+-------+------+- -+------+-------------+
1 row in set (0.00 sec)

mysql> set @v=0; execute stmt using @v;
Query OK, 0 rows affected (0.00 sec)

+----+-------------+-------+------+- -+------+-------------+
| id | select_type | table | type |   | rows | Extra       |
+----+-------------+-------+------+- -+------+-------------+
|  1 | SIMPLE      | t1    | ALL  |   |    4 | Using where |
+----+-------------+-------+------+- -+------+-------------+
1 row in set (0.00 sec)
mysql> prepare stmt from "explain select * from t1 where t1.a=2 and t1.a=t1.b and t1.b > 1 + ?";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> set @v=5; execute stmt using @v;
Query OK, 0 rows affected (0.00 sec)

+----+-------------+-------+------+- -+------------------+
| id | select_type | table | type |   | Extra            |
+----+-------------+-------+------+- -+------------------+
|  1 | SIMPLE      | NULL  | NULL |   | Impossible WHERE |
+----+-------------+-------+------+- -+------------------+
1 row in set (0.00 sec)

mysql> set @v=0; execute stmt using @v;
Query OK, 0 rows affected (0.00 sec)

+----+-------------+-------+------+- -+------+-------------+
| id | select_type | table | type |   | rows | Extra       |
+----+-------------+-------+------+- -+------+-------------+
|  1 | SIMPLE      | t1    | ALL  |   |    4 | Using where |
+----+-------------+-------+------+- -+------+-------------+
1 row in set (0.00 sec)

mysql> set @v=5; execute stmt using @v;
Query OK, 0 rows affected (0.00 sec)

+----+-------------+-------+------+- -+------+-------------+
| id | select_type | table | type |   | rows | Extra       |
+----+-------------+-------+------+- -+------+-------------+
|  1 | SIMPLE      | t1    | ALL  |   |    4 | Using where |
+----+-------------+-------+------+- -+------+-------------+
1 row in set (0.00 sec)

How to repeat:
See above.

Suggested fix:
Reset item->marker in item->cleanup
[12 Oct 2004 13:37] Konstantin Osipov
Fixed in 4.1.6:
Subject: bk commit - 4.1 tree (konstantin:1.2085) BUG#6042

ChangeSet
  1.2085 04/10/12 17:30:07 konstantin@mysql.com +1 -0
  A fix for Bug #6042 "constants propogation works only once (prepared
  statements)": reset item->marker in Item::cleanup.