metamodel-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kasper Sørensen <i.am.kasper.soren...@gmail.com>
Subject Re: Issue with NULL parameter values for compiled queries
Date Thu, 19 Apr 2018 23:42:50 GMT
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.Kingsbury@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
>
>
>
>
>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message