[MDEV-3805] Wrong order in result with ordered subquery and derived_merge = off Created: 2012-10-10  Updated: 2012-12-10  Resolved: 2012-12-10

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.27
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Tom Jansen Assignee: Unassigned
Resolution: Not a Bug Votes: 1
Labels: None
Environment:

mysqld Ver 5.5.27-MariaDB-mariadb1~squeeze-log for debian-linux-gnu on x86_64 (mariadb.org binary distribution) Debian 6.0, amd64, cleanly installed VM default debian config for mariaDB except for bind-address, which was changed to local network.


 Description   

Reproduced on three different machines all running Maria 5.5.27.
Might be related to MDEV-3795.
Running the following simple query returns weird ordering where the result is not ordered by x.sortme:

 
SET @@optimizer_switch='derived_merge=off';
 
SELECT x.sortme FROM ( 
  SELECT * FROM bugdata ORDER BY sortme 
) x;

The very weird part is that removing some of the columns of the bugdata table removes the problem. InnoDB, MyISAM or Aria for the bugdata table does not make a difference.

Reproducable on my systems by running the SQL below (Jira does not seems to like the attachment)

 
 
CREATE TEMPORARY TABLE `bugdata` (
 
  `d3` decimal(54,0) NOT NULL DEFAULT '0',
 
  `d4` decimal(54,0) NOT NULL DEFAULT '0',
 
  `i1` int(11) NOT NULL,
 
  `d1` decimal(11,2) NOT NULL DEFAULT '0.00',
 
  `sortme` smallint(6) unsigned NOT NULL DEFAULT '0',
 
  `d2` int(9) NOT NULL DEFAULT '0',
 
  `vc1` varchar(30) CHARACTER SET utf8 NOT NULL DEFAULT '',
 
  `vc2` varchar(7) CHARACTER SET utf8 NOT NULL DEFAULT '',
 
  `d5` decimal(54,0) NOT NULL DEFAULT '0',
 
  `d6` decimal(54,0) NOT NULL DEFAULT '0',
 
  `d7` decimal(60,4) NOT NULL DEFAULT '0.0000',
 
  `d8` decimal(60,4) NOT NULL DEFAULT '0.0000',
 
  `d9` decimal(60,4) NOT NULL DEFAULT '0.0000',
 
  `d10` decimal(60,4) NOT NULL DEFAULT '0.0000',
 
  `d11` decimal(60,4) NOT NULL DEFAULT '0.0000',
 
  `d12` decimal(60,4) NOT NULL DEFAULT '0.0000',
 
  `d13` decimal(60,4) NOT NULL DEFAULT '0.0000',
 
  `d14` decimal(60,4) NOT NULL DEFAULT '0.0000',
 
  `d15` decimal(54,0) NOT NULL DEFAULT '0',
 
  `d16` decimal(59,0) NOT NULL DEFAULT '0',
 
  `d17` decimal(54,0) NOT NULL DEFAULT '0',
 
  `d18` decimal(42,0) NOT NULL DEFAULT '0',
 
  `d19` decimal(42,0) NOT NULL DEFAULT '0',
 
  `d20` decimal(42,0) NOT NULL DEFAULT '0',
 
  `d21` decimal(47,0) DEFAULT NULL,
 
  `d22` decimal(47,0) DEFAULT NULL,
 
  `mt1` mediumtext CHARACTER SET utf8,
 
  `mt2` mediumtext CHARACTER SET utf8,
 
  `mt3` mediumtext CHARACTER SET utf8,
 
  `d23` decimal(47,0) DEFAULT NULL,
 
  `d24` int(1) NOT NULL DEFAULT '0',
 
  `d25` int(1) NOT NULL DEFAULT '0',
 
  `mt4` mediumtext CHARACTER SET utf8,
 
  `mt5` mediumtext CHARACTER SET utf8,
 
  `mt6` mediumtext CHARACTER SET utf8,
 
  `mt7` mediumtext CHARACTER SET utf8,
 
  `mt8` mediumtext CHARACTER SET utf8,
 
  `mt9` mediumtext CHARACTER SET utf8
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
 
 
INSERT INTO `bugdata` VALUES (0,0,258511,1.00,29,1,'abc','abc',1,1,1.0000,1.0000,1.0000,1.0000,1.0000,1.0000,1.0000,1.0000,1,1,1,1,1,1,1,1,'abc','abc','abc',1,1,1,'abc','2012-09-03, 5 | 2012-09-08, 6 | 2012-09-10, 6 | 2012-09-11, 6 | 2012-09-12, 6 | 2012-09-14, 5 | 2012-09-18, 5 | 2012-09-19, 5 | 2012-09-21, 5 | 2012-09-26, 5 | 2012-09-27, 5 | 2012-09-29, 5 | 2012-09-30, 5','abc','abc','2012-09-05, 2 | 2012-09-13, 2 | 2012-09-15, 2 | 2012-09-21, 2 | 2012-09-23, 2 | 2012-09-24, 2 | 2012-09-25, 2 | 2012-09-27, 2 | 2012-09-28, 2 | 2012-09-29, 2 | 2012-09-30, 2','2012-09-11, 2 | 2012-09-12, 2 | 2012-09-13, 2 | 2012-09-30, 2'),(0,0,258512,1.00,29,1,'abc','abc',1,1,1.0000,1.0000,1.0000,1.0000,1.0000,1.0000,1.0000,1.0000,1,1,1,1,1,1,1,1,'abc','abc','abc',1,1,1,'abc','2012-09-01, 5 | 2012-09-02, 5 | 2012-09-03, 5 | 2012-09-04, 4 | 2012-09-05, 4 | 2012-09-06, 4 | 2012-09-07, 4 | 2012-09-08, 4 | 2012-09-09, 4 | 2012-09-10, 4 | 2012-09-11, 4 | 2012-09-12, 4 | 2012-09-13, 4 | 2012-09-14, 4 | 2012-09-15, 4 | 2012-09-16, 4 | 2012-09-17, 4 | 2012-09-18, 4 | 2012-09-19, 4 | 2012-09-20, 6 | 2012-09-21, 4 | 2012-09-22, 4 | 2012-09-23, 4 | 2012-09-24, 4 | 2012-09-25, 4 | 2012-09-26, 4 | 2012-09-27, 4 | 2012-09-28, 4 | 2012-09-29, 4 | 2012-09-30, 4','abc','abc','2012-09-03, 2 | 2012-09-07, 2 | 2012-09-08, 2 | 2012-09-09, 2 | 2012-09-10, 2 | 2012-09-11, 2 | 2012-09-12, 2 | 2012-09-13, 2 | 2012-09-14, 2 | 2012-09-15, 2 | 2012-09-16, 2 | 2012-09-17, 2 | 2012-09-18, 2 | 2012-09-23, 2 | 2012-09-24, 2 | 2012-09-25, 2 | 2012-09-26, 2 | 2012-09-27, 2 | 2012-09-28, 2 | 2012-09-29, 2 | 2012-09-30, 2',NULL),(0,0,258513,1.00,29,1,'abc','abc',1,1,1.0000,1.0000,1.0000,1.0000,1.0000,1.0000,1.0000,1.0000,1,1,1,1,1,1,1,1,'abc','abc','abc',1,1,1,'abc','2012-09-01, 5 | 2012-09-02, 5 | 2012-09-03, 5 | 2012-09-04, 4 | 2012-09-05, 4 | 2012-09-06, 4 | 2012-09-07, 4 | 2012-09-08, 4 | 2012-09-09, 4 | 2012-09-10, 4 | 2012-09-11, 4 | 2012-09-12, 4 | 2012-09-13, 4 | 2012-09-14, 4 | 2012-09-15, 4 | 2012-09-16, 4 | 2012-09-17, 4 | 2012-09-18, 4 | 2012-09-19, 4 | 2012-09-20, 6 | 2012-09-21, 4 | 2012-09-22, 4 | 2012-09-23, 4 | 2012-09-24, 4 | 2012-09-25, 4 | 2012-09-26, 4 | 2012-09-27, 4','abc','abc','2012-09-03, 2 | 2012-09-07, 2 | 2012-09-08, 2 | 2012-09-09, 2 | 2012-09-10, 2 | 2012-09-11, 2 | 2012-09-12, 2 | 2012-09-13, 2 | 2012-09-14, 2 | 2012-09-15, 2 | 2012-09-16, 2 | 2012-09-17, 2 | 2012-09-18, 2 | 2012-09-23, 2 | 2012-09-24, 2 | 2012-09-25, 2 | 2012-09-26, 2 | 2012-09-27, 2',NULL),(0,0,258514,1.00,29,1,'abc','abc',1,1,1.0000,1.0000,1.0000,1.0000,1.0000,1.0000,1.0000,1.0000,1,1,1,1,1,1,1,1,'abc','abc','abc',1,1,1,'abc','2012-09-01, 5 | 2012-09-02, 5 | 2012-09-03, 5 | 2012-09-04, 6 | 2012-09-05, 6 | 2012-09-06, 6 | 2012-09-07, 6 | 2012-09-08, 6 | 2012-09-09, 6 | 2012-09-10, 6 | 2012-09-11, 6 | 2012-09-12, 6 | 2012-09-13, 6 | 2012-09-14, 5 | 2012-09-15, 5 | 2012-09-16, 5 | 2012-09-17, 5 | 2012-09-18, 6 | 2012-09-19, 4 | 2012-09-20, 4 | 2012-09-21, 4 | 2012-09-22, 4 | 2012-09-23, 4 | 2012-09-24, 4 | 2012-09-25, 4 | 2012-09-26, 4 | 2012-09-27, 4 | 2012-09-28, 4 | 2012-09-29, 4 | 2012-09-30, 4','abc','abc','2012-09-01, 2 | 2012-09-05, 2 | 2012-09-06, 2 | 2012-09-07, 2 | 2012-09-08, 2 | 2012-09-09, 2 | 2012-09-10, 2 | 2012-09-11, 2 | 2012-09-12, 2 | 2012-09-15, 2 | 2012-09-16, 2 | 2012-09-17, 2 | 2012-09-20, 2 | 2012-09-21, 2 | 2012-09-22, 2 | 2012-09-23, 2 | 2012-09-24, 2 | 2012-09-25, 2 | 2012-09-26, 2 | 2012-09-27, 2 | 2012-09-28, 2 | 2012-09-29, 2 | 2012-09-30, 2','2012-09-01, 2 | 2012-09-02, 2 | 2012-09-03, 2 | 2012-09-04, 2 | 2012-09-05, 2 | 2012-09-06, 2 | 2012-09-07, 2 | 2012-09-08, 2 | 2012-09-09, 2 | 2012-09-10, 2 | 2012-09-11, 2 | 2012-09-12, 2 | 2012-09-13, 2 | 2012-09-14, 2 | 2012-09-15, 2 | 2012-09-16, 2 | 2012-09-17, 2 | 2012-09-18, 2 | 2012-09-19, 2 | 2012-09-20, 2 | 2012-09-21, 2 | 2012-09-22, 2 | 2012-09-23, 2 | 2012-09-24, 2 | 2012-09-25, 2 | 2012-09-26, 2 | 2012-09-27, 2 | 2012-09-28, 2 | 2012-09-29, 2 | 2012-09-30, 2'),(0,0,258515,1.00,29,1,'abc','abc',1,1,1.0000,1.0000,1.0000,1.0000,1.0000,1.0000,1.0000,1.0000,1,1,1,1,1,1,1,1,'abc','abc','abc',1,1,1,'abc','2012-09-01, 5 | 2012-09-02, 5 | 2012-09-03, 5 | 2012-09-04, 6 | 2012-09-05, 6 | 2012-09-06, 6 | 2012-09-07, 6 | 2012-09-08, 6 | 2012-09-15, 5 | 2012-09-16, 5 | 2012-09-17, 5 | 2012-09-18, 6 | 2012-09-19, 4 | 2012-09-20, 4 | 2012-09-21, 4 | 2012-09-22, 4 | 2012-09-23, 4 | 2012-09-24, 4 | 2012-09-25, 4 | 2012-09-26, 4 | 2012-09-27, 4 | 2012-09-28, 4','abc','abc','2012-09-01, 2 | 2012-09-05, 2 | 2012-09-06, 2 | 2012-09-07, 2 | 2012-09-08, 2 | 2012-09-15, 2 | 2012-09-16, 2 | 2012-09-17, 2 | 2012-09-20, 2 | 2012-09-21, 2 | 2012-09-22, 2 | 2012-09-23, 2 | 2012-09-24, 2 | 2012-09-25, 2 | 2012-09-26, 2 | 2012-09-27, 2 | 2012-09-28, 2','2012-09-01, 2 | 2012-09-02, 2 | 2012-09-03, 2 | 2012-09-04, 2 | 2012-09-05, 2 | 2012-09-06, 2 | 2012-09-07, 2 | 2012-09-08, 2 | 2012-09-09, 2 | 2012-09-10, 2 | 2012-09-11, 2 | 2012-09-12, 2 | 2012-09-13, 2 | 2012-09-14, 2 | 2012-09-15, 2 | 2012-09-16, 2 | 2012-09-17, 2 | 2012-09-18, 2 | 2012-09-19, 2 | 2012-09-20, 2 | 2012-09-21, 2 | 2012-09-22, 2 | 2012-09-23, 2 | 2012-09-24, 2 | 2012-09-25, 2 | 2012-09-26, 2 | 2012-09-27, 2 | 2012-09-28, 2 | 2012-09-29, 2 | 2012-09-30, 2'),(0,0,261144,1.00,209,1,'abc','abc',1,1,1.0000,1.0000,1.0000,1.0000,1.0000,1.0000,1.0000,1.0000,1,1,1,1,1,1,1,1,'abc','abc','abc',1,1,1,'abc','2012-09-28, 4','abc','abc',NULL,NULL);
 
 
 
SET @@optimizer_switch='derived_merge=off';
 
 
 
SELECT x.sortme
 
FROM (
 
	SELECT * FROM bugdata
 
	ORDER BY sortme
 
) x;
 
 
 
DROP TEMPORARY TABLE `bugdata`;



 Comments   
Comment by Tom Jansen [ 2012-10-24 ]

Also reproducable on mysqld Ver 5.5.28-MariaDB-mariadb1~squeeze for debian-linux-gnu on x86_64 (mariadb.org binary distribution)

Comment by Elena Stepanova [ 2012-10-24 ]

Hi Tom,

Could you please point at any documentation that makes you think that the result should be sorted?
(I'm asking because I believe that the MySQL manual used to mention explicitly that it's NOT necessarily the case, but I couldn't find it right now by a quick search).

Basically, we are talking about a query similar, if not identical, to what one would expect while selecting from a table with pre-ordered records, but not using ORDER BY while doing so, right? Would you agree that in this case the order of the result set is, in general, unpredictable?

From the practical point of view, is there any reason why you need to have ORDER BY in the subquery rather than outside?

Comment by Tom Jansen [ 2012-10-24 ]

Hi Elena,

First of all, thanks for contacting! I can not find any documentation about whether the result should be sorted or not. Secondly, if not specifying any order, I'd have to agree with you that order is unpredictable.
We're using the order for something even more undocumented, but nonetheless very useful: rankings results in multiple categories. 

SET @rank := 1;
 
SET @prev := -1;
 
SELECT
 
	-- rank incrementally while current sortme equals previous sortme, else restart at 1
 
	IF(@prev=sortme, @rank:=@rank+1, ((@prev:=sortme) OR (@rank:=1))) as rank, sortme
 
FROM (
 
	SELECT * FROM bugdata
 
	ORDER BY sortme
 
) x;
 
 
 
which results in:
 
+------+--------+
 
| rank | sortme |
 
+------+--------+
 
|    1 |     29 |
 
|    2 |     29 |
 
|    3 |     29 |
 
|    4 |     29 |
 
|    1 |    209 |
 
|    1 |     29 |
 
+------+--------+

The last row is not expected in this case.

While in this example, the order by could have been done by the outer query, this does not work when using more complex inner queries that are sorted on aggregated fields. I can imagine that you believe that this is abuse of MariaDB and not a bug. It used to work though ...

Thanks for your time.

Comment by Elena Stepanova [ 2012-10-24 ]

Hi Tom,

I don't think it's abuse of MariaDB, I just think that relying on non-deterministic behavior is always dangerous in a long run, and the example you provided proves the point. It used to work by chance, then some other optimization was implemented or changed, and it started working differently – that's how it happens.
However, I can pass it to our optimizer team to confirm.

Comment by Elena Stepanova [ 2012-12-10 ]

Yet another confirmation by Sergei Golubchik in MDEV-3926:
https://mariadb.atlassian.net/browse/MDEV-3926?focusedCommentId=28800&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-28800

I've added his reply to the KB: https://kb.askmonty.org/en/why-is-order-by-in-a-from-subquery-ignored/

Generated at Thu Feb 08 06:51:20 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.