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

Order By with IFNULL and SELECT crashes server

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.1.17, 10.1.18, 10.1.19
    • 5.5.54, 10.0.29, 10.1.20, 10.2.3
    • Optimizer
    • None
    • Centos 7.2.1, InnoDB

    Description

      We know this is normally not how to order by on a column but because our software generated this query we experienced crashes and could reproduce it.

      Reproduction

      create table testcrash (id int not null primary key, first_name varchar(255), last_name varchar(255));
      		 
       
      		 
      select testcrash.first_name, testcrash.last_name,
      		 
      ifnull((SELECT count(id) from testcrash where id = 0), 0) as data
      		 
       from testcrash 
      group by id
      		 
      order by IFNULL((SELECT count(id) from testcrash where id = 0), 0)
      		 
      LIMIT 0,25;

      Attachments

        Activity

          elenst Elena Stepanova added a comment -

          Thanks for the report.
          This crash was fixed by the patch for MDEV-10776. The bugfix will be released in 10.1.20.

          #3  <signal handler called>
          		 
          #4  0x00007fdb3e444afb in Item_subselect::const_item (this=0x7fdb32c46c48) at /data/src/10.1-bug/sql/item_subselect.cc:910
          		 
          #5  0x00007fdb3e15aff8 in st_select_lex::optimize_unflattened_subqueries (this=0x7fdb34bfd1b8, const_only=true) at /data/src/10.1-bug/sql/sql_lex.cc:3719
          		 
          #6  0x00007fdb3e2f2f5d in JOIN::optimize_constant_subqueries (this=0x7fdb32c471f8) at /data/src/10.1-bug/sql/opt_subselect.cc:5090
          		 
          #7  0x00007fdb3e1a4d36 in JOIN::optimize_inner (this=0x7fdb32c471f8) at /data/src/10.1-bug/sql/sql_select.cc:1133
          		 
          #8  0x00007fdb3e1a4926 in JOIN::optimize (this=0x7fdb32c471f8) at /data/src/10.1-bug/sql/sql_select.cc:1040
          		 
          #9  0x00007fdb3e1acf62 in mysql_select (thd=0x7fdb34bf9070, rref_pointer_array=0x7fdb34bfd430, tables=0x7fdb32c44f00, wild_num=0, fields=..., conds=0x0, og_num=2, order=0x7fdb32c46ff0, group=0x7fdb32c45600, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fdb32c471d8, unit=0x7fdb34bfcab8, select_lex=0x7fdb34bfd1b8) at /data/src/10.1-bug/sql/sql_select.cc:3424
          		 
          #10 0x00007fdb3e1a2ae6 in handle_select (thd=0x7fdb34bf9070, lex=0x7fdb34bfc9f0, result=0x7fdb32c471d8, setup_tables_done_option=0) at /data/src/10.1-bug/sql/sql_select.cc:384
          		 
          #11 0x00007fdb3e172b2d in execute_sqlcom_select (thd=0x7fdb34bf9070, all_tables=0x7fdb32c44f00) at /data/src/10.1-bug/sql/sql_parse.cc:5896
          		 
          #12 0x00007fdb3e168a4f in mysql_execute_command (thd=0x7fdb34bf9070) at /data/src/10.1-bug/sql/sql_parse.cc:2968
          		 
          #13 0x00007fdb3e176286 in mysql_parse (thd=0x7fdb34bf9070, rawbuf=0x7fdb32c43088 "select testcrash.first_name, testcrash.last_name,\nifnull((SELECT count(id) from testcrash where id = 0), 0) as data\nfrom testcrash \ngroup by id\norder by IFNULL((SELECT count(id) from testcrash where i"..., length=221, parser_state=0x7fdb3f0865e0) at /data/src/10.1-bug/sql/sql_parse.cc:7319
          		 
          #14 0x00007fdb3e164c59 in dispatch_command (command=COM_QUERY, thd=0x7fdb34bf9070, packet=0x7fdb3653e071 "select testcrash.first_name, testcrash.last_name,\nifnull((SELECT count(id) from testcrash where id = 0), 0) as data\nfrom testcrash \ngroup by id\norder by IFNULL((SELECT count(id) from testcrash where i"..., packet_length=221) at /data/src/10.1-bug/sql/sql_parse.cc:1487
          		 
          #15 0x00007fdb3e163990 in do_command (thd=0x7fdb34bf9070) at /data/src/10.1-bug/sql/sql_parse.cc:1108
          		 
          #16 0x00007fdb3e299ed5 in do_handle_one_connection (thd_arg=0x7fdb34bf9070) at /data/src/10.1-bug/sql/sql_connect.cc:1350
          		 
          #17 0x00007fdb3e299c39 in handle_one_connection (arg=0x7fdb34bf9070) at /data/src/10.1-bug/sql/sql_connect.cc:1262
          		 
          #18 0x00007fdb3e57caf8 in pfs_spawn_thread (arg=0x7fdb3b027ef0) at /data/src/10.1-bug/storage/perfschema/pfs.cc:1860
          		 
          #19 0x00007fdb3d8610a4 in start_thread (arg=0x7fdb3f087b00) at pthread_create.c:309
          		 
          #20 0x00007fdb3ba1987d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111

          elenst Elena Stepanova added a comment - Thanks for the report. This crash was fixed by the patch for MDEV-10776 . The bugfix will be released in 10.1.20. #3 <signal handler called> #4 0x00007fdb3e444afb in Item_subselect::const_item (this=0x7fdb32c46c48) at /data/src/10.1-bug/sql/item_subselect.cc:910 #5 0x00007fdb3e15aff8 in st_select_lex::optimize_unflattened_subqueries (this=0x7fdb34bfd1b8, const_only=true) at /data/src/10.1-bug/sql/sql_lex.cc:3719 #6 0x00007fdb3e2f2f5d in JOIN::optimize_constant_subqueries (this=0x7fdb32c471f8) at /data/src/10.1-bug/sql/opt_subselect.cc:5090 #7 0x00007fdb3e1a4d36 in JOIN::optimize_inner (this=0x7fdb32c471f8) at /data/src/10.1-bug/sql/sql_select.cc:1133 #8 0x00007fdb3e1a4926 in JOIN::optimize (this=0x7fdb32c471f8) at /data/src/10.1-bug/sql/sql_select.cc:1040 #9 0x00007fdb3e1acf62 in mysql_select (thd=0x7fdb34bf9070, rref_pointer_array=0x7fdb34bfd430, tables=0x7fdb32c44f00, wild_num=0, fields=..., conds=0x0, og_num=2, order=0x7fdb32c46ff0, group=0x7fdb32c45600, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fdb32c471d8, unit=0x7fdb34bfcab8, select_lex=0x7fdb34bfd1b8) at /data/src/10.1-bug/sql/sql_select.cc:3424 #10 0x00007fdb3e1a2ae6 in handle_select (thd=0x7fdb34bf9070, lex=0x7fdb34bfc9f0, result=0x7fdb32c471d8, setup_tables_done_option=0) at /data/src/10.1-bug/sql/sql_select.cc:384 #11 0x00007fdb3e172b2d in execute_sqlcom_select (thd=0x7fdb34bf9070, all_tables=0x7fdb32c44f00) at /data/src/10.1-bug/sql/sql_parse.cc:5896 #12 0x00007fdb3e168a4f in mysql_execute_command (thd=0x7fdb34bf9070) at /data/src/10.1-bug/sql/sql_parse.cc:2968 #13 0x00007fdb3e176286 in mysql_parse (thd=0x7fdb34bf9070, rawbuf=0x7fdb32c43088 "select testcrash.first_name, testcrash.last_name,\nifnull((SELECT count(id) from testcrash where id = 0), 0) as data\nfrom testcrash \ngroup by id\norder by IFNULL((SELECT count(id) from testcrash where i"..., length=221, parser_state=0x7fdb3f0865e0) at /data/src/10.1-bug/sql/sql_parse.cc:7319 #14 0x00007fdb3e164c59 in dispatch_command (command=COM_QUERY, thd=0x7fdb34bf9070, packet=0x7fdb3653e071 "select testcrash.first_name, testcrash.last_name,\nifnull((SELECT count(id) from testcrash where id = 0), 0) as data\nfrom testcrash \ngroup by id\norder by IFNULL((SELECT count(id) from testcrash where i"..., packet_length=221) at /data/src/10.1-bug/sql/sql_parse.cc:1487 #15 0x00007fdb3e163990 in do_command (thd=0x7fdb34bf9070) at /data/src/10.1-bug/sql/sql_parse.cc:1108 #16 0x00007fdb3e299ed5 in do_handle_one_connection (thd_arg=0x7fdb34bf9070) at /data/src/10.1-bug/sql/sql_connect.cc:1350 #17 0x00007fdb3e299c39 in handle_one_connection (arg=0x7fdb34bf9070) at /data/src/10.1-bug/sql/sql_connect.cc:1262 #18 0x00007fdb3e57caf8 in pfs_spawn_thread (arg=0x7fdb3b027ef0) at /data/src/10.1-bug/storage/perfschema/pfs.cc:1860 #19 0x00007fdb3d8610a4 in start_thread (arg=0x7fdb3f087b00) at pthread_create.c:309 #20 0x00007fdb3ba1987d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111

          People

            Unassigned Unassigned
            b.brey Bas
            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.