Capital. Just capital.

March 21, 2007

Capital. Just capital.

Filed under: MySQL Gotchas — snoyes @ 7:48 am

Culled from the Certification Study Guide:

mysql> SELECT * FROM test;
+----------------+
| data           |
+----------------+
| This is a test |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT UPPER(data) FROM test;
+----------------+
| UPPER(data)    |
+----------------+
| This is a test |
+----------------+
1 row in set (0.03 sec)

How’s that work?

Show Answer ▼

 CREATE TABLE `test` (
   `data` varbinary(255) default NULL
 );

Binary strings are just a list of bytes. They aren’t characters anymore, so there is no upper or lower case. Remember that when choosing between char/varchar/text and binary/varbinary/blob.

Comments (3)

3 Comments »

I’d call this a bug. The Upper() function is defined as taking a string argument and returning a string. It mentions nothing about accepting binary as an argument.
If you pass it something that is not a string to the upper() function it should either:
1) Return an error
OR (better)
2) Convert the argument to a string and perform the desired function.

The fact that this is in a study guide for certification is bad.

Comment by Jason — March 21, 2007 @ 2:14 pm
binary is a string. It’s just not a string of characters; it’s a string of bytes.

Comment by snoyes — March 21, 2007 @ 2:34 pm
I agree with Jason. This is the sort of poor unexpected behavior you would get from weakly typed systems. Force an error to make the user understand the expected behavior, eliminating bugs.

Comment by Matt — March 24, 2011 @ 10:19 am