This service will be undergoing maintenance at 00:00 UTC, 2016-09-28. It is expected to last about 1 hours
Bug 109288 - mysql-3.23.58-1.73 client and 3.23.49-3 server breaks implicit 'join' behaviour
mysql-3.23.58-1.73 client and 3.23.49-3 server breaks implicit 'join' behaviour
Status: CLOSED WORKSFORME
Product: Red Hat Linux
Classification: Retired
Component: mysql (Show other bugs)
7.3
All Linux
medium Severity medium
: ---
: ---
Assigned To: Patrick Macdonald
David Lawrence
:
Depends On:
Blocks:
  Show dependency treegraph
 
Reported: 2003-11-06 11:09 EST by R P Herrold
Modified: 2007-04-18 12:59 EDT (History)
0 users

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2003-11-11 17:41:59 EST
Type: ---
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:


Attachments (Terms of Use)

  None (edit)
Description R P Herrold 2003-11-06 11:09:02 EST
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.
Comment 1 Patrick Macdonald 2003-11-10 13:45:02 EST
What's the syntax error you are receiving?  I'm also assuming that
the version in the subject is incorrect.
Comment 2 R P Herrold 2003-11-10 15:03:47 EST
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.
Comment 3 R P Herrold 2003-11-10 15:37:50 EST
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.
Comment 4 R P Herrold 2003-11-10 15:38:47 EST
amending subject line
Comment 5 Patrick Macdonald 2003-11-11 17:41:59 EST
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.

Note You need to log in before you can comment on or make changes to this bug.