SWI-Prolog -- CQL: Removing null comparisions

Documentation
- Reference manual
- Packages
  - Constraint Query Language A high level interface to SQL databases
    - library(cql/cql): CQL - Constraint Query Language
      - CQL Examples
        
        CQL Simple INSERT
        
        CQL Simple INSERT with retrieval of identity of the inserted
        
        CQL Simple DELETE
        
        CQL Simple SELECT
        
        CQL Simple UPDATE
        
        CQL WHERE with arithmetic comparison
        
        CQL Simple INNER JOIN
        
        CQL Arithmetic UPDATE with an INNER JOIN and a WHERE restriction
        
        CQL: Confirm row does not exist
        
        CQL: Aggregation - Count
        
        CQL: Aggregation - Sum
        
        CQL: Aggregation - Average
        
        CQL: Maximum Value
        
        CQL: Minimum Value
        
        CQL: Aggregation requiring GROUP BY
        
        CQL: INNER JOIN with an aggregation sub-query where the sub-query is constrained by a shared variable from the main query
        
        CQL: INNER JOIN in an aggregation sub-query
        
        CQL: Negation
        
        CQL: EXISTS
        
        CQL: Left Outer Join
        
        CQL: List-based Restrictions
        
        CQL: Compile time in-list constraint
        
        CQL: Disjunction resulting in OR in WHERE clause
        
        CQL: Disjunction resulting in different joins (implemented as a SQL UNION)
        
        CQL: Disjunction resulting in different SELECT attributes (implemented as separate ODBC queries)
        
        CQL: ORDER BY
        
        CQL: DISTINCT
        
        CQL: SELECT with NOT NULL restriction
        
        CQL: First N
        
        CQL: Self JOIN
        
        CQL: Removing null comparisions
        
        CQL: Three table JOIN
        
        CQL: Three table JOIN with NOLOCK locking hint
        
        CQL: SELECT with LIKE
        
        CQL: Writing exceptions directly to the database
        
        CQL: TOP N is Parametric
        
        CQL: Using compile_time_goal/1
        
        CQL: ON
        
        CQL: Expressions In Where Restrictions
        
        CQL: Explicitly avoid the "No WHERE restriction" message
        
        CQL: HAVING
        
        CQL: INSERT and UPDATE value in-line formatting
        
        CQL: Negations in WHERE Clauses
        
        CQL: Predicate-generated Attribute Values
        
        CQL: INSERT from SELECT

1.7.31 CQL: Removing null comparisions

Use the ignore_if_null wrapper in your CQL to’filter out' null input values. This is a useful extension for creating user-designed searches.

{[],
 se_lt_x :: [a-UserName,
             b-ignore_if_null(SearchKey),
             ...]}

At runtime, if SearchKey is bound to a value other than {null} then the query will contain WHERE ... b = ?. If, however, SearchKey is bound to {null}, then this comparison will be omitted.

Disjunctions

In general, don't use ignore_if_null in disjunctions. Consider this query:

SearchKey = '%ELSTON%',
{[],
 se_lt_x :: [a-UserName,
             b-RealName],
 ( RealName =~ SearchKey
 ; UserName =~ SearchKey)}

The query means "find a user where the UserName contains ELSTON OR the RealName contain ELSTON". If !SearchKey is {null} then RealName=~ {null} will fail, which is correct. If ignore_if_null was used, the test would succeed, which means the disjunction would always succeed i.e. the query would contain no restriction, which is clearly not the intended result. FIXME: Mike, what is this all about?