Generate table partitions failures for boolean types

[nj1973]

Describe the bug
Our test table for generate-table-partitions contains a boolean column. For many situations this fails to validate. Mostly I think when one engine supports booleans and the other doesn't. For example SQL Server and bit or Oracle and use of a string or numeric type.

Example Oracle vs PostgreSQL:

$ data-validation generate-table-partitions -sc=ora -tc=pg \
-tbls=pso_data_validator.test_generate_partitions_v2 \
--primary-keys="recd_timestamp,registration_date,approved" \
--concat='*'  --partition-num=2 -cdir=/tmp
Traceback (most recent call last):
...
    raise TypeError(
TypeError: Arguments approved:!boolean and Literal(N):string are not comparable

BigQuery vs Sybase (and SQL Server):

$ data-validation generate-table-partitions -sc=bq -tc=syb \
-tbls=pso_data_validator.test_generate_partitions_v2 \
--primary-keys="recd_timestamp,registration_date,approved" \
--concat='*'  --partition-num=2 -cdir=/tmp
Traceback (most recent call last):
...
Error '(pyodbc.ProgrammingError) ('42S22', "[42S22] [FreeTDS][SQL Server]Invalid column name 'false'.\n (207) (SQLExecDirectW)")

What version of DVT are you using?
8.2.0

What type of connections are you using for source and target?
Several different engine pairings.

Additional context
It is extremely unlikely a boolean will ever be in a primary key so this can be low priority to resolve.

[iamluan]

Hi, I noticed that the second example command for the "BigQuery vs Sybase (and SQL Server)" case is the same as the first example above. Should the source (-sc) and target (-tc) connections be different in the second one?
Thanks!

[nj1973]

Hi, I noticed that the second example command for the "BigQuery vs Sybase (and SQL Server)" case is the same as the first example above. Should the source (-sc) and target (-tc) connections be different in the second one? Thanks!

Great spot, thanks for pointing it out. 👍

[iamluan]

After investigation, I found that this error originates from data_validation/partition_builder.py, specifically from the following code inside the less_than_value() and geq_value() functions:

if len(keys) == 1:
    return key_column < ibis.literal(value)
else:
    return (key_column < ibis.literal(value)) | (
        (key_column == ibis.literal(value))
        & less_than_value(table, keys[1:], values[1:])
    )

Bug cause

In these cases, key_column and ibis.literal(value) end up having incompatible types because Oracle represents boolean columns in a way that Ibis reads as strings.
Additionally, the type of value always comes from the source side (variable first_elements), while type of key_column belongs to the target table when calling less_than_value() and geq_value() for taget source.

• sc=ora, tc=pg:
  • The value is a string-like value (Oracle: 'Y' / 'N').
  • The key_column is an Ibis BooleanColumn (Postgres).
• sc=pg, tc=ora:
  • The value is boolean (Postgres).
  • The key_column is an Ibis StringColumn (Oracle).

Proposed fix

A possible fix could be to cast either key_column or value to boolean types before the comparison in less_than_value() and geq_value(). If one side is boolean while the other is not, the non-boolean side can be cast to boolean:

is_bool_str = (
    lambda key_column, value: (
        # if key_colmn and value are not the same type, and one of them is boolean type
        key_column.type().is_boolean() is not ibis.literal(value).type().is_boolean()
    )
)
def cast_to_bool(key_column, value):
    oracle_true_str_list = ['t', 'y', '1', 1]
    if not key_column.type().is_boolean():
        key_column = key_column.cast(dt.Boolean)   
    else:
        value = True if value.lower() in oracle_true_str_list else False
    return key_column, value

if is_bool_str(key_column, value):
    key_column, value = cast_to_bool(key_column, value)

Testing

I tested both scenarios: sc=ora, tc=pg and sc=pg, tc=ora. Below are the generated filter statements for the first case (Oracle - PostgreSQL).

First partition filters (0000.yaml):

• source (ora):

recd_timestamp < TO_DATE('2023-08-27 15:00:00', 'YYYY-MM-DD HH24:MI:SS')
OR recd_timestamp = TO_DATE('2023-08-27 15:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND (
    registration_date < TO_DATE('1969-07-20', 'YYYY-MM-DD') 
    OR registration_date = TO_DATE('1969-07-20', 'YYYY-MM-DD') 
    AND approved < 'N'
)

• target (pg):

recd_timestamp < '2023-08-27 15:00:00' OR recd_timestamp = '2023-08-27 15:00:00' 
AND (
    registration_date < '1969-07-20' OR registration_date = '1969-07-20'
    AND approved < false
)

Second partition filters (0001.yaml):

• source (ora):

recd_timestamp > TO_DATE('2023-08-27 15:00:00', 'YYYY-MM-DD HH24:MI:SS')
OR recd_timestamp = TO_DATE('2023-08-27 15:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND (
    registration_date > TO_DATE('1969-07-20', 'YYYY-MM-DD') 
    OR registration_date = TO_DATE('1969-07-20', 'YYYY-MM-DD') 
    AND approved >= 'N'
)

• target (pg):

recd_timestamp > '2023-08-27 15:00:00' OR recd_timestamp = '2023-08-27 15:00:00' 
AND (
    registration_date > '1969-07-20' OR registration_date = '1969-07-20'
    AND approved >= false
)

I used these filters to query both the source and target databases, and each query returned 16 rows, indicating that the partitions are divided equally.

For BigQuery and MSSQL (docker: mcr.microsoft.com/mssql/server 2025-latest), I ran your second command and it executed successfully.

I’m happy to open a PR if this approach makes sense.