Bug #113338 return error result with a qubquery

• Submitted: 5 Dec 2023 11:16
• Modified: 7 Dec 2023 8:10
• Reporter: Alice Alice
• Status: Verified
• Category: MySQL Server: Partitions
• Severity: S3 (Non-critical)
• Version: 8.0.34, 8.0.35
• OS: Linux
• CPU Architecture: Any

[5 Dec 2023 11:16] Alice Alice

Description:
Execute query with partition tables,the returned value is error
I think the result should include 1.
mysql> SELECT (SELECT smallint_col FROM tbl_1_all_hashkey_type2index A WHERE A.decimal_col >= B.smallint_col AND A.smallint_col IN (1,32767,-32768,NULL)  GROUP BY 1 ORDER BY 1 LIMIT 1) AS c2 FROM tbl_1_all_keyhash_index B GROUP BY 1 ORDER BY 1 LIMIT 10;
+-------+
| c2    |
+-------+
|  NULL |
| 32767 |
+-------+
2 rows in set (0.45 sec)

mysql> SELECT (SELECT smallint_col FROM tbl_1_all_hashkey_type2index A WHERE A.decimal_col >= B.smallint_col AND A.smallint_col IN (1,32767,NULL)  GROUP BY 1 ORDER BY 1 LIMIT 1) AS c2 FROM tbl_1_all_keyhash_index B GROUP BY 1ORDER BY 1 LIMIT 10;
+-------+
| c2    |
+-------+
|  NULL |
|     1 |
| 32767 |
+-------+

How to repeat:
1.create table and insert data by the attachments
2.execute the query as follows:
SELECT (SELECT smallint_col FROM tbl_1_all_hashkey_type2index A WHERE A.decimal_col >= B.smallint_col AND A.smallint_col IN (1,32767,-32768,NULL)  GROUP BY 1 ORDER BY 1 LIMIT 1) AS c2 FROM tbl_1_all_keyhash_index B GROUP BY 1 ORDER BY 1 LIMIT 10;

[5 Dec 2023 11:26] Alice Alice

I am so sorry the sql statement is incorrect,please use the following statement:
SELECT (SELECT smallint_col FROM tt0 A WHERE A.decimal_col >= B.smallint_col AND A.smallint_col IN (1,32767,-32768,NULL)  GROUP BY 1 ORDER BY 1 LIMIT 1) AS c2 FROM tt1 B GROUP BY 1 ORDER BY 1 LIMIT 10;

[7 Dec 2023 8:10] MySQL Verification Team

Hello Alice Alice,

Thank you for the report and feedback.

regards,
Umesh