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 6:38	Modified:	9 Mar 2007 5:37
Reporter:	Neil Skrypuch
Status:	Closed
Category:	Server: Views	Severity:	S2 (Serious)
Version:	5.0.36-BK, 5.0.32	OS:	Linux (Linux)
Assigned to:	Bugs System	Target Version:

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.