bad BAT properties with binary copy into and NULL values #3937

monetdb-team · 2020-11-30T13:23:45Z

Date: 2016-03-01 15:42:38 +0100
From: @drstmane
To: SQL devs <>
Version: 11.21.13 (Jul2015-SP2)

Last updated: 2016-03-25 09:59:21 +0100

Comment 21844

Date: 2016-03-01 15:42:38 +0100
From: @drstmane

Created attachment 386
small C file to create binary (BAT) files with & without NULL value

When a binary file contains "valid" (MonetDB-) NULL values,
binary copy into does not set BAT properties correctly.

To reproduce, download the (to-be) attached scripts and run the following BASH commands sequence (or equivalent according to your preferences):

WARNING:
this one-line magic kills all mserver5 processes (because this magic does not seem to work without mserver5's --daemon=yes option --- it used to in the (very) old days ...)

gcc makeBats.c -o makeBats && ./makeBats && { until mclient -s'select 0;' >/dev/null 2>&1 ; do sleep 1 ; done ; sed "s|PWD|$PWD|g" noNULLint.sql | mclient -e -i ; sed "s|PWD|$PWD|g" NULLint.sql | mclient -e -i ; killall mserver5 ; } >&2 | mserver5 --debug=10 --dbpath=$PWD/MyDB --daemon=yes

Note the property assertions and the incorrect result for NULL-related queries at the end.

builtin opt gdk_debug = 0
builtin opt gdk_vmtrim = no
builtin opt monet_prompt = >
builtin opt monet_daemon = no
builtin opt mapi_port = 50000
builtin opt mapi_open = false
builtin opt mapi_autosense = false
builtin opt sql_optimizer = default_pipe
builtin opt sql_debug = 0
cmdline opt gdk_dbpath = /ufs/manegold//HDD/IBA/MyDB
cmdline opt monet_daemon = yes
cmdline opt gdk_debug = 10
MonetDB 5 server v11.21.14
This is an unreleased version
Serving database 'MyDB', using 8 threads
Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs and 128bit integers dynamically linked
Found 15.589 GiB available main-memory.
Copyright (c) 1993-July 2008 CWI.
Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved
Visit http://www.monetdb.org/ for further information
Listening for connection requests on mapi:monetdb://127.0.0.1:50000/
MonetDB/GIS module loaded
Start processing logs sql/sql_logs version 52200
Start reading the write-ahead log 'sql_logs/sql/log.4'
Finished reading the write-ahead log 'sql_logs/sql/log.4'
Finished processing logs sql/sql_logs
MonetDB/SQL module loaded
drop table "noNULLint";
operation successful (20.696ms)
create table "noNULLint" (x int);
operation successful (16.338ms)
copy binary into "noNULLint" from ('/ufs/manegold//HDD/IBA/noNULLintBAT');
3 affected rows (7.888ms)
select * from "noNULLint";
+------+
| x |
+======+
| 1 |
| 2 |
| 3 |
+------+
3 tuples (2.194ms)
select count(), count(x) from "noNULLint";
+------+------+
| L1 | L2 |
+======+======+
| 3 | 3 |
+------+------+
1 tuple (2.024ms)
select * from "noNULLint" where x is null;
+---+
| x |
+===+
+---+
0 tuples (0.934ms)
select * from "noNULLint" where x is not null;
+------+
| x |
+======+
| 1 |
| 2 |
| 3 |
+------+
3 tuples (0.873ms)
insert into "noNULLint" values (null);
1 affected row (9.805ms)
select * from "noNULLint";
+------+
| x |
+======+
| 1 |
| 2 |
| 3 |
| null |
+------+
4 tuples (0.507ms)
select count(), count(x) from "noNULLint";
+------+------+
| L1 | L2 |
+======+======+
| 4 | 3 |
+------+------+
1 tuple (0.429ms)
select * from "noNULLint" where x is null;
+------+
| x |
+======+
| null |
+------+
1 tuple (0.422ms)
select * from "noNULLint" where x is not null;
+------+
| x |
+======+
| 1 |
| 2 |
| 3 |
+------+
3 tuples (0.459ms)
drop table "NULLint";
operation successful (7.093ms)
create table "NULLint" (x int);
operation successful (16.508ms)
!WARNING: /ufs/manegold//Monet/HG/Jul2015/source/MonetDB/gdk/gdk_bat.c:3025: assertion !b->H->nonil || cmp != 0' failed !WARNING: /ufs/manegold/_/Monet/HG/Jul2015/source/MonetDB/gdk/gdk_bat.c:3025: assertion !b->H->nonil || cmp != 0' failed
copy binary into "NULLint" from ('/ufs/manegold//HDD/IBA/NULLintBAT');
3 affected rows (8.221ms)
!WARNING: /ufs/manegold//Monet/HG/Jul2015/source/MonetDB/gdk/gdk_bat.c:3025: assertion !b->H->nonil || cmp != 0' failed !WARNING: /ufs/manegold/_/Monet/HG/Jul2015/source/MonetDB/gdk/gdk_bat.c:3025: assertion !b->H->nonil || cmp != 0' failed
!WARNING: /ufs/manegold//Monet/HG/Jul2015/source/MonetDB/gdk/gdk_bat.c:3025: assertion !b->H->nonil || cmp != 0' failed !WARNING: /ufs/manegold/_/Monet/HG/Jul2015/source/MonetDB/gdk/gdk_bat.c:3025: assertion !b->H->nonil || cmp != 0' failed
!WARNING: /ufs/manegold//Monet/HG/Jul2015/source/MonetDB/gdk/gdk_bat.c:3025: assertion !b->H->nonil || cmp != 0' failed select * from "NULLint"; +------+ | x | +======+ | 1 | | null | | 3 | +------+ 3 tuples (0.982ms) !WARNING: /ufs/manegold/_/Monet/HG/Jul2015/source/MonetDB/gdk/gdk_bat.c:3025: assertion !b->H->nonil || cmp != 0' failed
!WARNING: /ufs/manegold//Monet/HG/Jul2015/source/MonetDB/gdk/gdk_bat.c:3025: assertion !b->H->nonil || cmp != 0' failed !WARNING: /ufs/manegold/_/Monet/HG/Jul2015/source/MonetDB/gdk/gdk_bat.c:3025: assertion !b->H->nonil || cmp != 0' failed
!WARNING: /ufs/manegold//Monet/HG/Jul2015/source/MonetDB/gdk/gdk_bat.c:3025: assertion !b->H->nonil || cmp != 0' failed !WARNING: /ufs/manegold/_/Monet/HG/Jul2015/source/MonetDB/gdk/gdk_bat.c:3025: assertion !b->H->nonil || cmp != 0' failed
select count(*), count(x) from "NULLint";
+------+------+
| L1 | L2 |
+======+======+
| 3 | 3 |
+------+------+
1 tuple (0.894ms)
!WARNING: /ufs/manegold//Monet/HG/Jul2015/source/MonetDB/gdk/gdk_bat.c:3025: assertion !b->H->nonil || cmp != 0' failed !WARNING: /ufs/manegold/_/Monet/HG/Jul2015/source/MonetDB/gdk/gdk_bat.c:3025: assertion !b->H->nonil || cmp != 0' failed
select * from "NULLint" where x is null;
+---+
| x |
+===+
+---+
0 tuples (1.888ms)
!WARNING: /ufs/manegold//Monet/HG/Jul2015/source/MonetDB/gdk/gdk_bat.c:3025: assertion !b->H->nonil || cmp != 0' failed !WARNING: /ufs/manegold/_/Monet/HG/Jul2015/source/MonetDB/gdk/gdk_bat.c:3025: assertion !b->H->nonil || cmp != 0' failed
!WARNING: /ufs/manegold//Monet/HG/Jul2015/source/MonetDB/gdk/gdk_bat.c:3025: assertion !b->H->nonil || cmp != 0' failed !WARNING: /ufs/manegold/_/Monet/HG/Jul2015/source/MonetDB/gdk/gdk_bat.c:3025: assertion !b->H->nonil || cmp != 0' failed
select * from "NULLint" where x is not null;
+------+
| x |
+======+
| 1 |
| null |
| 3 |
+------+
3 tuples (1.131ms)

Attached file: makeBats.c (text/x-csrc, 286 bytes)
Description: small C file to create binary (BAT) files with & without NULL value

Comment 21845

Date: 2016-03-01 15:43:33 +0100
From: @drstmane

Created attachment 387
working test for no-NULL case

Attached file: noNULLint.sql (text/plain, 481 bytes)
Description: working test for no-NULL case

Comment 21846

Date: 2016-03-01 15:43:54 +0100
From: @drstmane

Created attachment 388
failing test for NULL case

Attached file: NULLint.sql (text/plain, 263 bytes)
Description: failing test for NULL case

Comment 21848

Date: 2016-03-01 21:30:06 +0100
From: @drstmane

This might fix it:

diff -r 40afde5221b1 gdk/gdk_bat.c
--- a/gdk/gdk_bat.c Tue Mar 01 17:02:58 2016 +0100
+++ b/gdk/gdk_bat.c Tue Mar 01 21:29:09 2016 +0100
@@ -354,6 +354,11 @@ BATattach(int tt, const char *heapfile,
BATkey(bn, TRUE);
BATsetcapacity(bn, cap);
BATsetcount(bn, cap);

```
  /*
```

   * Unless/until we invest in a scan to check that there indeed

   * are no NIL values, we cannot safely assume there are none.

```
   */
```

  bn->T->nonil = 0;
  if (cap > 1) {
          bn->tsorted = 0;
          bn->trevsorted = 0;

testing and check-in pending ...

Comment 21851

Date: 2016-03-02 11:05:58 +0100
From: MonetDB Mercurial Repository <>

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

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=f38103527022

Changeset description:

fixing bug #3937: unset "nonil" property with copy binary into:

Unless/until we invest in a scan to check that there indeed
are no NIL values, we cannot safely assume there are none.

Test still pending.

Comment 21854

Date: 2016-03-03 00:16:57 +0100
From: MonetDB Mercurial Repository <>

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

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=9e6293cee17a

Changeset description:

added test for bug #3937

Comment 21864

Date: 2016-03-03 21:36:22 +0100
From: MonetDB Mercurial Repository <>

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

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=e37a9003fa0d

Changeset description:

test for bug #3937: simply use /bin/sh for portability

Comment 21865

Date: 2016-03-03 21:36:26 +0100
From: MonetDB Mercurial Repository <>

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

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=f5d29e95b1a8

Changeset description:

test for bug #3937: approve Windows-specific output

Comment 21962

Date: 2016-03-25 09:59:21 +0100
From: @sjoerdmullender

Jul2015-SP3 has been released.

The text was updated successfully, but these errors were encountered:

monetdb-team added bug normal SQL labels Nov 30, 2020

monetdb-team closed this as completed Nov 30, 2020

sjoerdmullender added this to the Ancient Release milestone Feb 7, 2022

bad BAT properties with binary copy into and NULL values #3937

bad BAT properties with binary copy into and NULL values #3937

Comments

monetdb-team commented Nov 30, 2020

Comment 21844

Comment 21845

Comment 21846

Comment 21848

Comment 21851

Comment 21854

Comment 21864

Comment 21865

Comment 21962