[MDEV-24201] DISTINCT does not return compressed columns Created: 2020-11-12  Updated: 2023-04-27

Status: Confirmed
Project: MariaDB Server
Component/s: Data types, Storage Engine - InnoDB
Affects Version/s: 10.3.17, 10.3, 10.4, 10.5
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Major
Reporter: Royston P Carter Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: DISTINCT, SELECT, compression
Environment:

CentOS 8


 Description   

I have a simple query listed below which correctly returns 20 rows of data. If I add DISTINCT to the clause, it still returns 20 rows but the "purchase_token" field is empty for all rows. This field, "purchase_token" is a compressed field, the only one returned in this query.

Working query:
select uid, order_id, purchase_token from ud_registration where is_consumable = 1;

Failing query:
select DISTINCT uid, order_id, purchase_token from ud_registration where is_consumable = 1;

 Comments   
Comment by Alice Sherepa [ 2020-11-12 ]

Could you please add the output of SHOW CREATE TABLE ud_registration; ?

Comment by Royston P Carter [ 2020-11-12 ]

Alice, as requested.

ud_registration CREATE TABLE `ud_registration` (
`registration_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`identity_id` int(10) unsigned NOT NULL,
`purchase_token` varchar(1024) /!100301 COMPRESSED/ COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`order_id` varchar(256) COLLATE utf8mb4_unicode_ci NOT NULL,
`purchase_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`duplicate_entry_found` tinyint(1) DEFAULT 0,
`time_created` timestamp NOT NULL DEFAULT current_timestamp(),
PRIMARY KEY (`registration_id`),
UNIQUE KEY `identity_id` (`identity_id`,`order_id`),
CONSTRAINT `ud_registration_ibfk_1` FOREIGN KEY (`identity_id`) REFERENCES `ud_identity` (`identity_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Regards

Roy

Comment by Alice Sherepa [ 2020-11-12 ]

Thanks a lot! Now I could repeat it. (on 10.3-10.5)

create table t1 (uid int, data varchar(1024) compressed collate utf8mb4_unicode_ci);
 
insert into t1 values (1,repeat("a",99)), (1,repeat("b",100));
 
 
 
select  data from t1;
 
select distinct data from t1;
 
 
 
drop table t1;


MariaDB [test]> select  data from t1;
 
+------------------------------------------------------------------------------------------------------+
 
| data                                                                                                 |
 
+------------------------------------------------------------------------------------------------------+
 
| aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa  |
 
| bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb |
 
+------------------------------------------------------------------------------------------------------+
 
2 rows in set (0.000 sec)
 
 
 
MariaDB [test]> select distinct data from t1;
 
+-----------------------------------------------------------------------------------------------------+
 
| data                                                                                                |
 
+-----------------------------------------------------------------------------------------------------+
 
| aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
 
|                                                                                                     |
 
+-----------------------------------------------------------------------------------------------------+
 
2 rows in set (0.001 sec)

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