Bug: DBI queries for NULL

Problem Statement

The Hazaar DBI module currently mishandles search criteria that include null values. When a query is constructed with a condition like ['parent' => null], the query builder incorrectly generates parent = :parent0. This is not valid SQL in PostgreSQL (and other SQL dialects), which requires parent IS NULL for null comparisons.

Who will benefit?

• Developers using Hazaar DBI with PostgreSQL or other SQL engines that require explicit IS NULL handling.
• Applications that rely on querying fields that may contain null values.
• Anyone using the QueryBuilder abstraction for dynamic SQL generation.

Benefits and risks

Benefits:

• Fixes invalid SQL generation for null values.
• Improves compatibility with PostgreSQL and standards-compliant SQL databases.
• Prevents query failures and reduces debugging effort.

Risks:

• Could cause unintended behavior if existing workarounds depend on the current (incorrect) behavior.
• May require regression testing to ensure compatibility with various query conditions and drivers.

Proposed solution

Update the query builder logic in Hazaar DBI to:

• Detect when a search condition value is null.
• Generate the SQL clause using IS NULL instead of = :param for those cases.
• Ensure this applies to both WHERE and HAVING conditions as appropriate.

Priority/Severity

• High (This will bring a huge increase in performance/productivity/usability/legislative cover)
• Medium (This will bring a good increase in performance/productivity/usability)
• Low (anything else e.g., trivial, minor improvements)