Date: 2017-11-11 23:50:06 +0100
From: @drstmane
To: SQL devs <>
Version: -- development
CC: @njnes

Last updated: 2017-11-26 18:32:46 +0100

Comment 25865

Date: 2017-11-11 23:50:06 +0100
From: @drstmane

When loading data via COPY LOADER INFO using a PYTHON LOADER function, but omitting columns in the emit() statements, the implementation of the emit() function replaces these columns by NULL values, rather than skipping these columns using the columns' default values --- I'm not sure which part of the code does / is supposed to take care of using default values for omitted columns --- thus effectively overruling the default, incl. triggering unexpected errors in case an omitted columns has a default other than NULL and is (explicitly or implicitly) declared NOT NULL.

As an example, for this code:

CREATE LOADER myloader(x integer, y string) LANGUAGE PYTHON {
z={}
i=0
for j in ('a','b','c','d','e','f','g'):
i += 1
if j in y:
z[j] = x - i
_emit.emit(z)
};
create table t (
a integer auto_increment primary key,
b integer generated always as identity (start with 2) unique,
c integer generated always as identity (start with 3) not null,
d integer generated always as identity (start with 4),
e integer default 5 not null,
f integer default 6,
g integer
);
COPY LOADER INTO t FROM myloader(-10,'abcdefg');
COPY LOADER INTO t FROM myloader(-20,'abcdef');
COPY LOADER INTO t FROM myloader(-30,'abcdeg');
COPY LOADER INTO t FROM myloader(-40,'abcdfg');
COPY LOADER INTO t FROM myloader(-50,'abcefg');
COPY LOADER INTO t FROM myloader(-60,'abdefg');
COPY LOADER INTO t FROM myloader(-70,'acdefg');
COPY LOADER INTO t FROM myloader(-80,'bcdefg');
select * from t;
drop table t;
drop loader myloader;

We get this results:

operation successful
operation successful
1 affected row
1 affected row
1 affected row
INSERT INTO: NOT NULL constraint violated for column t.e
1 affected row
INSERT INTO: NOT NULL constraint violated for column t.c
1 affected row
INSERT INTO: NOT NULL constraint violated for column t.a
+------+------+------+------+------+------+------+
| a | b | c | d | e | f | g |
+======+======+======+======+======+======+======+
| -11 | -12 | -13 | -14 | -15 | -16 | -17 |
| -21 | -22 | -23 | -24 | -25 | -26 | null |
| -31 | -32 | -33 | -34 | -35 | null | -37 |
| -51 | -52 | -53 | null | -55 | -56 | -57 |
| -71 | null | -73 | -74 | -75 | -76 | -77 |
+------+------+------+------+------+------+------+
5 tuples
operation successful
operation successful

While we would expect this results:

operation successful
1 affected row
1 affected row
1 affected row
1 affected row
1 affected row, last generated key: 4
1 affected row, last generated key: 3
1 affected row, last generated key: 2
1 affected row, last generated key: 1
+------+------+------+------+------+------+------+
| a | b | c | d | e | f | g |
+======+======+======+======+======+======+======+
| -11 | -12 | -13 | -14 | -15 | -16 | -17 |
| -21 | -22 | -23 | -24 | -25 | -26 | null |
| -31 | -32 | -33 | -34 | -35 | 6 | -37 |
| -41 | -42 | -43 | -44 | 5 | -46 | -47 |
| -51 | -52 | -53 | 4 | -55 | -56 | -57 |
| -61 | -62 | 3 | -64 | -65 | -66 | -67 |
| -71 | 2 | -73 | -74 | -75 | -76 | -77 |
| 1 | -82 | -83 | -84 | -85 | -86 | -87 |
+------+------+------+------+------+------+------+
8 tuples
operation successful

Comment 25866

Date: 2017-11-11 23:53:27 +0100
From: MonetDB Mercurial Repository <>

Changeset b6e8be70fea0 made by Stefan Manegold Stefan.Manegold@cwi.nl in the MonetDB repo, refers to this bug.

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

Changeset description:

added test for bug #6464

Comment 25911

Date: 2017-11-26 10:16:03 +0100
From: @njnes

the test has been approved with the correct result (as far as I can see).

Comment 25912

Date: 2017-11-26 13:02:52 +0100
From: MonetDB Mercurial Repository <>

Changeset 0f5827e686f3 made by Stefan Manegold Stefan.Manegold@cwi.nl in the MonetDB repo, refers to this bug.

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

Changeset description:

test for Bug-6464: undo output approval of changeset [b3bc5cb7c32e](https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=b3bc5cb7c32e)  on default branch, only;

while the behaviour in the Jul2017 release branch will not change,
i.e., the Python loader does not support default values,
I consider bug #6464 (still/now) a feature request for the default branch
and have the (failing) test (with "desired" output) remind us of it.

Comment 25913

Date: 2017-11-26 13:05:41 +0100
From: @drstmane

While the bug is "fixed" in the Jul2017 branch by accepting that the Python loader does not support default values, and making it throw an error message,
I consider this now a feature request for the default branch and have the (failing) test (with "desired" output) remind us of it.

Comment 25915

Date: 2017-11-26 18:32:46 +0100
From: MonetDB Mercurial Repository <>

Changeset dc98e14ee24a made by Stefan Manegold Stefan.Manegold@cwi.nl in the MonetDB repo, refers to this bug.

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

Changeset description:

test for Bug-6464: undo output approval of changeset [b3bc5cb7c32e](https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=b3bc5cb7c32e)  on default branch, only;

while the behaviour in the Jul2017 release branch will not change,
i.e., the Python loader does not support default values,
I consider bug #6464 (still/now) a feature request for the default branch
and have the (failing) test (with "desired" output) remind us of it.