Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.1.12
-
Debian GNU/Linux 8.4 (jessie)
Description
I think there's a possible optimisation that is not done in this case:
Some setup:
CREATE TABLE test_a (a int, KEY (a));
|
CREATE TABLE test_b (a int, KEY (a));
|
INSERT INTO test_a VALUES (1), (2), (4), (8), (16), (32), (64), (128);
|
INSERT INTO test_b VALUES (1), (2), (4), (8), (16), (32), (64), (128);
|
CREATE VIEW test_c_view AS select a from test_b; -- will probably be a merged view
|
CREATE VIEW test_d_view AS select a from test_b group by a; -- will probably be a tmptable view
|
The expected behavior:
EXPLAIN SELECT MAX(a) FROM test_a LEFT JOIN test_b USING(a);
|
...Select tables optimized away...
|
Still works with merged view:
EXPLAIN SELECT MAX(a) FROM test_a LEFT JOIN test_c_view USING(a);
|
...Select tables optimized away...
|
But won't work with TMPTABLE views and I don't understand why:
EXPLAIN SELECT MAX(a) FROM test_a LEFT JOIN test_d_view USING(a);
|
...Using index, join on the DERIVED table...
|
As far as I can see, the test_d_view can contain whatever is possible, it won't be able to change the MAX(a) value, so it should be optimized out too.