Re: [Info-ingres] case statement in where clause

"Gareth Williams" <Gareth.Williams@xxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:mailman.1125496441.19571.info-ingres@xxxxxxxxxxxxxxxxxx
> Ingres SPARC SOLARIS Version II 2.6/0305 (su9.us5/00)

> Anyone ever tried putting a case statement into a where clause
> e.g.

Yes.

> s.date_of_arrival = case when w.start_date = ' ' then
> null else W.episode_start_date end
>
> This managed to crash our production server and we managed to recreate the
> error on our development server (same version).
>
> My questions are...
>
> Is this a valid clause?

The syntax might be, but the intention isn't. You can't test for equality
to null. Null is not a value it is the absence of value. SQL doesn't allow
WHERE col = NULL.

> If it is... then should I log it as a bug?

Anything that crashes a server is a bug.

> If it isn't a valid syntax... then is it a bug anyway as it should have
been
> stopped by the query syntax checking?

That's a good question, because it would mean there are two types of CASE
expression. Those that can evaluate to null and those that can't. Those
that can shouldn't be allowed on the right of a comparison operator. The
lookahead while parsing that would be way over the horizon...!

(Yep, SQL sucks alright. And so do nulls.)

Roy Hann (rhann at rationalcommerce dot com)
Rational Commerce Ltd.
www.rationalcommerce.com
"Ingres development, tuning, and training experts"



.

Relevant Pages

  • Re: CROSS JOIN
    ... > was that depreciating the original FROM .. ... WHERE syntax would never ... > The problem is that the WHERE clause is done after the FROM clause. ... but thats just outer join stuff. ...
    (comp.databases)
  • Re: Extending define with where clause
    ... > parallel to lambda*, which implements automatic handling of keyword ... "where" clause is used that the new syntax really matters. ... introduced with the help of let, letrec, letrec*, or named let -- are ...
    (comp.lang.scheme)
  • Re: Not Exists joining 2 tables
    ... "'code' is a bad name for a key column" is a valid complaint. ... EXISTS clause with a correlated subquery properly, ... SQL+ syntax and start using the ANSI SQL syntax that seems ... Then the only criteria in the where clause ...
    (comp.databases.ms-sqlserver)
  • Re: update query: still having problems
    ... Only the records from Department that have a rate value present in sheet1, ... That is how an INNER JOIN works when there is no duplicated values (in one ... you add an extra WHERE clause. ... Your code throws up a syntax error: ...
    (microsoft.public.access.queries)
  • Re: Statement parameter in Mailmerge.OpenDataSource
    ... You can certainly pass an SQL SELECT statement with a WHERE clause. ... the syntax right can be difficult, ... You may be able to find out more about that in SQL Server Books Online. ... > What i'm trying to do is, passing a statement calling a table-valued ...
    (microsoft.public.word.mailmerge.fields)