MySQL impossible where clause ?

Discussion in 'Web Design and Development' started by big_malk, Aug 5, 2010.

  1. big_malk macrumors 6502a

    Joined:
    Aug 7, 2005
    Location:
    Scotland
    #1
    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!

    Code:
    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`) 
     
  2. BertyBoy macrumors 6502

    Joined:
    Feb 1, 2009
    #2
    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.
     
  3. big_malk thread starter macrumors 6502a

    Joined:
    Aug 7, 2005
    Location:
    Scotland
    #3
    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!
     
  4. JFreak macrumors 68040

    JFreak

    Joined:
    Jul 11, 2003
    Location:
    Tampere, Finland
    #4
    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.
     
  5. Cromulent macrumors 603

    Cromulent

    Joined:
    Oct 2, 2006
    Location:
    The Land of Hope and Glory
    #5
    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.
     
  6. BertyBoy macrumors 6502

    Joined:
    Feb 1, 2009
    #6
    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.

    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.
     

Share This Page