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

spider outputs wrong results when SELECTing columns not in GROUP BY

    XMLWordPrintable

Details

    Description

      To reproduce, apply the following change and run the test

      modified   storage/spider/mysql-test/spider/bugfix/t/mdev_34659.test
      @@ -5,6 +5,7 @@
       --enable_query_log
       
       SET spider_same_server_link= on;
      +SET spider_disable_group_by_handler= 1;
       SET sql_mode='';
       evalp CREATE SERVER srv FOREIGN DATA WRAPPER mysql
       OPTIONS (SOCKET "$MASTER_1_MYSOCK", DATABASE 'test',user 'root');
      @@ -18,6 +19,7 @@ SELECT * FROM t2 ORDER BY CAST(c AS INET6);
       --enable_view_protocol
       SELECT * FROM t2 GROUP BY CAST(c AS char(60));
       SELECT * FROM t2 GROUP BY CAST(c AS INET6);
      +SELECT * FROM t1 GROUP BY CAST(c AS INET6);
       # Cleanup
       DROP TABLE t1,t2;
       drop server srv;
      
      

      The results are different between SELECTing from the spider table and the remote table, but they should be the same:

       SELECT * FROM t2 GROUP BY CAST(c AS INET6);
       c
      +123
      +Warnings:
      +Warning	1292	Incorrect inet6 value: '123'
      +SELECT * FROM t1 GROUP BY CAST(c AS INET6);
      +c
       456
      +Warnings:
      +Warning	1292	Incorrect inet6 value: '456'
      +Warning	1292	Incorrect inet6 value: '123'
      

      From initial debugging this seems to be caused by the following commit that adds a "min" to the query to preempt the data node having "ONLY_FULL_GROUP_BY" sql mode.

      commit 9ba56c072c016cf226a796ca64f227f253be70c9
      Author: Kentoku Shiba <kentokushiba@gmail.com>
      Date:   Mon Apr 27 08:46:30 2020 +0900
       
          Add a parameter spider_strict_group_by for supporting ONLY_FULL_GROUP_BY
      
      

      Specifically, the query sent by spider to the data node is (note the extra "min")

      select min(`c`) from `test`.`t1` group by cast(`c`as inet6)
      

      Note that setting spider_strict_group_by to 0 does not fix the results, one also has to set the table parameter:

      CREATE TABLE t2 (c INT) ENGINE=Spider COMMENT='WRAPPER "mysql",SRV "srv",TABLE "t1", strict_group_by "0"';
      

      and then the results would be ok.

      Attachments

        Issue Links

          Activity

            People

              ycp Yuchen Pei
              ycp Yuchen Pei
              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.