Bug #44743 Join in combination with concat does not always work
| Submitted: | 8 May 2009 8:55 | Modified: | 26 Jun 2009 2:25 |
|---|---|---|---|
| Reporter: | Marco Janssen | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S1 (Critical) |
| Version: | 5.1.34-community, 5.1.35-bzr, 4.1, 5.0, 6.0 | OS: | Any (Vista Business SP1, Linux) |
| Assigned to: | Ramil Kalimullin | CPU Architecture: | Any |
| Tags: | compare, concat, join, string | ||
[8 May 2009 9:02]
Shane Bester
valgrind error (and potential crashing bug): ==32469== Invalid read of size 1 ==32469== at 0x85261F1: my_mb_wc_latin1 (ctype-latin1.c:367) ==32469== by 0x81F1D43: copy_and_convert(char*, unsigned, charset_info_st*, char const*, unsigned, charset_info_st*, unsigned*) (sql_string.cc:801) ==32469== by 0x81F319D: String::copy(char const*, unsigned, charset_info_st*, charset_info_st*, unsigned*) (sql_string.cc:345) ==32469== by 0x818F48B: Item_func_conv_charset::val_str(String*) (item_strfunc.cc:2717) ==32469== by 0x814C46C: Item::save_in_field(Field*, bool) (item.cc:4892) ==32469== by 0x82823D3: store_key_item::copy_inner() (sql_select.h:687) ==32469== by 0x8260E9C: cp_buffer_from_ref(THD*, st_table*, st_table_ref*) (sql_select.h:618) ==32469== by 0x827AC96: join_read_key(st_join_table*) (sql_select.cc:14114) ==32469== by 0x826DDC9: sub_select(JOIN*, st_join_table*, bool) (sql_select.cc:11120) ==32469== by 0x826DBB3: evaluate_join_record(JOIN*, st_join_table*, int) (sql_select.cc:11236) ==32469== by 0x826DDE7: sub_select(JOIN*, st_join_table*, bool) (sql_select.cc:11127) ==32469== by 0x827B49A: do_select(JOIN*, List<Item>*, st_table*, Procedure*) (sql_select.cc:10877) ==32469== Address 0x65EE688 is 0 bytes inside a block of size 32 free'd ==32469== at 0x400501A: free (vg_replace_malloc.c:233) ==32469== by 0x84F4ED1: my_no_flags_free (my_malloc.c:59) ==32469== by 0x81F2D40: String::real_alloc(unsigned) (sql_string.h:193) ==32469== by 0x81F3166: String::copy(char const*, unsigned, charset_info_st*, charset_info_st*, unsigned*) (sql_string.h:202) ==32469== by 0x818F48B: Item_func_conv_charset::val_str(String*) (item_strfunc.cc:2717) ==32469== by 0x814C46C: Item::save_in_field(Field*, bool) (item.cc:4892) ==32469== by 0x82823D3: store_key_item::copy_inner() (sql_select.h:687) ==32469== by 0x8260E9C: cp_buffer_from_ref(THD*, st_table*, st_table_ref*) (sql_select.h:618) ==32469== by 0x827AC96: join_read_key(st_join_table*) (sql_select.cc:14114) ==32469== by 0x826DDC9: sub_select(JOIN*, st_join_table*, bool) (sql_select.cc:11120) ==32469== by 0x826DBB3: evaluate_join_record(JOIN*, st_join_table*, int) (sql_select.cc:11236) ==32469== by 0x826DDE7: sub_select(JOIN*, st_join_table*, bool) (sql_select.cc:11127)
[8 May 2009 9:15]
Valeriy Kravchuk
Thank you for the bug report. Verified just as described with recent 5.1.35 from bzr on Linux. Note that different execution plans lead to wrong results:
mysql> explain SELECT CONCAT('gui_', t0.stringid) AS stringid , t1.data as BE_fr FROM jcr_guistrings t0 LEFT JOIN jcr_translations t1 ON t1.stringid=CONCAT('gui_', t0.stringid) and t1.country='be' and t1.language='fr'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t0
type: index
possible_keys: NULL
key: PRIMARY
key_len: 102
ref: NULL
rows: 3
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: eq_ref
possible_keys: PRIMARY,idx_stringid
key: PRIMARY
key_len: 318
ref: func,const,const
rows: 1
Extra:
2 rows in set (0.01 sec)
mysql> explain SELECT CONCAT('gui_', t0.stringid) AS stringid , t1.data as BE_fr FROM jcr_guistrings t0 LEFT JOIN jcr_translations t1 ON CONCAT(t1.stringid)=CONCAT('gui_', t0.stringid) and t1.country='be' and t1.language='fr'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t0
type: index
possible_keys: NULL
key: PRIMARY
key_len: 102
ref: NULL
rows: 3
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
Extra:
2 rows in set (0.01 sec)
[15 May 2009 9:27]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/74167 2874 Ramil Kalimullin 2009-05-15 Fix for bug#44743: Join in combination with concat does not always work bug#44766: valgrind error when using convert() in a subquery Problem: converting a string to some charset we didn't take into account that input and output buffers may be the same. Fix: reallocate output buffer in such cases. @ mysql-test/r/cast.result Fix for bug#44743: Join in combination with concat does not always work bug#44766: valgrind error when using convert() in a subquery - test result. @ mysql-test/r/func_concat.result Fix for bug#44743: Join in combination with concat does not always work bug#44766: valgrind error when using convert() in a subquery - test result. @ mysql-test/t/cast.test Fix for bug#44743: Join in combination with concat does not always work bug#44766: valgrind error when using convert() in a subquery - test case. @ mysql-test/t/func_concat.test Fix for bug#44743: Join in combination with concat does not always work bug#44766: valgrind error when using convert() in a subquery - test case. @ sql/sql_string.cc Fix for bug#44743: Join in combination with concat does not always work bug#44766: valgrind error when using convert() in a subquery - String::copy() - if the input string points to the output buffer, allocate the buffer anew.
[21 May 2009 8:07]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/74673 2897 Ramil Kalimullin 2009-05-21 Fix for bug#44743: Join in combination with concat does not always work bug#44766: valgrind error when using convert() in a subquery Problem: input and output buffers may be the same converting a string to some charset. That may lead to wrong results/valgrind warnings. Fix: use different buffers. @ mysql-test/r/cast.result Fix for bug#44743: Join in combination with concat does not always work bug#44766: valgrind error when using convert() in a subquery - test result. @ mysql-test/r/func_concat.result Fix for bug#44743: Join in combination with concat does not always work bug#44766: valgrind error when using convert() in a subquery - test result. @ mysql-test/t/cast.test Fix for bug#44743: Join in combination with concat does not always work bug#44766: valgrind error when using convert() in a subquery - test case. @ mysql-test/t/func_concat.test Fix for bug#44743: Join in combination with concat does not always work bug#44766: valgrind error when using convert() in a subquery - test case. @ sql/item.cc Fix for bug#44743: Join in combination with concat does not always work bug#44766: valgrind error when using convert() in a subquery - comment added. @ sql/item_strfunc.cc Fix for bug#44743: Join in combination with concat does not always work bug#44766: valgrind error when using convert() in a subquery - '&args[0]->str_value' used as a parameter of args[0]->val_str(), as 'str' may be equal to 'str_value' which we use as the output buffer converting strings. @ sql/sql_string.cc Fix for bug#44743: Join in combination with concat does not always work bug#44766: valgrind error when using convert() in a subquery - input and output buffers must NOT be the same.
[28 May 2009 8:16]
Bugs System
Pushed into 5.1.36 (revid:joro@sun.com-20090528073639-yohsb4q1jzg7ycws) (version source revid:ramil@mysql.com-20090521095605-b271vdcz4op3cc0y) (merge vers: 5.1.36) (pib:6)
[1 Jun 2009 19:29]
Paul DuBois
Noted in 5.1.36 changelog. Conversion of a string to a different character set could use the same buffer for input and output, leading to incorrect results or warnings. Setting report to NDI pending push into 6.0.x.
[17 Jun 2009 19:23]
Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090616183122-chjzbaa30qopdra9) (version source revid:ramil@mysql.com-20090521100743-a64v5anrz4jfhjdv) (merge vers: 6.0.12-alpha) (pib:11)
[26 Jun 2009 2:25]
Paul DuBois
Noted in 5.4.4 changelog.
[12 Aug 2009 22:29]
Paul DuBois
Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.
[15 Aug 2009 1:47]
Paul DuBois
Ignore previous comment about 5.4.2.
[26 Aug 2009 13:46]
Bugs System
Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[26 Aug 2009 13:46]
Bugs System
Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)
[26 Aug 2009 13:48]
Bugs System
Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)
[27 Aug 2009 16:32]
Bugs System
Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[7 Oct 2009 19:34]
Paul DuBois
The 5.4 fix has been pushed to 5.4.2.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.103 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: Dear MySQL Support, I use a select to merge some data for translation. But for some strange reason the LEFT JOIN doesn't always work. This is the query: SELECT CONCAT('gui_', t0.stringid) AS stringid , t1.data as BE_fr FROM jcr_guistrings t0 LEFT JOIN jcr_translations t1 ON t1.stringid=CONCAT('gui_', t0.stringid) and t1.country='be' and t1.language='fr' This query sometimes fail. When I change the query to: SELECT CONCAT('gui_', t0.stringid) AS stringid , t1.data as BE_fr FROM jcr_guistrings t0 LEFT JOIN jcr_translations t1 ON CONCAT(t1.stringid)=CONCAT('gui_', t0.stringid) and t1.country='be' and t1.language='fr' Notice the extra concat in the left join, everything works fine except because of this the query doesn't use the index anymore. Also when I change the concat to trim it also works but ofcourse no index. When I execute the first query I get this result: stringid BE_fr gui_about environ... gui_aboutjobcorner <NULL> gui_actmailsentjs Un e-mail... When I execute the query with the extra concat I get the expected result: stringid BE_fr gui_about environ... gui_aboutjobcorner A propos... gui_actmailsentjs Un e-mail... I also discovered when I change the value in jcr_translation from gui_aboutjobcorner to gui_aboutjc and change the value in jcr_guistrings from aboutjobcorner to aboutjc, also the third result is NULL. I added the full query in the How To Repeat section so you can check it yourself. I am really out of options. I really think this is a bug. If so, I really hope there is a solution, because in the full query and database the index is really needed. Best Regards, Marco How to repeat: DROP TABLE IF EXISTS `jcr_translations`; CREATE TABLE `jcr_translations` ( `stringid` varchar(100) NOT NULL default '0', `country` varchar(2) NOT NULL default '', `language` varchar(2) NOT NULL default '', `data` text NOT NULL, PRIMARY KEY (`stringid`,`country`,`language`), KEY `idx_stringid` (`stringid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; REPLACE INTO `jcr_translations` VALUES ('gui_about','be','fr','environ'); REPLACE INTO `jcr_translations` VALUES ('gui_aboutjobcorner','be','fr','A propos de Jobcorner'); REPLACE INTO `jcr_translations` VALUES ('gui_actmailsentjs','be','fr','Un e-mail vous a été envoyé avec davantage d\'explications sur la manière d\'activer votre compte Jobcorner.'); DROP TABLE IF EXISTS `jcr_guistrings`; CREATE TABLE `jcr_guistrings` ( `stringid` varchar(100) NOT NULL default '', `description` varchar(255) character set utf8 default NULL, PRIMARY KEY (`stringid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; REPLACE INTO `jcr_guistrings` VALUES ('about','googlemap: about (distance)'); REPLACE INTO `jcr_guistrings` VALUES ('aboutjobcorner','general'); REPLACE INTO `jcr_guistrings` VALUES ('actmailsentjs','registerform: activation mail sent (for jobseeker)'); SELECT CONCAT('gui_', t0.stringid) AS stringid , t1.data as BE_fr FROM jcr_guistrings t0 LEFT JOIN jcr_translations t1 ON t1.stringid=CONCAT('gui_', t0.stringid) and t1.country='be' and t1.language='fr'