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:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1 OS:Microsoft Windows (Windows 2000)
Assigned to: Sinisa Milivojevic CPU Architecture:Any

[10 Apr 2003 9:19] Francois MASUREL
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.
[10 Apr 2003 11:19] Sinisa Milivojevic
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] Miguel Solorzano
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.