MySQL Bugs: #59952: distinct ,distinct_key,group

Bug #59952	distinct ,distinct_key,group_key
Submitted:	4 Feb 2011 17:20	Modified:	11 Mar 2011 20:29
Reporter:	Miran Cvenkel	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.1.55-community, 5.6.3	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
2 machines, win, linux

W: 5.1.31-community
L: 5.1.31-community-log

Without distinct all works OK

select distinct g.id_galery_obfuscated,t.term from galery_1 g join tezaver t on g.id_tezaver = t.id_tezaver

-->/* SQL Error (1062): Duplicate entry '-24672854d7d7407d82debca64ed61a35-Knylhavre' for key 'distinct_key' */

As I noticed that limit 30000 works, I guesses that increasing
tmp_table_size would help, and it did on win machine.

If I put additional keys on any of tables I have to increase tmp_table_size further to avoid distinct problems.

On linux machine I'm getting nowhere with this. Copyed entire tables to win from linux and viceversa, so they are 100% identical.

if I try workarounding:

select distinct * from
(
select g.id_galery_obfuscated,t.term from galery_1 g join tezaver t on g.id_tezaver = t.id_tezaver
) as a

-->/* SQL Error (1062): Duplicate entry '24672854d7d7407d82debca64ed61a35-Knylhavre' for key 'group_key' */

if I add "left" join into sql, same thing, on different record.

Tables:

CREATE TABLE  `test`.`galery_1` (
  `id_galery` int(10) unsigned zerofill NOT NULL DEFAULT '0000000000' COMMENT '',
  `id_galery_obfuscated` varchar(36) COLLATE utf8_slovenian_ci DEFAULT NULL COMMENT 'zamegljen id_galery, da se ne da parse download',
  `id_member` varchar(45) COLLATE utf8_slovenian_ci DEFAULT NULL COMMENT '',
  `author` varchar(200) COLLATE utf8_slovenian_ci NOT NULL COMMENT '',
  `author_link` varchar(200) COLLATE utf8_slovenian_ci DEFAULT NULL,
  `original_file_name` varchar(400) COLLATE utf8_slovenian_ci DEFAULT NULL,
  `disk_file_name` varchar(400) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '''''',
  `title` varchar(400) COLLATE utf8_slovenian_ci DEFAULT NULL,
  `DSP` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `width_px` varchar(4) COLLATE utf8_slovenian_ci DEFAULT NULL,
  `height_px` varchar(4) COLLATE utf8_slovenian_ci DEFAULT NULL,
  `width_px_thumb` varchar(4) COLLATE utf8_slovenian_ci DEFAULT NULL,
  `height_px_thumb` varchar(4) COLLATE utf8_slovenian_ci DEFAULT NULL,
  `id_category` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '',
  `id_category_tree_path` varchar(400) COLLATE utf8_slovenian_ci DEFAULT NULL,
  `id_tezaver` bigint(20) NOT NULL DEFAULT '-1' COMMENT '',
  `home_jsp` datetime DEFAULT NULL COMMENT '',
  `id_license` tinyint(3) unsigned DEFAULT NULL,
  PRIMARY KEY (`id_galery`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_slovenian_ci;

CREATE TABLE  `test`.`tezaver` (
  `unique_row_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `id_tezaver` bigint(20) unsigned NOT NULL DEFAULT '0',
  `id_tezaver_obfuscated` varchar(36) COLLATE utf8_slovenian_ci NOT NULL,
  `l2` varchar(2) COLLATE utf8_slovenian_ci NOT NULL DEFAULT ' ',
  `term` varchar(100) COLLATE utf8_slovenian_ci NOT NULL DEFAULT ' ',
  `term_gender` enum('m','f','n','m_o','f_o','n_o','m_pl','f_pl','n_pl','m_o_pl','f_o_pl','n_o_pl') COLLATE utf8_slovenian_ci NOT NULL DEFAULT 'm' COMMENT 'm male, f femine, pl plural, o_m offspring male, o_f offspring femine, o_pl offspring plural',
  `DSP` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `term_description` varchar(2000) COLLATE utf8_slovenian_ci DEFAULT NULL,
  `term_link` varchar(400) COLLATE utf8_slovenian_ci DEFAULT NULL,
  `term_group` varchar(400) COLLATE utf8_slovenian_ci DEFAULT ' ',
  `member_id` varchar(40) COLLATE utf8_slovenian_ci DEFAULT NULL COMMENT 'info samo za admina',
  `edit_locked` int(10) unsigned DEFAULT '0' COMMENT '',
  `user_ip` varchar(45) COLLATE utf8_slovenian_ci DEFAULT NULL,
  `id_galery` int(10) unsigned zerofill DEFAULT NULL COMMENT '',
  `data_source` varchar(400) COLLATE utf8_slovenian_ci DEFAULT NULL,
  PRIMARY KEY (`term`,`term_gender`,`id_tezaver`,`l2`) USING BTREE,
  KEY `id_l2` (`id_tezaver`,`l2`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_slovenian_ci ROW_FORMAT=DYNAMIC;

How to repeat:
I guess it appears at certain amount of data/keys in tables

Thank you for the bug report. Your server version is quite older (today version released is 5.1.54) could you please upgrade and comment the results. Thanks in advance.

1.ver 5.1.55-community , windows
2.test the above sql, all OK.
3.set tmp_table_size=18M, from previously 54M. resstart instance.
4.test the above sql -->
5.SQL Error (1062): Duplicate entry '-82e74d9c3b464aedbd9979c0f417489e-Fliegen-Arten Mauer' for key 'distinct_key'

So, the problem is confirmed on latest version.

Note: if I create new tables via create table as select ... and select only keyed columns, set indexes on that new tables same as on original, then sql on that new tables works.

ignore last post, it is not correct ...

Any detailed instructions how to submit file > 500kb to developers ?
I can't figure it out.

You can upload big files to ftp://ftp.mysql.com/pub/mysql/upload/. Use bug # (59952) in the file name and add a comment when done. Note that you will not be able to see your file at that FTP site.

if I do in dos:
ftp ftp://ftp.mysql.com/pub/mysql/upload/
I get:
Unknown host ftp://ftp.mysql.com/pub/mysql/upload/.

?

OK, managed to do it by some miracle via fireFTP.
File: bug_(59952).zip
Forgot to mention in readme.txt that forinstance limit 30000 "solves" thing

Thank you for the test data.

Verified as described.