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 USING syntax.
  • One of the join sides is a view or derived table.
  • One of the USING columns 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

  1. Cast the NULL explicitly to an appropriate datatype:
  2. Join with ON instead of USING. 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.