Description:
When a query is executed on a nested derived table in the where subquery (the where clause involves JSON), the execution result is incorrect.
How to repeat:
1.set the session variable 'sql_mode'.
```
set session sql_mode='';
```
2.Creating a Table and Importing Data
Download the table.txt file in the attachment.
```
create database test;
use test;
source table.txt
```
3.execute the query.
```
SELECT int_col,json_col FROM tbl_gsi_1 WHERE json_col <= ALL (SELECT * FROM(SELECT json_col FROM tbl_gsi_1 A WHERE A.binary_col IS NOT NULL) as temp) group by 1;
```
The result we expect is:
+-------------+-----------------------+
| int_col | json_col |
+-------------+-----------------------+
| 2147483647 | {"key1": 1} |
+-------------+-----------------------+
1 rows in set (0.00 sec)
But the actual result is:
+-------------+-----------------------+
| int_col | json_col |
+-------------+-----------------------+
| -2147483648 | {"key2": 2} |
| 100 | {"key2": 2} |
| 101 | {"key2": 2} |
| 99 | {"key2": 2} |
| 80 | {"key2": 2} |
| 76 | {"key2": 2} |
| 65 | {"key2": 2} |
| 58 | {"key2": 2} |
| 43 | {"key2": 2} |
| 39 | {"key2": 2} |
| 22 | {"key2": 2} |
| 15 | {"key2": 2} |
| 121 | {"key2": 2} |
| 2147483647 | {"key1": 1} |
| 131 | {"key": 1, "key2": 2} |
| 84 | {"key": 1, "key2": 2} |
+-------------+-----------------------+
16 rows in set (0.00 sec)
Description: When a query is executed on a nested derived table in the where subquery (the where clause involves JSON), the execution result is incorrect. How to repeat: 1.set the session variable 'sql_mode'. ``` set session sql_mode=''; ``` 2.Creating a Table and Importing Data Download the table.txt file in the attachment. ``` create database test; use test; source table.txt ``` 3.execute the query. ``` SELECT int_col,json_col FROM tbl_gsi_1 WHERE json_col <= ALL (SELECT * FROM(SELECT json_col FROM tbl_gsi_1 A WHERE A.binary_col IS NOT NULL) as temp) group by 1; ``` The result we expect is: +-------------+-----------------------+ | int_col | json_col | +-------------+-----------------------+ | 2147483647 | {"key1": 1} | +-------------+-----------------------+ 1 rows in set (0.00 sec) But the actual result is: +-------------+-----------------------+ | int_col | json_col | +-------------+-----------------------+ | -2147483648 | {"key2": 2} | | 100 | {"key2": 2} | | 101 | {"key2": 2} | | 99 | {"key2": 2} | | 80 | {"key2": 2} | | 76 | {"key2": 2} | | 65 | {"key2": 2} | | 58 | {"key2": 2} | | 43 | {"key2": 2} | | 39 | {"key2": 2} | | 22 | {"key2": 2} | | 15 | {"key2": 2} | | 121 | {"key2": 2} | | 2147483647 | {"key1": 1} | | 131 | {"key": 1, "key2": 2} | | 84 | {"key": 1, "key2": 2} | +-------------+-----------------------+ 16 rows in set (0.00 sec)