|
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)
|