MySQL Bugs: #65706: SELECT statement used to create a view works, selecting from the view -> ER 1356

Bug #65706	SELECT statement used to create a view works, selecting from the view -> ER 1356
Submitted:	22 Jun 2012 7:42	Modified:	10 Jan 2013 12:16
Reporter:	Massimo Fierro	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S1 (Critical)
Version:	5.5.24, 5.5.27, 5.1.65, 5.0.97	OS:	Linux (Ubuntu 12.04)
Assigned to:		CPU Architecture:	Any

Description:
Hello, I am working on improving fudforum by replacing a table used as cache with a view. Unfortunately it seems I have hit a bug, or so I suppose.

With a fresh installation of fudforum, issuing the following SELECT statement 

(SELECT gm.user_id, gr.resource_id, 0 AS group_id, BIT_OR( gm.group_members_opt ) AS group_cache_opts
  FROM fud30_group_members gm 
  INNER JOIN fud30_group_resources gr
    ON gr.group_id=gm.group_id
  WHERE ( gm.group_members_opt>=65536 )
    AND ( gm.group_members_opt & 65536 ) > 0
    AND ( gm.group_members_opt & 131072 )
  GROUP BY user_id, resource_id, group_id)
UNION
(SELECT gm.user_id, gr.resource_id, 0 AS group_id, BIT_AND( gm.group_members_opt ) AS group_cache_opts
  FROM fud30_group_members AS gm
  INNER JOIN fud30_group_resources AS gr
    ON gr.group_id=gm.group_id
  WHERE ( gm.group_members_opt>=65536 )
    AND ( gm.group_members_opt & 65536 ) > 0
  GROUP BY user_id, resource_id, group_id);

gives the following output (as expected)

+------------+-------------+----------+------------------+
| user_id    | resource_id | group_id | group_cache_opts |
+------------+-------------+----------+------------------+
|          0 |           1 |        0 |           327683 |
|          0 |           2 |        0 |            65536 |
|          0 |           3 |        0 |            65536 |
|          2 |           3 |        0 |           378767 |
| 2147483647 |           1 |        0 |           378767 |
| 2147483647 |           2 |        0 |           378767 |
| 2147483647 |           3 |        0 |            65536 |
+------------+-------------+----------+------------------+

Yet, after deleting the table and creating a view (by prepending CREATE VIEW group_cache AS to the statement above), running the statement

SELECT * FROM group_cache;

gives the following error

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

The error arises both as a simple user (although with all the privileges on the DB) as well as root.

How to repeat:
- Install fudforum > 3
- execute the statements described

Please, send the output of:

show create view group_cache\G

Maybe I'll get some idea about the reason even without downloading that software...

I tried as root, but I always get 

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

May it be that this behaviour is related to http://bugs.mysql.com/bug.php?id=21809  ?

Thank you for the feedback.

Unfortunately we can not say if two bugs are related if don't see view definition. But I assume it is like SELECT you provided in the initial description. Please send us dump of Fudforum database structure. For example, taken with mysqldump --nodata

The view was created as described i.e.

CREATE VIEW group_cache AS
(SELECT gm.user_id, gr.resource_id, 0 AS group_id, BIT_OR( gm.group_members_opt ) AS group_cache_opts
  FROM fud30_group_members gm 
  INNER JOIN fud30_group_resources gr
    ON gr.group_id=gm.group_id
  WHERE ( gm.group_members_opt>=65536 )
    AND ( gm.group_members_opt & 65536 ) > 0
    AND ( gm.group_members_opt & 131072 )
  GROUP BY user_id, resource_id, group_id)
UNION
(SELECT gm.user_id, gr.resource_id, 0 AS group_id, BIT_AND( gm.group_members_opt ) AS group_cache_opts
  FROM fud30_group_members AS gm
  INNER JOIN fud30_group_resources AS gr
    ON gr.group_id=gm.group_id
  WHERE ( gm.group_members_opt>=65536 )
    AND ( gm.group_members_opt & 65536 ) > 0
  GROUP BY user_id, resource_id, group_id);

_____________________________________________________________________

mysqldump --no-data reports the following

-- MySQL dump 10.13  Distrib 5.5.24, for debian-linux-gnu (i686)
--
-- Host: localhost    Database: fud_test
-- ------------------------------------------------------
-- Server version	5.5.24-0ubuntu0.12.04.1

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
mysqldump: Couldn't execute 'show create table `group_cache`': View 'fud_test.group_cache' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356)

fud_test DB without group_cache view

Attachment: fud_test.sql (text/x-sql), 46.55 KiB.

I have dropped the view and the rest of the DB looks like the contents of this file http://bugs.mysql.com/file.php?id=18632 (from mysqldump --no-data)

Thank you for the feedback.

This is fixed in version 5.6, but I still mark this as verified, because can not find bug # with the fix. This is not duplicate of bug #60295, since it still exists in 5.5, 5.1 and 5.0. In 5.0 SHOW CREATE VIEW does not fail with error though.

No longer repeatable on 5.6.