Bug #69774 | Defining a view with case statement and having statement cause error1356 | ||
---|---|---|---|
Submitted: | 18 Jul 2013 4:00 | Modified: | 5 May 2018 14:44 |
Reporter: | D yy | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
Version: | 5.1.71, 5.5.32 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | 1356, case, having, null, VIEW |
[18 Jul 2013 4:00]
D yy
[18 Jul 2013 12:14]
MySQL Verification Team
Hello, Thank you for the bug report and the test case. Verified as described on 5.1.71, 5.5.32. Thanks, Umesh
[18 Jul 2013 12:16]
MySQL Verification Team
How to repeat: use test; create table t(a int); create or replace view tv_bad as select case when 1 then a end b from t having b is null; select * from tv_bad; create or replace view tv_okk as select case when 1 then a end b from t having b is not null; select * from tv_okk; // 5.1.71 - affected mysql> select version(); +------------+ | version() | +------------+ | 5.1.71-log | +------------+ 1 row in set (0.00 sec) mysql> use test \Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> create table t(a int); Query OK, 0 rows affected (0.01 sec) mysql> create or replace view tv_bad as select case when 1 then a end b from t having b is null; Query OK, 0 rows affected (0.01 sec) mysql> select * from tv_bad; ERROR 1356 (HY000): View 'test.tv_bad' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysql> mysql> create or replace view tv_okk as select case when 1 then a end b from t having b is not null; Query OK, 0 rows affected (0.00 sec) mysql> select * from tv_okk; Empty set (0.00 sec) mysql> // 5.5.32 - affected mysql> select version(); +------------+ | version() | +------------+ | 5.5.32-log | +------------+ 1 row in set (0.00 sec) mysql> use test Database changed mysql> create table t(a int); Query OK, 0 rows affected (0.01 sec) mysql> create or replace view tv_bad as select case when 1 then a end b from t having b is null; Query OK, 0 rows affected (0.01 sec) mysql> select * from tv_bad; ERROR 1356 (HY000): View 'test.tv_bad' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysql> mysql> create or replace view tv_okk as select case when 1 then a end b from t having b is not null; Query OK, 0 rows affected (0.00 sec) mysql> select * from tv_okk; Empty set (0.00 sec) // 5.6.12 - Not affected mysql> select version(); +-----------+ | version() | +-----------+ | 5.6.12 | +-----------+ 1 row in set (0.00 sec) mysql> create table t(a int); Query OK, 0 rows affected (0.03 sec) mysql> create or replace view tv_bad as select case when 1 then a end b from t having b is null; Query OK, 0 rows affected (0.00 sec) mysql> select * from tv_bad; Empty set (0.00 sec) mysql> create or replace view tv_okk as select case when 1 then a end b from t having b is not null; Query OK, 0 rows affected (0.01 sec) mysql> select * from tv_okk; Empty set (0.00 sec)
[5 May 2018 14:44]
Jon Stephens
Documented fix as follows in the MySQL 5.6.12 changelog: Queries against a view that was created using a CASE operator and a HAVING clause were incorrectly rejected with ER_VIEW_INVALID (Error 1356). Closed.