[MDEV-21319] COUNT(*) returns 1, actual SELECT returns no result in 10.3.21, but 1 result in 10.1.41 Created: 2019-12-13  Updated: 2019-12-20  Resolved: 2019-12-19

Status: Closed
Project: MariaDB Server
Component/s: Temporal Types
Affects Version/s: 10.2.27, 10.1.42, 10.3.18, 10.4.8
Fix Version/s: 10.1.44, 10.2.31, 10.3.22, 10.4.12, 10.5.1

Type: Bug Priority: Major
Reporter: Mario DE WEERD Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: select
Environment:

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+deb9u6

Attachments: File mysql_cnf.mdev-21319.tar.gz     File test_select.sql.gz    
Issue Links:
Problem/Incident
is caused by MDEV-19699 Server crashes in Item_null_result::f... Closed

 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.

 Comments   
Comment by Alice Sherepa [ 2019-12-16 ]

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)

Comment by Mario DE WEERD [ 2019-12-16 ]

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

Comment by Alice Sherepa [ 2019-12-17 ]

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`)

Comment by Alexander Barkov [ 2019-12-18 ]

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 |
 
+----------+

Comment by Alexander Barkov [ 2019-12-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.

Comment by Mario DE WEERD [ 2019-12-20 ]

Thanks for fixing this.
I am impressed by the delay.
I hope to see this update "downstream" in the near future..

Generated at Thu Feb 08 09:06:14 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.