Bug #6518 CREATE VIEW using UNION ALL bug...

• Submitted: 9 Nov 2004 11:10
• Modified: 9 Nov 2004 11:35
• Reporter: Rudi Harsono
• Status: Can't repeat
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.1-alpha
• OS: Windows (win2K)
• CPU Architecture: Any

[9 Nov 2004 11:10] Rudi Harsono

Description:
i have 2 table, called employee_jakarta and employee_surabaya
both table have same column name and data type, here the create table sql :

CREATE TABLE employee_jakarta (
       nama   varchar(20), alamat varchar(100)
);

CREATE TABLE employee_surabaya (
       nama   varchar(20), alamat varchar(100)
);

INSERT INTO employee_jakarta VALUES('john', 'jakarta');
INSERT INTO employee_surabaya VALUES('harmo', 'surabaya');

that's my tables, then i select those table using UNION ALL :
SELECT * FROM employee_jakarta
UNION ALL
SELECT * FROM employee_surabaya;

it results okay...

but when i create view for that query :
CREATE VIEW employee AS
SELECT * FROM employee_jakarta
UNION ALL
SELECT * FROM employee_surabaya;

mysql return error :

#1143 - create view command denied to user 'root'@'localhost' for column 'nama' in table 'employee' 

How to repeat:
see above.

[9 Nov 2004 11:35] MySQL Verification Team

Hi,

Thank you for the report. I was able to repeat it with version 5.0.1, but it worked like a charm with 5.0.2