Bug: DBI queries for NULL #340

New issue

Closed

opened 2025-06-20 10:28:46 +00:00 by jamie · 4 comments

jamie commented

2025-06-20 10:28:46 +00:00

(Migrated from git.hazaar.io)

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)

## 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 - [x] 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)

jamie commented

2025-06-20 10:28:47 +00:00

(Migrated from git.hazaar.io)

assigned to @jamie