Wrong result with subqery in select list and subquery cache=off in maria-5.3
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
High
|
Timour Katchaounov |
Bug Description
Not repeatable in maria-5.2 Repeatable in maria-5.3, maria-5.3-mwl89. The SUM part of following query:
SELECT t1.f1, ( SELECT SUM( f10 ) FROM t3 WHERE f10 > t1.f10 ) FROM t1 JOIN t2 ON t2.f2 > 0;
returns 2 different results for 2 rows where t1.f1 is the same, hence the SUM should also be the same.
Subquery cache appears to mask the bug by caching the first value and returning it twice.
Test case:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 ( f10 int NOT NULL) ;
INSERT IGNORE INTO t1 VALUES (104),(119);
DROP TABLE IF EXISTS t2;
CREATE TABLE t2 ( f2 int NOT NULL ) ;
INSERT IGNORE INTO t2 VALUES (8),(231);
DROP TABLE IF EXISTS t3;
CREATE TABLE t3 (f10 int NOT NULL ) ;
INSERT IGNORE INTO t3 VALUES (112);
SET SESSION optimizer_
SELECT t1.f10, ( SELECT SUM( f10 ) FROM t3 WHERE f10 > t1.f10 ) FROM t1 JOIN t2 ON t2.f2 ;
In maria-5.2:
f10 ( SELECT SUM( f10 ) FROM t3 WHERE f10 > t1.f10 )
104 112
119 NULL
104 112
119 NULL
in maria-5.3:
f10 ( SELECT SUM( f10 ) FROM t3 WHERE f10 > t1.f10 )
104 112
119 NULL
104 NULL <--------- notice NULL here
119 NULL
Explain in maria-5.3:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
2 DEPENDENT SUBQUERY t3 system NULL NULL NULL NULL 1
Changed in maria: | |
milestone: | none → 5.3 |
summary: |
- Wrong result with subqery in select list and subquery cache=off + Wrong result with subqery in select list and subquery cache=off |
summary: |
- Wrong result with subqery in select list and subquery cache=off + Wrong result with subqery in select list and subquery cache=off in + maria-5.3 |
Changed in maria: | |
assignee: | nobody → Timour Katchaounov (timour) |
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 |
Another test case:
SET @@optimizer_ switch= 'materializatio n=off,subquery_ cache=off, semijoin= off';
CREATE TABLE t1 ( f11 varchar(32)) ;
INSERT IGNORE INTO t1 VALUES ('x'),('b');
CREATE TABLE t2 ( f2 int, f10 varchar(32)) ;
INSERT IGNORE INTO t2 VALUES (1,'x');
SELECT ( SELECT MAX( f2 ) FROM t2 WHERE t2.f10 <> t1.f11 ) FROM t1;