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)
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.*
• OS: Any (All)
• Assigned to: Konstantin Osipov
• CPU Architecture: Any

[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 [e-mail] +1 -0
  A fix for Bug #6042 "constants propogation works only once (prepared
  statements)": reset item->marker in Item::cleanup.