MySQL Bugs: #48490: ORDER BY clause not honored when using UNION on two VIEWs. Wrong data returned.

Bug #48490	ORDER BY clause not honored when using UNION on two VIEWs. Wrong data returned.
Submitted:	2 Nov 2009 23:55	Modified:	11 Feb 2018 11:06
Reporter:	Bassam Tabbara	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.1.39	OS:	Linux (Debian 5.0 lenny 64-bit)
Assigned to:		CPU Architecture:	Any

Description:
When using an ORDER BY inside a VIEW and then using a UNION clause, the ORDER BY does not seem to be honored. The wrong data is returned.

How to repeat:
# create a new database
CREATE DATABASE viewbug;

USE viewbug;

# create the schema
CREATE TABLE `VirtualNode` (
  `VirtualNodeId` binary(20) NOT NULL,
  `NodeId` binary(20) NOT NULL,
  `Active` tinyint(1) NOT NULL,
  PRIMARY KEY (`VirtualNodeId`),
  KEY `VirtualNode_NodeId` (`NodeId`),
  CONSTRAINT `FK_VirtualNode_Node` FOREIGN KEY (`NodeId`) REFERENCES `Node` (`NodeId`)
) ENGINE=InnoDB DEFAULT CHARSET=ucs2 COLLATE=ucs2_bin
;

CREATE TABLE `Node` (
  `NodeId` binary(20) NOT NULL,
  `Name` varchar(100) CHARACTER SET ucs2 DEFAULT NULL,
  PRIMARY KEY (`NodeId`)
) ENGINE=InnoDB DEFAULT CHARSET=ucs2 COLLATE=ucs2_bin
;

CREATE VIEW ViewBug
AS
SELECT 
	vn.VirtualNodeId, n.NodeId
FROM 
	VirtualNode vn INNER JOIN Node n ON (vn.NodeId = n.NodeId)
WHERE 
	vn.Active = true
ORDER BY 
	VirtualNodeId
;

# from a shell, import data from attachment.
mysql -p viewbug < viewbug.sql

# run the following query
(
SELECT 
	HEX(vn.VirtualNodeId)
FROM 
	ViewBug vn
WHERE 
	vn.VirtualNodeId >= 0xB9E00154C6E4EE8DF71D95FC5DF8FAB036EC12F9
LIMIT 1
)
UNION ALL
(
SELECT 
	HEX(vn.VirtualNodeId)
FROM 
	ViewBug vn
LIMIT 1
)

# Actual
+------------------------------------------+
| HEX(vn.VirtualNodeId)                    |
+------------------------------------------+
| BA079969FB5664AF38480F845F3D6A7B323B8DDF |
| 00108B52DF4EE223766AC9D2B5C281B282B8874E |
+------------------------------------------+

Expected:
+------------------------------------------+
| HEX(vn.VirtualNodeId)                    |
+------------------------------------------+
| BA03C01F91EB85998D131F2A466B2CDE842E1D4E |
| 000538824B4791290068B532D8243029B3747DCB |
+------------------------------------------+

Suggested fix:
If we remove the UNION the correct result is returned:

SELECT 	HEX(vn.VirtualNodeId) FROM  ViewBug vn WHERE vn.VirtualNodeId >= 0xB9E00154C6E4EE8DF71D95FC5DF8FAB036EC12F9 LIMIT 1;

+------------------------------------------+
| HEX(vn.VirtualNodeId)                    |
+------------------------------------------+
| BA03C01F91EB85998D131F2A466B2CDE842E1D4E |
+------------------------------------------+

SELECT HEX(vn.VirtualNodeId) FROM  ViewBug vn LIMIT 1;

+------------------------------------------+
| HEX(vn.VirtualNodeId)                    |
+------------------------------------------+
| 000538824B4791290068B532D8243029B3747DCB |
+------------------------------------------+

Also if a redundant UNION is specified outside the view the correct result is returned:

(
SELECT 
	HEX(vn.VirtualNodeId)
FROM 
	ViewBug vn
WHERE 
	vn.VirtualNodeId >= 0xB9E00154C6E4EE8DF71D95FC5DF8FAB036EC12F9
ORDER BY
  vn.VirtualNodeId
LIMIT 1
)
UNION ALL
(
SELECT 
	HEX(vn.VirtualNodeId)
FROM 
	ViewBug vn
ORDER BY
  vn.VirtualNodeId
LIMIT 1
)

+------------------------------------------+
| HEX(vn.VirtualNodeId)                    |
+------------------------------------------+
| BA03C01F91EB85998D131F2A466B2CDE842E1D4E |
| 000538824B4791290068B532D8243029B3747DCB |
+------------------------------------------+

Data

Attachment: bug-data-48490.tar (application/x-tar, text), 370.00 KiB.

There's a small typo in the DDL above. Please remove the line "CONSTRAINT `FK_VirtualNode_Node` FOREIGN KEY (`NodeId`) REFERENCES `Node` (`NodeId`)".

Please, try to repeat with a newer version, 5.1.40, and inform about the results. Look:

valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.41-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE `VirtualNode` (   `VirtualNodeId` binary(20) NOT NULL,   `NodeId` binary(20) NOT NULL,   `Active` tinyint(1) NOT NULL,   PRIMARY KEY (`VirtualNodeId`),   KEY `VirtualNode_NodeId` (`NodeId`) ) ENGINE=InnoDB DEFAULT CHARSET=ucs2 COLLATE=ucs2_bin;
Query OK, 0 rows affected (0.48 sec)

mysql> CREATE TABLE `Node` (   `NodeId` binary(20) NOT NULL,   `Name` varchar(100) CHARACTER SET ucs2 DEFAULT NULL,   PRIMARY KEY (`NodeId`) ) ENGINE=InnoDB DEFAULT CHARSET=ucs2 COLLATE=ucs2_bin;Query OK, 0 rows affected (0.07 sec)

mysql> CREATE VIEW ViewBug AS SELECT  vn.VirtualNodeId, n.NodeId FROM  VirtualNode vn INNER JOIN Node n ON (vn.NodeId = n.NodeId) WHERE  vn.Active = true ORDER BY  VirtualNodeId;Query OK, 0 rows affected (0.09 sec)

mysql> exit
Bye
valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot test < ~/Downloads/bug-data-48490/viewbug.sql 
valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot testReading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.41-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> ( SELECT  HEX(vn.VirtualNodeId) FROM  ViewBug vn WHERE  vn.VirtualNodeId >= 0xB9E00154C6E4EE8DF71D95FC5DF8FAB036EC12F9 ORDER BY   vn.VirtualNodeId LIMIT 1 ) UNION ALL ( SELECT  HEX(vn.VirtualNodeId) FROM  ViewBug vn ORDER BY   vn.VirtualNodeId LIMIT 1 );
+------------------------------------------+
| HEX(vn.VirtualNodeId)                    |
+------------------------------------------+
| BA03C01F91EB85998D131F2A466B2CDE842E1D4E |
| 000538824B4791290068B532D8243029B3747DCB |
+------------------------------------------+
2 rows in set (0.01 sec)

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Posted by developer:
 
Closing since it has been suspended for 8 years.