MYSQL: self written string-manipulation function returns unexpected result

Question

I'm trying to implement a MYSQL function MY_LEFT_STR(STRING x,INT position) in such a way that

• MY_LEFT_STR('HELLO', 4) => returns 'HELL' (same as internal LEFT function)
• MY_LEFT_STR('HELLO',-1) => returns 'HELL'

---

DROP FUNCTION IF EXISTS MY_LEFT_STR;
CREATE FUNCTION MY_LEFT_STR(
  in_str VARCHAR(255),
  pos INT
)
RETURNS VARCHAR(255)
BEGIN
  IF (pos < 0) THEN 
      RETURN LEFT(in_str,LENGTH(in_str) - pos);
  ELSE          
    RETURN LEFT(in_str,pos);  
  END IF;           
END;

---

the result is

select left_str('HELLO', 4)            as A
     , left_str('HELLO',-1)            as B
     , left('HELLO',length('HELLO')-1) as C
from dual

+-----+-----+-----+
| A   | B   | C   |
+-----+-----+-----+
|HELL |HELLO|HELL | 
+-----+-----+-----+

QUESTION What is wrong with my function declaration? (Besides a generall lack of testing for bordercases like MY_LEFT_STR('A',-4) ...

---

ANSWER: so embarassing ... the answer lies in the double negative for pos=-1 in

RETURN LEFT(in_str,LENGTH(in_str) - pos); 

this should be

RETURN LEFT(in_str,LENGTH(in_str) + pos);

Answer 1

Here's a clue: What's the result of LENGTH(in_str) - (-1)?

When pos is negative, then LENGTH(in_str) - pos yields a number longer than the length of the string. So LEFT() is bound to return the whole string, because you're asking for more characters than the total length of the string.

Answer 2

RETURN LEFT(in_str,LENGTH(in_str) - pos);

If pos is negative, won't LENGTH(in_str) - pos give you (for your example):

LENGTH(HELLO) - (-1) = 6?