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.
