Wrong result with merge view , derived table, first execution of a prepared statement
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
High
|
Igor Babaev |
Bug Description
The following query:
SELECT * FROM (
ON (t2.a = v1.b)
) AS derived
returns the following incorrect result when run the first time as a prepared statement:
a b
6 0
The second run, as well as non-prepared execution, return the correct result:
a b
6 NULL
explain:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2
2 DERIVED t2 system NULL NULL NULL NULL 1
2 DERIVED t1 ALL NULL NULL NULL NULL 0 Using where
optimizer switch:
index_merge=
repeatable on maria-5.3. Not repeatable on maria-5.2, mysql-5.5
bzr version-info:
revision-id: <email address hidden>
date: 2011-10-13 13:44:50 +0200
build-date: 2011-10-14 13:08:11 +0300
revno: 3233
branch-nick: maria-5.3
test case:
CREATE TABLE t2 (a int NOT NULL);
INSERT INTO t2 VALUES (6);
CREATE TABLE t1 (b int NOT NULL);
CREATE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1;
PREPARE st1 FROM '
SELECT * FROM (
ON (t2.a = v1.b)
) AS derived
';
EXECUTE st1;
EXECUTE st1;
Changed in maria: | |
milestone: | none → 5.3 |
assignee: | nobody → Igor Babaev (igorb-seattle) |
Changed in maria: | |
status: | New → Confirmed |
importance: | Undecided → High |
Changed in maria: | |
status: | Confirmed → In Progress |
Changed in maria: | |
status: | In Progress → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |