| Bug #69590 | Problem using between with group by functions | ||
|---|---|---|---|
| Submitted: | 26 Jun 2013 15:59 | Modified: | 14 Mar 2018 20:53 |
| Reporter: | Claudio Fumagalli | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.5.32 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | betwee, count, group by functions, MAX, min, regression, sum | ||
[26 Jun 2013 16:45]
MySQL Verification Team
[miguel@tikal 5.5]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.33-debug Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> CREATE TABLE prova_minMax_noGroup (
-> id int,
-> value int,
-> perioddate datetime,
-> primary key (id,perioddate)
-> );
Query OK, 0 rows affected (0.15 sec)
mysql>
mysql> insert into prova_minMax_noGroup VALUES
-> (1,1,'2010-01-02'),
-> (2,2,'2010-01-10'),
-> (3,3,'2010-01-10'),
-> (4,4,'2010-01-10');
Query OK, 4 rows affected (0.06 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select min(perioddate),count(*) from prova_minMax_noGroup where id=1 and perioddate>=str_to_date('2010-01-01','%Y-%m-%d') and perioddate<=str_to_date('2010-01-20','%Y-%m-%d');
+---------------------+----------+
| min(perioddate) | count(*) |
+---------------------+----------+
| 2010-01-02 00:00:00 | 1 |
+---------------------+----------+
1 row in set (0.02 sec)
mysql> select min(perioddate),count(*) from prova_minMax_noGroup where id=1 and perioddate between str_to_date('2010-01-01','%Y-%m-%d') and str_to_date('2010-01-20','%Y-%m-%d');
+-----------------+----------+
| min(perioddate) | count(*) |
+-----------------+----------+
| NULL | 0 |
+-----------------+----------+
1 row in set (0.00 sec)
mysql> select min(perioddate),count(*) from prova_minMax_noGroup where id=1 and perioddate between str_to_date('2010-01-01','%Y-%m-%d') and str_to_date('2010-01-20','%Y-%m-%d') GROUP BY '1';
+---------------------+----------+
| min(perioddate) | count(*) |
+---------------------+----------+
| 2010-01-02 00:00:00 | 1 |
+---------------------+----------+
1 row in set (0.01 sec)
mysql> exit
Bye
[miguel@tikal 5.5]$ bin/mysqladmin -uroot shutdown
[miguel@tikal 5.5]$ cd ..
[miguel@tikal build]$ cd 5.6
[miguel@tikal 5.6]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.13-debug Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE TABLE prova_minMax_noGroup (
-> id int,
-> value int,
-> perioddate datetime,
-> primary key (id,perioddate)
-> );
Query OK, 0 rows affected (0.50 sec)
mysql>
mysql> insert into prova_minMax_noGroup VALUES
-> (1,1,'2010-01-02'),
-> (2,2,'2010-01-10'),
-> (3,3,'2010-01-10'),
-> (4,4,'2010-01-10');
Query OK, 4 rows affected (0.20 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql>
mysql> /* This will wotk */
mysql> select min(perioddate),count(*) from prova_minMax_noGroup where id=1 and perioddate>=str_to_date('2010-01-01','%Y-%m-%d') and perioddate<=str_to_date('2010-01-20','%Y-%m-%d');
+---------------------+----------+
| min(perioddate) | count(*) |
+---------------------+----------+
| 2010-01-02 00:00:00 | 1 |
+---------------------+----------+
1 row in set (0.03 sec)
mysql>
mysql> /* This will not work */
mysql> select min(perioddate),count(*) from prova_minMax_noGroup where id=1 and perioddate between str_to_date('2010-01-01','%Y-%m-%d') and str_to_date('2010-01-20','%Y-%m-%d');
+---------------------+----------+
| min(perioddate) | count(*) |
+---------------------+----------+
| 2010-01-02 00:00:00 | 1 |
+---------------------+----------+
1 row in set (0.00 sec)
mysql>
mysql> /* This will work */
mysql> select min(perioddate),count(*) from prova_minMax_noGroup where id=1 and perioddate between str_to_date('2010-01-01','%Y-%m-%d') and str_to_date('2010-01-20','%Y-%m-%d') GROUP BY '1';
+---------------------+----------+
| min(perioddate) | count(*) |
+---------------------+----------+
| 2010-01-02 00:00:00 | 1 |
+---------------------+----------+
1 row in set (0.00 sec)
mysql>
[26 Jun 2013 16:48]
MySQL Verification Team
5.1.XX not affected so regression.
[miguel@tikal 5.1]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.71-debug Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE TABLE prova_minMax_noGroup (
-> id int,
-> value int,
-> perioddate datetime,
-> primary key (id,perioddate)
-> );
Query OK, 0 rows affected (0.06 sec)
mysql>
mysql> insert into prova_minMax_noGroup VALUES
-> (1,1,'2010-01-02'),
-> (2,2,'2010-01-10'),
-> (3,3,'2010-01-10'),
-> (4,4,'2010-01-10');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql>
mysql> /* This will wotk */
mysql> select min(perioddate),count(*) from prova_minMax_noGroup where id=1 and perioddate>=str_to_date('2010-01-01','%Y-%m-%d') and perioddate<=str_to_date('2010-01-20','%Y-%m-%d');
+---------------------+----------+
| min(perioddate) | count(*) |
+---------------------+----------+
| 2010-01-02 00:00:00 | 1 |
+---------------------+----------+
1 row in set (0.02 sec)
mysql>
mysql> /* This will not work */
mysql> select min(perioddate),count(*) from prova_minMax_noGroup where id=1 and perioddate between str_to_date('2010-01-01','%Y-%m-%d') and str_to_date('2010-01-20','%Y-%m-%d');
+---------------------+----------+
| min(perioddate) | count(*) |
+---------------------+----------+
| 2010-01-02 00:00:00 | 1 |
+---------------------+----------+
1 row in set (0.00 sec)
mysql>
mysql> /* This will work */
mysql> select min(perioddate),count(*) from prova_minMax_noGroup where id=1 and perioddate between str_to_date('2010-01-01','%Y-%m-%d') and str_to_date('2010-01-20','%Y-%m-%d') GROUP BY '1';
+---------------------+----------+
| min(perioddate) | count(*) |
+---------------------+----------+
| 2010-01-02 00:00:00 | 1 |
+---------------------+----------+
1 row in set (0.00 sec)
mysql>
[14 Mar 2018 20:53]
Roy Lyseng
Posted by developer: Fixed in 5.6.13 and up.
Description: min or max group by functions in combination with other functions like count or sum will return wrong result with a between where condition and no group by condition. How to repeat: drop table if exists prova_minMax_noGroup; CREATE TABLE prova_minMax_noGroup ( id int, value int, perioddate datetime, primary key (id,perioddate) ); insert into prova_minMax_noGroup VALUES (1,1,'2010-01-02'), (2,2,'2010-01-10'), (3,3,'2010-01-10'), (4,4,'2010-01-10'); /* This will wotk */ select min(perioddate),count(*) from prova_minMax_noGroup where id=1 and perioddate>=str_to_date('2010-01-01','%Y-%m-%d') and perioddate<=str_to_date('2010-01-20','%Y-%m-%d'); /* This will not work */ select min(perioddate),count(*) from prova_minMax_noGroup where id=1 and perioddate between str_to_date('2010-01-01','%Y-%m-%d') and str_to_date('2010-01-20','%Y-%m-%d'); /* This will work */ select min(perioddate),count(*) from prova_minMax_noGroup where id=1 and perioddate between str_to_date('2010-01-01','%Y-%m-%d') and str_to_date('2010-01-20','%Y-%m-%d') GROUP BY '1'; Suggested fix: As reported in the previous example a possible workaround could be use ">= and <=" or to add "group by '1'" Upgrade to 5.6 (Using release 5.6.12 I have obtained the expected result)