MySQL Bugs: #60295: Error 1356 on view that executes fine as a query

Bug #60295	Error 1356 on view that executes fine as a query
Submitted:	1 Mar 2011 16:03	Modified:	27 Apr 2011 0:12
Reporter:	Wolfgang Schulze-Zachau	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S2 (Serious)
Version:	5.1.49, 5.1.55	OS:	Any (Debian squeeze, Mac OS X)
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
A query that executes without any warnings produces an error when executed from within a view. This can be overcome by explicitly identifying tables for each of the columns in the WHERE or HAVING clause.

This is a regression, as this works fine with 5.0.51a (as in Debian/Lenny), which is also why I suggest this is a serious bug. It only happened to me after I upgraded from lenny to squeeze and broke a web application.

How to repeat:
Create two tables name "schedule" and "bps" that have both a column of the same name. Example:

CREATE TABLE  `bcs`.`bps` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `status` smallint(6) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM 

CREATE TABLE  `bcs`.`schedule` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bps_id` int(11) NOT NULL,
  `status` smallint(6) NOT NULL,
  `message` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_bps` (`bps_id`),
) ENGINE=MyISAM

Then create a view with:
CREATE OR REPLACE VIEW rowcounter_1 AS SELECT s.*, b.name as bps_name FROM schedule s INNER JOIN bps b ON s.bps_id = b.id HAVING status IN ('10') ORDER BY status ASC

"schedule" and "bps" are both tables that have a column named "status". When the query in the view is executed as a query, no warnings or errors occur and the result set is displayed as expected. The view is created without error or warning. When the view is executed with "Select * from bcs.rowcounter", the following error occurs:

1356: View 'bcs.rowcounter' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

This can be overcome by explicitly identifying the table, i.e. by using "s.status" instead of "status".

Suggested fix:
Since the resultset of the query part only has one column named "status", the HAVING clause should not throw an error and implicitly use that column.

Please, check with a newer version, 5.1.55. I do not see any problem (other than extra comma in your code):

macbook-pro:mysql-5.1.55-osx10.5-x86_64 openxs$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.55 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> create database bcs;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE  `bcs`.`bps` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `name` varchar(100) NOT NULL,
    ->   `status` smallint(6) NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=MyISAM 
    -> ;
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE  `bcs`.`schedule` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `bps_id` int(11) NOT NULL,
    ->   `status` smallint(6) NOT NULL,
    ->   `message` varchar(255) DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `idx_bps` (`bps_id`),
    -> ) ENGINE=MyISAM
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ENGINE=MyISAM' at line 8
mysql> CREATE TABLE  `bcs`.`schedule` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `bps_id` int(11) NOT NULL,   `status` smallint(6) NOT NULL,   `message` varchar(255) DEFAULT NULL,   PRIMARY KEY (`id`),   KEY `idx_bps` (`bps_id`) ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.06 sec)

mysql> use bcs;
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>  SELECT s.*, b.name as bps_name FROM schedule s
    -> INNER JOIN bps b ON s.bps_id = b.id HAVING status IN ('10') ORDER BY status ASC
    -> ;
Empty set (0.00 sec)

mysql> CREATE OR REPLACE VIEW rowcounter_1 AS SELECT s.*, b.name as bps_name FROM schedule s
    -> INNER JOIN bps b ON s.bps_id = b.id HAVING status IN ('10') ORDER BY status ASC
    -> ;
Query OK, 0 rows affected (0.10 sec)

I tried to download and install the latest version of MySQL, but that is a major mission on my debian box since the RPM installer can't handle the installation properly under Debian.

Sorry for the comma, I removed all superfluous columns and forgot that.

Have you actually tried to select all rows from the newly created view? Does that work for you? If yes, then maybe the debian package maintainers should be informed that the package in squeeze breaks on upgrade.

Sorry, my fault. Indeed, we have error here on 5.1.55:

mysql> select * from rowcounter_1;
ERROR 1356 (HY000): View 'bcs.rowcounter_1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

This is a bug.

works in <= 5.0.82
returns error in >= 5.0.83

testcase:

drop table if exists `t1`;
create table `t1`(`a` int)engine=myisam;
drop view if exists `v1`;
create or replace view `v1` as select `s`.* from `t1` `s`,`t1` `b` having `a`;
select * from `v1`;

Looks like fix for bug #40825 is responsible for the change!

Noted in 5.1.57, 5.5.12, 5.6.3 changelogs.

Selecting from a view for which the definition included a HAVING
clause failed with an error:

1356: View '...' references invalid table(s) or column(s)
or function(s) or definer/invoker of view lack rights to use them

CHANGESET - http://lists.mysql.com/commits/134920

Bug#59005 was marked as a duplicate.