Why MySQL5.7 seems to ignore subquery

Question

I have the following SQL query which behaves as I expect in MySQL 8, but does not produce the same a nd correct results in version 5.7 and I can't figure out why...

SELECT concat(table_name, '_', column_name) as missing
    from information_schema.columns
    where table_schema = 'ref_schema' and table_name in ('aliases','indiv') and
            concat(table_name, '_', column_name) not in (
                select concat( columns.table_name,'_',columns.column_name) as v
                from information_schema.columns   
                where table_schema = 'new_schema' 
    );

The query is basically comparing 2 table (aliases and indiv) structures from 2 schemas (ref_schema and new_schema) and is supposed to list columns which are missing in the second one...

Assuming that the table structures are identical in both schema, it should return 0 row. Which it does with MySQL8. But in version 5.7, it returns the full set of columns. It seems that the "NOT IN" condition is not properly executed.

I am using MySQL8 for debugging purposes. 5.7 is for prod. So I need to have it running on 5.7.

Any help would be appreciated.

Answer 1

Applying Akina fix gives the correct answer and fixed my issue:

SELECT concat(table_name, '_', column_name) as missing
FROM information_schema.columns
WHERE
    table_schema = 'ref_schema' and
    table_name in ('aliases','indiv') and
    (table_name,column_name) not in (
        select table_name,column_name
        from information_schema.columns   
        where table_schema = 'new_schema' 
    );

Now both platform provides the same and exact result. Syntax is even easier to read!