UNION of NULLs with several tables fails

[monetdb-team]

Date: 2017-12-08 11:43:10 +0100
From: @hannesmuehleisen
To: SQL devs <>
Version: 11.27.9 (Jul2017-SP2)
CC: @njnes

Last updated: 2018-02-12 16:12:04 +0100

Comment 25968

Date: 2017-12-08 11:43:10 +0100
From: @hannesmuehleisen

User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/63.0.3239.84 Safari/537.36
Build Identifier:

CREATE TABLE g AS SELECT NULL AS j UNION ALL SELECT NULL AS j UNION ALL SELECT 'asdf' AS j WITH DATA;

worked fine with previous releases, fails now:
types char(4,0) and smallint(16,0) are not equal for column 'j'

Reproducible: Always

Actual Results:

This should work fine as it did before.

Comment 25969

Date: 2017-12-08 11:43:58 +0100
From: @hannesmuehleisen

Clarification: Fails with SP3 Release Candidate branch

Comment 25970

Date: 2017-12-08 14:39:42 +0100
From: @hannesmuehleisen

IMHO this is due to the first UNION of NULLs creating a smallint type (sql_semantic.c, function supertype). Then, under strict coercion rules, strings cannot be UNIONed with this.

Comment 25975

Date: 2017-12-08 16:54:23 +0100
From: @sjoerdmullender

Jul2017-SP1: works, Jul2017-SP2: fails.

Comment 25976

Date: 2017-12-08 17:02:14 +0100
From: @sjoerdmullender

Bisect says, the culprit is changeset b7681c154e83 "reimplemented IN/NOT IN. Fixed bug #6411".

Comment 26019

Date: 2017-12-16 18:13:00 +0100
From: MonetDB Mercurial Repository <>

Changeset bc4e4730f58d made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=bc4e4730f58d

Changeset description:

fixed bug #6487, ie handle union all null properly