|
--source include/have_innodb.inc
|
|
# InnoDB stats and ANALYZE are optional, the result is the same either way
|
SET @stats= @@innodb_stats_persistent;
|
SET GLOBAL innodb_stats_persistent= ON;
|
|
CREATE OR REPLACE TABLE t (
|
a VARCHAR(8),
|
b VARCHAR(8) AS (a),
|
c CHAR(8),
|
id INT PRIMARY KEY,
|
KEY ind(a,b),
|
KEY (c)
|
) ENGINE=InnoDB;
|
|
INSERT INTO t (id,a,c) VALUES (1,'bow','auto'),(2,'cow','no'),(3,'wow','yes');
|
|
ANALYZE TABLE t;
|
|
SET optimizer_switch= 'rowid_filter=on';
|
SELECT a, COUNT(*) FROM t WHERE c > 'good' AND c > a GROUP BY a HAVING a >= 'low';
|
c > a GROUP BY a HAVING a >= 'low';
|
|
SET optimizer_switch= 'rowid_filter=off';
|
SELECT a, COUNT(*) FROM t WHERE c > 'good' AND c > a GROUP BY a HAVING a >= 'low';
|
c > a GROUP BY a HAVING a >= 'low';
|
|
# Cleanup
|
DROP TABLE t;
|
SET GLOBAL innodb_stats_persistent= @stats;
|
With rowid_filter=on the result is empty:
|
10.5 2776635c
|
SET optimizer_switch= 'rowid_filter=on';
|
SELECT a, COUNT(*) FROM t WHERE c > 'good' AND c > a GROUP BY a HAVING a >= 'low';
|
a COUNT(*)
|
With rowid_filter=off the query returns a row:
SET optimizer_switch= 'rowid_filter=off';
|
SELECT a, COUNT(*) FROM t WHERE c > 'good' AND c > a GROUP BY a HAVING a >= 'low';
|
a COUNT(*)
|
wow 1
|
The latter is the expected result.
Plans for the queries, accordingly:
|
rowid_filter=on
|
EXPLAIN EXTENDED SELECT SQL_NO_CACHE a, COUNT(*) FROM t WHERE c > 'good' AND c > a GROUP BY a HAVING a >= 'low';
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 SIMPLE t range|filter ind,c ind|c 11|9 NULL 1 (67%) 66.67 Using where; Using rowid filter
|
Warnings:
|
Note 1003 select sql_no_cache `test`.`t`.`a` AS `a`,count(0) AS `COUNT(*)` from `test`.`t` where `test`.`t`.`c` > 'good' and `test`.`t`.`c` > `test`.`t`.`a` and `test`.`t`.`a` >= 'low' group by `test`.`t`.`a` having 1
|
|
rowid_filter=off
|
EXPLAIN EXTENDED SELECT SQL_NO_CACHE a, COUNT(*) FROM t WHERE c > 'good' AND c > a GROUP BY a HAVING a >= 'low';
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 SIMPLE t range ind,c ind 11 NULL 1 66.67 Using where
|
Warnings:
|
Note 1003 select sql_no_cache `test`.`t`.`a` AS `a`,count(0) AS `COUNT(*)` from `test`.`t` where `test`.`t`.`c` > 'good' and `test`.`t`.`c` > `test`.`t`.`a` and `test`.`t`.`a` >= 'low' group by `test`.`t`.`a` having 1
|
Reproducible on 10.5+ with InnoDB.
Not reproducible on 10.4.
Not reproducible with MyISAM.
|