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

1 files changed, 117 insertions, 0 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: