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

SQL query with duplicate table aliases consistently crashes server, Assertion `thd' failed in Item_subselect::const_item

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 10.0, 10.1, 10.1.38, 10.1.41, 10.2, 10.3, 10.4
    • 10.1.46, 10.2.33, 10.3.24, 10.4.14, 10.5.5
    • Server
    • None
    • Debian GNU/Linux 9 (amd64)

    Description

      During development we've noticed that a complicated SQL query that (by mistake) uses duplicate table aliases crashes the server every time.

      The query looks like this (notice how table2 and table4 are both aliased to "agu")

      select 
       
      ( 
      	select count(1) from table1 as com where ( 
      		exists (
      			select 1 from table2 as agu where agu.`ARTICLE_GROUP_ID` = (
      				select agu.`ID` from `table5` as acl 
      					inner join `table4` as agu on agu.`ID` = acl.`GROUPID` 
      						where acl.`ARTICLEID` = art.`ID` 
      			) and agu.`USER_ID` = com.`USERID`
      		) 
      	) 
      ) as test1
       
      from table3 as art;
      

      and tables are:

      CREATE TABLE `table1` (
      `ID` int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
      `USERID` int(11) NOT NULL DEFAULT '0'
      );
       
      CREATE TABLE `table2` (
      `ID` int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
      `USER_ID` int(11) NOT NULL,
      `ARTICLE_GROUP_ID` int(11) NOT NULL
      )  ; 
       
      CREATE TABLE `table3` (
      `ID` int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT
      );
       
      CREATE TABLE `table4` (
      `ID` int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT
      );
        
      CREATE TABLE `table5` (
      `ARTICLEID` int(11) NOT NULL,
      `GROUPID` int(11) NOT NULL
      );
      
      

      The core backtrace looks like this:

       
      #0  __pthread_kill (threadid=<optimized out>, signo=11) at ../sysdeps/unix/sysv/linux/pthread_kill.c:57
      #1  0x0000558f375ff3f7 in handle_fatal_signal ()
      #2  <signal handler called>
      #3  0x0000558f37684e8b in Item_subselect::const_item() const ()
      #4  0x0000558f37580091 in Item_in_subselect::const_item() const ()
      #5  0x0000558f373dd2f4 in Item_func::update_used_tables() ()
      #6  0x0000558f3745ad9f in st_select_lex::update_used_tables() ()
      #7  0x0000558f3745c0a8 in st_select_lex::optimize_unflattened_subqueries(bool) ()
      #8  0x0000558f374bf5e6 in JOIN::optimize_inner() ()
      #9  0x0000558f374c0b1b in JOIN::optimize() ()
      #10 0x0000558f374c14d8 in mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) ()
      #11 0x0000558f374c16af in handle_select(THD*, LEX*, select_result*, unsigned long) ()
      #12 0x0000558f373ab800 in ?? ()
      #13 0x0000558f3746a7ca in mysql_execute_command(THD*) ()
      #14 0x0000558f3746c4b1 in mysql_parse(THD*, char*, unsigned int, Parser_state*) ()
      #15 0x0000558f3746fe9c in dispatch_command(enum_server_command, THD*, char*, unsigned int) ()
      #16 0x0000558f374706d9 in do_command(THD*) ()
      #17 0x0000558f37541c82 in do_handle_one_connection(THD*) ()
      #18 0x0000558f37541d20 in handle_one_connection ()
      #19 0x00007fc2903144a4 in start_thread (arg=0x7fc2905df700) at pthread_create.c:456
      #20 0x00007fc28ef41d0f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:97
      

      Kernel log contains:
      [139380.540174] mysqld[56374]: segfault at 58 ip 0000555f94691e8b sp 00007fbe668a22d0 error 4 in mysqld[555f94059000+fb4000]

      I could consistently replicate this across different machines all running 10.1 MariaDB. Fixing the duplicate table alias makes to problem go away.

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              diab Mark Buranyi
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.