Wrong non-empty result with a constant table, aggregate function in subquery, MyISAM or Aria
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Invalid
|
Critical
|
Timour Katchaounov |
Bug Description
The following query
SELECT a FROM t1 WHERE ( SELECT MIN(a) = 100 );
if executed on a table with a single row, returns the row regardless the condition in WHERE -- it can be = <any number>, or IS NULL, or IS NOT NULL, -- and with any of MIN/MAX/
bzr version-info
revision-id: <email address hidden>
date: 2011-12-16 10:21:46 +0400
build-date: 2011-12-16 16:38:19 +0200
revno: 3356
branch-nick: maria-5.3
Also reproducible on revno 3250.
Not reproducible on 5.3.2, 5.2.10, MySQL 5.1.60.
Reproducible on MyISAM and Aria tables, but not on InnoDB.
Either in_to_exists or materialization is required, otherwise the query fails with ER_ILLEGAL_
EXPLAIN:
1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
1003 select 1 AS `a` from `test`.`t1` where (select (min(1) = 100))
Minimal optimizer_switch: in_to_exists=on or materialization=on (otherwise the query refuses to execute)
Full optimizer_switch: index_merge=
Test case:
# The table needs to be MyISAM or Aria
CREATE TABLE t1 ( a INT ) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1);
SELECT a FROM t1 WHERE ( SELECT MIN(a) = 100 );
# Expected result:
# a
#
# Actual result:
# a
# 1
Related branches
Changed in maria: | |
importance: | Undecided → High |
importance: | High → Undecided |
Changed in maria: | |
importance: | Undecided → Critical |
Changed in maria: | |
status: | New → In Progress |
Fixed by the following commit:
revno: 3380 5.3-lpb908269
committer: <email address hidden>
branch nick: work-maria-
timestamp: Tue 2012-01-10 23:26:00 +0200
message:
Fix for LP BUG#908269 Wrong result with subquery in select list, EXISTS, constant MyISAM/Aria table.
Problem: When building the condition for JOIN::outer_ ref_cond the optimizer forgot to take into account
that this condition could depend on constant tables as well.