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:
None 
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
Description:
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

How to repeat:
toetsuyideMacBook-Air:~ dyy$ mysql -A test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.1.39 MySQL Community Server (GPL)

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

mysql> create table t(a int);
Query OK, 0 rows affected (0.02 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> 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)

mysql> 

Suggested fix:
both tv_bad  and tv_okk should work well
[18 Jul 2013 12:14] Umesh Shastry
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] Umesh Shastry
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.