Does this behavior exist in all major databases?

Question

mysql> select 0.121='0.121';
+---------------+
| 0.121='0.121' |
+---------------+
|             1 |
+---------------+

Does it hold for other database that number='number' is true?

Answer 1

Even if it does. What does this help you ? I would never, ever, make this assumption anyway. You always need to convert both operands to the same type so that, at least, you know what you are comparing.

Answer 2

First of all: most databases are using localized number formats. So turning a number into a string will most probably not always be the same as your hard-coded string.

Then: you will get problems with the sql syntax you use. See my experiments with oracle bellow.

---

In Oracle you always need a FROM clause (except they changed this in version 10).

select 0.121='0.121' from sys.dual

In Oracle, you can't have an expression like this in the select clause.

You need a case statement:

select case when 0.121 = '0.121' then 1 else 0 end as xy 
from sys.dual

Then you get an error that it is no number. To fix this, convert it:

select case when To_Char(0.121) = '0.121' then 1 else 0 end as xy 
from sys.dual

this will return 0! Because, on my machine, 0.121 is converted to the string ".121". These are Swiss settings. If I had German settings, it would be ",121" (note the comma).

So to finally answer the question: No.

Answer 3

Most of the reputable databases will do an implicit conversion for this type of query. There may be published rules for implicit conversions on a particular system - you'd have to look at the vendor coumentation to find out what implicit conversions are done on your system.

For instance, here's an official reference from Microsoft for SQL Server 2000, and here's a blog entry on SQL Server implicit conversions.

Answer 4

No.

I don't know why Stackoverflow requires me to enter more than 3 characters in answer to this question.

Answer 5

Postgresql is a little more strict than mysql about type conversion, and does not let you implicitly cast/convert between numbers and strings. This is sane behaviour, and it is getting slightly more strict with newer versions. Some examples, from Postgres 8.4:

db=# select 0.112::float = '0.112'::text;
ERROR:  operator does not exist: double precision = text
LINE 1: select 0.112::float = '0.112'::text;
                            ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

db=# select 0.112 = ('0.1' || '12');
ERROR:  operator does not exist: numeric = text
LINE 1: select 0.112 = ('0.1' || '12');
                     ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

db=# select 0.112 = ('0.1' || '12')::float; -- explicit cast
 t

However, this example (the original question) works:

db=# select 0.122 = '0.122';
 t

This is a little surprising (or misleading), given the above. But it has to do with how the query is parsed: when it sees an (unqualified) '0.122' literal, the parser does not necessarily assumes it is of TEXT type, but assigns instead a preliminary "unknown" type; its final type is deduced later by some heuristics.

Anyway, it's bad practice to rely on this, as mentioned by others.