1 message in com.mysql.lists.bugsRe: Bug in SELECT statement
FromSent OnAttachments
sas...@mysql.com22 Jul 2000 14:57 
Subject:Re: Bug in SELECT statement
From:sas...@mysql.com (sas@mysql.com)
Date:07/22/2000 02:57:31 PM
List:com.mysql.lists.bugs

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 | | /*/ /*/ /*/ \*\_ |*| |*||*| sas@mysql.com | | /*/ /*/ /*/\*\/*/ \*\|*| |*||*| Provo, Utah, USA | | /*/ /*/ /*/\*\_/*/ \*\_/*/ |*|____ | | ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^ | | /*/ \*\ Developers Team | +------------------------------------------------------------------+