[jira] [Commented] (METAMODEL-1181) Problem with compiled queries to search for values that are not equal to null

["Kasper Sørensen (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/METAMODEL-1181?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16459429#comment-16459429 ] 

Kasper Sørensen commented on METAMODEL-1181:
--------------------------------------------

I don't think that concern makes sense, because if the query has to change anyway, you cannot reuse the statement. So you need to make a different statement for the null case here.

My solution would roughly translate into this patch to JdbcDataContext.java:
 
{code:java}
public DataSet executeQuery(CompiledQuery compiledQuery, Object... values) {
  if ( [one or more of values is null] ) {
    Query query = compiledQuery.cloneWithParameterValues(values);
    return executeQuery(query);
  }
  // or else just continue as already implemented
}{code}
 
Where the "cloneWithParameterValues" method can be lifted/reused from DefaultCompiledQuery.java.

> Problem with compiled queries to search for values that are not equal to null
> -----------------------------------------------------------------------------
>
>                 Key: METAMODEL-1181
>                 URL: https://issues.apache.org/jira/browse/METAMODEL-1181
>             Project: Apache MetaModel
>          Issue Type: Bug
>    Affects Versions: 5.0.0
>            Reporter: Tim W Kingsbury
>            Priority: Major
>
> 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.
>  
>  



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)