D. Richard Hipp wrote:
> I have lately noticed a need for an "IS" operator in SQLite.
> IS would work just like "=" for most things. The difference
> is that "IS" would compares NULLs as equals. There would,
> of course, need to be a corresponding "IS NOT" operator.
>
> You can already us the IS operator with a right-hand side
> of NULL. For example: "x IS NULL" or "x IS NOT NULL". What
> I am proposing is to expand IS so that the right-hand side
> can be an arbitrary expression. Like this: "x IS 5" or
> "x IS NOT y".
>
> The motivation for this change is so that one can compile
> statements that use "?" as the right-hand side of IS and
> then insert NULL or a value as appropriate.
>
> Thoughts?
Hi Richard,
Your proposed IS operator sounds ok, but it will be a non-standard extension
to SQL supported only by SQLite.
The case you mentioned as a motivation can be handled using the following
expression.
case
when :param is null then x is null
else x = :param
end
This expression can be used anywhere an expression using your proposed IS
operator can. Note, this expresion is simplified by the use of named
parameters. The user now only needs to bind a value or NULL to :param once.
To use either this expression or an expression using your IS operator, the
calling code must know if the value being bound to the parameter is NULL or
not. It must then use either, one of the sqlite3_bind_* API calls, or the
sqlite3_bind_null API call to set the value of the parameter. I suspect that
it is often just as simple to use one of two different SQL queries to handle
these cases.
I suspect that this IS operator would be more useful when used to compare
column values that already exist in tables, such as when joining tables.
This is where it really breaks with standard SQL. In standard SQL NULL means
the field has no value, so it can't be meaningfully compared to any other
value. Your proposed IS operator would treat NULL as an additional value in
each range (i.e. integers would have all possible numeric values plus NULL).
Again, all these case can be handled with standard expresions combining the
existing operators. For example:
x is 2 => x = 2
x is not 2 => x != 2
or x is null
t1.x is t2.x => t1.x = t2.x
or (t1.x is null and t2.x is null)
t1.x is not t2.x => t1.x != t2.x
or (t1.x is null and t2.x is not null)
or (t1.x is not null and t2.x is null)
The only benefit I can see to adding a new IS operator is that it could
perform the checks above (because that is really what it would be doing) in
a more effiecient manner. I suspect that it would be implemented with a new
VDBE opcode (or two) that would perform all these checks at once, where as
the equivalent expressions will compile into many VDBE opcodes (though the
code produced is not as bad as the length of the expressions make it look).
I question whether users really need or want to perform queries or joins
where null values are matched along with non-null values. If they do, this
would be a good way to do it.
At the end of the day I don't see it as being a very expensive extension to
the SQL supported by SQLite, but it should be clearly documented as such.
You should also document these equivalent standard expressions so users who
want to avoid SQLite specific extensions can easily get the same effect with
standard compatible SQL.
Just my two cents.
Dennis Cote
