
Consider the test case below. It is unexpected that the third query return `1 'a'`, as the first query return `1 NULL`, and the second query also return `1 NULL`.

```sql
DROP TABLE t0;
DROP TABLE t1;

CREATE TABLE t0(c0 INT);
CREATE TABLE t1(c0 VARCHAR(500));
INSERT INTO t0 (c0) VALUES (1);
INSERT INTO t1 (c0) VALUES ('a');

SELECT * FROM t0 LEFT  JOIN t1 ON (NULL IN ('')); -- 1 NULL
SELECT * FROM t0 LEFT  JOIN t1 ON (NULL IN ('')) WHERE 1; -- 1 NULL
SELECT * FROM t0 LEFT  JOIN t1 ON (NULL IN ('')) WHERE 1 UNION ALL SELECT * FROM t0 LEFT  JOIN t1 ON (NULL IN ('')) WHERE (NOT 1);
-- Expected: 1 NULL
-- Actual: 1 'a'
```

Here's the version: Version 7.2.13-dev.3239-pthreads as of Mar 13 2024 (bb53b41)

Unexpected results when using `LEFT JOIN` and `UNION`
This is a GitHub Issue
repo:virtuoso-opensource
owner:openlink
Title : Unexpected results when using `LEFT JOIN` and `UNION`
Issue date:

--- start body ---

Consider the test case below. It is unexpected that the third query return `1 'a'`, as the first query return `1 NULL`, and the second query also return `1 NULL`.

```sql
DROP TABLE t0;
DROP TABLE t1;

CREATE TABLE t0(c0 INT);
CREATE TABLE t1(c0 VARCHAR(500));
INSERT INTO t0 (c0) VALUES (1);
INSERT INTO t1 (c0) VALUES ('a');

SELECT * FROM t0 LEFT  JOIN t1 ON (NULL IN ('')); -- 1 NULL
SELECT * FROM t0 LEFT  JOIN t1 ON (NULL IN ('')) WHERE 1; -- 1 NULL
SELECT * FROM t0 LEFT  JOIN t1 ON (NULL IN ('')) WHERE 1 UNION ALL SELECT * FROM t0 LEFT  JOIN t1 ON (NULL IN ('')) WHERE (NOT 1);
-- Expected: 1 NULL
-- Actual: 1 'a'
```

Here's the version: Version 7.2.13-dev.3239-pthreads as of Mar 13 2024 (bb53b41)

--- end body ---
