Bug #11709 ORDER BY with views can use the wrong column
Submitted: 3 Jul 2005 13:13 Modified: 25 Jul 2005 20:43
Reporter: Tim Graves Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.7/5.0.9 OS:Microsoft Windows (Windows XP/linux)
Assigned to: Evgeny Potemkin CPU Architecture:Any

[3 Jul 2005 13:13] Tim Graves
Description:
I have created some nested views. 

If I select from these views the ORDER BY clause seems to get confused if the column name used is also a name in one of the tables used to create the view. If the column name in the ORDER BY clause is not a name from one of the tables used to create the view or a colum alias is used the results of the ORDER BY are as expected.

Additionally if the order by column is not in the view but is in one of the underlying tables then the SQL statement seems to work corectly (though it provides an unexpected ordering.

select  productName, majorName, minorName, name as MyName
from minorAreaName
order by name

How to repeat:
This uses the dtabases in bug 9593, I can provide this again if needed.

Create the views

DROP VIEW IF EXISTS majorAreaName ;
CREATE VIEW majorAreaName AS
SELECT product.name AS productName, majorArea.name AS majorName,
	CONCAT(product.name,' / ',majorArea.name) AS name,
	majorarea.productCode AS productCode, majorarea.majorCode AS majorCode, majorArea.description AS description,
	product.importanceAdjustment * majorArea.importanceAdjustment AS importanceAdjustment
FROM product 
	INNER JOIN majorarea ON majorarea.productCode=product.productCode ;

# this view builds on the majorAreaName
DROP VIEW IF EXISTS minorAreaName ;
CREATE VIEW minorAreaName AS
SELECT majorAreaName.productName AS productName, majorAreaName.majorName AS majorName, minorArea.name AS minorName,
	CONCAT(majorAreaName.name,' / ', minorArea.name) AS name,
	majorAreaName.productCode AS productCode, majorAreaName.majorCode AS majorCode, minorArea.minorCode AS minorCode, 
	minorArea.description AS description,
	majorAreaName.importanceAdjustment * minorArea.importanceAdjustment AS importanceAdjustment
FROM majorAreaName 
	INNER JOIN minorArea ON minorArea.productCode=majorAreaName.productCode AND minorArea.majorCode=majorAreaName.majorCode ;

Executing the SQL
select  productName, majorName, minorName, name
from minorAreaName
order by name

returns results
productName	majorName	minorName	name
M801.	Survey.	Q01 Challenges.	M801. / Survey. / Q01 Challenges.
M801.	Survey.	Q10 success	M801. / Survey. / Q10 success
M801.	Survey.	Q02 approaches	M801. / Survey. / Q02 approaches
M801.	Survey.	Q03 differences	M801. / Survey. / Q03 differences
M801.	Survey.	Q04 multiple customers	M801. / Survey. / Q04 multiple customers
M801.	Survey.	Q05 open source	M801. / Survey. / Q05 open source
M801.	Survey.	Q06 product goals definition	M801. / Survey. / Q06 product goals definition
M801.	Survey.	Q07 requirements definition	M801. / Survey. / Q07 requirements definition
M801.	Survey.	Q08 design check	M801. / Survey. / Q08 design check
M801.	Survey.	Q09 acceptance tests	M801. / Survey. / Q09 acceptance tests

Note that item name M801. / Survey. / Q10 success is out of order.

Using a column alias fixes this
select  productName, majorName, minorName, name AS MyName
from minorAreaName
order by MyName

productName	majorName	minorName	name
M801.	Survey.	Q01 Challenges.	M801. / Survey. / Q01 Challenges.
M801.	Survey.	Q02 approaches	M801. / Survey. / Q02 approaches
M801.	Survey.	Q03 differences	M801. / Survey. / Q03 differences
M801.	Survey.	Q04 multiple customers	M801. / Survey. / Q04 multiple customers
M801.	Survey.	Q05 open source	M801. / Survey. / Q05 open source
M801.	Survey.	Q06 product goals definition	M801. / Survey. / Q06 product goals definition
M801.	Survey.	Q07 requirements definition	M801. / Survey. / Q07 requirements definition
M801.	Survey.	Q08 design check	M801. / Survey. / Q08 design check
M801.	Survey.	Q09 acceptance tests	M801. / Survey. / Q09 acceptance tests
M801.	Survey.	Q10 success	M801. / Survey. / Q10 success

Also creating the view with a different column name has the same result
CREATE VIEW minorAreaNameA AS
SELECT majorAreaName.productName AS productName, majorAreaName.majorName AS majorName, minorArea.name AS minorName,
	CONCAT(majorAreaName.name,' / ', minorArea.name) AS aname,
	majorAreaName.productCode AS productCode, majorAreaName.majorCode AS majorCode, minorArea.minorCode AS minorCode, 
	minorArea.description AS description,
	majorAreaName.importanceAdjustment * minorArea.importanceAdjustment AS importanceAdjustment
FROM majorAreaName 
	INNER JOIN minorArea ON minorArea.productCode=majorAreaName.productCode AND minorArea.majorCode=majorAreaName.majorCode ;

select  productName, majorName, minorName, aname 
from minorAreaNameA
order by aname

productName	majorName	minorName	aname
M801.	Survey.	Q01 Challenges.	M801. / Survey. / Q01 Challenges.
M801.	Survey.	Q02 approaches	M801. / Survey. / Q02 approaches
M801.	Survey.	Q03 differences	M801. / Survey. / Q03 differences
M801.	Survey.	Q04 multiple customers	M801. / Survey. / Q04 multiple customers
M801.	Survey.	Q05 open source	M801. / Survey. / Q05 open source
M801.	Survey.	Q06 product goals definition	M801. / Survey. / Q06 product goals definition
M801.	Survey.	Q07 requirements definition	M801. / Survey. / Q07 requirements definition
M801.	Survey.	Q08 design check	M801. / Survey. / Q08 design check
M801.	Survey.	Q09 acceptance tests	M801. / Survey. / Q09 acceptance tests
M801.	Survey.	Q10 success	M801. / Survey. / Q10 success

Note that with this view it is still possible do a select using a colum in the ORDER BY clause that is not in the view (but is in an underlying table)
select  productName, majorName, minorName, aname 
from minorAreaNameA
order by name

 returns
productName	majorName	minorName	aname
M801.	Survey.	Q01 Challenges.	M801. / Survey. / Q01 Challenges.
M801.	Survey.	Q10 success	M801. / Survey. / Q10 success
M801.	Survey.	Q02 approaches	M801. / Survey. / Q02 approaches
M801.	Survey.	Q03 differences	M801. / Survey. / Q03 differences
M801.	Survey.	Q04 multiple customers	M801. / Survey. / Q04 multiple customers
M801.	Survey.	Q05 open source	M801. / Survey. / Q05 open source
M801.	Survey.	Q06 product goals definition	M801. / Survey. / Q06 product goals definition
M801.	Survey.	Q07 requirements definition	M801. / Survey. / Q07 requirements definition
M801.	Survey.	Q08 design check	M801. / Survey. / Q08 design check
M801.	Survey.	Q09 acceptance tests	M801. / Survey. / Q09 acceptance tests

Note that in this case the ordering is wrong again, this only seems to effect the ORDER BY clause, trying to use the column name from the underlying table in the SELECT clause results in an unknown column error message

Suggested fix:
Workaround is to use a column alias for any column names that are also in the underlying column and then use ORDER BY on that alias.
[3 Jul 2005 13:26] Tim Graves
I have uploaded the scrips to create the DB, the script to create the views and a compressed version of the database
[3 Jul 2005 21:23] Miguel Solorzano
Verified also on Linux.
[8 Jul 2005 12:18] Evgeny Potemkin
When searching column to sort on, item was compared to field under view
column, but not the column itself. Because names of view column and underlaid
field may differ, it leads to possibly choosing wrong column for sorting on.
[11 Jul 2005 11:54] 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/internals/26884
[11 Jul 2005 17:45] Oleksandr Byelkin
All looks good, but add before Item_direct_view_ref::eq explanation why we need it.
(and also check that all lines are less then 80 columns (better 72)
[12 Jul 2005 20:09] 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/internals/26963
[12 Jul 2005 20:42] Evgeny Potemkin
Fixed in 5.0.10, cset 1.1893.1.1
[25 Jul 2005 20:43] Mike Hillyer
Documented in 5.0.10 changelog:

    <listitem><para>Performing an <literal>ORDER BY</literal> on a <literal>SELECT</literal> from a <literal>VIEW</literal> produced unexpected results when <literal>VIEW</literal> and underlying table had the same column name on different columns. Bug #11709)</para></listitem>