Is NULL not allowed is SQL delete statement (for Derby)?

Does anyone know why the following query fails in Derby?

delete from MyTable
where
((null = null) or (col1 = null)) OR
((102 = null) or (col2 = 102)))

I get the following error:

Error: Syntax error: Encountered "null" at line 3, column 3.
SQLState:  42X01
ErrorCode: -1

The sql i开发者_JS百科s being generated in a java program based on SQL I've written into an iBatis ORM layer config. The prepared statement is as follows:

delete from MyTable
where
((? = null) or (col1 = ?)) OR
((? = null) or (col2 = ?)))

As you can see what I'm trying to say is that if the parameter is not null then test the column value against the parameter

I believe what you want is:

DELETE FROM `name_of_table` WHERE `name_of_column` IS NULL

SQL has this really weird property where you can use =value with most values, except NULL.

In SQL, any operator where one of the arguments is NULL evaluates to NULL. So 1 + NULL is NULL, MAX(NULL) is NULL, and so on. Most importantly, in your case, X = NULL always evaluates to NULL, even if X is NULL itself (that is, NULL = NULL evaluates to NULL not true).

The exact error you get is that the keyword NULL is not actually valid on the left-hand side of a comparison. But even if you fixed that, you'd still find that none of your rows ever match. As others have said, the correct comparison to use when trying to determine whether something is null is IS NULL, as in X IS NULL.

The first part of the where clause suggests it should match every row (if you expect null = null to be true, which it generally wouldn't be in SQL) - do you really want to delete the whole table's contents? Likewise 102 = null is a pretty bizarre kind of comparison, unless you've somehow got a column called 102 (which I'd at least hope is prohibited).

What are you trying to actually match? As others have said, you should use "is null" to compare a value against null - but it sounds like your query has rather bigger problems. If you could tell us what you're trying to achieve, we may be able to help you write the query better.

Hmm, you seem not to have gotten a good answer yet? I have a similar problem with Derby's handling of NULL values:

Derby's handling of NULL values

The fact is that Derby needs a type associated with NULL. What you would have to do if you wrote the SQL yourself is to cast(null to int) or something like that. Unfortunately, as you mentioned it, the SQL is generated by iBATIS, so maybe an upgrade might work? Did iBATIS create a fix for this, in the mean time?

Otherwise, this may help you:

http://anydoby.com/jblog/en/java/143