Bug #33133 Views are not transparent
Submitted: 11 Dec 2007 10:11 Modified: 15 Feb 2008 17:58
Reporter: Maciej Pilichowski Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S2 (Serious)
Version:5.0.45 OS:Any
Assigned to: Georgi Kodinov CPU Architecture:Any
Triage: D2 (Serious) / R3 (Medium) / E3 (Medium)

[11 Dec 2007 10:11] Maciej Pilichowski
Description:
Simple question -- are those commands equivalent:
select query;

create view V as select query;
select * from V;

?

Not in mysql.

How to repeat:
For example table T with fields count and date, sql mode set to full compliance with SQL standards.

create view A as select *,year(date) y from T;

and now:
select y,sum(count) c from A group by y;

vs

create view B as select y,sum(count) c from A group by y;
select * from B;

The former works, the latter does not. What worse __creating__ the view shows no error.

Suggested fix:
-
[11 Dec 2007 10:45] Susanne Ebrecht
I can't reproduce it.
Please, add a little sql test file. 
What the exactly different in your outputs?
[11 Dec 2007 11:02] Maciej Pilichowski
Data does not matter, sql_mode does:
PIPES_AS_CONCAT,ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO

The error (second query) I get:
ERROR 1055 (42000): 'test.T._date' isn't in GROUP BY

Sorry for "count" and "date" previously -- I tried to simplify the example too much.
[11 Dec 2007 11:06] Maciej Pilichowski
Full sql example:

create table T (_count int,_date datetime);
create view A as select *,year(_date) y from T;
create view B as select y,sum(_count) c from A group by y;
select * from B;
[11 Dec 2007 12:15] Sveta Smirnova
In your case:

mysql> create table T (_count int,_date datetime);
mysql> create view A as select *,year(_date) y from T;
mysql> insert into T values();
mysql> select * from A;
mysql> select * from A;
+--------+-------+------+
| _count | _date | y    |
+--------+-------+------+
|   NULL | NULL  | NULL | 
+--------+-------+------+
1 row in set (0.00 sec)

You have 3 columns and use aggregate funcion on one and GROUP BY on other. In case of SQL_MODE='ONLY_FULL_GROUP_BY' you should use all columns.

So I close the report as "Not a Bug"
[11 Dec 2007 15:59] Maciej Pilichowski
> You have 3 columns and use aggregate funcion on one and GROUP BY on other.
> In case of SQL_MODE='ONLY_FULL_GROUP_BY' you should use all columns.

Sveta, please tell me you are kidding. In case of that mode I cannot (!) use all columns. Nobody can -- it is technically impossible.

Ok, back to SQL: 

> create table T (_count int,_date datetime);

Valid? Valid.

> create view A as select *,year(_date) y from T;

Valid? Valid.

> select y,sum(_count) c from A group by y;

Valid? Yes -- I group one data, and aggregate the second one. Btw. I am really curious what third column should I use here, how, and why. _count? _date? And what meaning would it have.

Does it work? Sure.

So if this select is ok, it is ok to store it as a view.

> create view B as select y,sum(_count) c from A group by y;

Exactly like before. Valid? Yes.

> select * from B;

Should this work? Absolutely yes. But it does not.

Is is a bug, and serious one. Please do note it lightly.

And btw. you are missing a point -- it is just an EXAMPLE! Any valid select query you can "store" as a view -- if not, it is a bug.
[12 Dec 2007 10:27] Sveta Smirnova
Thank you for the feedback.

You are right: I am missed what you indicated correct quantity of columns in SELECT ... FROM B query. Verified as described.

Workaround: temporarily don't use ONLY_FULL_GROUP_BY
[13 Dec 2007 17:05] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/39912

ChangeSet@1.2600, 2007-12-13 19:04:01+02:00, gkodinov@macbook.gmz +3 -0
  Bug #33133: Views are not transparent
  
  When resolving references we need to take into consideration
  the view "fields" and allow qualified access to them.
  Fixed by extending the reference resolution to process view
  fields correctly.
[9 Jan 2008 14:39] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/40753

ChangeSet@1.2600, 2008-01-09 16:39:43+02:00, gkodinov@macbook.gmz +3 -0
  Bug #33133: Views are not transparent
  
  When resolving references we need to take into consideration
  the view "fields" and allow qualified access to them.
  Fixed by extending the reference resolution to process view
  fields correctly.
[9 Jan 2008 14:49] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/40755

ChangeSet@1.2600, 2008-01-09 16:49:13+02:00, gkodinov@macbook.gmz +3 -0
  Bug #33133: Views are not transparent
  
  When resolving references we need to take into consideration
  the view "fields" and allow qualified access to them.
  Fixed by extending the reference resolution to process view
  fields correctly.
[11 Jan 2008 12:18] Bugs System
Pushed into 6.0.5-alpha
[11 Jan 2008 12:21] Bugs System
Pushed into 5.1.23-rc
[11 Jan 2008 12:22] Bugs System
Pushed into 5.0.56
[15 Feb 2008 17:58] Paul Dubois
Noted in 5.0.56, 5.1.23, 6.0.5 changelogs.

Some valid SELECT statements could not be used as views due to
incorrect column reference resolution.