So these cases are largely around whether the value provides is auto converted to BIGINT
before execution. For clarity, what is the exact type of c_indx and I assume this is the primary key?
With the prepare and execute, is this a application language like php generating this? Which version?
A simplified prepared statement test is execute immediate, so EXECUTE IMMEDIATE "UPDATE CUSTOMER SET c_name='ttt' where c_index = ?" using '2387657687258'.
Yes c_indx is PK. Type is Integer.
EXECUTE IMMEDIATE "UPDATE customer SET c_name='ttt' where c_indx = ?" using 2387657687258
< 0.1 sec
EXECUTE IMMEDIATE "UPDATE customer SET c_name='ttt' where c_indx = ?" using '2387657687258'
> 20 sec.
I use PHP7.2 with PDO. I think there is an problem that c_indx is seen as string in PHP. I would handle this and give feedback.
MariaDB [test]> create table customer ( c_indx integer not null primary key, c_name varchar(30));
|
Query OK, 0 rows affected (0.002 sec)
|
|
|
MariaDB [test]> insert into customer select seq, concat("bob", seq ) from seq_1_to_90000;
|
Query OK, 90000 rows affected (0.172 sec)
|
Records: 90000 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> explain update customer set c_name = "ttt" where c_indx =2387657687258;
|
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
|
| 1 | SIMPLE | customer | range | PRIMARY | PRIMARY | 4 | NULL | 1 | Using where |
|
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
|
1 row in set (0.001 sec)
|
|
|
MariaDB [test]> explain update customer set c_name = "ttt" where c_indx ='2387657687258';
|
+------+-------------+----------+-------+---------------+---------+---------+------+-------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+----------+-------+---------------+---------+---------+------+-------+-------------+
|
| 1 | SIMPLE | customer | index | NULL | PRIMARY | 4 | NULL | 90497 | Using where |
|
+------+-------------+----------+-------+---------------+---------+---------+------+-------+-------------+
|
1 row in set (0.001 sec)
|
|
|
MariaDB [test]> explain select * from customer where c_indx ='2387657687258';
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
1 row in set (0.001 sec)
|
|
|
MariaDB [test]> explain select * from customer where c_indx = 2387657687258;
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
1 row in set (0.001 sec)
|
|
|
MariaDB [test]> explain select * from customer where c_indx ='23258';
|
+------+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
|
| 1 | SIMPLE | customer | const | PRIMARY | PRIMARY | 4 | const | 1 | |
|
+------+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
|
1 row in set (0.001 sec)
|
|
|
MariaDB [test]> explain update customer set c_name = "ttt" where c_indx ='23258';
|
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
|
| 1 | SIMPLE | customer | range | PRIMARY | PRIMARY | 4 | NULL | 1 | Using where |
|
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
|
1 row in set (0.001 sec)
|
|
|
MariaDB [test]> explain update customer set c_name = "ttt" where c_indx = 23258;
|
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
|
| 1 | SIMPLE | customer | range | PRIMARY | PRIMARY | 4 | NULL | 1 | Using where |
|
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
|
1 row in set (0.001 sec)
|
So obviously 2387657687258 is larger than the maximum int column. For select statements this translates to an "impossible where" in the optimizer. Its not unreasonable for UPDATE to do the same. When the value is within the allowed range for the int type you see the query plan being the same regardless of if its quoted or not.
For PDO you can [bindparam}https://www.php.net/manual/en/pdostatement.bindparam.php] to PARAM_INT.
So reframing this bug to mark update query plans to impossible where when outside the allowed range of the type.