MySQL subquery with multiple return values in WHERE clause returns empty set

Question

When using a subquery in a WHERE conditional the query returns empty set:

If I do: (1)

USE general_db;
SELECT * FROM general_db.master WHERE alert IN ('M1_001','G1_011');
+------+-------------+
| mkey | alert       |
+------+-------------+
|   17 | G1_011      |
|   19 | M1_001      |
+------+--------+----+

(1) works as expected.

Then I do: (2)

SELECT DISTINCT TABLE_NAME AS 'alert' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='maintenance_db';
+-------------+
| alert       |
+-------------+
| G1_011      |
| M1_001      |
+-------------+

(2) Works as expected.

But then if I use the second query (2) as a subquery of the first (1): (3)

SELECT * FROM general_db.master WHERE alert IN (SELECT DISTINCT TABLE_NAME AS 'alert' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='maintenance_db');

Empty set (0,00 sec)

(3) does not work as expected. I would think it should return the same results as in (1).

How can I do for the combination of both (3) to return the same results as in (1)? And why is this query (3) not working?

More info:

As you have may noticed, the table names in maintenance_db, may be contained in a column named alert in master table thats inside the general_db.

It is very strange. I am sure the subquery syntax is correct, this query works fine:

SELECT * FROM general_db.master WHERE alert IN (SELECT alert FROM general_db.master WHERE alert IN ('G1_011','M1_001'));

So I think it may be something related to query (2).

This works:

USE maintenance_db;
select * from M1_001;
select * from G1_011;

So the names are correct I guess.

Thanks

@GSerg Yes. I do not change to other databases. At least not explicitly. — Tomás Arturo Herrera Castro, Mar 29 '20 at 17:57
Instead of usign **master** could you please use **general_db.master** and try — Alpesh Jikadra, Mar 29 '20 at 18:12
@AlpeshJikadra Just tried it out but there is no difference. I will edit the question to add this remark. — Tomás Arturo Herrera Castro, Mar 29 '20 at 18:15
How can I be entirely sure the table names do not contain spaces or such ? Maybe they are not equal to 'M1_001' and may be 'M1_001 '. — Tomás Arturo Herrera Castro, Mar 29 '20 at 18:22
`select 'it does not matter' from dual where 'abc' in ('abc ');` `select 'it does not matter' from dual where 'abc' = 'abc ';` — GSerg, Mar 29 '20 at 18:50
Try this : https://stackoverflow.com/questions/36353922/mysql-select-data-from-dynamic-table-names — Alpesh Jikadra, Mar 30 '20 at 09:18
This is already answered at https://stackoverflow.com/questions/36353922/mysql-select-data-from-dynamic-table-names — Alpesh Jikadra, Mar 30 '20 at 12:14

sbrbot · Answer 1 · 2020-03-29T20:05:06.627

1

Why you are fetching table names from INFORMATION_SCHEMA.COLUMNS instead of INFORMATION_SCHEMA.TABLES table!? There's no WHERE for filtering by column names in COLUMNS table!

What about:

SELECT * 
FROM general_db.master 
WHERE TRIM(alert) IN 
(
    SELECT UPPER(TABLE_NAME) 
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_SCHEMA='maintenance_db'
)

Also instead of using inner query why not to use simple JOIN like

SELECT m.* 
FROM general_db.master m
JOIN INFORMATION_SCHEMA.TABLES t ON (UPPER(t.table_name)=TRIM(m.alert) AND t.table_schema='maintenance_db')

edited Mar 29 '20 at 20:05

answered Mar 29 '20 at 19:14

sbrbot

6,169
6
43
74

The second query works! But the first returns an empty set (the subqueries work by themself, but not when joined as one) – Tomás Arturo Herrera Castro Mar 29 '20 at 19:37
Depends how your MySQL server is set to manage table names: case sensitive or case insensitive. One can setup it inside MYSQL.INI file. Try with UPPPER-ing table names. By default MySQL stores table names in lower case while your entries are upper cased. – sbrbot Mar 29 '20 at 19:40
Tried: SELECT * FROM general_db.master WHERE alert IN (SELECT UPPER(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='maintenance_db'); but it returns empty set. – Tomás Arturo Herrera Castro Mar 29 '20 at 19:53
Maybe your data has trimming spaces. Try using TRIM() function. – sbrbot Mar 29 '20 at 20:04
Tried different combinations using: TRIM(UPPER(TABLE_NAME)), TRIM(TABLE_NAME), TRIM(alert) and TRIM(UPPER(alert)), still returns empty set – Tomás Arturo Herrera Castro Mar 29 '20 at 20:27

MySQL subquery with multiple return values in WHERE clause returns empty set

1 Answers1