Description:
Hi,
When trying to use a user-defined variable to report the result of the previous row to the next one + use of GROUP BY, it only works if the GROUP BY column has an index.
Adding WITH ROLLUP seems to make it work properly as well.
How to repeat:
DROP TABLE IF EXISTS users;
CREATE TABLE users (pseudo varchar(24) NOT NULL DEFAULT '', reg_date TIMESTAMP NOT NULL DEFAULT '2018-01-01 00:00:00');
INSERT INTO users (pseudo, reg_date) VALUES ('test', '2018-01-01 00:00:00'),('test2', '2018-01-01 00:00:00'),('test3', '2018-01-02 00:00:00'), ('test3', '2018-01-02 00:00:00');
SET @users:=0; SELECT COUNT(*), @users:=COUNT(*)+@users as total, reg_date FROM users GROUP BY reg_date;
*************************** 1. row ***************************
COUNT(*): 2
total: 2
reg_date: 2018-01-01 00:00:00
*************************** 2. row ***************************
COUNT(*): 2
total: 2
reg_date: 2018-01-02 00:00:00
2 rows in set (0.00 sec)
SET @users:=0; SELECT COUNT(*), @users:=COUNT(*)+@users as total, reg_date FROM users GROUP BY reg_date WITH ROLLUP\G
Query OK, 0 rows affected (0.00 sec)
*************************** 1. row ***************************
COUNT(*): 2
total: 2
reg_date: 2018-01-01 00:00:00
*************************** 2. row ***************************
COUNT(*): 2
total: 4
reg_date: 2018-01-02 00:00:00
*************************** 3. row ***************************
COUNT(*): 4
total: 6
reg_date: NULL
3 rows in set (0.00 sec)
ALTER TABLE users ADD KEY (reg_date);
SET @users:=0; SELECT COUNT(*), @users:=COUNT(*)+@users as total, reg_date FROM users GROUP BY reg_date\G
Query OK, 0 rows affected (0.00 sec)
*************************** 1. row ***************************
COUNT(*): 2
total: 2
reg_date: 2018-01-01 00:00:00
*************************** 2. row ***************************
COUNT(*): 2
total: 4
reg_date: 2018-01-02 00:00:00
2 rows in set (0.00 sec)
Suggested fix:
Result should not depend on index or WITH ROLLUP
Description: Hi, When trying to use a user-defined variable to report the result of the previous row to the next one + use of GROUP BY, it only works if the GROUP BY column has an index. Adding WITH ROLLUP seems to make it work properly as well. How to repeat: DROP TABLE IF EXISTS users; CREATE TABLE users (pseudo varchar(24) NOT NULL DEFAULT '', reg_date TIMESTAMP NOT NULL DEFAULT '2018-01-01 00:00:00'); INSERT INTO users (pseudo, reg_date) VALUES ('test', '2018-01-01 00:00:00'),('test2', '2018-01-01 00:00:00'),('test3', '2018-01-02 00:00:00'), ('test3', '2018-01-02 00:00:00'); SET @users:=0; SELECT COUNT(*), @users:=COUNT(*)+@users as total, reg_date FROM users GROUP BY reg_date; *************************** 1. row *************************** COUNT(*): 2 total: 2 reg_date: 2018-01-01 00:00:00 *************************** 2. row *************************** COUNT(*): 2 total: 2 reg_date: 2018-01-02 00:00:00 2 rows in set (0.00 sec) SET @users:=0; SELECT COUNT(*), @users:=COUNT(*)+@users as total, reg_date FROM users GROUP BY reg_date WITH ROLLUP\G Query OK, 0 rows affected (0.00 sec) *************************** 1. row *************************** COUNT(*): 2 total: 2 reg_date: 2018-01-01 00:00:00 *************************** 2. row *************************** COUNT(*): 2 total: 4 reg_date: 2018-01-02 00:00:00 *************************** 3. row *************************** COUNT(*): 4 total: 6 reg_date: NULL 3 rows in set (0.00 sec) ALTER TABLE users ADD KEY (reg_date); SET @users:=0; SELECT COUNT(*), @users:=COUNT(*)+@users as total, reg_date FROM users GROUP BY reg_date\G Query OK, 0 rows affected (0.00 sec) *************************** 1. row *************************** COUNT(*): 2 total: 2 reg_date: 2018-01-01 00:00:00 *************************** 2. row *************************** COUNT(*): 2 total: 4 reg_date: 2018-01-02 00:00:00 2 rows in set (0.00 sec) Suggested fix: Result should not depend on index or WITH ROLLUP