[MDEV-12124] Probably missing optimization on MIN/MAX with LEFT JOIN on tmptable views Created: 2017-02-24  Updated: 2022-11-29

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.1.12
Fix Version/s: 10.3

Type: Bug Priority: Minor
Reporter: Julien Palard Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: view
Environment:

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.

 Comments   
Comment by Elena Stepanova [ 2017-03-03 ]

Thanks for the report. Reproducible is described, I'll leave it to psergey to decide whether there is a bug in here.

Generated at Thu Feb 08 07:55:18 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.