Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Can't copy table into another table with constraints (mkey.bulk_rotate_xor_hash) #6718

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

Date: 2019-06-25 14:17:48 +0200
From: Henrique <>
To: SQL devs <>
Version: 11.33.3 (Apr2019)
CC: @kutsurak

Last updated: 2019-09-02 16:05:28 +0200

Comment 27081

Date: 2019-06-25 14:17:48 +0200
From: Henrique <>

User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/73.0.3683.103 Safari/537.36 OPR/60.0.3255.170
Build Identifier:

Hello, we use MonetDB in C3SL in some projects and now we are facing the following error:

Whenever we use the INSERT INTO table (...) SELECT table2 ... and table has constraints ( primary key / foreign key ) we get the error:

'mkey.bulk_rotate_xor_hash' undefined in: X_44:bat[:lng] := mkey.bulk_rotate_xor_hash(X_38:bat[:lng], X_43:bat[:int], X_37:bat[:int]);

What bugs us at C3SL, is that the previous version did not have this bug, so to test it we developed a simple set of commands that reproduce this error:

CREATE TABLE test2 (
order_number SERIAL,
order_id INTEGER NOT NULL,
qnt INTEGER NOT NULL
);

COPY 5 RECORDS INTO test2 FROM stdin USING DELIMITERS '|','\n';
1|32|57
2|15|105
3|33|0
4|57|20
5|67|134
;

CREATE TABLE test1 (
id INTEGER NOT NULL,
qnt INTEGER,
order_number INTEGER NOT NULL,
CONSTRAINT pk_test1_id PRIMARY KEY ("id", "qnt"),
CONSTRAINT pk_order_number FOREIGN KEY ("order_number") REFERENCES
test2("order_number")
);

INSERT INTO test1 (id, qnt, order_number) SELECT test2.order_id,
test2.qnt, test2.order_number from test2;

Reproducible: Always

Steps to Reproduce:

  1. Create a Table:
    CREATE TABLE test2 (
    order_number SERIAL,
    order_id INTEGER NOT NULL,
    qnt INTEGER NOT NULL
    );

  2. Insert data into this table:
    COPY 5 RECORDS INTO test2 FROM stdin USING DELIMITERS '|','\n';
    1|32|57
    2|15|105
    3|33|0
    4|57|20
    5|67|134
    ;

  3. Create another table:
    CREATE TABLE test1 (
    id INTEGER NOT NULL,
    qnt INTEGER,
    order_number INTEGER NOT NULL,
    CONSTRAINT pk_test1_id PRIMARY KEY ("id", "qnt"),
    CONSTRAINT pk_order_number FOREIGN KEY ("order_number") REFERENCES
    test2("order_number")
    );

  4. Copy data from the first table into the second table.
    INSERT INTO test1 (id, qnt, order_number) SELECT test2.order_id,
    test2.qnt, test2.order_number from test2;

Actual Results:

The error message that we receive is:

'mkey.bulk_rotate_xor_hash' undefined in: X_44:bat[:lng] := mkey.bulk_rotate_xor_hash(X_38:bat[:lng], X_43:bat[:int], X_37:bat[:int]);

Expected Results:

We expected that the table is copied into another table:

"5 affected rows"

$ mserver5 --version
MonetDB 5 server 11.33.3 (Apr2019) (64-bit, 128-bit integers)
Copyright (c) 1993 - July 2008 CWI
Copyright (c) August 2008 - 2019 MonetDB B.V., all rights reserved
Visit https://www.monetdb.org/ for further information
Found 15.3GiB available memory, 8 available cpu cores
Libraries:
libpcre: 8.39 2016-06-14
openssl: OpenSSL 1.1.1 11 Sep 2018 (compiled with OpenSSL 1.1.0g 2 Nov 2017)
libxml2: 2.9.4
Compiled by: pbuilder@dev.monetdb.org (x86_64-pc-linux-gnu)
Compilation: gcc -O3 -fomit-frame-pointer -pipe -fdebug-prefix-map=/build/monetdb-11.33.3=. -fstack-protector-strong -Wformat -Werror=format-security -g -D_FORTIFY_SOURCE=2
Linking : /usr/bin/x86_64-linux-gnu-ld -m elf_x86_64 -Wl,-Bsymbolic-functions -Wl,-z,relro -Wl,-Bsymbolic-functions

$ monetdb --version
MonetDB Database Server Toolkit v11.33.3 (Apr2019)

$ monetdbd --version
MonetDB Database Server v11.33.3 (Apr2019)

$ mclient --version
mclient, the MonetDB interactive terminal, version 11.33.3 (Apr2019)
support for command-line editing compiled-in
character encoding: UTF-8

Comment 27087

Date: 2019-06-26 12:41:13 +0200
From: MonetDB Mercurial Repository <>

Changeset fe7e23e93f37 made by Martin van Dinther martin.van.dinther@monetdbsolutions.com in the MonetDB repo, refers to this bug.

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

Changeset description:

Add test for bug #6718

Comment 27100

Date: 2019-07-02 16:12:34 +0200
From: MonetDB Mercurial Repository <>

Changeset dcc4eda85efa 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=dcc4eda85efa

Changeset description:

fixed bug #6718, ie don't split constant expression out of parameter list.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant