| 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 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.
Description: Hi everybody, I noticed a bogus behaviour of a SELECT query with a derived table. I'm doing the test on a Windows 2000 Professionnal pc with MySQL 4.1 Alpha. I hope this helps, François MASUREL BORDEAUX - FRANCE How to repeat: ############################################## # Here is the script to create the test tables ############################################## USE test; DROP TABLE IF EXISTS `label`; CREATE TABLE `label` ( `id` int(10) unsigned NOT NULL auto_increment, `id_object` int(10) unsigned default '0', `id_version` int(10) unsigned NOT NULL default '1', `label` varchar(100) NOT NULL default '', `description` text, PRIMARY KEY (`id`), KEY `id_object` (`id_object`), KEY `id_version` (`id_version`) ) TYPE=InnoDB CHARSET=latin1; INSERT INTO `label` (`id`, `id_object`, `id_version`, `label`, `description`) VALUES("6", "3382", "9", "Test", NULL); INSERT INTO `label` (`id`, `id_object`, `id_version`, `label`, `description`) VALUES("7", "102", "5", "Le Pekin (Test)", NULL); INSERT INTO `label` (`id`, `id_object`, `id_version`, `label`, `description`) VALUES("584", "1794", "4", "Test de resto", NULL); INSERT INTO `label` (`id`, `id_object`, `id_version`, `label`, `description`) VALUES("837", "1822", "6", "Test 3", NULL); INSERT INTO `label` (`id`, `id_object`, `id_version`, `label`, `description`) VALUES("1119", "3524", "1", "Societe Test", NULL); INSERT INTO `label` (`id`, `id_object`, `id_version`, `label`, `description`) VALUES("1122", "3525", "1", "Fournisseur Test", NULL); DROP TABLE IF EXISTS `objects`; CREATE TABLE `objects` ( `id` int(10) unsigned NOT NULL auto_increment, `id_version` int(10) unsigned NOT NULL default '1', PRIMARY KEY (`id`), KEY `id_version` (`id_version`) ) TYPE=InnoDB CHARSET=latin1; INSERT INTO `objects` (`id`, `id_version`) VALUES("3524", "1"); INSERT INTO `objects` (`id`, `id_version`) VALUES("3525", "1"); INSERT INTO `objects` (`id`, `id_version`) VALUES("1794", "4"); INSERT INTO `objects` (`id`, `id_version`) VALUES("102", "5"); INSERT INTO `objects` (`id`, `id_version`) VALUES("1822", "6"); INSERT INTO `objects` (`id`, `id_version`) VALUES("3382", "9"); ############################################## # Here is the script that doesn't seem to work ############################################## 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) ############################################ # What it should give ############################################ # id label # 3382 Test # 102 Le Pekin (Test) # 1794 Test de resto # 1822 Test 3 # 3524 Societe Test # 3525 Fournisseur Test ############################################ # What it gives on my PC ############################################ # id label # 3382 Fournisseur Test # 102 Fournisseur Test # 1794 Fournisseur Test # 1822 Fournisseur Test # 3524 Fournisseur Test # 3525 Fournisseur Test ############################################ # After modifying slightly the query # by adding one extra field, it works fine ############################################ SELECT objects.id, label.label, label.id_version 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) ############################################ # Correct results ############################################ # id label id_version # 3382 Test 9 # 102 Le Pekin (Test) 5 # 1794 Test de resto 4 # 1822 Test 3 6 # 3524 Societe Test 1 # 3525 Fournisseur Test 1 # I also noticed that it seems to work fine # when I remove the TEXT field in the 'label' table. Suggested fix: No idea, I'm a newbie :-) Good luck.