MySQL Bugs: #25897: Some queries are no longer possible after a CREATE VIEW fails

Bug #25897	Some queries are no longer possible after a CREATE VIEW fails
Submitted:	28 Jan 2007 5:38	Modified:	9 Mar 2007 4:37
Reporter:	Neil Skrypuch	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S2 (Serious)
Version:	5.0.36-BK, 5.0.32	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
It seems that MySQL starts applying VIEW restrictions to regular SELECTs after a CREATE VIEW fails, observe:

mysql> SELECT * FROM (SELECT * FROM `t1` NATURAL JOIN `t2`) AS `tmp`;
+------+------+------+
| c2   | c1   | c3   |
+------+------+------+
|    2 |    1 |    3 |
|    9 |    8 |   10 |
+------+------+------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM (SELECT * FROM `t2` NATURAL JOIN `t3`) AS `tmp`;
+------+------+------+
| c3   | c2   | c4   |
+------+------+------+
|    3 |    2 |    4 |
|   10 |    9 |   11 |
+------+------+------+
2 rows in set (0.00 sec)

mysql> CREATE VIEW `v1` AS SELECT * FROM (SELECT * FROM `t1` NATURAL JOIN `t2`) AS `tmp`;
ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause
mysql> SELECT * FROM (SELECT * FROM `t1` NATURAL JOIN `t2`) AS `tmp`;
ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause
mysql> SELECT * FROM (SELECT * FROM `t2` NATURAL JOIN `t3`) AS `tmp`;
ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause

Restarting the mysql monitor (and thus reconnecting) allows one to do SELECTs without VIEW restrictions again.

How to repeat:
DROP DATABASE IF EXISTS `testdb`;
CREATE DATABASE `testdb`;
USE `testdb`;
CREATE TABLE `t1` (`c1` int, `c2` int);
CREATE TABLE `t2` (`c2` int, `c3` int);
CREATE TABLE `t3` (`c3` int, `c4` int);
INSERT INTO `t1` VALUES (1, 2), (8, 9);
INSERT INTO `t2` VALUES (2, 3), (9, 10);
INSERT INTO `t3` VALUES (3, 4), (10, 11);
SELECT * FROM (SELECT * FROM `t1` NATURAL JOIN `t2`) AS `tmp`;
SELECT * FROM (SELECT * FROM `t2` NATURAL JOIN `t3`) AS `tmp`;
CREATE VIEW `v1` AS SELECT * FROM (SELECT * FROM `t1` NATURAL JOIN `t2`) AS `tmp`;
SELECT * FROM (SELECT * FROM `t1` NATURAL JOIN `t2`) AS `tmp`;
SELECT * FROM (SELECT * FROM `t2` NATURAL JOIN `t3`) AS `tmp`;

Suggested fix:
MySQL should not apply VIEW restrictions to regular SELECTs after a CREATE VIEW fails.

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

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.36 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `t1` (`c1` int, `c2` int);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `t2` (`c2` int, `c3` int);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `t3` (`c3` int, `c4` int);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `t1` VALUES (1, 2), (8, 9);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO `t2` VALUES (2, 3), (9, 10);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO `t3` VALUES (3, 4), (10, 11);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM (SELECT * FROM `t1` NATURAL JOIN `t2`) AS `tmp`;
S+------+------+------+
| c2   | c1   | c3   |
+------+------+------+
|    2 |    1 |    3 |
|    9 |    8 |   10 |
+------+------+------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM (SELECT * FROM `t2` NATURAL JOIN `t3`) AS `tmp`;
+------+------+------+
| c3   | c2   | c4   |
+------+------+------+
|    3 |    2 |    4 |
|   10 |    9 |   11 |
+------+------+------+
2 rows in set (0.01 sec)

mysql> CREATE VIEW `v1` AS SELECT * FROM (SELECT * FROM `t1` NATURAL JOIN `t2`)
 AS
    -> `tmp`;
ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause
mysql> SELECT * FROM (SELECT * FROM `t1` NATURAL JOIN `t2`) AS `tmp`;
ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause
mysql> SELECT * FROM (SELECT * FROM `t2` NATURAL JOIN `t3`) AS `tmp`;
ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause
mysql> SELECT * FROM (SELECT * FROM `t1` NATURAL JOIN `t3`) AS `tmp`;
ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/19296

ChangeSet@1.2392, 2007-02-04 16:49:24+03:00, kroki@moonlight.home +4 -0
  BUG#25897: Some queries are no longer possible after a CREATE VIEW
             fails
  
  The bug was introduced with the push of the fix for bug#20953: after
  the error on view creation we never reset the error state, so some
  valid statements would give the same error after that.
  
  The solution is to properly reset the error state.

Queued to 5.0-runtime and 5.1-runtime.

Pushed into 5.0.38, 5.1.17

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.38 and 5.1.17 changelogs.

This is still not fixed as of v5.1.47

Please Fix

Sorry, but original test case works with 5.1.47:

valeriy-kravchuks-macbook-pro:mysql-5.1.47-osx10.5-x86_64 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.47 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DROP DATABASE IF EXISTS `testdb`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE DATABASE `testdb`;
Query OK, 1 row affected (0.00 sec)

mysql> USE `testdb`;
Database changed
mysql> CREATE TABLE `t1` (`c1` int, `c2` int);
Query OK, 0 rows affected (0.41 sec)

mysql> CREATE TABLE `t2` (`c2` int, `c3` int);
Query OK, 0 rows affected (0.14 sec)

mysql> CREATE TABLE `t3` (`c3` int, `c4` int);
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO `t1` VALUES (1, 2), (8, 9);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO `t2` VALUES (2, 3), (9, 10);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO `t3` VALUES (3, 4), (10, 11);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM (SELECT * FROM `t1` NATURAL JOIN `t2`) AS `tmp`;
+------+------+------+
| c2   | c1   | c3   |
+------+------+------+
|    2 |    1 |    3 |
|    9 |    8 |   10 |
+------+------+------+
2 rows in set (0.01 sec)

mysql> SELECT * FROM (SELECT * FROM `t2` NATURAL JOIN `t3`) AS `tmp`;
+------+------+------+
| c3   | c2   | c4   |
+------+------+------+
|    3 |    2 |    4 |
|   10 |    9 |   11 |
+------+------+------+
2 rows in set (0.00 sec)

mysql> CREATE VIEW `v1` AS SELECT * FROM (SELECT * FROM `t1` NATURAL JOIN `t2`) AS `tmp`;
ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause
mysql> SELECT * FROM (SELECT * FROM `t1` NATURAL JOIN `t2`) AS `tmp`;
+------+------+------+
| c2   | c1   | c3   |
+------+------+------+
|    2 |    1 |    3 |
|    9 |    8 |   10 |
+------+------+------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM (SELECT * FROM `t2` NATURAL JOIN `t3`) AS `tmp`;
+------+------+------+
| c3   | c2   | c4   |
+------+------+------+
|    3 |    2 |    4 |
|   10 |    9 |   11 |
+------+------+------+
2 rows in set (0.00 sec)

If you have some other test case that proves your point, please, provide it.