CODEWITHSUNDEEP
mysqlsql
Jack Lu
Jack Lu

Reputation: 170

Why why '=' does not work in this SQL query?

I have a query against the sakila database in MySQL 8(You can easily setup one from https://hub.docker.com/r/1maa/sakila).

select * from store _0 where (
    select address_id from (
        select * from address _3 where (
            address_id = (
                select address_id from (
                    select * from store _1
                    where store_id=_0.store_id
                ) _2
            )
        )
    ) _4
);

This query returns an empty table.

However, when I rewrite the innermost

                select address_id from (
                    select * from store _1
                    where store_id=_0.store_id
                ) _2

into:

                select address_id from store _1
                where store_id=_0.store_id

, it returns all 2 rows from store table, which I expect.

Why is this? It seems I just simplified a 2-step select into 1.

I also noticed that if I use in instead of = in address_id = .. condition, I can get the expected result.

What's the theory behind this?

The query is over-complicated because it's generated. So thank you for your advice of simplified queries but I just want to know why it does not give the correct result.

And you must have MySQL 8.0.14 and above to reproduce it, because earlier versions does not allow alias beyond 1 level.

Upvotes: 0

Views: 165

Answers (3)

Jack Lu
Jack Lu

Reputation: 170

I reported this to MySQL. This is confirmed to be an optimizer bug: https://bugs.mysql.com/bug.php?id=97063.

Upvotes: 0

Jonathan
Jonathan

Reputation: 1

            select address_id from (
                select * from store _1
                where store_id=_0.store_id
            ) _2

In the first code snippet, you are selecting the columns from store _1 that have the equivalent store_id entries as store _0. If the comparison statement returns true, the address_id is retrieved from these columns, but what if no address_id entries are stored in these columns?

            select address_id from store _1
            where store_id=_0.store_id

In the second code snippet, you are selecting the address_id from store _1 where the comparison of store_ids of store _0 and _1 returns true. The scope for selecting the address_id in store _1 is broader than in the first code snippet, thus the address_id can be retrieved.

TL;DR The different results have everything to do with the scope of your searches and the structure of data in the relational database!

Upvotes: 0

Jimeh
Jimeh

Reputation: 397

The reason you would need an in clause is because the sub select is returning multiple results.

Are you just trying to join the two tables? Why not use a join instead of the subselects?

SELECT s.* 
  FROM store s
  JOIN address a
    ON a.address_id = s.address_id

Upvotes: 1

Related Questions