MySQL Bugs: #44743: Join in combination with concat does not always work

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:	None
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

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'

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)

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)

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.

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.

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)

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.

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)

Noted in 5.4.4 changelog.

Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.

Ignore previous comment about 5.4.2.

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)

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)

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)

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)

The 5.4 fix has been pushed to 5.4.2.