MySQL Bugs: #78581: Select with UNION: Error Code: 1100. Table was not locked with LOCK TABLES

Bug #78581	Select with UNION: Error Code: 1100. Table was not locked with LOCK TABLES
Submitted:	27 Sep 2015 7:32	Modified:	27 Sep 2015 10:14
Reporter:	Dilinos Dilinos	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S1 (Critical)
Version:	5.5.45	OS:	CentOS (CentOS 6)
Assigned to:		CPU Architecture:	Any
Tags:	1100, lock tables, UNION

Description:
Impossible to run UNION select queries on a locked table.

Tested on:
CentOS release 6.7
Server version: 5.5.45-log MySQL Community Server (GPL) by Remi

How to repeat:
CREATE TABLE test_union_lock (
  id INT NOT NULL AUTO_INCREMENT,
  test VARCHAR(45) NULL,
  PRIMARY KEY (`id`));
  
LOCK TABLES test_union_lock READ;

SELECT * FROM test_union_lock
WHERE id=1
UNION ALL
SELECT * FROM test_union_lock
WHERE id=2;

UNLOCK TABLES;

The above returns
Error Code: 1100. Table 'test_union_lock' was not locked with LOCK TABLES

Hi,

This is not a bug, according to:
https://dev.mysql.com/doc/refman/5.5/en/lock-tables.html

"You cannot refer to a locked table multiple times in a single query using the same name. Use aliases instead, and obtain a separate lock for the table and each alias: "

Consider this example that should work:

---
drop table if exists t;
create table t (a int)engine=innodb;
lock tables t as tt1 read, t as tt2 read;
select * from t as tt1 where a=1
union all
select * from t as tt2 where a=2;
unlock tables;
---

Hello,

I see... Unfortunately, the sql string is generated and run by a code which I can't edit. So I can't add the aliases. I don't see a reason why the same tables couldn't be referenced more than once without the table alias. Adding the aliases doesn't affect how the query executes.

The same applies to the requirement to list each table alias in the LOCK TABLES statement. It doesn't make sense. The aliases could be generated randomly by a third party code. It just makes serialized transactions unusable when a third party code is involved.

For me, it's a documented bug.