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 Something isn't working 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

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

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

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

monetdb-team commented Nov 30, 2020

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