Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-312

CHEAP SQ: A query with LEFT JOIN in outer SELECT and INNER JOIN and aggregate in subquery takes much longer than on the main tree

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Won't Fix
    • Affects Version/s: None
    • Fix Version/s: 5.5.27
    • Component/s: None
    • Labels:
      None

      Description

      The following query

      SELECT * FROM t1 LEFT JOIN t2 ON ( c = e )
      WHERE d IN ( SELECT SUM(a) FROM t1, t2 WHERE f = b );

      with ~550 rows in t1 and ~270 rows in t2 with the default optimizer_switch takes about 0.15 sec on the main 5.5 tree, and 6 sec on MDEV-193 tree.

      5.5-timour:

      date: 2012-06-01 14:10:15 +0300
      revno: 3407

      maria/5.5:

      revision-id: wlad@montyprogram.com-20120530182054-g7sll9u4gsitvgqe
      date: 2012-05-30 20:20:54 +0200
      revno: 3425

      optimizer_switch:

      index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off

      Also reproducible with all OFF values (except for in_to_exists which is required). With the minimal optimizer_switch execution time on the main tree is about the same, while on MDEV-193 it grows up to 20+ seconds.

      Reproducible with MyISAM, Aria, InnoDB.

      EXPLAIN on MDEV-193 tree:

       
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	274	100.00	Using where
      1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	655	100.00	Using where; Using join buffer (flat, BNL join)
      2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	274	100.00	
      2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	655	100.00	Using where; Using join buffer (flat, BNL join)
      Warnings:
      Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t2`.`e` AS `e`,`test`.`t2`.`f` AS `f` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`c` = `test`.`t2`.`e`) and <expr_cache><`test`.`t2`.`d`>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select sum(`test`.`t1`.`a`) from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`b` = `test`.`t2`.`f`) having (<cache>(`test`.`t2`.`d`) = <ref_null_helper>(sum(`test`.`t1`.`a`)))))))
      SELECT * FROM t1 LEFT JOIN t2 ON ( c = e )
      WHERE d IN ( SELECT SUM(a) FROM t1, t2 WHERE f = b );

      EXPLAIN on maria/5.5:

       
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	655	100.00	
      1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	274	100.00	Using where; Using join buffer (flat, BNL join)
      2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	274	100.00	
      2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	655	100.00	Using where; Using join buffer (flat, BNL join)
      Warnings:
      Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t2`.`e` AS `e`,`test`.`t2`.`f` AS `f` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`e` = `test`.`t1`.`c`)) where <expr_cache><`test`.`t2`.`d`>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select sum(`test`.`t1`.`a`) from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`b` = `test`.`t2`.`f`) having (<cache>(`test`.`t2`.`d`) = <ref_null_helper>(sum(`test`.`t1`.`a`))))))

      Test case:

       
      SET optimizer_switch = 'in_to_exists=on';
       
      CREATE TABLE t1 (a INT, b VARCHAR(3), c VARCHAR(3));
      INSERT INTO t1 VALUES 
      (12,'N/A','Abi'),(22,'USA','Akr'),(9,'USA','Alb'),(45,'USA','Alb'),
      (13,'USA','Ale'),(11,'N/A','All'),(17,'CAN','Ama'),(33,'USA','Ana'),
      (26,'FRA','Anc'),(11,'USA','Ann'),(9,'USA','Ard'),(33,'USA','Arl'),
      (17,'USA','Arl'),(10,'CAN','Arv'),(10,'USA','Ath'),(42,'USA','Atl'),
      (20,'USA','Aug'),(28,'USA','Aur'),(14,'N/A','Aur'),(66,'USA','Aus'),
      (25,'USA','Bak'),(65,'USA','Bal'),(23,'USA','Bat'),(11,'USA','Bea'),
      (11,'CAN','Bel'),(10,'FRA','Ber'),(9,'USA','Bil'),(24,'USA','Bir'),
      (19,'USA','Boi'),(59,'N/A','Bos'),(9,'USA','Bou'),(14,'USA','Bri'),
      (9,'USA','Bro'),(14,'USA','Bro'),(29,'USA','Buf'),(10,'USA','Bur'),
      (10,'USA','Cam'),(10,'USA','Cap'),(11,'USA','Car'),(9,'USA','Car'),
      (9,'USA','Car'),(12,'USA','Ced'),(18,'USA','Cha'),(9,'USA','Cha'),
      (54,'USA','Cha'),(16,'USA','Cha'),(20,'USA','Che'),(290,'USA','Chi'),
      (17,'USA','Chu'),(33,'USA','Cin'),(10,'USA','Cit'),(11,'FRA','Cla'),
      (10,'CAN','Cle'),(48,'USA','Cle'),(36,'USA','Col'),(12,'USA','Col'),
      (71,'USA','Col'),(19,'N/A','Col'),(9,'USA','Com'),(12,'USA','Con'),
      (12,'USA','Cor'),(12,'USA','Cor'),(28,'USA','Cor'),(11,'USA','Cos'),
      (119,'USA','Dal'),(10,'USA','Dal'),(10,'CAN','Dav'),(17,'USA','Day'),
      (55,'USA','Den'),(20,'USA','Des'),(95,'USA','Det'),(11,'USA','Dow'),
      (19,'USA','Dur'),(13,'USA','Eas'),(9,'USA','El '),(12,'USA','El '),
      (56,'N/A','El '),(9,'USA','Elg'),(12,'USA','Eli'),(10,'CAN','Eri'),
      (13,'USA','Esc'),(14,'USA','Eug'),(12,'USA','Eva'),(9,'USA','Fai'),
      (9,'USA','Fal'),(12,'USA','Fay'),(12,'USA','Fli'),(13,'FRA','Fon'),
      (12,'USA','For'),(15,'USA','For'),(21,'USA','For'),(53,'USA','For'),
      (20,'USA','Fre'),(43,'USA','Fre'),(13,'USA','Ful'),(9,'USA','Gai'),
      (17,'USA','Gar'),(22,'N/A','Gar'),(10,'USA','Gar'),(11,'USA','Gil'),
      (22,'USA','Gle'),(19,'USA','Gle'),(13,'CAN','Gra'),(20,'USA','Gra'),
      (10,'USA','Gre'),(22,'USA','Gre'),(15,'USA','Ham'),(12,'USA','Har'),
      (14,'USA','Hay'),(18,'USA','Hen'),(23,'USA','Hia'),(14,'USA','Hol'),
      (37,'USA','Hon'),(195,'USA','Hou'),(19,'USA','Hun'),(16,'USA','Hun'),
      (11,'USA','Ind'),(79,'FRA','Ind'),(11,'USA','Ing'),(14,'N/A','Irv'),
      (19,'CAN','Irv'),(18,'USA','Jac'),(74,'USA','Jac'),(24,'USA','Jer'),
      (11,'USA','Jol'),(44,'USA','Kan'),(15,'USA','Kan'),(9,'USA','Ken'),
      (17,'USA','Kno'),(11,'USA','Laf'),(14,'USA','Lak'),(12,'USA','Lan'),
      (12,'USA','Lan'),(18,'USA','Lar'),(48,'FRA','Las'),(26,'USA','Lex'),
      (23,'N/A','Lin'),(18,'USA','Lit'),(10,'USA','Liv'),(46,'USA','Lon'),
      (369,'USA','Los'),(26,'USA','Lou'),(11,'USA','Low'),(20,'USA','Lub'),
      (11,'USA','Mac'),(21,'USA','Mad'),(11,'USA','Man'),(11,'USA','McA'),
      (65,'USA','Mem'),(40,'USA','Mes'),(12,'USA','Mes'),(15,'N/A','Met'),
      (36,'USA','Mia'),(10,'FRA','Mia'),(10,'USA','Mid'),(60,'USA','Mil'),
      (38,'CAN','Min'),(10,'USA','Mis'),(20,'USA','Mob'),(19,'USA','Mod'),
      (20,'USA','Mon'),(14,'N/A','Mor'),(13,'USA','Nap'),(57,'USA','Nas'),
      (9,'USA','New'),(12,'USA','New'),(48,'USA','New'),(801,'USA','New'),
      (27,'N/A','New'),(18,'USA','New'),(23,'USA','Nor'),(9,'CAN','Nor'),
      (12,'USA','Nor'),(10,'FRA','Nor'),(40,'USA','Oak'),(16,'USA','Oce'),
      (9,'USA','Ode'),(51,'USA','Okl'),(39,'USA','Oma'),(16,'USA','Ont'),
      (13,'USA','Ora'),(19,'USA','Orl'),(15,'FRA','Ove'),(17,'N/A','Oxn'),
      (12,'USA','Pal'),(12,'USA','Par'),(14,'USA','Pas'),(13,'USA','Pas'),
      (15,'CAN','Pat'),(14,'USA','Pem'),(11,'USA','Peo'),(11,'USA','Peo'),
      (152,'N/A','Phi'),(132,'USA','Pho'),(33,'USA','Pit'),(22,'USA','Pla'),
      (15,'USA','Pom'),(53,'USA','Por'),(10,'USA','Por'),(17,'USA','Pro'),
      (11,'USA','Pro'),(10,'USA','Pue'),(28,'FRA','Ral'),(13,'CAN','Ran'),
      (18,'USA','Ren'),(20,'USA','Ric'),(9,'USA','Ric'),(26,'USA','Riv'),
      (9,'USA','Roa'),(22,'N/A','Roc'),(15,'USA','Roc'),(41,'USA','Sac'),
      (35,'USA','Sai'),(29,'USA','Sai'),(25,'USA','Sai'),(14,'USA','Sal'),
      (15,'USA','Sal'),(18,'USA','Sal'),(114,'FRA','San'),(19,'USA','San'),
      (10,'USA','San'),(122,'USA','San'),(78,'CAN','San'),(89,'USA','San'),
      (9,'USA','San'),(10,'USA','San'),(34,'USA','San'),(10,'USA','San'),
      (15,'N/A','San'),(9,'USA','San'),(15,'USA','San'),(13,'USA','Sav'),
      (20,'USA','Sco'),(56,'FRA','Sea'),(20,'CAN','Shr'),(11,'USA','Sim'),
      (12,'USA','Sio'),(11,'USA','Sou'),(20,'USA','Spo'),(15,'USA','Spr'),
      (15,'USA','Spr'),(11,'USA','Spr'),(12,'USA','Sta'),(12,'USA','Ste'),
      (24,'USA','Sto'),(13,'USA','Sun'),(10,'FRA','Sun'),(15,'USA','Syr'),
      (19,'N/A','Tac'),(15,'USA','Tal'),(30,'USA','Tam'),(16,'USA','Tem'),
      (12,'CAN','Tho'),(31,'USA','Tol'),(12,'USA','Top'),(14,'USA','Tor'),
      (49,'USA','Tuc'),(39,'FRA','Tul'),(12,'USA','Val'),(14,'USA','Van'),
      (43,'USA','Vir'),(9,'USA','Vis'),(11,'USA','Wac'),(14,'USA','War'),
      (57,'USA','Was'),(11,'USA','Wat'),(11,'FRA','Wes'),(11,'USA','Wes'),
      (10,'USA','Wes'),(34,'USA','Wic'),(10,'USA','Wic'),(19,'USA','Win'),
      (17,'FRA','Wor'),(20,'USA','Yon');
       
      CREATE TABLE t2 (d INT, e VARCHAR(3), f VARCHAR(3));
      INSERT INTO t2 SELECT * FROM t1;
       
      INSERT INTO t1 SELECT * FROM t1;
       
      SELECT * FROM t1 LEFT JOIN t2 ON ( c = e )
      WHERE d IN ( SELECT SUM(a) FROM t1, t2 WHERE f = b );
       

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                timour Timour Katchaounov (Inactive)
                Reporter:
                elenst Elena Stepanova
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: