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:Mac OS X
Assigned to: CPU Architecture:Any
Tags: count, order by, VIEW

[25 May 2007 22:30] John May
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'
[25 May 2007 23:58] MySQL Verification Team
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>
[29 May 2007 15:58] John May
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.
[4 Jun 2007 9:01] Sergei Golubchik
verified as a bug
[20 Dec 2007 18:23] Trudy Pelzer
Workaround: Use column names to sort.
[27 Feb 2008 14:44] Konstantin Osipov
A bug in name resolution of a merged view, resetting the lead.
[14 Nov 2013 15:32] Paul DuBois
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.
[4 Dec 2013 10:54] Laurynas Biveinis
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.