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
