BETWEEN clause not producing rangejoin #3259
Milestone
Comments
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
{{ message }}
Date: 2013-03-21 16:53:56 +0100
From: @swingbit
To: SQL devs <>
Version: 11.15.3 (Feb2013-SP1)
CC: @njnes
Last updated: 2015-03-22 00:16:35 +0100
Comment 18631
Date: 2013-03-21 16:53:56 +0100
From: @swingbit
User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.22 (KHTML, like Gecko) Chrome/25.0.1364.172 Safari/537.22
Build Identifier:
The following bit of SQL:
ROLLBACK;
START TRANSACTION;
CREATE TABLE treeitems (
"tree" CHARACTER LARGE OBJECT,
"subject" INTEGER,
"pre" BIGINT,
"post" BIGINT,
"size" BIGINT,
"level" TINYINT,
"prob" DOUBLE DEFAULT 1.0,
CONSTRAINT "treeitems_tree_pre_unique" UNIQUE ("tree", "pre"),
CONSTRAINT "treeitems_tree_post_unique" UNIQUE ("tree", "post")
);
explain
SELECT t1.subject as id1, t2.subject as id2
FROM treeitems t1, treeitems t2
WHERE t2.pre between t1.pre and t1.pre + t1.size;
On Jun2012 (notice X_15, which calls a BATrangejoin):
| function user.s1_1():void; |
| X_25 := nil:bat[:oid,:int]; |
| X_29 := nil:bat[:oid,:int]; |
| barrier X_50 := language.dataflow(); |
| X_1 := sql.mvc(); |
| X_2:bat[:oid,:lng] := sql.bind(X_1,"spinque","treeitems","pre",0); |
| X_7:bat[:oid,:oid] := sql.bind_dbat(X_1,"spinque","treeitems",1); |
| X_9 := bat.reverse(X_7); |
| X_10 := algebra.kdifference(X_2,X_9); |
| X_11:bat[:oid,:lng] := sql.bind(X_1,"spinque","treeitems","size",0); |
| X_13 := algebra.kdifference(X_11,X_9); |
| X_14:bat[:oid,:lng] := batcalc.+(X_10,X_13); |
| X_15 := algebra.join(X_10,X_10,X_14,true,true); |
| X_17 := bat.reverse(X_15); |
| X_18 := algebra.markT(X_17,0@0:oid); |
| X_19 := bat.reverse(X_18); |
| X_20:bat[:oid,:int] := sql.bind(X_1,"spinque","treeitems","subject",0); |
| X_25 := algebra.leftjoin(X_19,X_20); |
| X_27 := algebra.markT(X_15,0@0:oid); |
| X_28 := bat.reverse(X_27); |
| X_29 := algebra.leftjoin(X_28,X_20); |
| exit X_50; |
| X_31 := sql.resultSet(2,1,X_25); |
| sql.rsColumn(X_31,"spinque.t1","id1","int",32,0,X_25); |
| sql.rsColumn(X_31,"spinque.t2","id2","int",32,0,X_29); |
| X_40 := io.stdout(); |
| sql.exportResult(X_40,X_31); |
| end s1_1; |
On Feb2013 (notice X9, X_26, X22):
| function user.s7_1():void; |
| X_32:bat[:oid,:int] := nil:bat[:oid,:int]; |
| X_34:bat[:oid,:int] := nil:bat[:oid,:int]; |
| barrier X_54 := language.dataflow(); |
| X_1 := sql.mvc(); |
| X_2:bat[:oid,:oid] := sql.tid(X_1,"spinque","treeitems"); |
| X_5 := sql.bind(X_1,"spinque","treeitems","tree",0); |
| X_8 := algebra.leftfetchjoin(X_2,X_5); |
| (X_9,r1_18) := algebra.crossproduct(X_8,X_8); |
| X_11 := sql.bind(X_1,"spinque","treeitems","pre",0); |
| X_13 := algebra.leftfetchjoin(X_2,X_11); |
| X_14 := algebra.leftfetchjoin(X_9,X_13); |
| X_15 := algebra.leftfetchjoin(r1_18,X_13); |
| X_26:bat[:oid,:bit] := batcalc.>=(X_14,X_15); |
| X_16 := sql.bind(X_1,"spinque","treeitems","size",0); |
| X_20:bat[:oid,:lng] := algebra.leftfetchjoinPath(r1_18,X_2,X_16); |
| X_21:bat[:oid,:lng] := batcalc.+(X_15,X_20); |
| X_22:bat[:oid,:bit] := batcalc.<=(X_14,X_21); |
| X_23 := algebra.subselect(X_22,true,true,true,true,false); |
| X_27 := algebra.subselect(X_26,X_23,true,true,true,true,false); |
| X_28 := sql.bind(X_1,"spinque","treeitems","subject",0); |
| X_31 := algebra.leftfetchjoin(X_2,X_28); |
| X_32:bat[:oid,:int] := algebra.leftfetchjoinPath(X_27,r1_18,X_31); |
| X_34:bat[:oid,:int] := algebra.leftfetchjoinPath(X_27,X_9,X_31); |
| language.pass(X_8); |
| language.pass(X_13); |
| language.pass(X_15); |
| language.pass(X_14); |
| language.pass(X_2); |
| language.pass(r1_18); |
| language.pass(X_27); |
| language.pass(X_9); |
| language.pass(X_31); |
| exit X_54; |
| X_36 := sql.resultSet(2,1,X_32); |
| sql.rsColumn(X_36,"spinque.t1","id1","int",32,0,X_32); |
| sql.rsColumn(X_36,"spinque.t2","id2","int",32,0,X_34); |
| X_46 := io.stdout(); |
| sql.exportResult(X_46,X_36); |
| end s7_1; |
This version is no option: the crossproduct filled up my entire disk.
Reproducible: Always
Actual Results:
crossproduct + <= + >=
Expected Results:
rangejoin
MonetDB 5 server v11.15.4 (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.31 2012-07-06 (compiled with 8.31)
openssl: OpenSSL 1.0.1e 11 Feb 2013 (compiled with OpenSSL 1.0.1e-fips 11 Feb 2013)
libxml2: 2.9.0 (compiled with 2.9.0)
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 18634
Date: 2013-03-21 20:16:07 +0100
From: @njnes
Fixed by improving the function down optimizer (sql_add needs to be done first) and improved renaming of expressions in this optimizer.
Comment 18635
Date: 2013-03-21 20:19:54 +0100
From: MonetDB Mercurial Repository <>
Changeset 6e71a843ec7d 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=6e71a843ec7d
Changeset description:
Comment 18646
Date: 2013-03-25 09:47:02 +0100
From: MonetDB Mercurial Repository <>
Changeset ee9ebeb0d48c 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=ee9ebeb0d48c
Changeset description:
Comment 18890
Date: 2013-06-24 00:23:52 +0200
From: MonetDB Mercurial Repository <>
Changeset d8b80fc7ff83 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=d8b80fc7ff83
Changeset description:
Comment 19159
Date: 2013-09-15 22:32:33 +0200
From: MonetDB Mercurial Repository <>
Changeset d0b65a3e8eb6 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=d0b65a3e8eb6
Changeset description:
Comment 19393
Date: 2013-12-04 20:40:12 +0100
From: MonetDB Mercurial Repository <>
Changeset 66984fb5abf0 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=66984fb5abf0
Changeset description:
Comment 20321
Date: 2014-10-28 23:46:16 +0100
From: MonetDB Mercurial Repository <>
Changeset a7700c816e32 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=a7700c816e32
Changeset description:
Comment 20737
Date: 2015-03-22 00:16:35 +0100
From: MonetDB Mercurial Repository <>
Changeset a5dbe5a04a60 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=a5dbe5a04a60
Changeset description:
The text was updated successfully, but these errors were encountered: