MySQL Bugs: #17120: LOCK tables with subqueries and alias table

Bug #17120	LOCK tables with subqueries and alias table
Submitted:	4 Feb 2006 15:23	Modified:	6 Apr 2006 12:57
Reporter:	BB BB	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any

Description:
I observed an interesting phenomena (MyISAM engine) with the LOCK TABLES command and subqueries:

As it is specified in the MySQL doc, "If [the] queries refer to a table using an alias, then [we] must lock the table using that same alias. It does not work to lock the table without specifying the alias..."

But in the following message, even if I lock all tables with alias, I get the error message code 1100, which is about the unlocked tables:

LOCK TABLES pln READ LOCAL, sector AS READ LOCAL, sector AS ss READ LOCAL;
select distinct pln.id where locate('LECBVRN',(select GROUP_CONCAT(sector order by entrytime SEPARATOR " ") from sector AS ss where ss.id=id);
UNLOCK TABLES;

The error messages are:
Error Code : 1100
Table 'ss' was not locked with LOCK TABLES
(0 ms taken)

Actually the string parameter of LOCATE command is coming from a SQL query. The sector table (alias ss) should be already locked.

Thx for your help.

How to repeat:
Sector table creation:
CREATE TABLE `sector` (                            
          `id` char(30) NOT NULL,                      
          `Sector` char(15) default NULL,                  
          KEY `IfplidSectorIndex` (`id`,`Sector`),     
          KEY `SectorIndex` (`Sector`)                     
        ) ENGINE=MyISAM DEFAULT CHARSET=latin1   

We are concatenating the 'sector' field, with the same 'id' than in 'pln' table

Pln table creation:
CREATE TABLE `pln` (                          
          `id` char(30) NOT NULL,                 
          PRIMARY KEY  (`Ifplid`),                    
        ) ENGINE=MyISAM DEFAULT CHARSET=latin1

Sorry I did a mistake: the PRIMARY KEY for the table `pln` is PRIMARY KEY (`id`).

Thank you for a problem report. Can you, please, specify the exact query you use. This one:

select distinct pln.id where locate('LECBVRN',(select GROUP_CONCAT(sector order by entrytime SEPARATOR " ") from sector AS ss where ss.id=id);

just gives me syntax error. Please, copy and paste from mysql command line client, along with error message.

Sorry I was in hollydays. I wrote completely the script another time because my last one was particurlarily bad. In the mid-time, I have change my Mysql version to a new one (5.0.11), and I seems the bug has disapeared. I am giving you the script for the need of the test. On a previous version (4.xx), it was not working.
So I go on testing with this new release. Thank you for your help.

CREATE TABLE `sector` (                            
          `id` char(30) NOT NULL,                      
          `Sector` char(15) default NULL,                  
          KEY `IfplidSectorIndex` (`id`,`Sector`),     
          KEY `SectorIndex` (`Sector`)                     
        ) ENGINE=MyISAM DEFAULT CHARSET=latin1   ;

CREATE TABLE `pln` (                          
          `id` char(30) NOT NULL,                 
          PRIMARY KEY  (`id`)                    
        ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

LOCK TABLES pln READ LOCAL, sector READ LOCAL, sector AS ss READ LOCAL;
select distinct pln.id from pln where locate('LECBVRN',(select GROUP_CONCAT(Sector SEPARATOR " ") from sector AS ss where ss.id=pln.id));
UNLOCK TABLES;

Test case from last comment works OK (no error messages) both in 4.1.19-BK and 5.0.21-BK.