We read every piece of feedback, and take your input very seriously.
To see all available qualifiers, see our documentation.
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
Date: 2013-12-12 18:52:00 +0100 From: @swingbit To: SQL devs <> Version: 11.15.19 (Feb2013-SP6) CC: @njnes
Last updated: 2014-02-20 15:02:34 +0100
Date: 2013-12-12 18:52:00 +0100 From: @swingbit
User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/31.0.1650.57 Safari/537.36 Build Identifier:
set optimizer='sequential_pipe'; START TRANSACTION;
CREATE TABLE t ( "a" INT, "b" INT );
-- The following 3 queries are EQUIVALENT
explain SELECT t2.a FROM t t1, t t2 WHERE t2.a between t1.a and t1.b;
explain SELECT t2.a FROM t t1, t t2 WHERE t2.a >= t1.a AND t2.a <= t1.b;
explain SELECT t2.a FROM t t1, t t2 WHERE t1.a <= t2.a AND t1.b >= t2.a;
The last query, however, does not produce a (range)join.
The effect, on large data, is terrible. This is somewhat related to bug #3259.
Here below the MAL plans for the three queries. The first two are fine. The LAST plan is correct but much slower than the other two.
sql>set optimizer='sequential_pipe'; operation successful (0.394ms) sql> sql>START TRANSACTION; auto commit mode: off sql> sql>CREATE TABLE t ( more>"a" INT, more>"b" INT more>); operation successful (0.847ms) sql> sql>explain more>SELECT t2.a more>FROM t t1, t t2 more>WHERE t2.a between t1.a and t1.b; +-----------------------------------------------------------+ | mal | +===========================================================+ | function user.s4_1():void; | | X_1 := sql.mvc(); | | X_2:bat[:oid,:oid] := sql.tid(X_1,"spinque","t"); | | X_5 := sql.bind(X_1,"spinque","t","a",0); | | X_8 := algebra.leftfetchjoin(X_2,X_5); | | X_9 := sql.bind(X_1,"spinque","t","b",0); | | X_11 := algebra.leftfetchjoin(X_2,X_9); | | (X_12,r1_15) := algebra.join(X_8,X_8,X_11,true,true); | | X_15 := algebra.leftfetchjoin(X_12,X_8); | | X_16 := sql.resultSet(1,1,X_15); | | sql.rsColumn(X_16,"spinque.t2","a","int",32,0,X_15); | | X_22 := io.stdout(); | | sql.exportResult(X_22,X_16); | | end s4_1; | +-----------------------------------------------------------+ 14 tuples (1.291ms) sql> sql>explain more>SELECT t2.a more>FROM t t1, t t2 more>WHERE t2.a >= t1.a more>AND t2.a <= t1.b; +-----------------------------------------------------------+ | mal | +===========================================================+ | function user.s5_1():void; | | X_1 := sql.mvc(); | | X_2:bat[:oid,:oid] := sql.tid(X_1,"spinque","t"); | | X_5 := sql.bind(X_1,"spinque","t","a",0); | | X_8 := algebra.leftfetchjoin(X_2,X_5); | | X_9 := sql.bind(X_1,"spinque","t","b",0); | | X_11 := algebra.leftfetchjoin(X_2,X_9); | | (X_12,r1_15) := algebra.join(X_8,X_8,X_11,true,true); | | X_15 := algebra.leftfetchjoin(X_12,X_8); | | X_16 := sql.resultSet(1,1,X_15); | | sql.rsColumn(X_16,"spinque.t2","a","int",32,0,X_15); | | X_22 := io.stdout(); | | sql.exportResult(X_22,X_16); | | end s5_1; | +-----------------------------------------------------------+ 14 tuples (1.254ms) sql> sql>explain more>SELECT t2.a more>FROM t t1, t t2 more>WHERE t1.a <= t2.a more>AND t1.b >= t2.a; +------------------------------------------------------------------------+ | mal | +========================================================================+ | function user.s6_1():void; | | X_1 := sql.mvc(); | | X_2:bat[:oid,:oid] := sql.tid(X_1,"spinque","t"); | | X_5 := sql.bind(X_1,"spinque","t","a",0); | | X_8 := algebra.leftfetchjoin(X_2,X_5); | | (X_9,r1_12) := algebra.thetajoin(X_8,X_8,-2); | | X_12 := sql.bind(X_1,"spinque","t","b",0); | | X_14:bat[:oid,:int] := algebra.leftfetchjoinPath(X_9,X_2,X_12); | | X_15 := algebra.leftfetchjoin(r1_12,X_8); | | X_16:bat[:oid,:bit] := batcalc.>=(X_14,X_15); | | X_17 := algebra.subselect(X_16,true,true,true,true,false); | | X_20:bat[:oid,:int] := algebra.leftfetchjoinPath(X_17,r1_12,X_8); | | X_21 := sql.resultSet(1,1,X_20); | | sql.rsColumn(X_21,"spinque.t2","a","int",32,0,X_20); | | X_27 := io.stdout(); | | sql.exportResult(X_27,X_21); | | end s6_1; | +------------------------------------------------------------------------+ 17 tuples (1.774ms) sql>
Reproducible: Always
The pattern in the last query should be recognised as a BETWEEN clause and produce a rangejoin.
$ mserver5 --version MonetDB 5 server v11.15.20 (64-bit, 64-bit oids) This is an unreleased version Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008-2013 MonetDB B.V., all rights reserved Visit http://www.monetdb.org/ for further information Found 15.6GiB available memory, 8 available cpu cores Libraries: libpcre: 8.32 2012-11-30 (compiled with 8.32) openssl: OpenSSL 1.0.1e 11 Feb 2013 (compiled with OpenSSL 1.0.1e-fips 11 Feb 2013) libxml2: 2.9.1 (compiled with 2.9.1) Compiled by: roberto@photon.spinque.com (x86_64-unknown-linux-gnu) Compilation: gcc -g -Werror -Wall -Wextra -W -Werror-implicit-function-declaration -Wpointer-arith -Wdeclaration-after-statement -Wundef -Wformat=2 -Wno-format-nonliteral -Winit-self -Winvalid-pch -Wmissing-declarations -Wmissing-format-attribute -Wmissing-prototypes -Wold-style-definition -Wpacked -Wunknown-pragmas -Wvariadic-macros -fstack-protector-all -Wstack-protector -Wpacked-bitfield-compat -Wsync-nand -Wjump-misses-init -Wmissing-include-dirs -Wlogical-op -Wunreachable-code Linking : /usr/bin/ld -m elf_x86_64
Date: 2013-12-13 15:25:02 +0100 From: @njnes
added missing case in exp_merge_range
Date: 2013-12-13 16:08:44 +0100 From: MonetDB Mercurial Repository <>
Changeset 482b6561151a made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.
For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=482b6561151a
Changeset description:
added tests for bugs 3411 and 3412
Date: 2014-02-20 15:02:34 +0100 From: @sjoerdmullender
Jan2014 has been released.
The text was updated successfully, but these errors were encountered:
No branches or pull requests
Date: 2013-12-12 18:52:00 +0100
From: @swingbit
To: SQL devs <>
Version: 11.15.19 (Feb2013-SP6)
CC: @njnes
Last updated: 2014-02-20 15:02:34 +0100
Comment 19418
Date: 2013-12-12 18:52:00 +0100
From: @swingbit
User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/31.0.1650.57 Safari/537.36
Build Identifier:
set optimizer='sequential_pipe';
START TRANSACTION;
CREATE TABLE t (
"a" INT,
"b" INT
);
-- The following 3 queries are EQUIVALENT
explain
SELECT t2.a
FROM t t1, t t2
WHERE t2.a between t1.a and t1.b;
explain
SELECT t2.a
FROM t t1, t t2
WHERE t2.a >= t1.a
AND t2.a <= t1.b;
explain
SELECT t2.a
FROM t t1, t t2
WHERE t1.a <= t2.a
AND t1.b >= t2.a;
The last query, however, does not produce a (range)join.
The effect, on large data, is terrible.
This is somewhat related to bug #3259.
Here below the MAL plans for the three queries.
The first two are fine.
The LAST plan is correct but much slower than the other two.
sql>set optimizer='sequential_pipe';
operation successful (0.394ms)
sql>
sql>START TRANSACTION;
auto commit mode: off
sql>
sql>CREATE TABLE t (
more>"a" INT,
more>"b" INT
more>);
operation successful (0.847ms)
sql>
sql>explain
more>SELECT t2.a
more>FROM t t1, t t2
more>WHERE t2.a between t1.a and t1.b;
+-----------------------------------------------------------+
| mal |
+===========================================================+
| function user.s4_1():void; |
| X_1 := sql.mvc(); |
| X_2:bat[:oid,:oid] := sql.tid(X_1,"spinque","t"); |
| X_5 := sql.bind(X_1,"spinque","t","a",0); |
| X_8 := algebra.leftfetchjoin(X_2,X_5); |
| X_9 := sql.bind(X_1,"spinque","t","b",0); |
| X_11 := algebra.leftfetchjoin(X_2,X_9); |
| (X_12,r1_15) := algebra.join(X_8,X_8,X_11,true,true); |
| X_15 := algebra.leftfetchjoin(X_12,X_8); |
| X_16 := sql.resultSet(1,1,X_15); |
| sql.rsColumn(X_16,"spinque.t2","a","int",32,0,X_15); |
| X_22 := io.stdout(); |
| sql.exportResult(X_22,X_16); |
| end s4_1; |
+-----------------------------------------------------------+
14 tuples (1.291ms)
sql>
sql>explain
more>SELECT t2.a
more>FROM t t1, t t2
more>WHERE t2.a >= t1.a
more>AND t2.a <= t1.b;
+-----------------------------------------------------------+
| mal |
+===========================================================+
| function user.s5_1():void; |
| X_1 := sql.mvc(); |
| X_2:bat[:oid,:oid] := sql.tid(X_1,"spinque","t"); |
| X_5 := sql.bind(X_1,"spinque","t","a",0); |
| X_8 := algebra.leftfetchjoin(X_2,X_5); |
| X_9 := sql.bind(X_1,"spinque","t","b",0); |
| X_11 := algebra.leftfetchjoin(X_2,X_9); |
| (X_12,r1_15) := algebra.join(X_8,X_8,X_11,true,true); |
| X_15 := algebra.leftfetchjoin(X_12,X_8); |
| X_16 := sql.resultSet(1,1,X_15); |
| sql.rsColumn(X_16,"spinque.t2","a","int",32,0,X_15); |
| X_22 := io.stdout(); |
| sql.exportResult(X_22,X_16); |
| end s5_1; |
+-----------------------------------------------------------+
14 tuples (1.254ms)
sql>
sql>explain
more>SELECT t2.a
more>FROM t t1, t t2
more>WHERE t1.a <= t2.a
more>AND t1.b >= t2.a;
+------------------------------------------------------------------------+
| mal |
+========================================================================+
| function user.s6_1():void; |
| X_1 := sql.mvc(); |
| X_2:bat[:oid,:oid] := sql.tid(X_1,"spinque","t"); |
| X_5 := sql.bind(X_1,"spinque","t","a",0); |
| X_8 := algebra.leftfetchjoin(X_2,X_5); |
| (X_9,r1_12) := algebra.thetajoin(X_8,X_8,-2); |
| X_12 := sql.bind(X_1,"spinque","t","b",0); |
| X_14:bat[:oid,:int] := algebra.leftfetchjoinPath(X_9,X_2,X_12); |
| X_15 := algebra.leftfetchjoin(r1_12,X_8); |
| X_16:bat[:oid,:bit] := batcalc.>=(X_14,X_15); |
| X_17 := algebra.subselect(X_16,true,true,true,true,false); |
| X_20:bat[:oid,:int] := algebra.leftfetchjoinPath(X_17,r1_12,X_8); |
| X_21 := sql.resultSet(1,1,X_20); |
| sql.rsColumn(X_21,"spinque.t2","a","int",32,0,X_20); |
| X_27 := io.stdout(); |
| sql.exportResult(X_27,X_21); |
| end s6_1; |
+------------------------------------------------------------------------+
17 tuples (1.774ms)
sql>
Reproducible: Always
Expected Results:
The pattern in the last query should be recognised as a BETWEEN clause and produce a rangejoin.
$ mserver5 --version
MonetDB 5 server v11.15.20 (64-bit, 64-bit oids)
This is an unreleased version
Copyright (c) 1993-July 2008 CWI
Copyright (c) August 2008-2013 MonetDB B.V., all rights reserved
Visit http://www.monetdb.org/ for further information
Found 15.6GiB available memory, 8 available cpu cores
Libraries:
libpcre: 8.32 2012-11-30 (compiled with 8.32)
openssl: OpenSSL 1.0.1e 11 Feb 2013 (compiled with OpenSSL 1.0.1e-fips 11 Feb 2013)
libxml2: 2.9.1 (compiled with 2.9.1)
Compiled by: roberto@photon.spinque.com (x86_64-unknown-linux-gnu)
Compilation: gcc -g -Werror -Wall -Wextra -W -Werror-implicit-function-declaration -Wpointer-arith -Wdeclaration-after-statement -Wundef -Wformat=2 -Wno-format-nonliteral -Winit-self -Winvalid-pch -Wmissing-declarations -Wmissing-format-attribute -Wmissing-prototypes -Wold-style-definition -Wpacked -Wunknown-pragmas -Wvariadic-macros -fstack-protector-all -Wstack-protector -Wpacked-bitfield-compat -Wsync-nand -Wjump-misses-init -Wmissing-include-dirs -Wlogical-op -Wunreachable-code
Linking : /usr/bin/ld -m elf_x86_64
Comment 19424
Date: 2013-12-13 15:25:02 +0100
From: @njnes
added missing case in exp_merge_range
Comment 19426
Date: 2013-12-13 16:08:44 +0100
From: MonetDB Mercurial Repository <>
Changeset 482b6561151a made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.
For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=482b6561151a
Changeset description:
Comment 19598
Date: 2014-02-20 15:02:34 +0100
From: @sjoerdmullender
Jan2014 has been released.
The text was updated successfully, but these errors were encountered: