Bug #6972 SELECT UNION on same table with LOCK

• Submitted: 3 Dec 2004 0:29
• Modified: 3 Dec 2004 19:49
• Reporter: Eric Grossi
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.7
• OS: Windows (Win2000)
• Assigned to: Sergey Petrunya
• CPU Architecture: Any

[3 Dec 2004 0:29] Eric Grossi

Description:
LOCK TABLE `object` READ;
SELECT * FROM `object` WHERE `tid`=1
UNION
SELECT * FROM `object` WHERE `tid`=2;

Give #1100 - Table 'object' was not locked with LOCK TABLES 

How to repeat:
LOCK TABLE `object` READ;
SELECT * FROM `object` WHERE `tid`=1
UNION
SELECT * FROM `object` WHERE `tid`=2;

Suggested fix:
Work around:
LOCK TABLE `object` READ, `object` AS `object1` READ;
SELECT * FROM `object` WHERE `tid`=1
UNION
SELECT * FROM `object1` WHERE `tid`=2

[3 Dec 2004 2:47] MySQL Verification Team

Thank you for the bug report. Verified on Windows 4.1.7 and Linux
4.1.8.

Additional Info.
This works on Windows 4.0.22

mysql> LOCK TABLE `object` READ;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM `object` WHERE `tid`=1
    -> UNION
    -> SELECT * FROM `object` WHERE `tid`=2;
+------+
| tid  |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.05 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.0.22-nt |
+-----------+
1 row in set (0.00 sec)

[3 Dec 2004 19:49] MySQL Verification Team

In discussion with Sanja from the development team this lock behavior
is an intentional implementation introduced starting from the 4.1.XX
version, as mentioned in the Manual:

http://dev.mysql.com/doc/mysql/en/LOCK_TABLES.html

Thank you for the bug report.