MySQL Bugs: #25180: Sub-select fails when table is locked

Bug #25180	Sub-select fails when table is locked
Submitted:	19 Dec 2006 15:45	Modified:	10 Jan 2007 18:40
Reporter:	Karl Lloyd	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	5.0.34-BK, 5.0.22	OS:	Linux (Linux, ubuntu 6.06.2)
Assigned to:		CPU Architecture:	Any

Description:
Don't know whether this is a bug as such, or intended behaviour, but to me it seems illogical:

After you have done a LOCK TABLE `foo` WRITE, if your subsequent SELECT contains a sub-SELECT, such as "SELECT * FROM `foo` WHERE `bar` NOT IN (SELECT DISTINCT `bar` FROM `foo` WHERE `pending`=1) AND `updated`=0 LIMIT 1;" it fails with an "ERROR 1100 (HY000): Table 'foo' was not locked with LOCK TABLES".

At a quick guess, I presume the "SELECT DISTINCT `bar` FROM `foo` WHERE `pending`=1" is performed by a child thread which is locked out of `foo` even though, in my opinion at least, it shouldn't be. Perhaps I'm wrong and something else is going on, but I still think it's not doing what it should.

How to repeat:
mysql> CREATE TABLE `foo` (
  `id` int(11) NOT NULL auto_increment,
  `at` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `bar` varchar(15) NOT NULL,
  `pending` tinyint(1) NOT NULL default '0',
  `updated` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO `foo` (`bar`, `pending`, `updated`) VALUES
('abcde', 1, 1),
('abcde', 1, 0),
('fghij', 1, 0),
('fghij', 0, 1),
('fghij', 0, 0),
('klmno', 0, 0),
('pqrst', 0, 0),
('pqrst', 0, 0),
('uvwxy', 0, 0);
Query OK, 9 rows affected (0.01 sec) Records: 9  Duplicates: 0  Warnings: 0

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

mysql> SELECT * FROM `foo` WHERE `bar` NOT IN (SELECT DISTINCT `bar` FROM `foo` WHERE `pending`=1) AND `updated`=0 LIMIT 1;
ERROR 1100 (HY000): Table 'foo' was not locked with LOCK TABLES

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM `foo` WHERE `bar` NOT IN (SELECT DISTINCT `bar` FROM `foo` WHERE `pending`=1) AND `updated`=0;
+----+---------------------+-------+---------+---------+
| id | at                  | bar   | pending | updated |
+----+---------------------+-------+---------+---------+
|  6 | 2006-12-19 15:38:05 | klmno |       0 |       0 |
|  7 | 2006-12-19 15:38:05 | pqrst |       0 |       0 |
|  8 | 2006-12-19 15:38:05 | pqrst |       0 |       0 |
|  9 | 2006-12-19 15:38:05 | uvwxy |       0 |       0 |
+----+---------------------+-------+---------+---------+
4 rows in set (0.00 sec)

mysql> DROP TABLE `foo`;
Query OK, 0 rows affected (0.02 sec)

Thank you for a bug report. Verified just as described with 5.0.34-BK on Linux:

mysql> CREATE TABLE `foo` (
    ->   `id` int(11) NOT NULL auto_increment,
    ->   `at` timestamp NOT NULL default CURRENT_TIMESTAMP on update
    -> CURRENT_TIMESTAMP,
    ->   `bar` varchar(15) NOT NULL,
    ->   `pending` tinyint(1) NOT NULL default '0',
    ->   `updated` tinyint(1) NOT NULL default '0',
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO `foo` (`bar`, `pending`, `updated`) VALUES
    -> ('abcde', 1, 1),
    -> ('abcde', 1, 0),
    -> ('fghij', 1, 0),
    -> ('fghij', 0, 1),
    -> ('fghij', 0, 0),
    -> ('klmno', 0, 0),
    -> ('pqrst', 0, 0),
    -> ('pqrst', 0, 0),
    -> ('uvwxy', 0, 0);
Query OK, 9 rows affected (0.01 sec)
Records: 9  Duplicates: 0  Warnings: 0

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

mysql> SELECT * FROM `foo` WHERE `bar` NOT IN (SELECT DISTINCT `bar` FROM `foo`
    -> WHERE `pending`=1) AND `updated`=0 LIMIT 1;
ERROR 1100 (HY000): Table 'foo' was not locked with LOCK TABLES
mysql>  UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM `foo` WHERE `bar` NOT IN (SELECT DISTINCT `bar` FROM `foo`
 WHERE `pending`=1) AND `updated`=0 LIMIT 1;
+----+---------------------+-------+---------+---------+
| id | at                  | bar   | pending | updated |
+----+---------------------+-------+---------+---------+
|  6 | 2006-12-19 17:26:12 | klmno |       0 |       0 |
+----+---------------------+-------+---------+---------+
1 row in set (0.01 sec)

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

Even if this is intended behaviour, error message looks misleading. So, it is a bug in any case.

This affects more than just subqueries; it affects UNIONs as well. Replace the original SELECT with this one:

(SELECT * FROM `foo` WHERE `bar` IN ('abcde','fghij')) UNION (SELECT * FROM `foo` WHERE `bar` IN ('pqrst'));

and the same results occur. Note that I am using version 4.1.21. When testing this same case in 4.0.27, it works.

mysql> CREATE TABLE `foo` (
    ->   `id` int(11) NOT NULL auto_increment,
    ->   `at` timestamp NOT NULL default CURRENT_TIMESTAMP on update
    ->     CURRENT_TIMESTAMP,
    ->   `bar` varchar(15) NOT NULL,
    ->   `pending` tinyint(1) NOT NULL default '0',
    ->   `updated` tinyint(1) NOT NULL default '0',
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=MyISAM;

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `foo` (`bar`, `pending`, `updated`) VALUES
    ->   ('abcde', 1, 1),
    ->   ('abcde', 1, 0),
    ->   ('fghij', 1, 0),
    ->   ('fghij', 0, 1),
    ->   ('fghij', 0, 0),
    ->   ('klmno', 0, 0),
    ->   ('pqrst', 0, 0),
    ->   ('pqrst', 0, 0),
    ->   ('uvwxy', 0, 0);
Query OK, 9 rows affected (0.01 sec)
Records: 9  Duplicates: 0  Warnings: 0

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

mysql> (SELECT * FROM `foo` WHERE `bar` IN ('abcde','fghij')) UNION (SELECT * FROM `foo` WHERE `bar` IN ('pqrst'));
ERROR 1100 (HY000): Table 'foo' was not locked with LOCK TABLES

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> (SELECT * FROM `foo` WHERE `bar` IN ('abcde','fghij')) UNION (SELECT * FROM `foo` WHERE `bar` IN ('pqrst'));
+----+---------------------+-------+---------+---------+
| id | at                  | bar   | pending | updated |
+----+---------------------+-------+---------+---------+
|  1 | 2007-01-09 12:02:15 | abcde |       1 |       1 |
|  2 | 2007-01-09 12:02:15 | abcde |       1 |       0 |
|  3 | 2007-01-09 12:02:15 | fghij |       1 |       0 |
|  4 | 2007-01-09 12:02:15 | fghij |       0 |       1 |
|  5 | 2007-01-09 12:02:15 | fghij |       0 |       0 |
|  7 | 2007-01-09 12:02:15 | pqrst |       0 |       0 |
|  8 | 2007-01-09 12:02:15 | pqrst |       0 |       0 |
+----+---------------------+-------+---------+---------+
7 rows in set (0.00 sec)

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

Just to follow up on this further: interestingly, following a "LOCK TABLE `foo` WRITE;" the following query is successful:

mysql> SELECT `at` FROM (SELECT * FROM `foo` WHERE `pending`=1) AS `baz` WHERE `updated` = 0;
+---------------------+
| at                  |
+---------------------+
| 2007-01-10 11:43:04 |
| 2007-01-10 11:43:04 |
+---------------------+
2 rows in set (0.01 sec)

but the following are not:

mysql> SELECT `at`, (SELECT COUNT(*) FROM `foo` WHERE `pending`=1) AS `count` FROM `foo` WHERE `updated` = 1;
ERROR 1100 (HY000): Table 'foo' was not locked with LOCK TABLES

mysql> INSERT INTO `foo` (`at`, `bar`, `pending`, `updated`) (SELECT `at`, 'ABCDE', `pending`, `updated` FROM `foo` WHERE `pending` = 1 AND `updated` = 1);
ERROR 1100 (HY000): Table 'foo' was not locked with LOCK TABLES

mysql> SELECT * FROM `foo` ORDER BY (SELECT COUNT(`bar`) FROM `foo`);
ERROR 1100 (HY000): Table 'foo' was not locked with LOCK TABLES
(although I'm not sure of the extent of support for subselects within ORDER BY clauses)

and, by adding another table:
mysql> CREATE TABLE `bars` (
    ->   `id` int(11) NOT NULL auto_increment,
    ->   `bar` varchar(15) NOT NULL,
    ->   `pending` tinyint(1) NOT NULL default '0',
    ->   `updated` tinyint(1) NOT NULL default '0',
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO `foo` (`bar`, `pending`, `updated`) VALUES
    ->   ('abcde', 1, 1),
    ->   ('abcde', 1, 0),
    ->   ('fghij', 1, 0),
    ->   ('fghij', 0, 1),
    ->   ('fghij', 0, 0),
    ->   ('klmno', 0, 0),
    ->   ('pqrst', 0, 0),
    ->   ('pqrst', 0, 0),
    ->   ('uvwxy', 0, 0);
Query OK, 9 rows affected (0.02 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> UPDATE `foo` SET `bar` = (SELECT REVERSE(`bar`) FROM `bars` WHERE `id` = 1) WHERE `pending` = 1 AND `updated` = 1;
ERROR 1100 (HY000): Table 'bars' was not locked with LOCK TABLES

mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.01 sec)

mysql> DROP TABLE `bars`;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE `foo`;
Query OK, 0 rows affected (0.00 sec)

Perhaps this is helpful info dev-wise.

Should have been "INSERT INTO `bars`" in my previous submission, of course.

Hi, Karl!

Thank you for your interest in MySQL!

This report is duplicate of already known "Not a Bug" bug #5651.

Altough I agree that this restriction is not very natural and
error message can be improved. Feel free to add appropriate
feature request!

Yes, I concede that in the last example (UPDATE with subselect) I should have locked both `foo` and `bars` and does indeed work when this is done. However, according to the documentation quoted in #5651 (http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html) the following should work:

mysql> LOCK TABLES `foo` WRITE, `foo` `oof` WRITE;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM `foo` WHERE `bar` NOT IN (SELECT DISTINCT `bar` FROM `oof` WHERE `pending`=1) AND `updated`=0 LIMIT 1;
ERROR 1100 (HY000): Table 'oof' was not locked with LOCK TABLES

but doesn't. As this is different from my initial submission, and I think this IS a bug, perhaps I should submit this as a new report?

Hi, Karl!

Still this is not a bug. This query is not supposed to work as it refers to the aliased table incorrectly. Please re-read documentation, particularly pay attention to the example below:

<quote>
Conversely, if you lock a table using an alias, you must refer to it in your queries using that alias:

mysql> LOCK TABLE t AS myalias READ;
mysql> SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> SELECT * FROM t AS myalias;
</quote>

Apologies, my syntax was wrong. Many thanks for your help.