>> We should report it in JIRA [1] - would you mind doing that? Done!
https://issues.apache.org/jira/browse/METAMODEL-1181 -----Original Message----- From: Kasper Sørensen [mailto:[email protected]] Sent: Thursday, April 19, 2018 7:43 PM To: [email protected] Subject: Re: Issue with NULL parameter values for compiled queries EXTERNAL 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 <[email protected]>: > 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 > > > > > >
