Details
-
Bug
-
Status: Open
-
Major
-
Resolution: Unresolved
-
5.0.0
-
None
-
None
Description
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.
The problem occurs in AbstractQueryRewriter line 216. Unfortunately, at this stage in the query rewriting process, the input parameters for the SQL query might not be defined yet. The query could be compiled before the parameters are created. My guess is that the only proper solution is to create some new operator types:
OperatorType.IS_NULL
OperatorType.IS_NOT_NULL
For now, the only workaround is to avoid using parameterized types with queries that are checking for NULL values.