DOUBT WITH WORKING OF "IN" OPERATOR

[pradeep kaltari]

Hello friends,
I have a doubt in the working of IN operator. I have a schema by name 'test_schema'. If you look at the following query the expected output is "1", but nothing is displayed.

SELECT 1
FROM TABLE1
WHERE UPPER( 'test_schema' )
IN
(
SELECT UPPER(SCHEMA_NAME)
FROM INFORMATION_SCHEMA.SCHEMATA
)
Please help. Thanks.

[pbmods]

Hello friends,
I have a doubt in the working of IN operator. I have a schema by name 'test_schema'. If you look at the following query the expected output is "1", but nothing is displayed.

SELECT 1
FROM TABLE1
WHERE UPPER( 'test_schema' )
IN
(
SELECT UPPER(SCHEMA_NAME)
FROM INFORMATION_SCHEMA.SCHEMATA
)
Please help. Thanks.

Is there a 'TEST_SCHEMA' entry in information_schema.schemata.schema_name? If not, MySQL will return an empty set.

Did you mean WHERE UPPER(`test_schema`) instead (note backticks instead of quotes)?

[pradeep kaltari]

Thanks for your reply.
Yes there is "test_schema" in information_schema.schemata.schema_name.
By UPPER('test_schema') I meant with single quotes not backticks.

[chandu031]

Hi Pradeep,

I tried out your query and it seems the query will not return anything when you use it on System tables. The same query is working fine when used with User tables. I have no idea why this happens. One more interesting thing is that,
while a
SELECT 1 FROM TABLE1 WHERE value IN (Select Column from TABLE1) is not working for system tables, a
SELECT 1 FROM TABLE1 WHERE column IN (Select value from TABLE1) is working. Does anyone know why this is the case in MYSQL?

[pradeep kaltari]

Hi Pradeep,

I tried out your query and it seems the query will not return anything when you use it on System tables. The same query is working fine when used with User tables. I have no idea why this happens. One more interesting thing is that,
while a
SELECT 1 FROM TABLE1 WHERE value IN (Select Column from TABLE1) is not working for system tables, a
SELECT 1 FROM TABLE1 WHERE column IN (Select value from TABLE1) is working. Does anyone know why this is the case in MYSQL?

Thanks Chandu.