Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3.17, 10.3(EOL), 10.4(EOL), 10.5
-
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;
Attachments
Activity
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
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)
|
|
Could you please add the output of SHOW CREATE TABLE ud_registration; ?