evaluation of SQL "between SYMMETRIC" requires MAL iterator because there is no (bulk) MIN/MAX(a,b)

[monetdb-team]

Date: 2011-12-08 18:34:48 +0100
From: @drstmane
To: MonetDB5 devs <>
Version: -- development
CC: @njnes, @yzchang

Last updated: 2014-10-31 14:14:17 +0100

Comment 16640

Date: 2011-12-08 18:34:48 +0100
From: @drstmane

[For now, I file this as a feature request; it might be considered a (performance) bug, though ...]

There appear to be no bulk implementations of binary MIN(a,b) / MAX(a,b) (e.g., in module batcalc).
Consequently, the translation of SQL queries using "between SYMMETRIC" is sub-optimal, using BAT iterators in MAL, rather than an efficient bulk implementation of binary MIN(a,b) / MAX(a,b).

Cf.:

$ mclient -s 'create table T (a int, b int, c int);'
operation successful

$ mclient -s 'PLAN select * from T where a between b and c;'
+------------------------------------------------------+
| rel |
+======================================================+
| project ( |
| | select ( |
| | | table(sys.t) [ t.a, t.b, t.c, t.%TID% NOT NULL ] |
| | ) [ t.b <= t.a <= t.c ] |
| ) [ t.a, t.b, t.c ] |
+------------------------------------------------------+
5 tuples

$ mclient -s 'EXPLAIN select * from T where a between b and c;' | grep -9 -i iterator

$ mclient -s 'PLAN select * from T where a between SYMMETRIC b and c;'
+---------------------------------------------------------------+
| rel |
+===============================================================+
| project ( |
| | select ( |
| | | table(sys.t) [ t.a, t.b, t.c, t.%TID% NOT NULL ] |
| | ) [ sys.sql_min(t.b, t.c) <= t.a <= sys.sql_max(t.b, t.c) ] |
| ) [ t.a, t.b, t.c ] |
+---------------------------------------------------------------+
5 tuples

$ mclient -s 'EXPLAIN select * from T where a between SYMMETRIC b and c;' | grep -9 -i iterator
| X_36 := algebra.kunion(X_35,X_27); |
| X_37 := algebra.kdifference(X_36,X_16); |
| X_10:bat[:oid,:int] := sql.bind(X_2,"sys","t","a",0); |
| X_8:bat[:oid,:int] := sql.bind(X_2,"sys","t","a",2); |
| X_12 := algebra.kdifference(X_10,X_8); |
| X_13 := algebra.kunion(X_12,X_8); |
| X_5:bat[:oid,:int] := sql.bind(X_2,"sys","t","a",1); |
| X_14 := algebra.kunion(X_13,X_5); |
| X_156 := bat.new(nil:oid,nil:int); |
| barrier (X_159,X_160,X_161) := bat.newIterator(X_24); |
| X_163 := algebra.find(X_37,X_160); |
| X_165 := calc.max(X_161,X_163); |
| bat.insert(X_156,X_160,X_165); |
| redo (X_159,X_160,X_161) := bat.hasMoreElements(X_24); |
| exit (X_159,X_160,X_161); |
| X_38:bat[:oid,:int] := X_156; |
| X_68 := bat.mirror(X_10); |
| X_42 := bat.mirror(X_38); |
| X_41 := bat.mirror(X_5); |

| X_49 := algebra.join(X_44,X_38); |
| X_50:bat[:oid,:bit] := batcalc.<=(X_48,X_49); |
| X_51 := algebra.uselect(X_50,true); |
| X_53 := bat.reverse(X_51); |
| X_54 := algebra.join(X_53,X_44); |
| X_55 := bat.reverse(X_54); |
| X_83 := algebra.kunion(X_82,X_55); |
| X_84 := algebra.kdifference(X_83,X_16); |
| X_170 := bat.new(nil:oid,nil:int); |
| barrier (X_172,X_173,X_174) := bat.newIterator(X_24); |
| X_176 := algebra.find(X_37,X_173); |
| X_178 := calc.min(X_174,X_176); |
| bat.insert(X_170,X_173,X_178); |
| redo (X_172,X_173,X_174) := bat.hasMoreElements(X_24); |
| exit (X_172,X_173,X_174); |
| X_24 := nil:BAT; |
| X_37 := nil:BAT; |
| X_85:bat[:oid,:int] := X_170; |
| X_87 := bat.mirror(X_85); |

Comment 18080

Date: 2012-11-27 14:25:10 +0100
From: @yzchang

No test for performance issues

Comment 20363

Date: 2014-10-31 14:14:17 +0100
From: @sjoerdmullender

Oct2014 has been released.