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

Running of EXPLAIN for query with straight_join and WHERE clause containing a subquery produces different results if it is executed in PS mode

    XMLWordPrintable

    Details

      Description

      The following test case run both as regular statements and in PS mode produces different results.

      MariaDB [test]> create table t1 (c1 int);
      Query OK, 0 rows affected (0,038 sec)
       
      MariaDB [test]> create table t2 (c2 int);
      Query OK, 0 rows affected (0,035 sec)
       
      MariaDB [test]> create table t3 (c3 int);
      Query OK, 0 rows affected (0,066 sec)
       
      MariaDB [test]> insert into t1 values (1), (2);
      Query OK, 2 rows affected (0,003 sec)
      Records: 2  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> insert into t2 values (1), (2);
      Query OK, 2 rows affected (0,002 sec)
      Records: 2  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> insert into t3 values (2);
      Query OK, 1 row affected (0,002 sec)
      

      The statement

      MariaDB [test]> explain
          -> select count(*) from t1 straight_join t2
          -> where c1 = c2-0 and c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@counter+1);
      

      Produces the following result set:

      id select_type table type possible_keys key key_len ref rows Extra
      1 PRIMARY t1 ALL NULL NULL NULL NULL 2  
      1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
      2 UNCACHEABLE SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE

      3 rows in set (0,001 sec)

      On the other hand, the statement executed in PS mode produces different result set:

      MariaDB [test]> PREPARE stmt FROM 'explain select count(*) from t1 straight_join t2 where c1 = c2-0 and c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@counter+1)';
      Query OK, 0 rows affected (0,001 sec)
      Statement prepared
       
      MariaDB [test]> EXECUTE stmt;
      

      produces different result set:

      id select_type table type possible_keys key key_len ref rows Extra
      1 PRIMARY t1 ALL NULL NULL NULL NULL 2  
      1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
      2 UNCACHEABLE SUBQUERY t3 ALL NULL NULL NULL NULL 1 Using where

      3 rows in set (0,001 sec)

        Attachments

          Activity

            People

            Assignee:
            shulga Dmitry Shulga
            Reporter:
            shulga Dmitry Shulga
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated: