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

BETWEEN clause not producing rangejoin #3259

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

BETWEEN clause not producing rangejoin #3259

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

Comments

@monetdb-team
Copy link

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

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:

fixed bug #3259

The rel_push_func_down optimizer didn't push down expressions from
joins

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:

between.Bug-3259: approved single-threaded output (MAL plan)

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:

between.Bug-3259: propagated changesets [52ed955b70c9](https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=52ed955b70c9)  & [054504135056](https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=054504135056)  to single-threaded output

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:

between.Bug-3259: approved new MAL plan after changeset [79e66f677113](https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=79e66f677113) 

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:

between.Bug-3259: propagated changeset [bbcf019e6fef](https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=bbcf019e6fef)  to single-threaded output

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:

between.Bug-3259: propagated changeset [192a529eaa4a](https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=192a529eaa4a)  to single-threaded output

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:

between.Bug-3259: approved non-int128 output after recent changes
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