Joining a view with a NULL literal with USING causes "Neither TRUE nor FALSE at string to Boolean cast" exception
Details
| Detail name | Value |
|---|---|
| Changelog Number | 29740 |
| Type | Bug |
| Status | Resolved |
| Affected Versions | Exasol 8.0.0 |
| Fix Versions | Exasol 2025.2.0 |
| Resolution Date | 2025-12-17 |
Description
If a query has the following conditions then it will give the error “data exception - Neither TRUE nor FALSE at string to boolean cast”:
- The query has a join with the
USINGsyntax. - One of the join sides is a view or derived table.
- One of the
USINGcolumns in the view or derived table is an untyped NULL literal. - The datatype of the corresponding column from the other join table does not matter.
Example
-- Expected: 0 rows returned -- Observed: Error "Neither TRUE nor FALSE at string to Boolean cast" with X0 as (select null as JOIN_COLUMN from DUAL) , X1 as (select 'qwe' as JOIN_COLUMN from DUAL) , X2 as (select * from X1 inner join X0 using (JOIN_COLUMN)) select * from X2;
Workaround
- Cast the NULL explicitly to an appropriate datatype:
- Join with
ONinstead ofUSING. You might also need to add column aliases, for example as below, to prevent an error like “identifier JOIN_COLUMN in query X2 is ambiguous”.
-- Expected/Observed: 0 rows returned with X0 as (select cast(null as varchar(10)) as JOIN_COLUMN from DUAL) , X1 as (select 'qwe' as JOIN_COLUMN from DUAL) , X2 as (select * from X1 inner join X0 using (JOIN_COLUMN)) select * from X2;
-- Expected/Observed: 0 rows returned with X0 as (select null as JOIN_COLUMN from DUAL) , X1 as (select 'qwe' as JOIN_COLUMN from DUAL) , X2(JOIN_COLUMN0, JOIN_COLUMN1) as (select * from X1 inner join X0 on X0.JOIN_COLUMN = X1.JOIN_COLUMN) select * from X2;
Fix
The query succeeds as expected.