I'm using SQuirreL SQL Client Version 3.9.1. Here's how to reproduce the bug:
Starting out with a new, empty HSQLDB database (the HSQLDB version is 2.2.9, but I don't think this is relevant), I create a sample table with some data:
CREATE CACHED TABLE "Some data" ("Item" VARCHAR(100) PRIMARY KEY NOT NULL, "Property" VARCHAR(100))
INSERT INTO "Some data" VALUES ('First item', 'Property of first item')
I can modify the records in this table via the GUI seemingly without a problem (after making the table editable). However, when I try to change the value in the column "Property" from 'Property of first item' to NULL, I get the following error:
Unknown error during check on DB. Update is probably not safe.
Do you wish to proceed?
Nevertheless, the following statement runs just fine:
UPDATE "Some data" SET "Property" = NULL WHERE "Item" = 'First item'
Also, after running the aforementioned statement, when I try to change the NULL value back to something else via the GUI, I get the following error:
Exception seen during check on DB. Exception was:
user lacks privilege or object not found: PROPERTY
Update is probably not safe to do.
Do you wish to proceed?
Again, the following statement runs without a problem:
UPDATE "Some data" SET "Property" = 'Property of first item' WHERE "Item" = 'First item'
The fact that the column name "Property" is written in uppercase letters in the second error message leads me to suspect that SQuirreL is somehow passing the column name as a regular identifier instead of a delimited identifier, i.e. without double quotes. According to the HSQLDB guide, letters in regular identifiers are translated to their uppercase version. So SQuirreL looks for a column named "PROPERTY", but doesn't find it, because the name of the column is "Property".
For some reason, this only seems to be a problem when a NULL value is involved.
In constructor of class "net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause.ParameterWhereClausePart":
:
setWhereClause(getColumn() + " = ?");
:
Where getColumn() is inherited from class "net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause.AbstractWhereClausePart"
and just returns the internal "private String column".
In constructor of class "net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.whereClause.IsNullWhereClausePart":
:
super(columnDef, valueIsNull(columnDef));
:
Which results in the WHERE-clause to be constructed by using the column name as returned by ColumnDisplayDefinition.getColumnName().
Hope that helps.
Fixed in our GIT repository. Will be available in future snapshots and versions.