Hello,
I've run into an interesting problem with using compiled queries to search for
values that are not equal to null.
Our goal is to run a query like this: SELECT * FROM MyTable WHERE MyValue IS
NOT NULL.
If I build the query like this, everything works perfectly:
Query query = context.query().from(schema, "MyTable").select("*").toQuery();
SelectItem manufacturer = new SelectItem(schema.getTableByName("MyTable
").getColumnByName("MyValue"));
query.where(new FilterItem(manufacturer, OperatorType.DIFFERENT_FROM, null));
DataSet dataSet = context.executeQuery(query);
However, if I do the same thing with a compiled query, I get into trouble:
Query query2 = context.query().from(schema, "MyTable").select("*").toQuery();
query2.where(new FilterItem(manufacturer, OperatorType.DIFFERENT_FROM, new
QueryParameter()));
CompiledQuery compiled = context.compileQuery(query2);
Object[] params = new Object[1];
params[0] = null;
DataSet dataSet2 = context.executeQuery(compiled, params);
In this case, the generated SQL is:
SELECT . . . FROM "MySchema"."MyTable" WHERE ("MyTable"."MyValue" <> ? OR
"MyTable"."MyValue" IS NULL)
If the input parameter is a NULL, this where clause is not helpful. "<> NULL"
is likely to be invalid and "IS NULL" is the wrong thing. The goal is "IS NOT
NULL"
The generated query would make perfect sense if the input parameter was a value
like "foo", but this approach falls apart with null input parameters.
Any suggestions for a workaround would be greatly appreciated!
Tim Kingsbury
