SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Angry strange cartesian product

    Merry Christmas and Happy New Year to all.

    I have a strange cartesian product error. I am doing a query from a db I did not create. I can see the tables in it and have a search query that searches in 2 tables. If I search for "name LIKE '670%' OR model LIKE '%670%' " I get the cartesian product error.

    However, if I search for "name LIKE '670%' " by itself I get an empty query set, 0 rows returned.
    And model LIKE '%670%' pdoduces an expected query of 7 rows.

    This makes absolutely no sense to me. If it does not match anything in the name column, how the heck is it creating a cartesian product result???

    Furthermore, if I change the query to something that is contained in the name column, then I don't get the error.

    This is driving me nuts. To compund the matter further there is a third column involved which is doing the same thing. But I am currently testing this with just the two columns to narrow things down a bit.

    Has anyone seen something like this or does it sound familiar? I am desperate.

    Thanks
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    how would we know it's a cartesian product error if we can't see the query?

    any chance you could show it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SELECT DISTINCT p.products_id, p.products_model, pd.products_name FROM products p, products_description pd WHERE p.products_id = pd.products_id AND pd.language_id=2 AND pd.products_name LIKE '670%' OR p.products_model LIKE '%670%'

    How would you know it's a cartesian product error if you don't know the structure of the db?
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    parentheses problem

    what your query says is

    ... WHERE A AND B AND C OR D

    because ANDs take precendence over ORs, this is evaluated as --

    ... WHERE ( A AND B AND C ) OR ( D )

    what you actually want in this instance is

    ... WHERE A AND B AND ( C OR D )

    rule of thumb: whenever you mix ANDs and ORs, you should hardcode the parentheses to be sure you get exactly what you want

    tip: use JOIN syntax, and many of these situations will be eliminated

    in this case, though, you still do need parentheses even with JOIN syntax
    Code:
    select p.products_id
         , p.products_model
         , pd.products_name 
      from products p
    inner
      join products_description pd 
        on p.products_id 
         = pd.products_id 
     where pd.language_id = 2 
       and ( 
           pd.products_name like '670%' 
        or p.products_model like '%670%' 
           )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    tip: use JOIN syntax, and many of these situations will be eliminated
    Could you PLEASE elaborate on this! I have purposely avoided using JOIN syntax because it doesn't seem as clear as using a WHERE clause. Also, admitedlly, it confused me a bit. I was just reading on this very forum, how the difference between a JOIN syntax and a WHERE clause was entirely semantic. And I've read in other sources that they are the exact same thing, so I figured, why complicate my life and try to make sense of inner and outer joins when I can do things with WHERE clauses.

    Please elinghten me as to why I should learn JOINS. If they really avoid these types of problems I'll hit the books right now!

    Thanks.
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  6. #6
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    BTW
    The parentheses trick worked! You have my undying gratitude! I don't know why I never thought of it. What an idiot!

    Thank you, thank you, thank you.

    PS
    I'd still like to know why a JOIN would be better

    How's the weather in Toronto? I lived there for a year in the late 80's and absolutely hated it (the weather).
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    1. JOIN syntax is self-documenting, because the join conditions for the latest table are right there in the ON clause and not mixed into the WHERE clause with other join conditions for other tables as well as non-join filter conditions, making it difficult to distinguish which conditions are for which tables (especially if there are three or more tables in the join), and, occasionally, leading to mixups such as the one you encountered in this example

    2. JOIN syntax is easier to write, and therefore avoids the accidental omission of the join conditions altogether, since you get a syntax error, whereas with the conditions omitted from the WHRE clause, you get a cartesian product

    3. JOIN syntax is trivial to change from INNER join to LEFT/RIGHT OUTER join, whereas an outer join in the WHERE style is problematic, if not outright impossible in some databases like MySQL (if you don't know the difference between an inner and an outer join, you will benefit from doing a bit of research)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    note that if you are always looking only for pd.language_id=2, then you could make that condition part of the ON clause, thus you wouldn't need the parentheses

    Code:
    select p.products_id
         , p.products_model
         , pd.products_name 
      from products p
    inner
      join products_description pd 
        on p.products_id 
         = pd.products_id 
       and pd.language_id 
         = 2 
     where pd.products_name like '670%' 
        or p.products_model like '%670%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •