[Rails] ActiveRecord: It's not only quoting

[Curt Sampson]

On Fri, 17 Dec 2004, Jarkko Laine wrote:

> On 17.12.2004, at 10:06, <contact at maik-schmidt.de> wrote:
> >
> > Additionally, most of the "SELECT" statements will not work with NULL
> > values. In DB2 it's not possible to run a statement like "SELECT * FROM
> > x WHERE y = NULL". It has to be "SELECT * FROM x WHERE y IS NULL".

It certainly is possible. It's likely not a very useful query, since
"<anything> = NULL" will always return NULL, not true or false. But you
can do it.

> I think the latter is the SQL standard and works for sure in Oracle and
> PostgreSQL.

This behavior is definitely the SQL standard, and MySQL conforms to it
as well. I just did a quick test:

mysql> SELECT 1 = 1, 1 = 2, 1 <> 2, 1 = NULL, 1 <> NULL, NULL = NULL, NULL != NULL;
+-------+-------+--------+----------+-----------+-------------+--------------+
| 1 = 1 | 1 = 2 | 1 <> 2 | 1 = NULL | 1 <> NULL | NULL = NULL | NULL != NULL |
+-------+-------+--------+----------+-----------+-------------+--------------+
|     1 |     0 |      1 |     NULL |      NULL |        NULL |         NULL |
+-------+-------+--------+----------+-----------+-------------+--------------+

Of course, those first three values would be true, false, and true in a
more reasonable DBMS.

> > I think it's not a good idea to clutter the code base with esoteric
> > DB2/Oracle/... knowledge. Any suggestions?

I think the adapater will just have to know how to DTRT if this is
essential. Fortunately, it doesn't seem difficult, though it could mean
executing a couple of extra queries on some DBMSes.

But is this just needed for user stuff, or is it used elsewhere? A good
relational design shouldn't really need to use stuff like LIMIT.

cjs
-- 
Curt Sampson  <cjs at cynic.net>   +81 90 7737 2974   http://www.NetBSD.org
     Make up enjoying your city life...produced by BIC CAMERA