Subquery ORDER BY doesn't work on MySQL 5.6, but works on 5.5

Question

I try to join 3 tables (products, prices, categories) together to get a result of latest & lowest prices from the joined tables.

I was able to get the expected result on MySQL 5.5, but after upgrading to 5.6, ORDER BY on the subquery seems be ignored. How to change the query to make the ORDER BY work?

The query is supposed to:

find products which is under specified category id (eg: category id 488).
find the latest price of each product.
group products with same 'matchkey' (matchkey generated by removing stopwords from the product name. generated by other programming)
find the lowest price of each grouped products
show each grouped products info with lowest price

Incorrect result:

The 3 tables relationship are as below:

products <-> many-to-many <-> categories
products -> one-to-many -> prices

table information:

mysql> show columns from products;
+----------------+------------------+------+-----+---------------------+-----------------------------+
| Field          | Type             | Null | Key | Default             | Extra                       |
+----------------+------------------+------+-----+---------------------+-----------------------------+
| id             | int(10) unsigned | NO   | PRI | NULL                | auto_increment              |
| name           | varchar(300)     | NO   | MUL | NULL                |                             |
| active         | tinyint(1)       | YES  |     | 1                   |                             |
| created_at     | timestamp        | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| updated_at     | timestamp        | NO   |     | 0000-00-00 00:00:00 |                             |
| matchkey       | varchar(300)     | NO   | MUL | NULL                |                             |
| sku            | varchar(50)      | YES  | MUL | NULL                |                             |
+----------------+------------------+------+-----+---------------------+-----------------------------+

mysql> show columns from prices;
+---------------+---------------------+------+-----+---------------------+-----------------------------+
| Field         | Type                | Null | Key | Default             | Extra                       |
+---------------+---------------------+------+-----+---------------------+-----------------------------+
| id            | bigint(20) unsigned | NO   | PRI | NULL                | auto_increment              |
| price_selling | decimal(8,2)        | NO   | MUL | NULL                |                             |
| price_before  | decimal(8,2)        | NO   |     | 0.00                |                             |
| product_id    | int(10) unsigned    | NO   | MUL | NULL                |                             |
| created_at    | timestamp           | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| updated_at    | timestamp           | NO   |     | 0000-00-00 00:00:00 |                             |
+---------------+---------------------+------+-----+---------------------+-----------------------------+

mysql> show columns from categories;
+------------------+------------------+------+-----+---------------------+-----------------------------+
| Field            | Type             | Null | Key | Default             | Extra                       |
+------------------+------------------+------+-----+---------------------+-----------------------------+
| id               | int(10) unsigned | NO   | PRI | NULL                | auto_increment              |
| parent_id        | int(11)          | YES  | MUL | NULL                |                             |
| lft              | int(11)          | YES  | MUL | NULL                |                             |
| rgt              | int(11)          | YES  | MUL | NULL                |                             |
| depth            | int(11)          | YES  |     | NULL                |                             |
| name             | varchar(255)     | NO   | MUL | NULL                |                             |
| active           | tinyint(1)       | YES  |     | 1                   |                             |
| created_at       | timestamp        | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| updated_at       | timestamp        | NO   |     | 0000-00-00 00:00:00 |                             |
+------------------+------------------+------+-----+---------------------+-----------------------------+

mysql> show columns from category_product;
+--------------------+------------------+------+-----+---------+-------+
| Field              | Type             | Null | Key | Default | Extra |
+--------------------+------------------+------+-----+---------+-------+
| product_id         | int(10) unsigned | NO   | PRI | NULL    |       |
| category_id        | int(10) unsigned | NO   | PRI | NULL    |       |
+--------------------+------------------+------+-----+---------+-------+

The query that works on MySQL 5.5 is:

SELECT 
    p3.pid AS id, category_product.product_id, p3.name, p3.priceId, 
    p3.product_id, p3.price_selling, p3.price_before, MIN(p3.price_selling) AS minprice,
    p3.matchkey, categories.url_key AS category_urlkey
FROM category_product
RIGHT JOIN (
            SELECT
                pp.priceId, pp.product_id, pp.price_selling, pp.price_before,
                pp.pid, pp.name, pp.matchkey
            FROM (
                    SELECT 
                        prices.id AS priceId, prices.product_id, prices.price_selling, prices.price_before,
                        products.id AS pid, products.name, products.matchkey
                    FROM prices
                    RIGHT JOIN products ON prices.product_id = products.id
                    WHERE prices.id = (SELECT MAX(p.id) FROM prices p WHERE p.product_id = prices.product_id)
            ) AS pp
            GROUP BY pp.pid
            ORDER BY pp.price_selling ASC
            ) AS p3 ON category_product.product_id = p3.pid
RIGHT JOIN categories ON categories.id = category_product.category_id AND categories.id = 488 
GROUP BY p3.matchkey 
ORDER BY p3.pid DESC;

The table structures & sample data can be used for test:

--
-- Table structure for table `products`
--
CREATE TABLE IF NOT EXISTS `products` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(300) COLLATE utf8_unicode_ci NOT NULL,
  `active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `matchkey` varchar(300) COLLATE utf8_unicode_ci NOT NULL,
  `sku` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `products_sku_index` (`sku`),
  KEY `id` (`id`),
  KEY `matchkey` (`matchkey`),
  FULLTEXT KEY `name` (`name`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=60057 ;

--
-- Dumping data for table `products`
--
INSERT INTO `products` (`id`, `name`, `active`, `created_at`, `updated_at`, `matchkey`, `sku`) VALUES
(26195, 'Samsung Galaxy S3 Mini GT-I8200 Unlocked Cellphone, White, 8GB', 1, '2016-01-23 14:38:25', '0000-00-00 00:00:00', '8gb cellphone galaxy gt i8200 mini s3 samsung white', NULL),
(26425, 'Samsung Galaxy S3 Mini GT-i8200 Factory Unlocked Cellphone, International Version, 8GB, White', 1, '2016-01-23 14:38:28', '0000-00-00 00:00:00', '8gb cellphone galaxy gt i8200 mini s3 samsung white', NULL),
(26632, 'Samsung Galaxy S3 Mini GT-i8200 Factory Unlocked Cellphone, International Version, 8GB, White', 1, '2016-01-23 14:38:29', '0000-00-00 00:00:00', '8gb cellphone galaxy gt i8200 mini s3 samsung white', NULL),
(28212, 'Samsung Galaxy S3 Mini GT-I8200 Unlocked Cellphone, White, 8GB', 1, '2016-01-23 14:38:51', '0000-00-00 00:00:00', '8gb cellphone galaxy gt i8200 mini s3 samsung white', NULL),
(35111, 'Samsung Galaxy S3 Mini GT-i8200 Factory Unlocked Cellphone, International Version, 8GB, White', 1, '2016-01-23 14:39:03', '0000-00-00 00:00:00', '8gb cellphone galaxy gt i8200 mini s3 samsung white', NULL),
(37611, '[From U.S.A]Samsung Galaxy S3 Mini GT-i8200 Factory Unlocked Cellphone, International Version, 8GB, White', 1, '2016-01-23 14:39:29', '0000-00-00 00:00:00', '8gb cellphone galaxy gt i8200 mini s3 samsung white', NULL),
(60017, 'Samsung Galaxy S3 Mini GT-i8200 Factory Unlocked Cellphone, International Version, 8GB, White', 1, '2016-01-23 14:38:11', '0000-00-00 00:00:00', '8gb cellphone galaxy gt i8200 mini s3 samsung white', NULL);

--
-- Table structure for table `prices`
--
CREATE TABLE IF NOT EXISTS `prices` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `price_selling` decimal(8,2) NOT NULL,
  `price_before` decimal(8,2) NOT NULL DEFAULT '0.00',
  `product_id` int(10) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `price_selling` (`price_selling`),
  KEY `id` (`id`),
  KEY `product_id` (`product_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=114663 ;

--
-- Dumping data for table `prices`
--
INSERT INTO `prices` (`id`, `price_selling`, `price_before`, `product_id`, `created_at`, `updated_at`) VALUES
(33585, '1052.40', '0.00', 26195, '2016-01-22 09:53:02', '0000-00-00 00:00:00'),
(65594, '1052.40', '0.00', 26195, '2016-01-23 01:27:08', '0000-00-00 00:00:00'),
(110038, '1052.40', '0.00', 26195, '2016-01-23 14:38:25', '0000-00-00 00:00:00'),
(33815, '1052.40', '0.00', 26425, '2016-01-22 09:55:17', '0000-00-00 00:00:00'),
(65790, '1052.40', '0.00', 26425, '2016-01-23 01:27:11', '0000-00-00 00:00:00'),
(110209, '1016.40', '0.00', 26425, '2016-01-23 14:38:28', '0000-00-00 00:00:00'),
(34022, '1052.40', '0.00', 26632, '2016-01-22 09:57:43', '0000-00-00 00:00:00'),
(66020, '1052.40', '0.00', 26632, '2016-01-23 01:27:13', '0000-00-00 00:00:00'),
(110373, '1052.40', '0.00', 26632, '2016-01-23 14:38:29', '0000-00-00 00:00:00'),
(35602, '960.25', '0.00', 28212, '2016-01-22 10:12:50', '0000-00-00 00:00:00'),
(67396, '960.25', '0.00', 28212, '2016-01-23 01:27:45', '0000-00-00 00:00:00'),
(111555, '960.25', '0.00', 28212, '2016-01-23 14:38:51', '0000-00-00 00:00:00'),
(71346, '929.20', '0.00', 35111, '2016-01-23 01:37:05', '0000-00-00 00:00:00'),
(112318, '899.30', '0.00', 35111, '2016-01-23 14:39:03', '0000-00-00 00:00:00'),
(73852, '936.10', '0.00', 37611, '2016-01-23 01:54:47', '0000-00-00 00:00:00'),
(113711, '936.10', '0.00', 37611, '2016-01-23 14:39:29', '0000-00-00 00:00:00'),
(109601, '1108.99', '2369.91', 60017, '2016-01-23 14:38:08', '0000-00-00 00:00:00');


--
-- Table structure for table `categories`
--
CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `lft` int(11) DEFAULT NULL,
  `rgt` int(11) DEFAULT NULL,
  `depth` int(11) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `url_key` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `categories_parent_id_index` (`parent_id`),
  KEY `categories_lft_index` (`lft`),
  KEY `categories_rgt_index` (`rgt`),
  KEY `url_key` (`url_key`),
  KEY `name` (`name`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=825 ;

--
-- Dumping data for table `categories`
--
INSERT INTO `categories` (`id`, `parent_id`, `lft`, `rgt`, `depth`, `name`, `active`, `url_key`, `created_at`, `updated_at`) VALUES
(1, NULL, 1, 968, 0, 'Root', 1, NULL, '2016-01-23 13:19:51', '2016-01-23 13:19:51'),
(487, 1, 446, 453, 1, 'Mobile & Communication', 1, 'mobile-communication', '2016-01-23 13:19:51', '2016-01-23 13:19:51'),
(488, 487, 447, 448, 2, 'Mobile Phones', 1, 'mobile-phones', '2016-01-25 06:27:37', '2016-01-25 06:27:37');


--
-- Table structure for table `category_product`
--
CREATE TABLE IF NOT EXISTS `category_product` (
  `product_id` int(10) unsigned NOT NULL,
  `category_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`product_id`,`category_id`),
  KEY `product_id` (`product_id`),
  KEY `category_id` (`category_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `category_product`
--
INSERT INTO `category_product` (`product_id`, `category_id`) VALUES
(26195, 1),
(26195, 487),
(26195, 488),
(26425, 1),
(26425, 487),
(26425, 488),
(26632, 1),
(26632, 487),
(26632, 488),
(28212, 1),
(28212, 487),
(28212, 488),
(35111, 1),
(35111, 487),
(35111, 488),
(37611, 1),
(37611, 487),
(37611, 488),
(60017, 1),
(60017, 487),
(60017, 488);

Responses to comments

Downgrading to 5.5 is my last option if can't fix it. I would like to rewrite the query, I tried changing the query still no lucky to get it work. I think is something wrong in the query I write.

For the testing data provided above, the query should return products under (category id: 488). The query is used for a price comparison site. When a user clicks on the category ('Mobile Phones' - category id in database- 'categories' table is 488) link on website, the website will list all the products (grouped by 'matchkey' column), showing the lowest price of that product group.

Adding WHERE categories.id = 488 or categories.name = 'Mobile Phones', I still get the same result. The problem is the query result not showing the lowest price, even I write ORDER BY pp.price_selling ASC on the subquery.

Demo:

Both demos above are using the same query, but the results are different.

Rick James · Accepted Answer · 2016-01-25 20:32:17Z

SELECT a, b ...
    GROUP BY a

is asking for trouble. You may have been 'lucky' in 5.5 and 'unlucky' in 5.6.

The problem is that any value of b can be shown for each 'group'. Sure, there are cases where a given a maps uniquely to a given b (eg, in a normalization table), but that does not feel like the case here. To see that there is a problem:

SET sql_mode = 'ONLY_FULL_GROUP_BY';

before doing the SELECT.

See also: http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sqlmode_only_full_group_by

You really should switch to InnoDB.

When you get to 5.7, that setting will be defaulted on.

Efficient techniques for groupwise max.

Stack Exchange Network

Subquery ORDER BY doesn't work on MySQL 5.6, but works on 5.5

Responses to comments

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged
mysql
performance
mysql-5.6
subquery
order-by
query-performance
or ask your own question.

Hot Network Questions

Subquery ORDER BY doesn't work on MySQL 5.6, but works on 5.5

Responses to comments

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged mysqlperformancemysql-5.6subqueryorder-byquery-performance or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
mysql
performance
mysql-5.6
subquery
order-by
query-performance
or ask your own question.