From 10805ec23465bd3bc38744da89f855331db96251 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 8 Aug 2005 13:46:13 -0700 Subject: item_strfunc.cc: BUG #11104 Took out the offset-=delimiter_length-1 out of the for loop. It was causing basically this: select substring_index('the king of the the hill', 'the', -2) to not work. The first iteration, offset would be initialised to 24, then strstr would point at 'the king of the the* hill' ('*'means right before the character following), returning a offset of 16. The for loop would then decrement offset by two (3 - 1), to 14, now pointing at "the king of th*e the hill", _skipping_ past the 'e' in the second to last 'the', and therefore strstr would never have a chance of matching the second to last 'the', then moving on to the 'the' at the begginning of the string! In a nutshell, offset was being decremented by too great a value, preventing the second to last 'the' from being ever found, hence the result of 'king of the the hill' from the query that is reported in the bug report func_str.test: BUG #11104 Added tests to make sure fix addresses issues in original bug report func_str.result: BUG #11104 New results for new tests mysql-test/r/func_str.result: BUG #11104 New results for new tests mysql-test/t/func_str.test: BUG #11104 Added tests to make sure fix addresses issues in original bug report sql/item_strfunc.cc: BUG #11104 Took out the offset-=delimiter_length-1 out of the for loop. It was causing basically this: select substring_index('the king of the the hill', 'the', -2) to not work. The first iteration, offset would be initialised to 24, then strstr would point at 'the king of the the* hill' ('*'means right before the character following), returning a offset of 16. The for loop would then decrement offset by two (3 - 1), to 14, now pointing at "the king of th*e the hill", _skipping_ past the 'e' in the second to last 'the', and therefore strstr would never have a chance of matching the second to last 'the', then moving on to the 'the' at the begginning of the string! In a nutshell, offset was being decremented by too great a value, preventing the second to last 'the' from being ever found, hence the result of 'king of the the hill' from the query that is reported in the bug report --- mysql-test/r/func_str.result | 117 +++++++++++++++++++++++++++++++++++++++++++ mysql-test/t/func_str.test | 39 +++++++++++++++ sql/item_strfunc.cc | 16 +++++- 3 files changed, 170 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index 4c3a03b8066..ae6578795f6 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -45,6 +45,123 @@ www. .se select substring_index('.tcx.se','.',-2),substring_index('.tcx.se','.tcx',-1); substring_index('.tcx.se','.',-2) substring_index('.tcx.se','.tcx',-1) tcx.se .se +select substring_index('aaaaaaaaa1','a',1); +substring_index('aaaaaaaaa1','a',1) + +select substring_index('aaaaaaaaa1','aa',1); +substring_index('aaaaaaaaa1','aa',1) + +select substring_index('aaaaaaaaa1','aa',2); +substring_index('aaaaaaaaa1','aa',2) +aa +select substring_index('aaaaaaaaa1','aa',3); +substring_index('aaaaaaaaa1','aa',3) +aaaa +select substring_index('aaaaaaaaa1','aa',4); +substring_index('aaaaaaaaa1','aa',4) +aaaaaa +select substring_index('aaaaaaaaa1','aa',5); +substring_index('aaaaaaaaa1','aa',5) +aaaaaaaaa1 +select substring_index('aaaaaaaaa1','aaa',1); +substring_index('aaaaaaaaa1','aaa',1) + +select substring_index('aaaaaaaaa1','aaa',2); +substring_index('aaaaaaaaa1','aaa',2) +aaa +select substring_index('aaaaaaaaa1','aaa',3); +substring_index('aaaaaaaaa1','aaa',3) +aaaaaa +select substring_index('aaaaaaaaa1','aaa',4); +substring_index('aaaaaaaaa1','aaa',4) +aaaaaaaaa1 +select substring_index('aaaaaaaaa1','aaaa',1); +substring_index('aaaaaaaaa1','aaaa',1) + +select substring_index('aaaaaaaaa1','aaaa',2); +substring_index('aaaaaaaaa1','aaaa',2) +aaaa +select substring_index('aaaaaaaaa1','1',1); +substring_index('aaaaaaaaa1','1',1) +aaaaaaaaa +select substring_index('aaaaaaaaa1','a',-1); +substring_index('aaaaaaaaa1','a',-1) +1 +select substring_index('aaaaaaaaa1','aa',-1); +substring_index('aaaaaaaaa1','aa',-1) +1 +select substring_index('aaaaaaaaa1','aa',-2); +substring_index('aaaaaaaaa1','aa',-2) +aa1 +select substring_index('aaaaaaaaa1','aa',-3); +substring_index('aaaaaaaaa1','aa',-3) +aaaa1 +select substring_index('aaaaaaaaa1','aa',-4); +substring_index('aaaaaaaaa1','aa',-4) +aaaaaa1 +select substring_index('aaaaaaaaa1','aa',-5); +substring_index('aaaaaaaaa1','aa',-5) +aaaaaaaaa1 +select substring_index('aaaaaaaaa1','aaa',-1); +substring_index('aaaaaaaaa1','aaa',-1) +1 +select substring_index('aaaaaaaaa1','aaa',-2); +substring_index('aaaaaaaaa1','aaa',-2) +aaa1 +select substring_index('aaaaaaaaa1','aaa',-3); +substring_index('aaaaaaaaa1','aaa',-3) +aaaaaa1 +select substring_index('aaaaaaaaa1','aaa',-4); +substring_index('aaaaaaaaa1','aaa',-4) + +select substring_index('the king of thethe hill','the',-2); +substring_index('the king of thethe hill','the',-2) +the hill +select substring_index('the king of the the hill','the',-2); +substring_index('the king of the the hill','the',-2) + the hill +select substring_index('the king of the the hill','the',-2); +substring_index('the king of the the hill','the',-2) + the hill +select substring_index('the king of the the hill',' the ',-1); +substring_index('the king of the the hill',' the ',-1) +hill +select substring_index('the king of the the hill',' the ',-2); +substring_index('the king of the the hill',' the ',-2) + the hill +select substring_index('the king of the the hill',' ',-1); +substring_index('the king of the the hill',' ',-1) +hill +select substring_index('the king of the the hill',' ',-2); +substring_index('the king of the the hill',' ',-2) +the hill +select substring_index('the king of the the hill',' ',-3); +substring_index('the king of the the hill',' ',-3) + the hill +select substring_index('the king of the the hill',' ',-4); +substring_index('the king of the the hill',' ',-4) +the the hill +select substring_index('the king of the the hill',' ',-5); +substring_index('the king of the the hill',' ',-5) +of the the hill +select substring_index('the king of the.the hill','the',-2); +substring_index('the king of the.the hill','the',-2) +.the hill +select substring_index('the king of thethethe.the hill','the',-3); +substring_index('the king of thethethe.the hill','the',-3) +the.the hill +select substring_index('the king of thethethe.the hill','the',-1); +substring_index('the king of thethethe.the hill','the',-1) + hill +select substring_index('the king of the the hill','the',1); +substring_index('the king of the the hill','the',1) + +select substring_index('the king of the the hill','the',2); +substring_index('the king of the the hill','the',2) +the king of +select substring_index('the king of the the hill','the',3); +substring_index('the king of the the hill','the',3) +the king of the select concat(':',ltrim(' left '),':',rtrim(' right '),':'); concat(':',ltrim(' left '),':',rtrim(' right '),':') :left : right: diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index ddfc83e620a..8afbe673ce3 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -23,6 +23,45 @@ select concat('',left(right(concat('what ',concat('is ','happening')),9),4),'',s select substring_index('www.tcx.se','.',-2),substring_index('www.tcx.se','.',1); select substring_index('www.tcx.se','tcx',1),substring_index('www.tcx.se','tcx',-1); select substring_index('.tcx.se','.',-2),substring_index('.tcx.se','.tcx',-1); +select substring_index('aaaaaaaaa1','a',1); +select substring_index('aaaaaaaaa1','aa',1); +select substring_index('aaaaaaaaa1','aa',2); +select substring_index('aaaaaaaaa1','aa',3); +select substring_index('aaaaaaaaa1','aa',4); +select substring_index('aaaaaaaaa1','aa',5); +select substring_index('aaaaaaaaa1','aaa',1); +select substring_index('aaaaaaaaa1','aaa',2); +select substring_index('aaaaaaaaa1','aaa',3); +select substring_index('aaaaaaaaa1','aaa',4); +select substring_index('aaaaaaaaa1','aaaa',1); +select substring_index('aaaaaaaaa1','aaaa',2); +select substring_index('aaaaaaaaa1','1',1); +select substring_index('aaaaaaaaa1','a',-1); +select substring_index('aaaaaaaaa1','aa',-1); +select substring_index('aaaaaaaaa1','aa',-2); +select substring_index('aaaaaaaaa1','aa',-3); +select substring_index('aaaaaaaaa1','aa',-4); +select substring_index('aaaaaaaaa1','aa',-5); +select substring_index('aaaaaaaaa1','aaa',-1); +select substring_index('aaaaaaaaa1','aaa',-2); +select substring_index('aaaaaaaaa1','aaa',-3); +select substring_index('aaaaaaaaa1','aaa',-4); +select substring_index('the king of thethe hill','the',-2); +select substring_index('the king of the the hill','the',-2); +select substring_index('the king of the the hill','the',-2); +select substring_index('the king of the the hill',' the ',-1); +select substring_index('the king of the the hill',' the ',-2); +select substring_index('the king of the the hill',' ',-1); +select substring_index('the king of the the hill',' ',-2); +select substring_index('the king of the the hill',' ',-3); +select substring_index('the king of the the hill',' ',-4); +select substring_index('the king of the the hill',' ',-5); +select substring_index('the king of the.the hill','the',-2); +select substring_index('the king of thethethe.the hill','the',-3); +select substring_index('the king of thethethe.the hill','the',-1); +select substring_index('the king of the the hill','the',1); +select substring_index('the king of the the hill','the',2); +select substring_index('the king of the the hill','the',3); select concat(':',ltrim(' left '),':',rtrim(' right '),':'); select concat(':',trim(LEADING FROM ' left'),':',trim(TRAILING FROM ' right '),':'); diff --git a/sql/item_strfunc.cc b/sql/item_strfunc.cc index d316c7eaf72..52a2dedb67c 100644 --- a/sql/item_strfunc.cc +++ b/sql/item_strfunc.cc @@ -42,7 +42,7 @@ static void my_coll_agg_error(DTCollation &c1, DTCollation &c2, const char *fname) { my_error(ER_CANT_AGGREGATE_2COLLATIONS,MYF(0), - c1.collation->name,c1.derivation_name(), + c1.collation->name,c1.derivation_name(), c2.collation->name,c2.derivation_name(), fname); } @@ -1188,10 +1188,22 @@ String *Item_func_substr_index::val_str(String *str) } else { // Start counting at end - for (offset=res->length() ; ; offset-=delimeter_length-1) + /* + Negative index, start counting at the end + */ + for (offset=res->length(); offset ;) { + /* + this call will result in finding the position pointing to one + address space less than where the found substring is located + in res + */ if ((int) (offset=res->strrstr(*delimeter,offset)) < 0) return res; // Didn't find, return org string + /* + At this point, we've searched for the substring + the number of times as supplied by the index value + */ if (!++count) { offset+=delimeter_length; -- cgit v1.2.1