Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5(EOL), 10.6
-
None
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)