You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@metamodel.apache.org by Tim Kingsbury <Ti...@sas.com> on 2018/04/19 17:23:34 UTC
Issue with NULL parameter values for compiled queries
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
RE: Issue with NULL parameter values for compiled queries
Posted by Tim Kingsbury <Ti...@sas.com>.
>> 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:i.am.kasper.sorensen@gmail.com]
Sent: Thursday, April 19, 2018 7:43 PM
To: dev@metamodel.apache.org
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 <Ti...@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
>
>
>
>
>
>
Re: Issue with NULL parameter values for compiled queries
Posted by Kasper Sørensen <i....@gmail.com>.
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 <Ti...@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
>
>
>
>
>
>