#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    5
    Rep Power
    0

    New version of MYSQL will not parse valid select


    Hi - I have two separate instances of MYSQL running on two seperate Verio VPS accounts (Lynux). The older one is running MYSQL version 5.0.51a.

    The newer one was just created today. MYSQL on this is version 5.1.63. I loaded the exact same database as I was using on the older server - same name same tables same data. Now, certain simple selects work just fine but a more complex select fails.

    To test I am making a connection to the MYSQL application through a Putty shell connection.

    The error generated is not very helpful. It says "Unknown column 'x.y' in "on clause'". Obviously, the table x and the column y do exist and that is evident because the exact same select works on the other machine.

    I am unable to offer any other clues other than the select is quite complex. It uses the term "LEFT OUTER JOIN" as well as two additional nested selects within the where clause. However, I removed both of these nested selects and still get the same error. If I keep stripping the select eventually I get a result but I am unable to determine the problem element.

    Could this be some difference between the two versions?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by jfarrow
    Could this be some difference between the two versions?
    you betcha

    look at the ON clause, and you will notice that it references a table other than the ones immediately involved

    this is a typical scenario --
    Code:
    SELECT ...
      FROM a
         , b
    LEFT OUTER
      JOIN c
        ON c.foo = a.bar
    the LEFT OUTER JOIN is supposed to join b and c, but the ON clause references a column from a

    rewrite the query so that it uses explicit JOIN syntax only and removes any commas from the FROM clause
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    5
    Rep Power
    0
    Thanks for the quick reply!

    Your scenario is similar to my select. I suppose this is familiar to you. To strip my select down I have:

    SELECT ...
    FROM a, b, c
    LEFT OUTER JOIN d
    ON d.foo = b.bar

    The error message would then reference b.bar.

    Can you help me by showing me how to rewrite this select correctly?
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    5
    Rep Power
    0
    Further testing indicates that I still may not be clear about your solution. Here is an actual select that I just ran on the problem machine and it works. Would seem to me that it shouldn't:

    select * from parent, child left outer join prices on prices.childID=child.childID where parent.parentID=child.parentID limit 10;
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    5
    Rep Power
    0
    Solved.

    The correct syntax is:

    SELECT ...
    FROM a, c, b
    LEFT OUTER JOIN d
    ON d.foo = b.bar

    IE it appears the table b needs to be reference last in the FROM clause because it also appears in the ON clause.

    That seems to be a backwards step to me.

    How would you handle this? Would this work?

    SELECT ...
    FROM a, c, b
    LEFT OUTER JOIN d
    ON d.foo = b.bar
    LEFT OUTER JOIN e
    ON e.foo = a.bar
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by jfarrow
    Can you help me by showing me how to rewrite this select correctly?
    i would much rather do this using your actual query

    also, remember that you should remove those commas from the FROM clause, and replace them with INNER JOINs
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  12. #7
  13. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    Originally Posted by jfarrow
    The correct syntax is:

    SELECT ...
    FROM a, c, b
    LEFT OUTER JOIN d
    ON d.foo = b.bar

    IE it appears the table b needs to be reference last in the FROM clause because it also appears in the ON clause.

    That seems to be a backwards step to me.
    No, the main issue here is that you are still using the old join syntax with commas in the FROM clause.

    What Rudy meant with his:
    Originally Posted by r937
    rewrite the query so that it uses explicit JOIN syntax only and removes any commas from the FROM clause
    Is that you should write your query with the newer standard explicit join syntax like:
    Code:
    SELECT
      ...
    FROM
      a
    INNER
      JOIN
        b
        ON a.id = b.aid
    LEFT OUTER
      JOIN c
        ON c.foo = a.bar
    The main reasons for this are:
    1. It's the new standard
    2. Much easier to read the query
    3. There is almost no risk for you to forget a join condition and accidentally doing a cross join


    The reason why it works when you place table b last in your "FROM a,c,b" is because the old ","-join syntax are in the newer versions interpreted last so basically your query looks like this:
    Code:
    -- Working but not recommended
    SELECT ...
    FROM 
      (
      b
      LEFT OUTER JOIN d
      ON d.foo = b.bar
      )
    JOIN a, c
    Which would look like this if you had c last:
    Code:
    -- Not working at all
    SELECT ...
    FROM 
      (
      c
      LEFT OUTER JOIN d
      ON d.foo = b.bar
      )
    JOIN a, b
    Which of course doesn't work since b is not known at the point when the ON condition is applied.

    So to summarize, never use the comma syntax and you will be fine and you will write much better queries.
    /Stefan
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    5
    Rep Power
    0
    Thank you both. I'm self taught at php and mysql and it's certainly not my day job! Don't have time to keep up with the updates. The database is for an animal rescue web site that I volunteer my time. The animals thank you.

IMN logo majestic logo threadwatch logo seochat tools logo