Posted on 5 Comments

Two 5.0 bugs in a day

I filed these two MySQL bugs today:

Bug#37910 Dropping unrelated index fails if fk on first col
Bug#37925 Select with comma join pretends a column does not exist

They originate with clients, and occur on recent 5.0 versions. No fatal stuff, but annoying. Both are of the fairly awkward variety and I’m curious why they still exist so late in the 5.0 series.

For instance, the second one looks like a parser bug, and I’d have figured someone tripping over this ages ago. It’s actually a regression as it worked fine in 4.x, the testcase is directly from an app that was in production on a 4.x server, then upgraded to 5.0. Kaboom on this. Quite unexpected, and very annoying as going through the entire app changing the join syntax is a bit of a pest for the developers.

(update on this below, with assistance from Mark Leith – thanks Mark!):

Being aware of
– http://bugs.mysql.com/13551
– http://bugs.mysql.com/14817

and the information from the Docs (http://dev.mysql.com/doc/refman/5.0/en/join.html):

‘However, the precedence of the comma operator is less than of INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column ‘col_name’ in ‘on clause’ may occur. Information about dealing with this problem is given later in this section.’
[…]
‘Previously, the comma operator (,) and JOIN both had the same precedence, so the join expression t1, t2 JOIN t3 was interpreted as ((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression is interpreted as (t1, (t2 JOIN t3)). This change affects statements that use an ON clause, because that clause can refer only to columns in the operands of the join, and the change in precedence changes interpretation of what those operands are.’

That still leaves us with a unclear error reporting. Technically it may be correct based
on parser internals, but users should not need to care about such black magic.

Posted on 5 Comments

5 thoughts on “Two 5.0 bugs in a day

  1. The precedence of , has changed too with respect to JOIN.

    (, binds weaker than JOIN, see http://dev.mysql.com/doc/refman/5.0/en/join.html and http://dev.mysql.com/doc/refman/4.1/en/join.html)

    So, esp. when mixed with LEFT joins it’s a good idea to check all queries anyway.

    Roland.

  2. Regardless. Precedence change does not affect acceptance of syntax, it merely changes execution behaviour.

  3. Sure. but you mentioned:

    “very annoying as going through the entire app changing the join syntax is a bit of a pest for the developers.”

    And regardless of this particular behaviour with the column, bug or not, developers would still need to scrutinize all of their joins, esp. when mixing “comma join” with outer joins.

    Roland

  4. Precedence also affects query acceptance. MySQL 4.0 accepts queries with ambiguous column references that are rightly rejected by the MySQL 5.0 parser. This was very painful for me during an upgrade from 4 to 5, but hopefully it is a one time cost.

  5. precedence doesn’t not affect acceptance of syntax. The query was and is syntactically correct. You don’t get “syntax error” do you ?

    But it affects name resolution rules. And it’s explained in the manual.

    Sergei
    (who is sure that Arjen knew the outcome of his bugreport even before submitting it)

Comments are closed.