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

(disguised) BETWEEN clause not recognised. Hence no rangejoin. #3411

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

(disguised) BETWEEN clause not recognised. Hence no rangejoin. #3411

monetdb-team opened this issue Nov 30, 2020 · 0 comments
Labels
bug Something isn't working normal SQL

Comments

@monetdb-team
Copy link

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:

added tests for bugs 3411 and 3412

Comment 19598

Date: 2014-02-20 15:02:34 +0100
From: @sjoerdmullender

Jan2014 has been released.

@monetdb-team monetdb-team added bug Something isn't working normal SQL labels Nov 30, 2020
@sjoerdmullender sjoerdmullender added this to the Ancient Release milestone Feb 7, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working normal SQL
Projects
None yet
Development

No branches or pull requests

2 participants