MySQL impossible where clause ?

[big_malk]

I have a bit of a huge MySQL query that I'm having trouble debugging, when I run EXPLAIN on it, it says I have an impossible where clause.

It's a big query, but its basically just a lot of joins and nothing too complicated. The system is for print based products and allows admins to add products, product attributes (groups of options), product attribute values (the options for each attribute), also paper size and price ranges. Then, printers can add their own prices for all these combinations, this query selects available products, in this case no options have been selected, so using the default attribute values.

This is being built up using codeIgniter and DMZ.
When I remove the lines like "AND `product_type_attribute_2_product_attributes`.`id` = 2", both attributes prices return with the same ID, like maybe the same tables being joined more than once are using the same WHERE clause, but since they are all being renamed and the aliases are given the WHERE clauses, that shouldn't happen?

Hopefully my explanation makes sense and someone can make sense of this beastly query Thanks!

SELECT DISTINCT `products`.*, `product_types`.`name` AS product_type_name, `printers`.`display_name` AS printer_display_name, `product_type_attribute_1_product_attribute_value_product_attribute_value_prices`.`value` as product_type_attribute_1_product_attribute_value_product_attribute_value_prices_value, `product_type_attribute_1_product_attribute_value_product_attribute_value_prices`.`id` as product_type_attribute_1_product_attribute_value_product_attribute_value_prices_id, `product_type_attribute_2_product_attribute_value_product_attribute_value_prices`.`value` as product_type_attribute_2_product_attribute_value_product_attribute_value_prices_value, `product_type_attribute_2_product_attribute_value_product_attribute_value_prices`.`id` as product_type_attribute_2_product_attribute_value_product_attribute_value_prices_id, ( `products`.`base_price` + product_type_attribute_1_product_attribute_value_product_attribute_value_prices.value + product_type_attribute_2_product_attribute_value_product_attribute_value_prices.value) as price_total
FROM (`products`)
LEFT OUTER JOIN `product_types` product_types ON `product_types`.`id` = `products`.`product_type_id`
LEFT OUTER JOIN `printers` printers ON `printers`.`id` = `products`.`printer_id`
LEFT OUTER JOIN `_product_attributes_product_types` product_type_attribute_1__product_attributes_product_types ON `product_types`.`id` = `product_type_attribute_1__product_attributes_product_types`.`_id`
LEFT OUTER JOIN `product_attributes` product_type_attribute_1_product_attributes ON `product_type_attribute_1_product_attributes`.`id` = `product_type_attribute_1__product_attributes_product_types`.`_id`
LEFT OUTER JOIN `product_attribute_values` product_type_attribute_1_product_attribute_values ON `product_type_attribute_1_product_attributes`.`id` = `product_type_attribute_1_product_attribute_values`.`product_attribute_id`
LEFT OUTER JOIN `product_attribute_value_prices` product_type_attribute_1_product_attribute_value_product_attribute_value_prices ON `product_type_attribute_1_product_attribute_values`.`id` = `product_type_attribute_1_product_attribute_value_product_attribute_value_prices`.`product_attribute_value_id`
LEFT OUTER JOIN `_product_attributes_product_types` product_type_attribute_2__product_attributes_product_types ON `product_types`.`id` = `product_type_attribute_2__product_attributes_product_types`.`_id`
LEFT OUTER JOIN `product_attributes` product_type_attribute_2_product_attributes ON `product_type_attribute_2_product_attributes`.`id` = `product_type_attribute_2__product_attributes_product_types`.`_id`
LEFT OUTER JOIN `product_attribute_values` product_type_attribute_2_product_attribute_values ON `product_type_attribute_2_product_attributes`.`id` = `product_type_attribute_2_product_attribute_values`.`product_attribute_id`
LEFT OUTER JOIN `product_attribute_value_prices` product_type_attribute_2_product_attribute_value_product_attribute_value_prices ON `product_type_attribute_2_product_attribute_values`.`id` = `product_type_attribute_2_product_attribute_value_product_attribute_value_prices`.`product_attribute_value_id`
WHERE `product_types`.`active` = 1
AND `printers`.`active` = 1
AND `product_type_attribute_1_product_attribute_values`.`default` = 1
AND `product_type_attribute_1_product_attributes`.`id` = 1
AND `product_type_attribute_1_product_attribute_value_product_attribute_value_prices`.`value` >= 0
AND product_type_attribute_1_product_attribute_value_product_attribute_value_prices.product_id = `products`.`id`
AND product_type_attribute_1_product_attribute_value_product_attribute_value_prices.size_id = (SELECT `sizes`.`id` FROM `sizes` WHERE `sizes`.`default` = 1 AND `sizes`.`product_type_id` = `product_types`.`id`)
AND product_type_attribute_1_product_attribute_value_product_attribute_value_prices.number_range_id = (SELECT `number_ranges`.`id` FROM `number_ranges` WHERE `number_ranges`.`default` = 1 AND `number_ranges`.`product_type_id` = `product_types`.`id`)
AND `product_type_attribute_2_product_attribute_values`.`default` = 1
AND `product_type_attribute_2_product_attributes`.`id` = 2
AND `product_type_attribute_2_product_attribute_value_product_attribute_value_prices`.`value` >= 0
AND product_type_attribute_2_product_attribute_value_product_attribute_value_prices.product_id = `products`.`id`
AND product_type_attribute_2_product_attribute_value_product_attribute_value_prices.size_id = (SELECT `sizes`.`id` FROM `sizes` WHERE `sizes`.`default` = 1 AND `sizes`.`product_type_id` = `product_types`.`id`)
AND product_type_attribute_2_product_attribute_value_product_attribute_value_prices.number_range_id = (SELECT `number_ranges`.`id` FROM `number_ranges` WHERE `number_ranges`.`default` = 1 AND `number_ranges`.`product_type_id` = `product_types`.`id`)

 

[BertyBoy]

The four subqueries don't help the explain plan. Unless the conditions used to select in the subquery make use of the primary / unique key. You may know it will return a single value, but Oracle / MySQL doesn't unless you use Unique / Primary Key constraints.

It woud help us help you if the aliases in the query were a little more terse.

 

[big_malk]

The four subqueries don't help the explain plan. Unless the conditions used to select in the subquery make use of the primary / unique key. You may know it will return a single value, but Oracle / MySQL doesn't unless you use Unique / Primary Key constraints.

It woud help us help you if the aliases in the query were a little more terse.

I've tried simplifying it by swapping the subquery for the specific value they'd select, but it made no difference.
I know the aliases are pretty unreadable, but they are generated automatically by DMZ, which supposedly keeps things simple, but doesn't make very clean queries!

 

[JFreak]

The subqueries are your problem. You have a comparison "A = (subquery)" and this will fail if the subquery returns more than one row. If you cannot be sure it only returns one row, you should think some alternative solutions. Or replace the equal comparison with an "IN" operator, but that would skew your results.

I've said this to you earlier, but let me repeat myself: make it simpler.

 

[Cromulent]

This is where table views come into play. I'm not sure if MySQL supports them but if it does just create a view with the columns from the tables you want to query and then query the view. That way it just looks like a simple single table query and all the junk is handled behind the scenes.

 

[BertyBoy]

In your original question, you wanted to know why the EXPLAIN PLAN fails. Perhaps I was being to subtle when trying to show you that it is because you cannot guarantee a single value coming back from each of the subqueries that the explain plan fails.

The four subqueries don't help the explain plan. Unless the conditions used to select in the subquery make use of the primary / unique key. You may know it will return a single value, but Oracle / MySQL doesn't unless you use Unique / Primary Key constraints.

You could just add a MAX( ) or MIN( ) to the field in each subquery, that should satisfy the EXPLAIN PLAN. It looks like you're selecting the default value for the product type in each subquery, so it should be safe to use MIN( ) or MAX( ).

You also asked about getting too many rows back, or similar rows where you only expect one of them. This will be due to a fault in your SQL. Without a better understanding of your schema, people here can only make guesses.

Two of the LEFT JOINs look suspect to me immediately ...

LEFT OUTER JOIN `product_attributes` product_type_attribute_1_product_attributes ON `product_type_attribute_1_product_attributes`.`id` = `product_type_attribute_1__product_attributes_product_types`.`_id`

joining a product_attributes ID with a product_types ID ??

Again, I need to know more on your schema to give a better answer, but I do that for a living with Oracle anyway - on a highly paid contract basis in Edinburgh / Glasgow.

Draw out what your query does and you'll see the issues straight away.