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

XML

Word

Printable

Details

Type: Bug
Status: Closed (View Workflow)
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`;

Attachments

Activity

People

Assignee:: Unassigned

Reporter:: Tom Jansen

Votes:: 1 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2012-10-10 17:03

Updated:: 2012-12-10 14:59

Resolved:: 2012-12-10 14:59

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.