List:General Discussion« Previous MessageNext Message »
From:Jocelyn Fournier Date:April 11 2003 12:49am
Subject:Re: 4.1 Alpha - Bug in select with derived table - Same value in each row
View as plain text  
Hi,

With the latest 4.1 bk tree on Linux, I have no problem :


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 |
+------+------------------+


Regards,
  Jocelyn



----- Original Message -----
From: <mably@stripped>
To: "mysql" <mysql@stripped>
Sent: Thursday, April 10, 2003 11:39 PM
Subject: 4.1 Alpha - Bug in select with derived table - Same value in each
row


Hi everybody,

I noticed a bogus behaviour of a SELECT query with a derived table in MySQL
4.1 Win32 Alpha.

I'm doing the test on a Windows 2000 Professionnal machine and a Windows
2000 Server one.

Could you tell me if you get the same strange behavior?

Thanx in advance.

##############################################
# 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 two PCs
############################################

 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.

------------------------------------------

Faites un voeu et puis Voila ! www.voila.fr



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1






Thread
4.1 Alpha - Bug in select with derived table - Same value in each rowmably@voila.fr11 Apr
  • Re: 4.1 Alpha - Bug in select with derived table - Same value in each rowJocelyn Fournier11 Apr