MySQL Bugs: #33133: Views are not transparent

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

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

I can't reproduce it.
Please, add a little sql test file. 
What the exactly different in your outputs?

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.

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;

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"

> 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.

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

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.

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.

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.

Pushed into 6.0.5-alpha

Pushed into 5.1.23-rc

Pushed into 5.0.56

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.