Bug #18602: Table left joins return wrong results

From: php_bugs@intrax.com Operating system: all (bug in RH7.2 & Tru64 4.0F) PHP version: 4.2.2 PHP Bug Type: MySQL related Bug description: Table left joins return wrong results Linux: MySQL v3.53.21 Server version: Apache/2.0.39 Tru64 Unix: MySQL v3.21.3 Server version: Apache/1.3.6 and Server version: Apache/2.0.39 PHP compile line: configure --with-mysql=/usr/local \ --with-apxs2=/usr/local/apache2/bin/apxs MySQL database schema: Project table: primary key (projname) projname varchar(60) si_guy varchar(30) hw_guy varchar(30) sw_guy varchar(30) User table: primary key (user) user varchar(30) fullname varchar(30) With either version of MySQL, typing this query into MySQL's command line interface works: select p.projname, u1.fullname, u2.fullname, u3.fullname from projects p left join users u1 on u1.user = p.si_guy left join users u2 on u2.user = p.sw_guy left join users u3 on u3.user = p.hw_guy However, this PHP query returns the project name, and the hw_guy only. If I shuffle the left joins around, I find that PHP always returns the last left join, and drops the two other left joins. <?php /* Connecting, selecting database */ $link = mysql_connect() or die("Can't connect to MySQL database!"); mysql_select_db("proj2") or die("Could not select the project database"); /* Performing SQL query */ $query = "select p.projname, u1.fullname, u2.fullname, u3.fullname from projects p left join users u1 on u1.user = p.si_guy left join users u2 on u2.user = p.sw_guy left join users u3 on u3.user = p.hw_guy"; $result = mysql_query($query) or die("Query failed"); while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) { print "<TR>\n"; foreach ($line as $col_value) { print "\t\t<TD ALIGN=CENTER>$col_value</TD>\n"; } print "</TR>\n"; } mysql_free_result($result); mysql_close($link); ?> -- Edit bug report at http://bugs.php.net/?id=18602&edit=1 -- Fixed in CVS: http://bugs.php.net/fix.php?id=18602&r=fixedcvs Fixed in release: http://bugs.php.net/fix.php?id=18602&r=alreadyfixed Need backtrace: http://bugs.php.net/fix.php?id=18602&r=needtrace Try newer version: http://bugs.php.net/fix.php?id=18602&r=oldversion Not developer issue: http://bugs.php.net/fix.php?id=18602&r=support Expected behavior: http://bugs.php.net/fix.php?id=18602&r=notwrong Not enough info: http://bugs.php.net/fix.php?id=18602&r=notenoughinfo Submitted twice: http://bugs.php.net/fix.php?id=18602&r=submittedtwice register_globals: http://bugs.php.net/fix.php?id=18602&r=globals