Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.27, 10.1.42, 10.3.18, 10.4.8
-
10.3.21-MariaDB-1:10.3.21+maria~stretch-log - mariadb.org binary distribution
Apache/2.4.25 (Debian)
Version du client de base de données : libmysql - mysqlnd 5.0.12-dev - 20150407 - $Id: b5c5906d452ec590732a93b051f3827e02749b83 $
Extension PHP : mysqliDocumentation curlDocumentation mbstringDocumentation
Version de PHP : 7.0.33-0+deb9u610.3.21-MariaDB-1:10.3.21+maria~stretch-log - mariadb.org binary distribution Apache/2.4.25 (Debian) Version du client de base de données : libmysql - mysqlnd 5.0.12-dev - 20150407 - $Id: b5c5906d452ec590732a93b051f3827e02749b83 $ Extension PHP : mysqliDocumentation curlDocumentation mbstringDocumentation Version de PHP : 7.0.33-0+deb9u6
Description
The select query works as expected on 10.1.41-MariaDB-0+deb9u1 - Debian 9.9, but not on 10.3.21-MariaDB-1:10.3.21+maria~stretch-log - mariadb.org binary distribution .
Next Request indicates that there is 1 result
SELECT COUNT(*) FROM (SELECT *,UNIX_TIMESTAMP(time) as utcTime FROM `positions` `t` USE INDEX(dev_good_reason_time) WHERE ((`t`.`device_id`=54) AND (`t`.`log_reason`=0)) AND (`t`.`is_good_position`=1) GROUP BY `t`.`time` HAVING `t`.`time`=MAX(`t`.`time`)) sq; |
Actual request returns no result on 10.3.21
SELECT *,UNIX_TIMESTAMP(time) as utcTime FROM `positions` `t` USE INDEX(dev_good_reason_time) WHERE ((`t`.`device_id`=54) AND (`t`.`log_reason`=0)) AND (`t`.`is_good_position`=1) GROUP BY `t`.`time` HAVING `t`.`time`=MAX(`t`.`time`) ORDER BY time DESC LIMIT 1; |
The above query returns record number 30031 on mariadb 10.1.41
This was verified using the attached data imported in a new database after exporting the sample from the original database and anomyzing the data. The unexpected behavior occurs on the original database and both test databases running in the checked systems.
This is blocking (to use 10.3) - I downgraded the setup to 10.1 already used in production to be able to put this new instance in production. After downgrading to 10.1 the functionnality relying on this query works fine.
Attachments
Issue Links
- is caused by
-
-
- Closed
-
Activity
The
COUNT(*) |
is working, but not the actual select (2nd query in the report).
I had to revert this server instance to version 10.1, but another server instance that served as the reference also has the issue.
The version is:
10.3.20-MariaDB-1:10.3.20+maria~stretch-log
I've attache the configuration files (I removed the passwords).
mysql_cnf.mdev-21319.tar.gz
Thanks for the report! The query returns wrong results after 29bbf4749ebcfe328d73ca93c7ee5 commit
( after MDEV-19699)
create table t1 ( |
id int not null primary key, |
id2 int, |
k tinyint(1),
|
j int, |
t datetime,
|
key k1 (id2,k,j,t)); |
|
|
insert into `t1` values (53,54,1,0,'2019-12-13 10:09:59'),(54,54,1,0,'2019-12-13 16:28:41'),(55,54,1,0,'2019-12-13 16:29:10'),(56,54,1,0,'2019-12-13 16:29:43'),(57,54,1,0,'2019-12-13 16:30:16'),(58,54,1,0,'2019-12-13 16:30:49'),(59,54,1,0,'2019-12-13 16:31:23'),(60,54,1,0,'2019-12-13 16:31:55'),(61,54,1,0,'2019-12-13 16:32:28'),(62,54,1,0,'2019-12-13 16:33:01'),(63,54,1,0,'2019-12-13 16:33:34'),(64,54,1,0,'2019-12-13 16:34:07'),(65,54,1,0,'2019-12-13 16:34:40'),(66,54,1,0,'2019-12-13 16:35:13'),(67,54,1,0,'2019-12-13 16:35:46'),(68,54,1,0,'2019-12-13 16:36:19'); |
|
|
select t from t1 group by t having t=max(t); |
select t from t1 where id2=54 and j=0 and k=1 group by t having t=max(t); #should give the same results as previous query |
MariaDB [test]> select t from t1 where id2=54 and j=0 and k=1 group by t having t=max(t);
|
+---------------------+
|
| t |
|
+---------------------+
|
| 2019-12-13 16:36:19 |
|
+---------------------+
|
1 row in set (0.000 sec)
|
|
|
MariaDB [test]> select t from t1 group by t having t=max(t);
|
+---------------------+
|
| t |
|
+---------------------+
|
| 2019-12-13 10:09:59 |
|
| 2019-12-13 16:28:41 |
|
| 2019-12-13 16:29:10 |
|
| 2019-12-13 16:29:43 |
|
| 2019-12-13 16:30:16 |
|
| 2019-12-13 16:30:49 |
|
| 2019-12-13 16:31:23 |
|
| 2019-12-13 16:31:55 |
|
| 2019-12-13 16:32:28 |
|
| 2019-12-13 16:33:01 |
|
| 2019-12-13 16:33:34 |
|
| 2019-12-13 16:34:07 |
|
| 2019-12-13 16:34:40 |
|
| 2019-12-13 16:35:13 |
|
| 2019-12-13 16:35:46 |
|
| 2019-12-13 16:36:19 |
|
+---------------------+
|
16 rows in set (0.000 sec)
|
|
|
MariaDB [test]> explain extended select t from t1 where id2=54 and j=0 and k=1 group by t having t=max(t);
|
+------+-------------+-------+------+---------------+------+---------+-------------------+------+----------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+-------------------+------+----------+--------------------------+
|
| 1 | SIMPLE | t1 | ref | k1 | k1 | 12 | const,const,const | 16 | 100.00 | Using where; Using index |
|
+------+-------------+-------+------+---------------+------+---------+-------------------+------+----------+--------------------------+
|
1 row in set, 1 warning (0.001 sec)
|
|
|
Note (Code 1003): select `test`.`t1`.`t` AS `t` from `test`.`t1` where `test`.`t1`.`id2` = 54 and `test`.`t1`.`j` = 0 and `test`.`t1`.`k` = 1 group by `test`.`t1`.`t` having `test`.`t1`.`t` = max(`test`.`t1`.`t`)
|
|
The same problem is repeatable with TIME:
CREATE OR REPLACE TABLE t1 |
(
|
id INT NOT NULL PRIMARY KEY, |
id2 INT, |
k TINYINT,
|
j INT, |
t TIME, |
KEY k1 (id2,k,j,t) |
);
|
|
|
INSERT INTO t1 VALUES |
(53,54,1,0,'10:09:59'), |
(54,54,1,0,'16:28:41'), |
(55,54,1,0,'16:29:10'), |
(56,54,1,0,'16:29:43'), |
(57,54,1,0,'16:30:16'), |
(58,54,1,0,'16:30:49'), |
(59,54,1,0,'16:31:23'), |
(60,54,1,0,'16:31:55'), |
(61,54,1,0,'16:32:28'), |
(62,54,1,0,'16:33:01'), |
(63,54,1,0,'16:33:34'), |
(64,54,1,0,'16:34:07'), |
(65,54,1,0,'16:34:40'), |
(66,54,1,0,'16:35:13'), |
(67,54,1,0,'16:35:46'), |
(68,54,1,0,'16:36:19'); |
|
|
SELECT t FROM t1 GROUP BY t HAVING t=MAX(t); |
SELECT t FROM t1 WHERE id2=54 AND j=0 AND k=1 GROUP BY t HAVING t=MAX(t); |
The first SELECT correctly returns:
+----------+
|
| t |
|
+----------+
|
| 10:09:59 |
|
| 16:28:41 |
|
| 16:29:10 |
|
| 16:29:43 |
|
| 16:30:16 |
|
| 16:30:49 |
|
| 16:31:23 |
|
| 16:31:55 |
|
| 16:32:28 |
|
| 16:33:01 |
|
| 16:33:34 |
|
| 16:34:07 |
|
| 16:34:40 |
|
| 16:35:13 |
|
| 16:35:46 |
|
| 16:36:19 |
|
+----------+
|
The second SELECT erroneously returns:
+----------+
|
| t |
|
+----------+
|
| 16:36:19 |
|
+----------+
|
A related problem:
DROP TABLE IF EXISTS t1; |
DROP VIEW IF EXISTS v1; |
CREATE TABLE t1 (pk INT); |
CREATE VIEW v1 AS SELECT * FROM t1; |
INSERT INTO t1 VALUES (1); |
SELECT pk<DATE'2001-01-01' FROM t1 GROUP BY pk; |
SELECT pk<DATE'2001-01-01' FROM v1 GROUP BY pk; |
+---------------------+
|
| pk<DATE'2001-01-01' |
|
+---------------------+
|
| 1 |
|
+---------------------+
|
+---------------------+
|
| pk<DATE'2001-01-01' |
|
+---------------------+
|
| NULL |
|
+---------------------+
|
Notice, there's inconsistency between result from the table and from the equivalent view.
Expect to get equal result sets.
Thanks for fixing this.
I am impressed by the delay.
I hope to see this update "downstream" in the near future..
could you please add your .cnf file(s), it does not reproduce for me with the default settings
MariaDB [test]> SELECT COUNT(*) FROM (SELECT *,UNIX_TIMESTAMP(time) as utcTime FROM `positions` `t` USE INDEX(dev_good_reason_time) WHERE ((`t`.`device_id`=54) AND (`t`.`log_reason`=0)) AND (`t`.`is_good_position`=1) GROUP BY `t`.`time` HAVING `t`.`time`=MAX(`t`.`time`)) sq;+----------+| COUNT(*) |+----------+| 1 |+----------+1 row in set (0.002 sec)MariaDB [test]> select version();+-----------------+| version() |+-----------------+| 10.3.21-MariaDB |+-----------------+1 row in set (0.000 sec)