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

MWL#253: Server hangs in table_cond_selectivity with optimizer_use_condition_selectivity>1, IN subquery with aggregate function



    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 10.0.2
    • Component/s: None
    • Labels:


      SET use_stat_tables=PREFERABLY;
      SET optimizer_use_condition_selectivity=3;
      CREATE TABLE t1 (a INT);
      INSERT INTO t1 VALUES (5),(9);
      CREATE TABLE t2 (b VARCHAR(8));
      INSERT INTO t2 VALUES ('red'),('blue');
      CREATE TABLE t3 (c VARCHAR(8), d VARCHAR(8));
      INSERT INTO t3 VALUES ('white','black'),('cyan','yellow');
      ANALYZE TABLE t1, t2, t3;
      SELECT * FROM t1, t2 WHERE ( 'orange', 'green' ) IN ( 
        SELECT MAX(c), MAX(d) FROM t3, t2 WHERE c >= d AND b = c 

      The last query hangs, seemingly forever, in 'statistics' state:

      MariaDB [test]> show full processlist \G
      *************************** 1. row ***************************
            Id: 2
          User: root
          Host: localhost
            db: test
       Command: Query
          Time: 37
         State: statistics
          Info: SELECT * FROM t1, t2 WHERE ( 'orange', 'green' ) IN ( 
      SELECT MAX(c), MAX(d) FROM t3, t2 WHERE c >= d AND b = c 
      Progress: 0.000

      EXPLAIN behaves the same way.
      Reproducible with MyISAM, Aria, InnoDB.

      CPU usage 100%

      Stack trace from the running server:

      #0  0x0000000000658977 in table_cond_selectivity (join=0x7f693010e480, idx=0, s=0x7f6930012e60, rem_tables=3) at /data/bzr/maria-10.0-mwl253/sql/sql_select.cc:7095
      #1  0x0000000000659092 in best_extension_by_limited_search (join=0x7f693010e480, remaining_tables=7, idx=0, record_count=1, read_time=0, search_depth=62, prune_level=1, use_cond_selectivity=3) at /data/bzr/maria-10.0-mwl253/sql/sql_select.cc:7344
      #2  0x000000000065775d in greedy_search (join=0x7f693010e480, remaining_tables=7, search_depth=62, prune_level=1, use_cond_selectivity=3) at /data/bzr/maria-10.0-mwl253/sql/sql_select.cc:6665
      #3  0x0000000000656d63 in choose_plan (join=0x7f693010e480, join_tables=7) at /data/bzr/maria-10.0-mwl253/sql/sql_select.cc:6242
      #4  0x0000000000650c6d in make_join_statistics (join=0x7f693010e480, tables_list=..., conds=0x7f6930011f50, keyuse_array=0x7f693010e7b8) at /data/bzr/maria-10.0-mwl253/sql/sql_select.cc:3899
      #5  0x0000000000647b0d in JOIN::optimize_inner (this=0x7f693010e480) at /data/bzr/maria-10.0-mwl253/sql/sql_select.cc:1306
      #6  0x0000000000646ab8 in JOIN::optimize (this=0x7f693010e480) at /data/bzr/maria-10.0-mwl253/sql/sql_select.cc:1008
      #7  0x000000000064e76d in mysql_select (thd=0x3ec2608, rref_pointer_array=0x3ec55b0, tables=0x7f6930040f10, wild_num=1, fields=..., conds=0x7f6930006ef0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f6930002e40, unit=0x3ec4c08, select_lex=0x3ec5300) at /data/bzr/maria-10.0-mwl253/sql/sql_select.cc:3220
      #8  0x0000000000644f23 in handle_select (thd=0x3ec2608, lex=0x3ec4b50, result=0x7f6930002e40, setup_tables_done_option=0) at /data/bzr/maria-10.0-mwl253/sql/sql_select.cc:376
      #9  0x000000000061c3d4 in execute_sqlcom_select (thd=0x3ec2608, all_tables=0x7f6930040f10) at /data/bzr/maria-10.0-mwl253/sql/sql_parse.cc:4797
      #10 0x00000000006149d4 in mysql_execute_command (thd=0x3ec2608) at /data/bzr/maria-10.0-mwl253/sql/sql_parse.cc:2266
      #11 0x000000000061ec76 in mysql_parse (thd=0x3ec2608, rawbuf=0x7f69300f5860 "SELECT * FROM t1, t2 WHERE ( 'orange', 'green' ) IN ( \nSELECT MAX(c), MAX(d) FROM t3, t2 WHERE c >= d AND b = c \n)", length=114, parser_state=0x7f6948a4d4f0) at /data/bzr/maria-10.0-mwl253/sql/sql_parse.cc:5920
      #12 0x0000000000611cf8 in dispatch_command (command=COM_QUERY, thd=0x3ec2608, packet=0x3ec75e9 "SELECT * FROM t1, t2 WHERE ( 'orange', 'green' ) IN ( \nSELECT MAX(c), MAX(d) FROM t3, t2 WHERE c >= d AND b = c \n)", packet_length=114) at /data/bzr/maria-10.0-mwl253/sql/sql_parse.cc:1091
      #13 0x0000000000610f26 in do_command (thd=0x3ec2608) at /data/bzr/maria-10.0-mwl253/sql/sql_parse.cc:810
      #14 0x0000000000729f64 in do_handle_one_connection (thd_arg=0x3ec2608) at /data/bzr/maria-10.0-mwl253/sql/sql_connect.cc:1266
      #15 0x000000000072994f in handle_one_connection (arg=0x3ec2608) at /data/bzr/maria-10.0-mwl253/sql/sql_connect.cc:1181
      #16 0x0000000000c59fe6 in pfs_spawn_thread (arg=0x3e637e8) at /data/bzr/maria-10.0-mwl253/storage/perfschema/pfs.cc:1015
      #17 0x00007f694c683e9a in start_thread (arg=0x7f6948a4e700) at pthread_create.c:308
      #18 0x00007f694b977cbd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112


          Issue Links



              igor Igor Babaev
              elenst Elena Stepanova
              0 Vote for this issue
              2 Start watching this issue