Description:
Getting the same digest for different queries if we use integer value in the ORDER BY clause.
How to repeat:
CREATE TABLE test_digest(
user_name char(1) not null,
amount int not null;
INSERT INTO test_digest (user_name, amount) VALUES ('a',10),('a',5),('b',3),('b',11);
> SELECT user_name,MIN(amount) FROM test_digest GROUP BY 1 ORDER BY 1;
+-----------+-------------+
| user_name | MIN(amount) |
+-----------+-------------+
| a | 5 |
| b | 3 |
+-----------+-------------+
2 rows in set (0.00 sec)
> SELECT user_name,MIN(amount) FROM test_digest GROUP BY 1 ORDER BY 2;
+-----------+-------------+
| user_name | MIN(amount) |
+-----------+-------------+
| b | 3 |
| a | 5 |
+-----------+-------------+
2 rows in set (0.00 sec)
> SELECT digest_text FROM performance_schema.events_statements_summary_by_digest where digest_text like 'SELECT%test_digest%';
+---------------------------------------------------------------------------------+
| digest_text |
+---------------------------------------------------------------------------------+
| SELECT `user_name` , MIN ( `amount` ) FROM `test_digest` GROUP BY ? ORDER BY ? |
+---------------------------------------------------------------------------------+
1 row in set (0.00 sec)
If we use column names in group by and order by we get different digest.
SELECT user_name,MIN(amount) as m_amount FROM test_digest GROUP BY 1 ORDER BY user_name;
SELECT user_name,MIN(amount) as m_amount FROM test_digest GROUP BY 1 ORDER BY m_amount;
> SELECT digest_text FROM performance_schema.events_statements_summary_by_digest where digest_text like 'SELECT%test_digest%';
+---------------------------------------------------------------------------------------------------------+
| digest_text |
+---------------------------------------------------------------------------------------------------------+
| SELECT `user_name` , MIN ( `amount` ) AS `m_amount` FROM `test_digest` GROUP BY ? ORDER BY `user_name` |
| SELECT `user_name` , MIN ( `amount` ) AS `m_amount` FROM `test_digest` GROUP BY ? ORDER BY `m_amount` |
+---------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)
Suggested fix:
digest should handle such GROUP BY and ORDER BY case properly with integers
Description: Getting the same digest for different queries if we use integer value in the ORDER BY clause. How to repeat: CREATE TABLE test_digest( user_name char(1) not null, amount int not null; INSERT INTO test_digest (user_name, amount) VALUES ('a',10),('a',5),('b',3),('b',11); > SELECT user_name,MIN(amount) FROM test_digest GROUP BY 1 ORDER BY 1; +-----------+-------------+ | user_name | MIN(amount) | +-----------+-------------+ | a | 5 | | b | 3 | +-----------+-------------+ 2 rows in set (0.00 sec) > SELECT user_name,MIN(amount) FROM test_digest GROUP BY 1 ORDER BY 2; +-----------+-------------+ | user_name | MIN(amount) | +-----------+-------------+ | b | 3 | | a | 5 | +-----------+-------------+ 2 rows in set (0.00 sec) > SELECT digest_text FROM performance_schema.events_statements_summary_by_digest where digest_text like 'SELECT%test_digest%'; +---------------------------------------------------------------------------------+ | digest_text | +---------------------------------------------------------------------------------+ | SELECT `user_name` , MIN ( `amount` ) FROM `test_digest` GROUP BY ? ORDER BY ? | +---------------------------------------------------------------------------------+ 1 row in set (0.00 sec) If we use column names in group by and order by we get different digest. SELECT user_name,MIN(amount) as m_amount FROM test_digest GROUP BY 1 ORDER BY user_name; SELECT user_name,MIN(amount) as m_amount FROM test_digest GROUP BY 1 ORDER BY m_amount; > SELECT digest_text FROM performance_schema.events_statements_summary_by_digest where digest_text like 'SELECT%test_digest%'; +---------------------------------------------------------------------------------------------------------+ | digest_text | +---------------------------------------------------------------------------------------------------------+ | SELECT `user_name` , MIN ( `amount` ) AS `m_amount` FROM `test_digest` GROUP BY ? ORDER BY `user_name` | | SELECT `user_name` , MIN ( `amount` ) AS `m_amount` FROM `test_digest` GROUP BY ? ORDER BY `m_amount` | +---------------------------------------------------------------------------------------------------------+ 2 rows in set (0.01 sec) Suggested fix: digest should handle such GROUP BY and ORDER BY case properly with integers