[MDEV-3805] Wrong order in result with ordered subquery and derived_merge = off - JIRA

Details

Type: Bug
Status: Closed
Priority: Major
Resolution: Not a Bug
Affects Version/s: 5.5.27
Fix Version/s: None
Component/s: None
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`;

Gliffy Diagrams

Attachments

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Tom Jansen added a comment - 2012-10-24 21:21 - edited

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

Show

Tom Jansen added a comment - 2012-10-24 21:21 - edited Also reproducable on mysqld Ver 5.5.28-MariaDB-mariadb1~squeeze for debian-linux-gnu on x86_64 (mariadb.org binary distribution)

Hide

Permalink

Elena Stepanova added a comment - 2012-10-24 21:40

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?

Show

Elena Stepanova added a comment - 2012-10-24 21:40 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?

Hide

Permalink

Tom Jansen added a comment - 2012-10-24 22:57

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.

Show

Tom Jansen added a comment - 2012-10-24 22:57 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.

Hide

Permalink

Elena Stepanova added a comment - 2012-10-24 23:50

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.

Show

Elena Stepanova added a comment - 2012-10-24 23:50 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.

Hide

Permalink

Elena Stepanova added a comment - 2012-12-10 14:58

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/

Show

Elena Stepanova added a comment - 2012-12-10 14:58 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/

Wrong order in result with ordered subquery and derived_merge = off

Details

Description

Gliffy Diagrams

Attachments

Activity

People

Dates

Agile