Wednesday, March 12, 2008

MySQL stored procedurs: ...the CASE that they gave me...

Let's see if you can solve this little puzzle...

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:

Anonymous said...

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)

Koke said...

Change

when v_char = '/' then

to

when '/' then

and it works

Anonymous said...

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 '/'.

Unknown said...

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 |
+-----------+

Erik said...

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...

Grant Limberg said...

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.

Unknown said...

Could it be that "/" is an identifier and '/' is the string literal you were hoping for?

Andrew said...

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)

Anonymous said...

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.

dbscience said...

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;

Fortxun said...

Changing the equality operator to a strcmp() fixes the problem

-
when v_char = '/' then
-
+
when strcmp(v_char, '/') then
+

John J. said...

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='/'...

Gabi D said...

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.

SAP HANA Trick: DISTINCT STRING_AGG

Nowadays, many SQL implementations offer some form of aggregate string concatenation function. Being an aggregate function, it has the effe...