Details
-
Bug
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
-
5.3.12, 5.5.34, 10.0.6
-
None
-
None
-
All
Description
When executing a prepared statement twicce, second execution returns invalid results.
How to repeat:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (i1 INT, j1 INT NOT NULL, PRIMARY KEY (i1));
|
|
|
INSERT INTO t1 VALUES (30,300),(40,400);
|
|
|
DROP TABLE IF EXISTS t2;
|
CREATE TABLE t2 (i2 INT);
|
INSERT INTO t2 VALUES (50),(60);
|
|
|
DROP TABLE IF EXISTS t3;
|
CREATE TABLE t3 (c3 VARCHAR(20), i3 INT);
|
INSERT INTO t3 VALUES ('a',10),('b',2);
|
|
|
DROP TABLE IF EXISTS t4;
|
CREATE TABLE t4 (i4 INT);
|
INSERT INTO t4 VALUES (1),(2);
|
|
|
DROP VIEW IF EXISTS v1;
|
CREATE VIEW v1 AS select coalesce(j1,i3) AS v1_field1 from t2 join t3 left join t1 on ( i1 = i2 );
|
|
|
DROP VIEW IF EXISTS v2;
|
CREATE VIEW v2 AS select v1_field1 from t4 join v1;
|
|
|
prepare my_stmt from "select v1_field1 from v2";
|
execute my_stmt;
|
execute my_stmt;
|
deallocate prepare my_stmt;
|
|
|
DROP TABLE t1,t2,t3,t4;
|
DROP VIEW v1,v2;
|
Output:
1st run
|
+-----------+
|
| v1_field1 |
|
+-----------+
|
| 10 |
|
| 10 |
|
| 10 |
|
| 10 |
|
| 2 |
|
| 2 |
|
| 2 |
|
| 2 |
|
+-----------+
|
|
|
2nd run
|
|
|
MySQL 5.3
|
+-----------+
|
| v1_field1 |
|
+-----------+
|
| 0 |
|
| 0 |
|
| 0 |
|
| 0 |
|
| 0 |
|
| 0 |
|
| 0 |
|
| 0 |
|
+-----------+
|
|
|
MariaDB 10.0
|
|
|
+-------------+
|
| v1_field1 |
|
+-------------+
|
| -1886417009 |
|
| -1886417009 |
|
| -1886417009 |
|
| -1886417009 |
|
| -1886417009 |
|
| -1886417009 |
|
| -1886417009 |
|
| -1886417009 |
|
+-------------+
|
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Fix Version/s | 10.0.8 [ 14200 ] | |
| Fix Version/s | 5.5.35 [ 14000 ] | |
| Fix Version/s | 5.3.13 [ 12602 ] | |
| Assignee | Oleksandr Byelkin [ sanja ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
IF() has the same problem, GREATEST() has more interesting result:
execute my_stmt;
v1_field1
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
execute my_stmt;
v1_field1
10
10
10
10
2
2
2
2
deallocate prepare my_stmt;
Adding fields to SELECT list of the query fixes the problem, so probably read flags set incorrectly during second execution.
according to EXPLAIN EXTENDED ON expression lost on second execution:
execute my_stmt;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join)
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.i2 1 100.00 Using where
1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join)
Warnings:
Note 1003 select coalesce(`test`.`t1`.`j1`,`test`.`t3`.`i3`) AS `v1_field1` from `test`.`t4` join `test`.`t2` join `test`.`t3` left join `test`.`t1` on(((`test`.`t1`.`i1` = `test`.`t2`.`i2`) and (`test`.`t2`.`i2` is not null))) where 1
execute my_stmt;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join)
1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (incremental, BNL join)
Warnings:
Note 1003 select coalesce(`test`.`t1`.`j1`,`test`.`t3`.`i3`) AS `v1_field1` from `test`.`t4` join `test`.`t2` join `test`.`t3` where 1
eliminate_tables() got wrong used tables map from SELECT item list (6 instead of 12) on second execution
it looks like lack of update_used_tables() after handling derived tables 
(worked for usual queries because there was other call only for firest execution)
| Status | In Progress [ 3 ] | Stalled [ 10000 ] |
Debugging the example, I see:
(gdb) p join->fields_list.head()
$39 = (Item_direct_view_ref *) 0x7fffcd4eefb8
(gdb) p *join->fields_list.head()->ref
$44 = (Item_direct_view_ref *) 0x7fffcd4eed78
(gdb) p *(*join->fields_list.head()>ref)>ref
$49 = (Item_func_coalesce *) 0x7fffcd4683d0
This is probably not related to this bug, but why is one Item_direct_view_ref
wrapped within the other Item_direct_view_ref.
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Resolution | Fixed [ 1 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
| Workflow | defaullt [ 32700 ] | MariaDB v2 [ 42455 ] |
| Workflow | MariaDB v2 [ 42455 ] | MariaDB v3 [ 61406 ] |
| Workflow | MariaDB v3 [ 61406 ] | MariaDB v4 [ 147398 ] |
It could be duplicate of
MDEV-5356(I am cheking it).