Bug #270 | Bug in select with derived table - Same value in each row | ||
---|---|---|---|
Submitted: | 10 Apr 2003 9:19 | Modified: | 23 Aug 2003 12:07 |
Reporter: | Francois MASUREL | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server | Severity: | S2 (Serious) |
Version: | 4.1 | OS: | Windows (Windows 2000) |
Assigned to: | CPU Architecture: | Any |
[10 Apr 2003 9:19]
Francois MASUREL
[10 Apr 2003 11:19]
MySQL Verification Team
I used your script without ANY changes and I have got the following result: id label 3382 Test 102 Le Pekin (Test) 1794 Test de resto 1822 Test 3 3524 Societe Test 3525 Fournisseur Test
[11 Apr 2003 4:40]
Francois MASUREL
Here is an exact screen copy of what I get: C:\Program Files\mysql\bin>mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 17 to server version: 4.1.0-alpha-max-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use test; Database changed mysql> select * from label -> -> select * from label; ERROR 1064: You have an error in your SQL syntax. Check the manual that corresp onds to your MySQL server version for the right syntax to use near 'select * fro m label' at line 2 mysql> select * from label; +------+-----------+------------+------------------+-------------+ | id | id_object | id_version | label | description | +------+-----------+------------+------------------+-------------+ | 6 | 3382 | 9 | Test | NULL | | 7 | 102 | 5 | Le Pekin (Test) | NULL | | 584 | 1794 | 4 | Test de resto | NULL | | 837 | 1822 | 6 | Test 3 | NULL | | 1119 | 3524 | 1 | Societe Test | NULL | | 1122 | 3525 | 1 | Fournisseur Test | NULL | +------+-----------+------------+------------------+-------------+ 6 rows in set (0.14 sec) mysql> SELECT objects.id, label.label -> FROM objects INNER JOIN -> (SELECT label.id_object as id_object FROM label -> WHERE label.label LIKE '%test%') AS lbl -> ON (objects.id = lbl.id_object) INNER JOIN -> label ON (objects.id = label.id_object) -> ; +------+------------------+ | id | label | +------+------------------+ | 3382 | Fournisseur Test | | 102 | Fournisseur Test | | 1794 | Fournisseur Test | | 1822 | Fournisseur Test | | 3524 | Fournisseur Test | | 3525 | Fournisseur Test | +------+------------------+ 6 rows in set (0.09 sec) mysql>
[11 Apr 2003 6:53]
MySQL Verification Team
Sorry but in my Win2k Server, I get the correct result: Microsoft Windows 2000 [Versão 5.00.2195] (C) Copyright 1985-2000 Microsoft Corp. c:\mysql\bin>mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.0-alpha-max-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> SELECT objects.id, label.label -> FROM objects INNER JOIN -> (SELECT label.id_object as id_object FROM label -> WHERE label.label LIKE '%test%') AS lbl -> ON (objects.id = lbl.id_object) INNER JOIN -> label ON (objects.id = label.id_object); +------+------------------+ | id | label | +------+------------------+ | 3382 | Test | | 102 | Le Pekin (Test) | | 1794 | Test de resto | | 1822 | Test 3 | | 3524 | Societe Test | | 3525 | Fournisseur Test | +------+------------------+ 6 rows in set (0.01 sec)
[15 May 2003 8:52]
Michael Widenius
I have opened this as I was able to repeat this in 4.1.0 with InnoDB tables. I have also added the test case to our test suite. Sinisa will fix this when he comes back from his vacation, which should be before 4.1.1 will be out.
[23 Jul 2003 15:08]
Mikael Sheikh
FYI, I have replicated this bug using Francois' test script under Mac OS X. I had the same problem with a similar query.