MySql subquery runs instead of returning error

Question

I faced some weird mysql subquery issues ...

UPDATE site_plugin_products_items 
    SET item_distributor = 176
WHERE item_id in ( 
    SELECT item_id 
    FROM `site_plugin_products_field_values` 
    WHERE value_val in ( 554, 1384  , 785 )
)

I the problem is:

Result that query updated ENTIRE table site_plugin_products_items with item_distributor = 176

If i ran the subquery individual:

SELECT item_id 
FROM `site_plugin_products_field_values` 
WHERE value_val in ( 554, 1384  , 785 )

I get the following error because i put the wrong field, it was supposed to be value_product, not item_id

Error in query (1054): Unknown column 'item_id' in 'field list'

Why did this happen ? Shouldn't the big query return an error instead of messing the entire table ?

Lost 2 years when I saw the result, and didn't know if I have a close backup :)

if you don't have item_id column in site_plugin_products_field_values table how you can to select it??? — godot, Mar 22 '18 at 12:12
It's using the item_id from the site_plugin_products_items table, which of course will always be the same as item_id in the site_plugin_products_items table - so every record matches. — Nigel Ren, Mar 22 '18 at 12:13
Exactly, that table doesn't have the item_id field and it updated ALL the items from the first table. — Emanuel, Mar 22 '18 at 12:15
You are executing the code inside php .. and don't manage error message? — ScaisEdge, Mar 22 '18 at 12:15
Simple solution - change item_id in your subquery to be the field you should be using. — Nigel Ren, Mar 22 '18 at 12:18
I ran it inside phpmyadmin and it returned success ~20.000 records updated. — Emanuel, Mar 22 '18 at 12:18
The table structure is irrelevant. Only important thing is the second table doesn't have that field item_id and the sql didn't crash. — Emanuel, Mar 22 '18 at 12:22
It also runs in my phpmyadmin, but gives an empty resultset. It seems the subquery returns false, and checks the rest of the query accordingly. But since there are not false values, there is no return. — kry, Mar 22 '18 at 12:24
indeed what @NigelRen says that's why you should always quantify the columns with the table name like so. `UPDATE site_plugin_products_items SET site_plugin_products_items.item_distributor = 176 WHERE site_plugin_products_items .item_id in ( SELECT site_plugin_products_field_values.item_id FROM `site_plugin_products_field_values` WHERE site_plugin_products_field_values.value_val in ( 554, 1384 , 785 ) )` or use ofcource shorter aliases. This will prevent it from happing in the future. — Raymond Nijland, Mar 22 '18 at 12:26
Lessons learned. Never ever again run an update command in production if you don't know exactly what you are doing — Jorge Campos, Mar 22 '18 at 12:28

score 3 · Accepted Answer · answered Mar 22 '18 at 12:26

3

In the query -

UPDATE site_plugin_products_items 
    SET item_distributor = 176
WHERE item_id in ( 
    SELECT item_id 
    FROM `site_plugin_products_field_values` 
    WHERE value_val in ( 554, 1384  , 785 )
)

As item_id doesn't exist in the site_plugin_products_field_values table, it will use the column from the site_plugin_products_items table.

As this will always be the same as the condition (item_id will always equal itself) it will update every row.

I tried something to prove this (to myself) using...

SELECT * FROM `order` WHERE id in ( select customerID from user u)

This runs, but the user table doesn't have a customerID. BUT if I add the alias to the customerID field...

SELECT * FROM `order` WHERE id in ( select u.customerID from user u)

This fails.

Simple solution - use aliases to ensure which table your using per column and use the right columns in the first place. Although I won't mention backups!

answered Mar 22 '18 at 12:26

Nigel Ren

56,122
11
43
55

Exactly, but is this something normal ? Is it mentioned in sql documentation ? I expected it break and return some error :) – Emanuel Mar 22 '18 at 12:28
Now that's a funky situation. Personally, since every column starts with the name of the table it's in, it's practically impossible to get into a situation like this. But I wonder, is it mentioned in the documentation somewhere? It seems the inner query is also searching from the table of the outer query. – kry Mar 22 '18 at 12:30
1

"It seems the inner query is also searching from the table of the outer query" @kry not seems the inner qeury has column acces to the outer query that's why co-related subqeuries are possible. – Raymond Nijland Mar 22 '18 at 12:40

MySql subquery runs instead of returning error

1 Answers1