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:Microsoft Windows (XP)
Assigned to: CPU Architecture:Any

[4 Feb 2006 15:23] BB BB
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
[6 Feb 2006 8:37] BB BB
Sorry I did a mistake: the PRIMARY KEY for the table `pln` is PRIMARY KEY (`id`).
[10 Feb 2006 16:28] Valeriy Kravchuk
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.
[7 Mar 2006 17:57] BB BB
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;
[6 Apr 2006 12:57] Valeriy Kravchuk
Test case from last comment works OK (no error messages) both in 4.1.19-BK and 5.0.21-BK.