Consider this stored procedure:
-- finds the first slash and exits
create procedure p_find_slash(p_text text)
begin
declare v_index int default 1;
declare v_length int default character_length(p_text);
declare v_char char(1);
_main_loop: while v_index <= v_length do -- loop over all characters
set v_char := substring(p_text, v_index, 1); -- grab the current character
case v_char
when v_char = '/' then -- found a slash!
select concat('A slash at ', v_index) message; -- report it
leave _main_loop; -- and then stop
else
select concat(v_char, ' is not a slash.') message; -- not a slash...
end case;
set v_index := v_index + 1; -- next character pls
end while;
end;
Of course, it's a bogus stored procedure, but that's not the point right now. Most people can guess what the intention is of this procedure: it should examine each character in the argument text, check if it is a slash, and if so, report its position and then stop execution. If the character is not a slash, the procedure moves on to the next character.
So, what do you expect when we call it with only a literal slash as argument? Let's find out:
call p_find_slash('/');
Well? It may come as a surprise to some, but this is the result:
+-------------------+
| message |
+-------------------+
| / is not a slash. |
+-------------------+
So, can you explain this result? Can you fix it?
Just leave a comment to this post with your explanation and the solution. Results published later this week....and oh!! If you know how to fix this, maybe you're ready to move on to the next level and should attend the "Advanced Stored Procedures" tutorial by Mariella Di Giacomothe at the MySQL User's Conference. Like Giuseppe Maxia wrote earlier, you can earn a 20% discount code by asking a speaker!!
So that is the return: if you post your solution as a comment on this blog, I'll make sure you'll get that code for a 20% discount.
13 comments:
The CASE statement is wrong, you are mixing up both forms of the CASE statement together, use one form or the other:
mysql> create procedure p_find_slash(p_text text)
-> begin
-> declare v_index int default 1;
-> declare v_length int default character_length(p_text);
-> declare v_char char(1);
->
-> _main_loop: while v_index <= v_length do -- loop over all characters
->
-> set v_char := substring(p_text, v_index, 1); -- grab the current character
-> case v_char
-> when '/' then -- found a slash!
-> select concat('A slash at ', v_index) message; -- report it
-> leave _main_loop; -- and then stop
-> else
-> select concat(v_char, ' is not a slash.') message; -- not a slash...
-> end case;
-> set v_index := v_index + 1; -- next character pls
-> end while;
-> end;
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql>
mysql> call p_find_slash('/');
+--------------+
| message |
+--------------+
| A slash at 1 |
+--------------+
1 row in set (0.00 sec)
Change
when v_char = '/' then
to
when '/' then
and it works
As hinted in the title, the error is in the case statement.
You should either do "CASE vchar WHEN '/' THEN ..." or "CASE WHEN v_char = '/'.
In your example, you end up asking whether v_char is equal to (v_char = '/') which is 1 (one), and not equal to '/'.
Just for fun, I can't attend the conference. So If you really want to award the winner your discount code, don't post my answer until you get one from an eligible participant.
The Case syntax is messed up. The where clause needs to be changed to WHEN '/', or change the case to
CASE
WHEN v_char = '/' THEN
To eliminate the v_char between CASE and WHEN.
As originally written, wont report a slash until it hits the first char that is not a slash. As the condition in the where needs to be false to match.
run a
SELECT false ='/';
+-----------+
| false='/' |
+-----------+
| 1 |
+-----------+
This is a mix-up of the two case alternative syntaxes.
Change to:
case v_char
when '/' then -- found a slash!
Or, since there are only two alternatives, use an IF...
You're mixing up the two uses of the CASE statement in this example. You can do:
CASE v_char
WHEN '/' THEN ...
ELSE ...
END CASE;
or:
CASE
WHEN v_char = '/' THEN ...
ELSE ...
END CASE;
But you can't mix them together as you did above using:
CASE v_char
WHEN v_char = '/' THEN ...
Using either one of the two correct examples returns the correct result.
Could it be that "/" is an identifier and '/' is the string literal you were hoping for?
The problem is in the lines:
case v_char
when v_char = '/' then -- found a slash!
Where, in this case, you're comparing v_char to the expression "v_char = '/'". This expression is true (counter-intuitively) and is compared to v_char (from case v_char) which fails.
Why? Because of the type conversion rules in MySQL, where if there's not a conversion rule it defaults to converting both arguments to float (real) values. This causes the SP to treat a slash as not a slash. There's also weird behavior when you have a character that's not a slash, where 'test/' will compare 't' to ('t' == '/' => false) which after conversion to floats is true(!), which follows the first case path and incorrectly state that 't' is a slash.
To fix it you can just change the when line from:
when v_char = '/' then -- found a slash!
to:
when '/' then -- found a slash!
Or change the initial "case" line from:
case v_char
when v_char = '/' then -- found a slash!
to:
case
when v_char = '/' then -- found a slash!
So you're only comparing what you want to compare :)
My rewritten SP (using the first approach), including some cleanup to not spit out every character that's not a slash:
-- finds the first slash and exits
create procedure p_find_slash(p_text text)
begin
declare v_index int default 1;
declare v_length int default character_length(p_text);
declare v_char char(1);
_main_loop: while v_index <= v_length do -- loop over all characters
set v_char := substring(p_text, v_index, 1); -- grab the current character
case v_char
when '/' then -- found a slash!
select concat('A slash at ', v_index) message; -- report it
leave _main_loop; -- and then stop
else
begin
end;
end case;
set v_index := v_index + 1; -- next character pls
end while;
if v_index > v_length then
select concat(p_text, ' does not contain a slash.') message; -- not a slash...
end if;
end;
And some examples:
single slash:
mysql> call p_find_slash('/');
+--------------+
| message |
+--------------+
| A slash at 1 |
+--------------+
1 row in set (0.00 sec)
slash at the end:
mysql> call p_find_slash('test/');
+--------------+
| message |
+--------------+
| A slash at 5 |
+--------------+
1 row in set (0.00 sec)
no slash:
mysql> call p_find_slash('test');
+--------------------------------+
| message |
+--------------------------------+
| test does not contain a slash. |
+--------------------------------+
1 row in set (0.01 sec)
slash in the middle:
mysql> call p_find_slash('te/st');
+--------------+
| message |
+--------------+
| A slash at 3 |
+--------------+
1 row in set (0.00 sec)
You need to be strong now. ;-)
Just check the CASE syntax, instead of "when v_char = '/' then" you need to write "when '/' then".
I wonder how MySQL evaluates your code, but it obviously goes wrong...
Another problem is that the counter doesn't work properly, the "A slash at %i" is always one to high.
I didn't test this code, but I believe the problem is "v_char = '/'" is evaluated to true, which isn't equal to '/'. So the code would be:
create procedure p_find_slash(p_text text)
begin
declare v_index int default 1;
declare v_length int default character_length(p_text);
declare v_char char(1);
_main_loop: while v_index <= v_length do -- loop over all characters
set v_char := substring(p_text, v_index, 1); -- grab the current character
case v_char
when '/' then -- found a slash!
select concat('A slash at ', v_index) message; -- report it
leave _main_loop; -- and then stop
else
select concat(v_char, ' is not a slash.') message; -- not a slash...
end case;
set v_index := v_index + 1; -- next character pls
end while;
end;
Changing the equality operator to a strcmp() fixes the problem
-
when v_char = '/' then
-
+
when strcmp(v_char, '/') then
+
Best option to fix is to not use a CASE, but to use an IF/ELSE.
Failing that the problem is that you are comparing v_char to a boolean value (v_char='/'). It should either be
CASE v_char
WHEN '/'...
or
CASE
WHEN v_char='/'...
Hi Roland,
nice :-) trying to think mysql diverted me from trying to think programming language...
here is the fix:
instead of
when v_char = '/' then
use :
when '/' then
simply because the case itself evaluates your expression and then compares it to the value of v_char. Since the expression evaluates to 0 or 1 it is never equal to '/' (or any other char you would test against for that matter except for 1).
So you should compare to / instead.
But you already knew that, didn't you ;-)
Check out the behavior of 0 by the way - if you put 0 in your input string, for the existing code you gave (looking for slash), it will think there's a slash there because the expression will evaluate to 0 and that would also be v_char's value.
Post a Comment