Details
-
Bug
-
Status: Open (View Workflow)
-
Critical
-
Resolution: Unresolved
-
13.1
-
None
-
Not for Release Notes
Description
LIMIT 1 incorrectly stops the scan after examining the first table (tb3), regardless of whether a matching row is found based on the WHERE condition, thus returning an empty result for tb1/tb2.
CREATE DATABASE db1;
|
CREATE TABLE db1.tb1(a INT);
|
CREATE TABLE db1.tb2(a INT);
|
CREATE TABLE db1.tb3(a INT);
|
|
|
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='db1';
|
+------------+
|
| tb3 | <- scan order, not sorted
|
| tb2 |
|
| tb1 |
|
+------------+
|
|
|
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='db1' AND (TABLE_NAME='tb1' OR TABLE_CATALOG='nope');
|
+------------+
|
| tb1 |
|
+------------+
|
|
|
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='db1' AND (TABLE_NAME='tb1' OR TABLE_CATALOG='nope') LIMIT 1;
|
Empty set
|
CLI Output
13.1.0-opt>CREATE DATABASE db1; |
Query OK, 1 row affected (0.000 sec)
|
|
|
13.1.0-opt>CREATE TABLE db1.tb1(a INT); |
Query OK, 0 rows affected (0.008 sec) |
|
|
13.1.0-opt>CREATE TABLE db1.tb2(a INT); |
Query OK, 0 rows affected (0.006 sec) |
|
|
13.1.0-opt>CREATE TABLE db1.tb3(a INT); |
Query OK, 0 rows affected (0.007 sec) |
|
|
13.1.0-opt>SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='db1' AND (TABLE_NAME='tb1' OR TABLE_CATALOG='nope') LIMIT 1; |
Empty set (0.000 sec) |
|
|
13.1.0-opt>SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='db1' AND (TABLE_NAME='tb2' OR TABLE_CATALOG='nope') LIMIT 1; |
Empty set (0.000 sec) |
|
|
13.1.0-opt>SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='db1' AND (TABLE_NAME='tb3' OR TABLE_CATALOG='nope') LIMIT 1; |
+------------+ |
| TABLE_NAME |
|
+------------+ |
| tb3 |
|
+------------+ |
1 row in set (0.000 sec) |
|
|
13.1.0-opt>
|
Attachments
Issue Links
- blocks
-
MDEV-31342 I_S optimization: avoid temp table
-
- In Testing
-
