MySQL Bugs: #28695: spurious error when counting rows in view with order by clause

Bug #28695	spurious error when counting rows in view with order by clause
Submitted:	25 May 2007 22:30	Modified:	14 Nov 2013 15:32
Reporter:	John May	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.0.41	OS:	MacOS
Assigned to:		CPU Architecture:	Any
Tags:	count, order by, VIEW

Description:
I have created a view on a table with many columns and used an order by clause.  When this clause
refers by number to a column other than the first column, and I do a select count(*) to count the
rows, I get an apparently spurious error:

create table t1 ( x integer, y integer, z integer );
insert into t1 values ( 1, 2, 3 ), (3, 1, 2), (2, 3, 1 );

create view v1 as select x, y, z from t1 order by 1;
select count(*) from v1;
+----------+
| count(*) |
+----------+
|        3 | 
+----------+
1 row in set (0.00 sec)

-- so far, so good --
-- Now order by column 2 instead of column 1 --
create view v2 as select x, y, z from t1 order by 2;
select count(*) from v2;
ERROR 1054 (42S22): Unknown column '2' in 'order clause'
-- Same error happens if a create a view and order by column 3 --
-- But referencing column 2 by its name works OK!
create view v4 as select x, y, z from t1 order by y;
select count(*) from v4;
+----------+
| count(*) |
+----------+
|        3 | 
+----------+
1 row in set (0.00 sec)

Also, the view itself looks OK, even though select count(*) fails:
select * from v2;                                 
+------+------+------+
| x    | y    | z    |
+------+------+------+
|    3 |    1 |    2 | 
|    1 |    2 |    3 | 
|    2 |    3 |    1 | 
+------+------+------+
3 rows in set (0.00 sec)

How to repeat:
mysql> create table t1 ( x integer, y integer, z integer );
mysql> insert into t1 values ( 1, 2, 3 ), (3, 1, 2), (2, 3, 1 );
mysql> create view v2 as select x, y, z from t1 order by 2;
mysql> select count(*) from v2;
ERROR 1054 (42S22): Unknown column '2' in 'order clause'

Thank you for the bug report. Sorry but this isn't a bug since
select count(*) from v2; always returns 1 column the order by
position 2 is out of range of the number of items in the select list.

mysql> select count(*) from v2;
ERROR 1054 (42S22): Unknown column '2' in 'order clause'
mysql> select x from v2;
ERROR 1054 (42S22): Unknown column '2' in 'order clause'
mysql> select x,z from v2;
+------+------+
| x    | z    |
+------+------+
|    2 |    1 |
|    3 |    2 |
|    1 |    3 |
+------+------+
3 rows in set (0.01 sec)

mysql>

Thank you for looking into this.  If this isn't a bug, it's certainly unexpected behavior.  It appears that in order to count the rows in my view, I need to issue a command like this:
select count(*), 1 from v1;
... or if I had ordered by row 9 in a wider view, I'd need:
select count(*), 1, 1, 1, 1, 1, 1, 1, 1 from v1;
From your examples, it looks like the order-by clause in the statement that creates the view does not bind to the select statement there; instead it binds to any and all future queries on the view.  I'm not an expert on SQL, but this seems odd to me.  It's also not the behavior I see from other DBMSs that I'm familiar with.

verified as a bug

Workaround: Use column names to sort.

A bug in name resolution of a merged view, resetting the lead.

Noted in 5.7.3 changelog.

Views containing ORDER BY integer could result in errors at view
evaluation time. Consider these view definitions, which use ORDER BY
with an ordinal number:

CREATE VIEW v1 AS SELECT x, y, z FROM t ORDER BY 2;
CREATE VIEW v2 AS SELECT x, 1, z FROM t ORDER BY 2;

In the first case, ORDER BY 2 refers to a named column y. In the
second case, it refers to a constant 1. For queries that select from
either view fewer than 2 columns (the number named in the ORDER BY
clause), an error occurred if the server evaluated the view using the
MERGE algorithm. Examples:

mysql> SELECT x FROM v1; 
ERROR 1054 (42S22): Unknown column '2' in 'order clause'
mysql> SELECT x FROM v2; 
ERROR 1054 (42S22): Unknown column '2' in 'order clause'

To handle view definitions like this, the server now writes them
differently into the .frm file that stores the view definition. This
difference is visible with SHOW CREATE VIEW. Previously, the .frm
file contained this for the ORDER BY 2 clause:

For v1: ORDER BY 2
For v2: ORDER BY 2

Now, the .frm file contains this:

For v1: ORDER BY `t`.`y`
For v2: ORDER BY ''

That is, for v1, 2 is replaced by a reference to the name of the
column referred to. For v2, 2 is replaced by a constant string
expression (ordering by a constant has no effect, so ordering by any
constant will do).

If you experience view-evaluation errors such as just described, drop
and recreate the view so that the .frm file contains the updated view
representation. Alternatively, for views like v2 that order by a
constant value, drop and recreate the view with no ORDER BY clause.

mysql-server$ bzr log -r 6712
------------------------------------------------------------
revno: 6712
committer: Neeraj Bisht <neeraj.x.bisht@oracle.com>
branch nick: trunk
timestamp: Thu 2013-10-17 14:12:21 +0530
message:
  Bug#11746789 : SPURIOUS ERROR WHEN COUNTING ROWS IN VIEW WITH 
  	       ORDER BY CLAUSE
  
  Problem:-
  In select query where number of field in projection list is 
  less than the number in view order by clause, will generate 
  an error.
  
  Analysis:-
  
  When we use VIEW_ALGORITHM_MERGE and we have order by in view.
  
  We convert query like
  create view v2 as select x,y,z from t1 order by 2
  select x from v2;
  
  to(by VIEW_ALGORITHM_MERGE)
  
  select x from t1 order by 2;
  Which will generate error.
  
  
  Solution:-
  To make sure that the body of the view cannot contain "ORDER BY
  ordinal" anymore (instead they will contain the referenced item).
  This will fix the problem for all newly created views (views
  created by a server which has the bugfix). Older views will still
  produce an error (because they have the ordinal in their body),
  then people will just have to drop and re-create their view.
  
  This solution still create problem in query like
  create view v1 as select 5 from t1 order by 1;
  select x from v1;
  
  Because select will be read as
  select x from t1 order by 5;
  In this case, instead of writting order by 5 we will change
  grouping/ordering by a constant string.