Of Types and Comparisons
Submitted by Matthew Turland on Wed, 01/14/2009 - 09:38While in the #phpc IRC channel on the Freenode network recently, I got into a discussion with Maggie Nelson regarding database data types. In particular, she mentioned that the databases we work with on a regular basis, namely MySQL and Oracle, don't have an equivalent reference to the types comparison section of the PHP manual and that it handy to have cheat sheets for them.
I've discussed this topic a few times in various forms on my own blog including various data type systems, an offhand mention of lack of boolean support in Oracle functions, and treatment of NULL in Oracle and MySQL. While the Data Types and Type Conversion sections of the MySQL manual and the Datatypes and Datatype Comparison Rules sections of the Oracle manual are excellent equivalents for the Types and Type Juggling sections of the PHP manual, I think Maggie has a point: it would be nice to have a resource that makes it easier to find this information at a glance. So, I decided to give it a go in this blog post. One very handy reference to supplement my experimentation was a comparison of various SQL implementations.
Oracle
These tests were performed on Oracle 10gR2 using queries of the form shown below. An "E" indicates that no result was returned because an error was encountered. Unlike the PHP type comparison table, this table excludes boolean values because Oracle doesn't have a boolean data type. And yes, I know the tables are symmetric; a table is simply easier to reference from a visual standpoint than a list.
SELECT COUNT(*) FROM DUAL WHERE X = Y
| 1 | 0 | -1 | '1' | '0' | '-1' | NULL | 'oracle' | '' | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 1 | 0 | 0 | 1 | 0 | 0 | 0 | E | 0 |
| 0 | 0 | 1 | 0 | 0 | 1 | 0 | 0 | E | 0 |
| -1 | 0 | 0 | 1 | 0 | 0 | 1 | 0 | E | 0 |
| '1' | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
| '0' | 0 | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 0 |
| '-1' | 0 | 0 | 1 | 0 | 0 | 1 | 0 | 0 | 0 |
| NULL | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 'oracle' | E | E | E | 0 | 0 | 0 | 0 | 1 | 0 |
| '' | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Summary
- Numbers and strings comply with the law of identity (0 = 0, 'oracle' = 'oracle').
- A number and the string equivalent of that number are equal. (1 = '1')
- NULL and '' are synonymous and not equal to anything, even themselves. (That is, they violate the law of identity.)
- Comparing a number to a non-empty string will result in an error. (ORA-01722: invalid number)
MySQL
These tests were performed on MySQL 5.0.67 (Ubuntu package) using queries of the form shown below. As in the Oracle table, an "E" indicates that the operation resulted in an error. Unlike Oracle, MySQL will return NULL in some of the cases shown below. As such, an "N" result in the table below indicates that the operation had a NULL result.
SELECT X = Y
| TRUE | FALSE | 1 | 0 | -1 | '1' | '0' | '-1' | NULL | 'mysql' | '' | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| TRUE | 1 | 0 | 1 | 0 | 0 | 1 | 0 | 0 | N | 0 | 0 |
| FALSE | 0 | 1 | 0 | 1 | 0 | 0 | 1 | 0 | N | 1 | 1 |
| 1 | 1 | 0 | 1 | 0 | 0 | 1 | 0 | 0 | N | 0 | 0 |
| 0 | 0 | 1 | 0 | 1 | 0 | 0 | 1 | 0 | N | 1 | 1 |
| -1 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 1 | N | 0 | 0 |
| '1' | 1 | 0 | 1 | 0 | 0 | 1 | 0 | 0 | N | 0 | 0 |
| '0' | 0 | 1 | 0 | 1 | 0 | 0 | 1 | 0 | N | 0 | 0 |
| '-1' | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 1 | N | 0 | 0 |
| NULL | N | N | N | N | N | N | N | N | N | N | N |
| 'mysql' | 0 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | N | 1 | 0 |
| '' | 0 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | N | 0 | 1 |
Summary
- As with Oracle, strings and numbers comply with the law of identity.
- As with Oracle, numbers and their string equivalents are equal.
- 0, '', and FALSE are equal.
- 0 and FALSE are equal to non-empty non-numeric strings, but such comparisons cause warnings to be emitted.
- 1 and TRUE are equal. Because 1 and '1' and equal, TRUE and '1' are also equal. By the same principle, FALSE and '0' are equal.
- Any comparison involving NULL results in NULL.
