[MDEV-5515] 2nd execution of a prepared statement returns wrong results - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Blocker
Resolution: Fixed
Affects Version/s: 5.3.12, 5.5.34, 10.0.6
Fix Version/s: 5.5.35, 10.0.8, 5.3.13
Component/s: None
Labels:
None
Environment:
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

Ascending order - Click to sort in descending order

Oleksandr Byelkin added a comment - 2014-01-13 10:30 - edited

It could be duplicate of ~~MDEV-5356~~ (I am cheking it).

Oleksandr Byelkin added a comment - 2014-01-13 10:30 - edited It could be duplicate of MDEV-5356 (I am cheking it).

Oleksandr Byelkin added a comment - 2014-01-13 10:42

NO, it is not duplicate.

Oleksandr Byelkin added a comment - 2014-01-13 10:42 NO, it is not duplicate.

Oleksandr Byelkin added a comment - 2014-01-13 14:14

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;

Oleksandr Byelkin added a comment - 2014-01-13 14:14 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;

Oleksandr Byelkin added a comment - 2014-01-13 14:20

Adding fields to SELECT list of the query fixes the problem, so probably read flags set incorrectly during second execution.

Oleksandr Byelkin added a comment - 2014-01-13 14:20 Adding fields to SELECT list of the query fixes the problem, so probably read flags set incorrectly during second execution.

Oleksandr Byelkin added a comment - 2014-01-13 16:11

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

Oleksandr Byelkin added a comment - 2014-01-13 16:11 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

Oleksandr Byelkin added a comment - 2014-01-13 19:58

eliminate_tables() got wrong used tables map from SELECT item list (6 instead of 12) on second execution

Oleksandr Byelkin added a comment - 2014-01-13 19:58 eliminate_tables() got wrong used tables map from SELECT item list (6 instead of 12) on second execution

Oleksandr Byelkin added a comment - 2014-01-13 20:49

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)

Oleksandr Byelkin added a comment - 2014-01-13 20:49 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)

Oleksandr Byelkin added a comment - 2014-01-13 21:32

Patch sent for review.

Oleksandr Byelkin added a comment - 2014-01-13 21:32 Patch sent for review.

Sergei Petrunia added a comment - 2014-01-14 11:22

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.

Sergei Petrunia added a comment - 2014-01-14 11:22 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.

Daniel Bartholomew added a comment - 2014-01-29 19:29

http://bazaar.launchpad.net/~maria-captains/maria/5.5/revision/2502.567.182

Daniel Bartholomew added a comment - 2014-01-29 19:29 http://bazaar.launchpad.net/~maria-captains/maria/5.5/revision/2502.567.182

People

Assignee:: Oleksandr Byelkin

Reporter:: Georg Richter

Votes:: 0 Vote for this issue

Watchers:: 7 Start watching this issue

Dates

Created:: 2014-01-10 12:01

Updated:: 2014-01-29 19:29

Resolved:: 2014-01-16 12:29

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server