Uploaded image for project: 'Apache MetaModel'
  1. Apache MetaModel
  2. METAMODEL-1181

Problem with compiled queries to search for values that are not equal to null

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 5.0.0
    • Fix Version/s: None
    • Labels:
      None

      Description

      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.

       

       

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              tkingsbury Tim W Kingsbury
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: