Red Hat Bugzilla – Bug 109288
mysql-3.23.58-1.73 client and 3.23.49-3 server breaks implicit 'join' behaviour
Last modified: 2007-04-18 12:59:12 EDT
Previously this syntatically correct SQL code worked. [herrold@server4 herrold]$ diff /var/www/backup/manager/boxscore/body.php /var/www/html/manager/boxscore/body.php 60,63c60,72 < $query1 = "select userid,xtn "; < $query1 .= " from useridxtn,useridteam "; < $query1 .= " where useridxtn.userid = useridteam.userid "; < $query1 .= "and useridteam.team=$ls_limitteam " ; --- but with mysql-3.23.49-3 a two step temporary table is needed, because the implicit join no longer works, returning a syntax error Workaround is this: > // This ugly hack is because mysql-3.23.58-1.73 (Oct 10 2003 issue) > // changes how join namespace works > // > $query1 = "CREATE TEMPORARY TABLE ut "; > $query1 .= "SELECT useridteam.team AS utt, useridteam.userid as utu "; > $query1 .= "FROM useridteam "; > $result1 = mysql_query($query1); > > $query1 = "select userid, xtn "; > $query1 .= " from useridxtn "; > $query1 .= "LEFT JOIN ut "; > $query1 .= "on ut.utu = useridxtn.userid "; > $query1 .= "where ut.utt='$ls_limitteam' " ; =========================== This behaviour is improper as identical namespaces in an implicit join which are duly qualified in the where clause are (or at least were) distinguishable. There is also the obvious performance hit of the extra query hit, AND the setuptime for the temporary table. Please revert to prior behaviour here.
What's the syntax error you are receiving? I'm also assuming that the version in the subject is incorrect.
Taking things in somewhat reverse order: server is at: 3.23.49-3; client is sending queries which newly do not work: client version history is: [herrold@server4 herrold]$ rpm -qa --last | grep 'mysql-' mysql-devel-3.23.58-1.73 Fri 10 Oct 2003 09:35:54 AM EDT mysql-server-3.23.58-1.73 Fri 10 Oct 2003 09:35:41 AM EDT mysql-3.23.58-1.73 Fri 10 Oct 2003 09:35:08 AM EDT php-mysql-4.1.2-7.3.6 Fri 15 Nov 2002 04:22:47 AM EST [herrold@server4 herrold]$ The version on the server side is 'pinned' against upgrade (the unit is in a protected network, and queried by several clients -- the server side version is: 3.23.49-3; the client showing breakage is 3.23.58-1.73. I will set up a test case and get the error message in a moment.
This error with one variant: mysql_error bugzilla 109288: Column: 'userid' in field list is ambiguous -|select userid,xtn from useridxtn,useridteam where useridxtn.userid = useridteam.userid and useridteam.team=2 order by xtn|- Warning: Supplied argument is not a valid MySQL result resource in /var/www/html/manager/boxscore/body.php on line 96 -------------------------- on this query code: $query1 = "select userid,xtn "; $query1 .= " from useridxtn,useridteam "; $query1 .= " where useridxtn.userid = useridteam.userid "; $query1 .= " and useridteam.team=$ls_limitteam " ; with this error display code added: $result1 = mysql_query($query1) or $mysql_eval_error = mysql_error(); if ($mysql_eval_error) { print "<hr>"; print "mysql_error bugzilla 109288: $mysql_eval_error <br>\n"; print "-|" . $query1 . "|- <br>\n"; print "<hr>"; } ==================================== so ... removing ambiguity the query parser sees(first cut): $query1 = "select t1.userid as userid, t1.xtn as xtn "; $query1 .= " from useridxtn as t1 left join useridteam as t2 "; $query1 .= " where t1.userid=t2.userid and t2.team='$ls_limitteam'" ; yields: mysql_error bugzilla 109288: You have an error in your SQL syntax near 'where t1.userid=t2.userid and t2.team='2' order by xtn' at line 1 -|select t1.userid as userid, t1.xtn as xtn from useridxtn as t1 left join useridteam as t2 where t1.userid=t2.userid and t2.team='2' order by xtn|- Warning: Supplied argument is not a valid MySQL result resource in /var/www/html/manager/boxscore/body.php on line 96 ====================== so get rid of the doubled 'as' arguments ... $query1 = "select useridxtn.userid as userid, useridxtn.xtn as xtn "; $query1 .= " from useridxtn left join useridteam "; $query1 .= " where useridxtn.userid=useridteam.userid "; $query1 .= " and useridteam.team='$ls_limitteam'" ; mysql_error bugzilla 109288: You have an error in your SQL syntax near 'where useridxtn.userid=useridteam.userid and useridteam.team='2' order by xtn' at line 1 -|select useridxtn.userid as userid, useridxtn.xtn as xtn from useridxtn left join useridteam where useridxtn.userid=useridteam.userid and useridteam.team='2' order by xtn|- Warning: Supplied argument is not a valid MySQL result resource in /var/www/html/manager/boxscore/body.php on line 97 ================== and this is not totally unexpected as a prototype left join uses 'on' and then 'where' (although a pure 'where' version has also historicly worked). $query1 = "select useridxtn.userid as userid, useridxtn.xtn as xtn "; $query1 .= " from useridxtn left join useridteam "; $query1 .= " on useridxtn.userid=useridteam.userid "; $query1 .= " where useridteam.team='$ls_limitteam'" ; and at this point, it works ... hmmm -- I had not gone back, once I had it working with a two step approach to re-simplify the 'as' expansion. So what seems _really_ to have changed is the scan order expansion of the 'as' code for (output) columns and (input) tables _and_ new strictness of a form to require 'select ... left join ... on ... where' where previously the plain implicit left join with just a 'where' clause worked. =================================================================== dunno if this bug is still interesting in light of this. I have no problem with a close WORKS4ME, but you may have an interest in reverting its behaviour, for support purposes.
amending subject line
Generally, for support purposes, we ask that the client and server be at the same level. I would have probably sent this up to the MySQL folks anyway as a compatibility issue. However, they are only fixing security bugs in 3.23.x now (as focus has transfered to 4.0.x). Closing as worksforme.