Re: Bug in SELECT statement

[sasha]

Edward Leigh wrote:
> 
> The following statements yield an incorrect result:
> 
> SELECT tableA.*,tableB.ID,tableB.title FROM tableA LEFT JOIN tableB ON
> tableB.ID IN (tableA.list) WHERE tableA.ID=1
> 
> SELECT tableA.*,tableB.ID,tableB.title FROM tableA,tableB WHERE tableB.ID IN
> (tableA.list) AND tableA.ID=1
> 
> If tableA.list has the value "1,2,3", only one row is returned. If "IN
> (tableA.list)" is replaced with "IN (1,2,3)", 3 rows are returned.
> 
> (There is a good explanation for preferring this way to do a join than using
> another table to store the contents of tableA.list.)
> 
> User: Edward Leigh, OSoLiS
> Version: Ver 9.33 Distrib 3.22.25, for pc-linux-gnu (i686)
> MySQL support: email

First upgrade to 3.22.32 and make sure the bug still persists.

Second, please provide a full test case that we can run, here is the easiest way
to do it:

mysqldump your_db tableA tableB tableC >> /tmp/test-case.sql
then edit /tmp/test-case.sql and append the queries 
mysql test < /tmp/test-case.sql will produce some output -- explain what output
you are expecting

-- 
Sasha Pachev

+------------------------------------------------------------------+
|      ____  __     _____   _____  ___     http://www.mysql.com    |
|     /*/\*\/\*\   /*/ \*\ /*/ \*\ |*|     Sasha Pachev            |
|    /*/ /*/ /*/   \*\_   |*|   |*||*|     sasha@stripped         |
|   /*/ /*/ /*/\*\/*/  \*\|*|   |*||*|     Provo, Utah, USA        |
|  /*/     /*/  /*/\*\_/*/ \*\_/*/ |*|____                         |
|  ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^                        |
|             /*/             \*\ Developers Team                  |
+------------------------------------------------------------------+