Very good finding. I would say that this is probably a bug. We should report it in JIRA [1] - would you mind doing that?
As for a workaround - I can only think of the obvious one: "don't use compiled query when your argument is NULL". Thanks, Kasper [1] https://issues.apache.org/jira/browse/METAMODEL 2018-04-19 10:23 GMT-07:00 Tim Kingsbury <tim.kingsb...@sas.com>: > 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 > > > > > >
