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

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • None
    • 10.0.2
    • None
    • None

    Description

      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;
      FLUSH TABLES;
       
      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

      Attachments

        Issue Links

          Activity

            elenst Elena Stepanova added a comment - - edited

            I observed a similar-looking "hanging" on a custom tree based on MySQL 5.6.10 (with a different query) - of course, without selectivity, but possibly they have the same origin, and selectivity just reveals the problem on the small test case.

            The stack trace from MySQL shows some movement,

            From MySQL:

            8 root 127.0.0.1:37745 test Query 671 statistics SELECT alias2 . `col_varchar_10_latin1` AS field1 FROM BB AS alias1 RIGHT JOIN M AS alias2 LE
            9 root 127.0.0.1:37747 test Query 325 statistics SELECT alias1 . `col_varchar_1024_latin1` AS field1 , alias1 . `col_varchar_1024_utf8` AS field2 , a

            Thread 3 (Thread 0x7f40f238a700 (LWP 15293)):
            #0 __lll_unlock_wake () at ../nptl/sysdeps/unix/sysv/linux/x86_64/lowlevellock.S:368
            #1 0x00007f41085c72fc in pthread_rwlock_rdlock () at ../nptl/sysdeps/unix/sysv/linux/x86_64/pthread_rwlock_rdlock.S:136
            #2 0x0000000000db7096 in read_lock_stack (cs=0x7f40b00009a0) at dbug/dbug.c:424
            #3 0x0000000000db9982 in db_return (line=1059, stack_frame=0x7f40f2382910) at dbug/dbug.c:1300
            #4 0x000000000099321b in Optimize_table_order::best_access_path (this=0x7f40f2387160, s=0x7f40b01e6df8, remaining_tables=26626, idx=14, disable_jbuf=false, record_count=1.2884080000000001e+30, pos=0x7f40b01e8ef8, loose_scan_pos=0x7f40f2382d60) at sql/sql_planner.cc:1059
            #5 0x00000000009948f8 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=26626, idx=14, record_count=1.2884080000000001e+30, read_time=9.2016425319781671e+29, current_search_depth=48) at sql/sql_planner.cc:1841
            #6 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=27138, idx=13, record_count=1.6105100000000001e+29, read_time=3.2773243197816783e+28, current_search_depth=49) at sql/sql_planner.cc:1976
            #7 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=27170, idx=12, record_count=1.6105100000000001e+26, read_time=2.1245205836670787e+26, current_search_depth=50) at sql/sql_planner.cc:1976
            #8 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=158242, idx=11, record_count=1.4641000000000001e+25, read_time=1.9190858366707861e+25, current_search_depth=51) at sql/sql_planner.cc:1976
            #9 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=223778, idx=10, record_count=1.3310000000000001e+24, read_time=1.6216583667078606e+24, current_search_depth=52) at sql/sql_planner.cc:1976
            #10 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=223786, idx=9, record_count=1.2100000000000001e+23, read_time=2.4458366707860238e+22, current_search_depth=53) at sql/sql_planner.cc:1976
            #11 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=223850, idx=8, record_count=1.21e+20, read_time=2.4429558812383281e+19, current_search_depth=54) at sql/sql_planner.cc:1976
            #12 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=227946, idx=7, record_count=1.21e+17, read_time=24562627080547260, current_search_depth=55) at sql/sql_planner.cc:1976
            #13 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=228202, idx=6, record_count=121000000000000, read_time=158600209819661.25, current_search_depth=56) at sql/sql_planner.cc:1976
            #14 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=228203, idx=5, record_count=11000000000000, read_time=13400209819661.268, current_search_depth=57) at sql/sql_planner.cc:1976
            #15 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=260971, idx=4, record_count=1000000000000, read_time=200209819661.2677, current_search_depth=58) at sql/sql_planner.cc:1976
            #16 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=261099, idx=3, record_count=1000000000, read_time=200206564.1583252, current_search_depth=59) at sql/sql_planner.cc:1976
            #17 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=262123, idx=2, record_count=1000000, read_time=200324.34875488281, current_search_depth=60) at sql/sql_planner.cc:1976
            #18 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=262139, idx=1, record_count=1000, read_time=260, current_search_depth=61) at sql/sql_planner.cc:1976
            #19 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=262143, idx=0, record_count=1, read_time=0, current_search_depth=62) at sql/sql_planner.cc:1976
            #20 0x0000000000993e07 in Optimize_table_order::greedy_search (this=0x7f40f2387160, remaining_tables=262143) at sql/sql_planner.cc:1466
            #21 0x0000000000993626 in Optimize_table_order::choose_table_order (this=0x7f40f2387160) at sql/sql_planner.cc:1139
            #22 0x000000000096eb0e in make_join_statistics (join=0x7f40b01e0e00, tables_arg=0x7f40b000bb20, conds=0x7f40b01e3378, keyuse_array=0x7f40b01e1028, first_optimization=true) at sql/sql_optimizer.cc:3740
            #23 0x0000000000965b84 in JOIN::optimize (this=0x7f40b01e0e00) at sql/sql_optimizer.cc:379
            #24 0x00000000007cdb56 in mysql_execute_select (thd=0x28af1c0, select_lex=0x28b19a8, free_join=true) at sql/sql_select.cc:1086
            #25 0x00000000007cde8c in mysql_select (thd=0x28af1c0, tables=0x7f40b000bb20, wild_num=0, fields=..., conds=0x7f40b01e0ad0, order=0x28b1b70, group=0x28b1aa8, having=0x0, select_options=2148272640, result=0x7f40b01e0dd8, unit=0x28b1368, select_lex=0x28b19a8) at sql/sql_select.cc:1221
            #26 0x00000000007cbfda in handle_select (thd=0x28af1c0, result=0x7f40b01e0dd8, setup_tables_done_option=0) at sql/sql_select.cc:110
            #27 0x00000000007a8697 in execute_sqlcom_select (thd=0x28af1c0, all_tables=0x7f40b000bb20) at sql/sql_parse.cc:5019
            #28 0x00000000007a1509 in mysql_execute_command (thd=0x28af1c0) at sql/sql_parse.cc:2589
            #29 0x00000000007aaafb in mysql_parse (thd=0x28af1c0, rawbuf=0x7f40b000a250 "SELECT alias1 . `col_varchar_1024_latin1` AS field1 , alias1 . `col_varchar_1024_utf8` AS field2 , alias1 . `col_date` AS field3 , alias1 . `col_int` AS field4 FROM P AS alias1 LEFT OUTER JOIN W A"..., length=2336, parser_state=0x7f40f2389110) at sql/sql_parse.cc:6149
            #30 0x000000000079e822 in dispatch_command (command=COM_QUERY, thd=0x28af1c0, packet=0x28b2e11 " SELECT alias1 . `col_varchar_1024_latin1` AS field1 , alias1 . `col_varchar_1024_utf8` AS field2 , alias1 . `col_date` AS field3 , alias1 . `col_int` AS field4 FROM P AS alias1 LEFT OUTER JOIN W"..., packet_length=2340) at sql/sql_parse.cc:1334
            #31 0x000000000079d972 in do_command (thd=0x28af1c0) at sql/sql_parse.cc:1036
            #32 0x0000000000768af5 in do_handle_one_connection (thd_arg=0x28af1c0) at sql/sql_connect.cc:969
            #33 0x000000000076851a in handle_one_connection (arg=0x28af1c0) at sql/sql_connect.cc:885
            #34 0x0000000000d8a168 in pfs_spawn_thread (arg=0x286e530) at storage/perfschema/pfs.cc:1853
            #35 0x00007f41085c3e9a in start_thread (arg=0x7f40f238a700) at pthread_create.c:308
            #36 0x00007f4107adecbd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
            #37 0x0000000000000000 in ?? ()

            A couple min later:

            Thread 3 (Thread 0x7f40f238a700 (LWP 15293)):
            #0 __lll_lock_wait () at ../nptl/sysdeps/unix/sysv/linux/x86_64/lowlevellock.S:132
            #1 0x00007f41085c72e1 in pthread_rwlock_rdlock () at ../nptl/sysdeps/unix/sysv/linux/x86_64/pthread_rwlock_rdlock.S:120
            #2 0x0000000000db7096 in read_lock_stack (cs=0x7f40b00009a0) at dbug/dbug.c:424
            #3 0x0000000000db96d1 in db_enter (func=0xfcdb78 "Optimize_table_order::best_access_path", file=0xfcdb48 "sql/sql_planner.cc", line=429, stack_frame=0x7f40f2382500) at dbug/dbug.c:1229
            #4 0x0000000000991266 in Optimize_table_order::best_access_path (this=0x7f40f2387160, s=0x7f40b01e7ff8, remaining_tables=213024, idx=14, disable_jbuf=false, record_count=1.6105100000000001e+29, pos=0x7f40b01e8ef8, loose_scan_pos=0x7f40f2382950) at sql/sql_planner.cc:429
            #5 0x00000000009948f8 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=213024, idx=14, record_count=1.6105100000000001e+29, read_time=3.2823793261380907e+28, current_search_depth=48) at sql/sql_planner.cc:1841
            #6 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=213026, idx=13, record_count=1.6105100000000001e+26, read_time=3.2948573270550628e+25, current_search_depth=49) at sql/sql_planner.cc:1976
            #7 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=217122, idx=12, record_count=1.6105100000000001e+23, read_time=1.9624900518502574e+23, current_search_depth=50) at sql/sql_planner.cc:1976
            #8 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=225314, idx=11, record_count=1.4641e+22, read_time=2.9878051850257181e+21, current_search_depth=51) at sql/sql_planner.cc:1976
            #9 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=225570, idx=10, record_count=1.4641e+19, read_time=2.076641167977035e+19, current_search_depth=52) at sql/sql_planner.cc:1976
            #10 0x0000000000995ac6 in Optimize_table_order::eq_ref_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=225570, idx=10, record_count=1.4641e+19, read_time=2.076641167977035e+19, current_search_depth=52) at sql/sql_planner.cc:2286
            #11 0x0000000000994d63 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=226082, idx=9, record_count=1.4641e+19, read_time=1.7838211679770339e+19, current_search_depth=53) at sql/sql_planner.cc:1950
            #12 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=228130, idx=8, record_count=1.331e+18, read_time=2.6901167977034016e+17, current_search_depth=54) at sql/sql_planner.cc:1976
            #13 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=228258, idx=7, record_count=1331000000000000, read_time=270358511795177, current_search_depth=55) at sql/sql_planner.cc:1976
            #14 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=229282, idx=6, record_count=1331000000000, read_time=1621760162426.585, current_search_depth=56) at sql/sql_planner.cc:1976
            #15 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=229290, idx=5, record_count=121000000000, read_time=24560162426.585083, current_search_depth=57) at sql/sql_planner.cc:1976
            #16 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=229354, idx=4, record_count=121000000, read_time=158600324.34875488, current_search_depth=58) at sql/sql_planner.cc:1976
            #17 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=229355, idx=3, record_count=11000000, read_time=13400324.348754883, current_search_depth=59) at sql/sql_planner.cc:1976
            #18 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=262123, idx=2, record_count=1000000, read_time=200324.34875488281, current_search_depth=60) at sql/sql_planner.cc:1976
            #19 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=262139, idx=1, record_count=1000, read_time=260, current_search_depth=61) at sql/sql_planner.cc:1976
            #20 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=262143, idx=0, record_count=1, read_time=0, current_search_depth=62) at sql/sql_planner.cc:1976
            #21 0x0000000000993e07 in Optimize_table_order::greedy_search (this=0x7f40f2387160, remaining_tables=262143) at sql/sql_planner.cc:1466
            #22 0x0000000000993626 in Optimize_table_order::choose_table_order (this=0x7f40f2387160) at sql/sql_planner.cc:1139
            #23 0x000000000096eb0e in make_join_statistics (join=0x7f40b01e0e00, tables_arg=0x7f40b000bb20, conds=0x7f40b01e3378, keyuse_array=0x7f40b01e1028, first_optimization=true) at sql/sql_optimizer.cc:3740
            #24 0x0000000000965b84 in JOIN::optimize (this=0x7f40b01e0e00) at sql/sql_optimizer.cc:379
            #25 0x00000000007cdb56 in mysql_execute_select (thd=0x28af1c0, select_lex=0x28b19a8, free_join=true) at sql/sql_select.cc:1086
            #26 0x00000000007cde8c in mysql_select (thd=0x28af1c0, tables=0x7f40b000bb20, wild_num=0, fields=..., conds=0x7f40b01e0ad0, order=0x28b1b70, group=0x28b1aa8, having=0x0, select_options=2148272640, result=0x7f40b01e0dd8, unit=0x28b1368, select_lex=0x28b19a8) at sql/sql_select.cc:1221
            #27 0x00000000007cbfda in handle_select (thd=0x28af1c0, result=0x7f40b01e0dd8, setup_tables_done_option=0) at sql/sql_select.cc:110
            #28 0x00000000007a8697 in execute_sqlcom_select (thd=0x28af1c0, all_tables=0x7f40b000bb20) at sql/sql_parse.cc:5019
            #29 0x00000000007a1509 in mysql_execute_command (thd=0x28af1c0) at sql/sql_parse.cc:2589
            #30 0x00000000007aaafb in mysql_parse (thd=0x28af1c0, rawbuf=0x7f40b000a250 "SELECT alias1 . `col_varchar_1024_latin1` AS field1 , alias1 . `col_varchar_1024_utf8` AS field2 , alias1 . `col_date` AS field3 , alias1 . `col_int` AS field4 FROM P AS alias1 LEFT OUTER JOIN W A"..., length=2336, parser_state=0x7f40f2389110) at sql/sql_parse.cc:6149
            #31 0x000000000079e822 in dispatch_command (command=COM_QUERY, thd=0x28af1c0, packet=0x28b2e11 " SELECT alias1 . `col_varchar_1024_latin1` AS field1 , alias1 . `col_varchar_1024_utf8` AS field2 , alias1 . `col_date` AS field3 , alias1 . `col_int` AS field4 FROM P AS alias1 LEFT OUTER JOIN W"..., packet_length=2340) at sql/sql_parse.cc:1334
            #32 0x000000000079d972 in do_command (thd=0x28af1c0) at sql/sql_parse.cc:1036
            #33 0x0000000000768af5 in do_handle_one_connection (thd_arg=0x28af1c0) at sql/sql_connect.cc:969
            #34 0x000000000076851a in handle_one_connection (arg=0x28af1c0) at sql/sql_connect.cc:885
            #35 0x0000000000d8a168 in pfs_spawn_thread (arg=0x286e530) at storage/perfschema/pfs.cc:1853
            #36 0x00007f41085c3e9a in start_thread (arg=0x7f40f238a700) at pthread_create.c:308
            #37 0x00007f4107adecbd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112

            elenst Elena Stepanova added a comment - - edited I observed a similar-looking "hanging" on a custom tree based on MySQL 5.6.10 (with a different query) - of course, without selectivity, but possibly they have the same origin, and selectivity just reveals the problem on the small test case. The stack trace from MySQL shows some movement, From MySQL: 8 root 127.0.0.1:37745 test Query 671 statistics SELECT alias2 . `col_varchar_10_latin1` AS field1 FROM BB AS alias1 RIGHT JOIN M AS alias2 LE 9 root 127.0.0.1:37747 test Query 325 statistics SELECT alias1 . `col_varchar_1024_latin1` AS field1 , alias1 . `col_varchar_1024_utf8` AS field2 , a Thread 3 (Thread 0x7f40f238a700 (LWP 15293)): #0 __lll_unlock_wake () at ../nptl/sysdeps/unix/sysv/linux/x86_64/lowlevellock.S:368 #1 0x00007f41085c72fc in pthread_rwlock_rdlock () at ../nptl/sysdeps/unix/sysv/linux/x86_64/pthread_rwlock_rdlock.S:136 #2 0x0000000000db7096 in read_lock_stack (cs=0x7f40b00009a0) at dbug/dbug.c:424 #3 0x0000000000db9982 in db_return ( line =1059, stack_frame =0x7f40f2382910) at dbug/dbug.c:1300 #4 0x000000000099321b in Optimize_table_order::best_access_path (this=0x7f40f2387160, s=0x7f40b01e6df8, remaining_tables=26626, idx=14, disable_jbuf=false, record_count=1.2884080000000001e+30, pos=0x7f40b01e8ef8, loose_scan_pos=0x7f40f2382d60) at sql/sql_planner.cc:1059 #5 0x00000000009948f8 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=26626, idx=14, record_count=1.2884080000000001e+30, read_time=9.2016425319781671e+29, current_search_depth=48) at sql/sql_planner.cc:1841 #6 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=27138, idx=13, record_count=1.6105100000000001e+29, read_time=3.2773243197816783e+28, current_search_depth=49) at sql/sql_planner.cc:1976 #7 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=27170, idx=12, record_count=1.6105100000000001e+26, read_time=2.1245205836670787e+26, current_search_depth=50) at sql/sql_planner.cc:1976 #8 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=158242, idx=11, record_count=1.4641000000000001e+25, read_time=1.9190858366707861e+25, current_search_depth=51) at sql/sql_planner.cc:1976 #9 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=223778, idx=10, record_count=1.3310000000000001e+24, read_time=1.6216583667078606e+24, current_search_depth=52) at sql/sql_planner.cc:1976 #10 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=223786, idx=9, record_count=1.2100000000000001e+23, read_time=2.4458366707860238e+22, current_search_depth=53) at sql/sql_planner.cc:1976 #11 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=223850, idx=8, record_count=1.21e+20, read_time=2.4429558812383281e+19, current_search_depth=54) at sql/sql_planner.cc:1976 #12 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=227946, idx=7, record_count=1.21e+17, read_time=24562627080547260, current_search_depth=55) at sql/sql_planner.cc:1976 #13 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=228202, idx=6, record_count=121000000000000, read_time=158600209819661.25, current_search_depth=56) at sql/sql_planner.cc:1976 #14 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=228203, idx=5, record_count=11000000000000, read_time=13400209819661.268, current_search_depth=57) at sql/sql_planner.cc:1976 #15 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=260971, idx=4, record_count=1000000000000, read_time=200209819661.2677, current_search_depth=58) at sql/sql_planner.cc:1976 #16 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=261099, idx=3, record_count=1000000000, read_time=200206564.1583252, current_search_depth=59) at sql/sql_planner.cc:1976 #17 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=262123, idx=2, record_count=1000000, read_time=200324.34875488281, current_search_depth=60) at sql/sql_planner.cc:1976 #18 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=262139, idx=1, record_count=1000, read_time=260, current_search_depth=61) at sql/sql_planner.cc:1976 #19 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=262143, idx=0, record_count=1, read_time=0, current_search_depth=62) at sql/sql_planner.cc:1976 #20 0x0000000000993e07 in Optimize_table_order::greedy_search (this=0x7f40f2387160, remaining_tables=262143) at sql/sql_planner.cc:1466 #21 0x0000000000993626 in Optimize_table_order::choose_table_order (this=0x7f40f2387160) at sql/sql_planner.cc:1139 #22 0x000000000096eb0e in make_join_statistics (join=0x7f40b01e0e00, tables_arg=0x7f40b000bb20, conds=0x7f40b01e3378, keyuse_array=0x7f40b01e1028, first_optimization=true) at sql/sql_optimizer.cc:3740 #23 0x0000000000965b84 in JOIN::optimize (this=0x7f40b01e0e00) at sql/sql_optimizer.cc:379 #24 0x00000000007cdb56 in mysql_execute_select (thd=0x28af1c0, select_lex=0x28b19a8, free_join=true) at sql/sql_select.cc:1086 #25 0x00000000007cde8c in mysql_select (thd=0x28af1c0, tables=0x7f40b000bb20, wild_num=0, fields=..., conds=0x7f40b01e0ad0, order=0x28b1b70, group=0x28b1aa8, having=0x0, select_options=2148272640, result=0x7f40b01e0dd8, unit=0x28b1368, select_lex=0x28b19a8) at sql/sql_select.cc:1221 #26 0x00000000007cbfda in handle_select (thd=0x28af1c0, result=0x7f40b01e0dd8, setup_tables_done_option=0) at sql/sql_select.cc:110 #27 0x00000000007a8697 in execute_sqlcom_select (thd=0x28af1c0, all_tables=0x7f40b000bb20) at sql/sql_parse.cc:5019 #28 0x00000000007a1509 in mysql_execute_command (thd=0x28af1c0) at sql/sql_parse.cc:2589 #29 0x00000000007aaafb in mysql_parse (thd=0x28af1c0, rawbuf=0x7f40b000a250 "SELECT alias1 . `col_varchar_1024_latin1` AS field1 , alias1 . `col_varchar_1024_utf8` AS field2 , alias1 . `col_date` AS field3 , alias1 . `col_int` AS field4 FROM P AS alias1 LEFT OUTER JOIN W A"..., length=2336, parser_state=0x7f40f2389110) at sql/sql_parse.cc:6149 #30 0x000000000079e822 in dispatch_command (command=COM_QUERY, thd=0x28af1c0, packet=0x28b2e11 " SELECT alias1 . `col_varchar_1024_latin1` AS field1 , alias1 . `col_varchar_1024_utf8` AS field2 , alias1 . `col_date` AS field3 , alias1 . `col_int` AS field4 FROM P AS alias1 LEFT OUTER JOIN W"..., packet_length=2340) at sql/sql_parse.cc:1334 #31 0x000000000079d972 in do_command (thd=0x28af1c0) at sql/sql_parse.cc:1036 #32 0x0000000000768af5 in do_handle_one_connection (thd_arg=0x28af1c0) at sql/sql_connect.cc:969 #33 0x000000000076851a in handle_one_connection (arg=0x28af1c0) at sql/sql_connect.cc:885 #34 0x0000000000d8a168 in pfs_spawn_thread (arg=0x286e530) at storage/perfschema/pfs.cc:1853 #35 0x00007f41085c3e9a in start_thread (arg=0x7f40f238a700) at pthread_create.c:308 #36 0x00007f4107adecbd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112 #37 0x0000000000000000 in ?? () A couple min later: Thread 3 (Thread 0x7f40f238a700 (LWP 15293)): #0 __lll_lock_wait () at ../nptl/sysdeps/unix/sysv/linux/x86_64/lowlevellock.S:132 #1 0x00007f41085c72e1 in pthread_rwlock_rdlock () at ../nptl/sysdeps/unix/sysv/linux/x86_64/pthread_rwlock_rdlock.S:120 #2 0x0000000000db7096 in read_lock_stack (cs=0x7f40b00009a0) at dbug/dbug.c:424 #3 0x0000000000db96d1 in db_enter ( func =0xfcdb78 "Optimize_table_order::best_access_path", file =0xfcdb48 "sql/sql_planner.cc", line =429, stack_frame =0x7f40f2382500) at dbug/dbug.c:1229 #4 0x0000000000991266 in Optimize_table_order::best_access_path (this=0x7f40f2387160, s=0x7f40b01e7ff8, remaining_tables=213024, idx=14, disable_jbuf=false, record_count=1.6105100000000001e+29, pos=0x7f40b01e8ef8, loose_scan_pos=0x7f40f2382950) at sql/sql_planner.cc:429 #5 0x00000000009948f8 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=213024, idx=14, record_count=1.6105100000000001e+29, read_time=3.2823793261380907e+28, current_search_depth=48) at sql/sql_planner.cc:1841 #6 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=213026, idx=13, record_count=1.6105100000000001e+26, read_time=3.2948573270550628e+25, current_search_depth=49) at sql/sql_planner.cc:1976 #7 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=217122, idx=12, record_count=1.6105100000000001e+23, read_time=1.9624900518502574e+23, current_search_depth=50) at sql/sql_planner.cc:1976 #8 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=225314, idx=11, record_count=1.4641e+22, read_time=2.9878051850257181e+21, current_search_depth=51) at sql/sql_planner.cc:1976 #9 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=225570, idx=10, record_count=1.4641e+19, read_time=2.076641167977035e+19, current_search_depth=52) at sql/sql_planner.cc:1976 #10 0x0000000000995ac6 in Optimize_table_order::eq_ref_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=225570, idx=10, record_count=1.4641e+19, read_time=2.076641167977035e+19, current_search_depth=52) at sql/sql_planner.cc:2286 #11 0x0000000000994d63 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=226082, idx=9, record_count=1.4641e+19, read_time=1.7838211679770339e+19, current_search_depth=53) at sql/sql_planner.cc:1950 #12 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=228130, idx=8, record_count=1.331e+18, read_time=2.6901167977034016e+17, current_search_depth=54) at sql/sql_planner.cc:1976 #13 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=228258, idx=7, record_count=1331000000000000, read_time=270358511795177, current_search_depth=55) at sql/sql_planner.cc:1976 #14 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=229282, idx=6, record_count=1331000000000, read_time=1621760162426.585, current_search_depth=56) at sql/sql_planner.cc:1976 #15 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=229290, idx=5, record_count=121000000000, read_time=24560162426.585083, current_search_depth=57) at sql/sql_planner.cc:1976 #16 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=229354, idx=4, record_count=121000000, read_time=158600324.34875488, current_search_depth=58) at sql/sql_planner.cc:1976 #17 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=229355, idx=3, record_count=11000000, read_time=13400324.348754883, current_search_depth=59) at sql/sql_planner.cc:1976 #18 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=262123, idx=2, record_count=1000000, read_time=200324.34875488281, current_search_depth=60) at sql/sql_planner.cc:1976 #19 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=262139, idx=1, record_count=1000, read_time=260, current_search_depth=61) at sql/sql_planner.cc:1976 #20 0x0000000000994f00 in Optimize_table_order::best_extension_by_limited_search (this=0x7f40f2387160, remaining_tables=262143, idx=0, record_count=1, read_time=0, current_search_depth=62) at sql/sql_planner.cc:1976 #21 0x0000000000993e07 in Optimize_table_order::greedy_search (this=0x7f40f2387160, remaining_tables=262143) at sql/sql_planner.cc:1466 #22 0x0000000000993626 in Optimize_table_order::choose_table_order (this=0x7f40f2387160) at sql/sql_planner.cc:1139 #23 0x000000000096eb0e in make_join_statistics (join=0x7f40b01e0e00, tables_arg=0x7f40b000bb20, conds=0x7f40b01e3378, keyuse_array=0x7f40b01e1028, first_optimization=true) at sql/sql_optimizer.cc:3740 #24 0x0000000000965b84 in JOIN::optimize (this=0x7f40b01e0e00) at sql/sql_optimizer.cc:379 #25 0x00000000007cdb56 in mysql_execute_select (thd=0x28af1c0, select_lex=0x28b19a8, free_join=true) at sql/sql_select.cc:1086 #26 0x00000000007cde8c in mysql_select (thd=0x28af1c0, tables=0x7f40b000bb20, wild_num=0, fields=..., conds=0x7f40b01e0ad0, order=0x28b1b70, group=0x28b1aa8, having=0x0, select_options=2148272640, result=0x7f40b01e0dd8, unit=0x28b1368, select_lex=0x28b19a8) at sql/sql_select.cc:1221 #27 0x00000000007cbfda in handle_select (thd=0x28af1c0, result=0x7f40b01e0dd8, setup_tables_done_option=0) at sql/sql_select.cc:110 #28 0x00000000007a8697 in execute_sqlcom_select (thd=0x28af1c0, all_tables=0x7f40b000bb20) at sql/sql_parse.cc:5019 #29 0x00000000007a1509 in mysql_execute_command (thd=0x28af1c0) at sql/sql_parse.cc:2589 #30 0x00000000007aaafb in mysql_parse (thd=0x28af1c0, rawbuf=0x7f40b000a250 "SELECT alias1 . `col_varchar_1024_latin1` AS field1 , alias1 . `col_varchar_1024_utf8` AS field2 , alias1 . `col_date` AS field3 , alias1 . `col_int` AS field4 FROM P AS alias1 LEFT OUTER JOIN W A"..., length=2336, parser_state=0x7f40f2389110) at sql/sql_parse.cc:6149 #31 0x000000000079e822 in dispatch_command (command=COM_QUERY, thd=0x28af1c0, packet=0x28b2e11 " SELECT alias1 . `col_varchar_1024_latin1` AS field1 , alias1 . `col_varchar_1024_utf8` AS field2 , alias1 . `col_date` AS field3 , alias1 . `col_int` AS field4 FROM P AS alias1 LEFT OUTER JOIN W"..., packet_length=2340) at sql/sql_parse.cc:1334 #32 0x000000000079d972 in do_command (thd=0x28af1c0) at sql/sql_parse.cc:1036 #33 0x0000000000768af5 in do_handle_one_connection (thd_arg=0x28af1c0) at sql/sql_connect.cc:969 #34 0x000000000076851a in handle_one_connection (arg=0x28af1c0) at sql/sql_connect.cc:885 #35 0x0000000000d8a168 in pfs_spawn_thread (arg=0x286e530) at storage/perfschema/pfs.cc:1853 #36 0x00007f41085c3e9a in start_thread (arg=0x7f40f238a700) at pthread_create.c:308 #37 0x00007f4107adecbd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
            igor Igor Babaev added a comment -

            I failed to reproduce this bug on my laptop.

            igor Igor Babaev added a comment - I failed to reproduce this bug on my laptop.
            igor Igor Babaev added a comment -

            Ok, I succeeded to reproduce the test case after all.

            igor Igor Babaev added a comment - Ok, I succeeded to reproduce the test case after all.

            It was fixed in maria-10.0-mwl253 by the following revision, and later merged into 10.0-base and 10.0 before 10.0.2 release:

            revno: 3525
            revision-id: igor@askmonty.org-20130409043042-cth07pd61g2lb0ph
            parent: igor@askmonty.org-20130409004058-zmmd17ikz0lvz9wt
            committer: Igor Babaev <igor@askmonty.org>
            branch nick: maria-10.0-mwl253
            timestamp: Mon 2013-04-08 21:30:42 -0700
            message:
            Fixed mdev-4380.
            Uninitialized field next_equal_field of the Field objects created
            for the fields of a temporary table could hang the server.

            elenst Elena Stepanova added a comment - It was fixed in maria-10.0-mwl253 by the following revision, and later merged into 10.0-base and 10.0 before 10.0.2 release: revno: 3525 revision-id: igor@askmonty.org-20130409043042-cth07pd61g2lb0ph parent: igor@askmonty.org-20130409004058-zmmd17ikz0lvz9wt committer: Igor Babaev <igor@askmonty.org> branch nick: maria-10.0-mwl253 timestamp: Mon 2013-04-08 21:30:42 -0700 message: Fixed mdev-4380. Uninitialized field next_equal_field of the Field objects created for the fields of a temporary table could hang the server.

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.