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

Spider: ERROR 1056 (42000): Can't group on 'min(`c`)', Got error 1056 when reading table

Details

    Description

      Consider:

      CREATE TABLE t2 (c INT KEY) ENGINE=InnoDB;
      		 
      LOCK TABLE t2 READ;
      		 
      SELECT 1 FROM t2 GROUP BY 1;

      Gives:

      11.2.5 03807c8449cdccbf5b8afc0dddabb1d8ec7ba85a (Optimized)

      		 
      11.2.5-opt>SELECT 1 FROM t2 GROUP BY 1;
      		 
      Empty set (0.001 sec)

      Yet, with Spider, for:

      INSTALL PLUGIN Spider SONAME 'ha_spider.so';
      		 
      CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET '../socket.sock',DATABASE'',USER'',PASSWORD'');
      		 
      CREATE TABLE t1 (c INT KEY) ENGINE=InnoDB;
      		 
      CREATE TABLE t2 (c INT KEY) ENGINE=Spider COMMENT='WRAPPER "mysql",SRV "srv",TABLE "t1"';
      		 
      LOCK TABLE t2 READ;
      		 
      SELECT 1 FROM t2 GROUP BY 1;

      We see:

      11.2.5 03807c8449cdccbf5b8afc0dddabb1d8ec7ba85a (Optimized)

      		 
      11.2.5-dbg>SELECT 1 FROM t2 GROUP BY 1;
      		 
      ERROR 1056 (42000): Can't group on 'min(`c`)'

      And in the error log:

      11.2.5 03807c8449cdccbf5b8afc0dddabb1d8ec7ba85a (Optimized)

      		 
      2024-07-23 14:22:58 4 [ERROR] Got error 1056 when reading table './test/t2'

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          Roel Roel Van de Paar added a comment - - edited

          MTR Testcase:

          --source plugin/spider/spider/include/init_spider.inc
          		 
          --source include/have_innodb.inc
          		 
          SET spider_same_server_link=on;
          		 
          eval CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (HOST "127.0.0.1", DATABASE "test", USER "root", PORT $MASTER_MYPORT);
          		 
          CREATE TABLE t1 (c INT KEY) ENGINE=InnoDB;
          		 
          CREATE TABLE t2 (c INT KEY) ENGINE=Spider COMMENT='WRAPPER "mysql",SRV "srv",TABLE "t1"';
          		 
          LOCK TABLE t2 READ;
          		 
          SELECT 1 FROM t2 GROUP BY 1;
          		 
          # Cleanup
          		 
          DROP TABLE t1,t2;
          		 
          --source plugin/spider/spider/include/deinit_spider.inc

          11.2.5 03807c8449cdccbf5b8afc0dddabb1d8ec7ba85a (Optimized)

          		 
          mysqltest: At line 8: query 'SELECT 1 FROM t2 GROUP BY 1' failed: ER_WRONG_GROUP_FIELD (1056): Can't group on 'min(`c`)'

          Roel Roel Van de Paar added a comment - - edited MTR Testcase: --source plugin/spider/spider/include/init_spider.inc --source include/have_innodb.inc SET spider_same_server_link= on ; eval CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (HOST "127.0.0.1" , DATABASE "test" , USER "root" , PORT $MASTER_MYPORT); CREATE TABLE t1 (c INT KEY ) ENGINE=InnoDB; CREATE TABLE t2 (c INT KEY ) ENGINE=Spider COMMENT= 'WRAPPER "mysql",SRV "srv",TABLE "t1"' ; LOCK TABLE t2 READ ; SELECT 1 FROM t2 GROUP BY 1; # Cleanup DROP TABLE t1,t2; --source plugin/spider/spider/include/deinit_spider.inc 11.2.5 03807c8449cdccbf5b8afc0dddabb1d8ec7ba85a (Optimized) mysqltest: At line 8: query 'SELECT 1 FROM t2 GROUP BY 1' failed: ER_WRONG_GROUP_FIELD (1056): Can't group on 'min(`c`)'
          ycp Yuchen Pei added a comment - - edited

          The following analysis is based on a custom branch (bb-10.5-mdev-34639 bc792266f9b1c83182bbcfc9d8eb256cf11dffe7) with two commits above the 10.5 main branch 872dbec93570bb934473907f5d1fcf74aeee4f85, but the behaviour should be the same at the latter.

          The offending query sent to the data node is:

          "select min(`c`) from `test`.`t1` group by 1 order by `c`"

          It is sent in the index_first call to the handler.

          One issue is that spider seems oblivious with GROUP BY a column index number:

          bool spider_db_check_select_colum_in_group(
          		 
            st_select_lex *select_lex,
          		 
            Field *field
          		 
          ) {
          		 
            ORDER *group;
          		 
            DBUG_ENTER("spider_db_check_select_colum_in_group");
          		 
            for (group = (ORDER *) select_lex->group_list.first; group;
          		 
              group = group->next)
          		 
            {
          		 
              Item *item = *group->item;
          		 
              if (item->type() == Item::FIELD_ITEM)
          		 
              {
          		 
                Item_field *item_field = (Item_field *) item;
          		 
                if (item_field->field == field)
          		 
                {
          		 
                  /* This field can be used directly */
          		 
                  DBUG_RETURN(TRUE);
          		 
                }
          		 
              }
          		 
            }
          		 
            DBUG_RETURN(FALSE);
          		 
          }

          which causes the caller to enter the else branch:

                if (select_lex &&
          		 
                  !spider_db_check_select_colum_in_group(select_lex, *field))
          		 
                {
          		 
          #  [... 8 lines elided]
          		 
                } else {
          		 
          #endif
          		 
                  if (str->reserve(field_length +
          		 
                    /* SPIDER_SQL_NAME_QUOTE_LEN */ 2 + SPIDER_SQL_COMMA_LEN))
          		 
                    DBUG_RETURN(HA_ERR_OUT_OF_MEM);
          		 
                  mysql_share->append_column_name(str, (*field)->field_index);
          		 
          #ifdef HANDLER_HAS_DIRECT_AGGREGATE
          		 
                }

          There are other things in the stack that are not clear, including the meaning of spider_select_column_mode which causes entering the else branch in spider_db_append_select_columns() that resulted in calling spider->append_minimum_select_sql_part, as well as the meaning of spider->append_minimum_select_sql_part itself, e.g. whether the minimum means appending min or just appending minimum amount of "select parts" (whatever that means)...

          The stack:

           0 in spider_db_check_select_colum_in_group of /home/ycp/source/mariadb-server/10.5/src/storage/spider/spd_db_conn.cc:9579
          		 
           1 in spider_mbase_handler::append_minimum_select of /home/ycp/source/mariadb-server/10.5/src/storage/spider/spd_db_mysql.cc:9739
          		 
           2 in spider_mbase_handler::append_minimum_select_part of /home/ycp/source/mariadb-server/10.5/src/storage/spider/spd_db_mysql.cc:9698
          		 
           3 in ha_spider::append_minimum_select_sql_part of /home/ycp/source/mariadb-server/10.5/src/storage/spider/ha_spider.cc:13375
          		 
           4 in spider_db_append_select_columns of /home/ycp/source/mariadb-server/10.5/src/storage/spider/spd_db_conn.cc:1419
          		 
           5 in ha_spider::index_first_internal of /home/ycp/source/mariadb-server/10.5/src/storage/spider/ha_spider.cc:2599
          		 
           6 in ha_spider::index_first of /home/ycp/source/mariadb-server/10.5/src/storage/spider/ha_spider.cc:2916
          		 
           7 in handler::ha_index_first of /home/ycp/source/mariadb-server/10.5/src/sql/handler.cc:3338

          ycp Yuchen Pei added a comment - - edited The following analysis is based on a custom branch (bb-10.5-mdev-34639 bc792266f9b1c83182bbcfc9d8eb256cf11dffe7) with two commits above the 10.5 main branch 872dbec93570bb934473907f5d1fcf74aeee4f85, but the behaviour should be the same at the latter. The offending query sent to the data node is: "select min(`c`) from `test`.`t1` group by 1 order by `c`" It is sent in the index_first call to the handler. One issue is that spider seems oblivious with GROUP BY a column index number: bool spider_db_check_select_colum_in_group( st_select_lex *select_lex, Field *field ) { ORDER *group; DBUG_ENTER( "spider_db_check_select_colum_in_group" ); for (group = (ORDER *) select_lex->group_list.first; group; group = group->next) { Item *item = *group->item; if (item->type() == Item::FIELD_ITEM) { Item_field *item_field = (Item_field *) item; if (item_field->field == field) { /* This field can be used directly */ DBUG_RETURN(TRUE); } } } DBUG_RETURN(FALSE); } which causes the caller to enter the else branch: if (select_lex && !spider_db_check_select_colum_in_group(select_lex, *field)) { # [... 8 lines elided] } else { #endif if (str->reserve(field_length + /* SPIDER_SQL_NAME_QUOTE_LEN */ 2 + SPIDER_SQL_COMMA_LEN)) DBUG_RETURN(HA_ERR_OUT_OF_MEM); mysql_share->append_column_name(str, (*field)->field_index); #ifdef HANDLER_HAS_DIRECT_AGGREGATE } There are other things in the stack that are not clear, including the meaning of spider_select_column_mode which causes entering the else branch in spider_db_append_select_columns() that resulted in calling spider->append_minimum_select_sql_part , as well as the meaning of spider->append_minimum_select_sql_part itself, e.g. whether the minimum means appending min or just appending minimum amount of "select parts" (whatever that means)... The stack: 0 in spider_db_check_select_colum_in_group of /home/ycp/source/mariadb-server/10.5/src/storage/spider/spd_db_conn.cc:9579 1 in spider_mbase_handler::append_minimum_select of /home/ycp/source/mariadb-server/10.5/src/storage/spider/spd_db_mysql.cc:9739 2 in spider_mbase_handler::append_minimum_select_part of /home/ycp/source/mariadb-server/10.5/src/storage/spider/spd_db_mysql.cc:9698 3 in ha_spider::append_minimum_select_sql_part of /home/ycp/source/mariadb-server/10.5/src/storage/spider/ha_spider.cc:13375 4 in spider_db_append_select_columns of /home/ycp/source/mariadb-server/10.5/src/storage/spider/spd_db_conn.cc:1419 5 in ha_spider::index_first_internal of /home/ycp/source/mariadb-server/10.5/src/storage/spider/ha_spider.cc:2599 6 in ha_spider::index_first of /home/ycp/source/mariadb-server/10.5/src/storage/spider/ha_spider.cc:2916 7 in handler::ha_index_first of /home/ycp/source/mariadb-server/10.5/src/sql/handler.cc:3338

          People

            ycp Yuchen Pei
            Roel Roel Van de Paar
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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