# Algebra

This module contains the most common algebraic BAT manipulation commands including joins, selections, grouping, sorting and projections.

Most of the commands support optional candidate list arguments.

## Algebra module

``````MODULE algebra;

COMMAND algebra.bandjoin(X_0:bat[:any_1], X_1:bat[:any_1], X_2:bat[:oid], X_3:bat[:oid], X_4:any_1, X_5:any_1, X_6:bit, X_7:bit, X_8:lng):bat[:oid];
COMMENT "Band join: values in l and r match if r - c1 <[=] l <[=] r + c2; only produce left output";

COMMAND algebra.bandjoin(X_0:bat[:any_1], X_1:bat[:any_1], X_2:bat[:oid], X_3:bat[:oid], X_4:any_1, X_5:any_1, X_6:bit, X_7:bit, X_8:lng) (X_9:bat[:oid], X_10:bat[:oid]);
COMMENT "Band join: values in l and r match if r - c1 <[=] l <[=] r + c2";

COMMAND algebra.copy(X_0:bat[:any_1]):bat[:any_1];
COMMENT "Returns physical copy of a BAT.";

COMMAND algebra.crossproduct(X_0:bat[:any_1], X_1:bat[:any_2], X_2:bit):bat[:oid];
COMMENT "Compute the cross product of both input bats; but only produce left output";

COMMAND algebra.crossproduct(X_0:bat[:any_1], X_1:bat[:any_2], X_2:bit) (X_3:bat[:oid], X_4:bat[:oid]);
COMMENT "Returns 2 columns with all BUNs, consisting of the head-oids\nfrom 'left' and 'right' for which there are BUNs in 'left'\nand 'right' with equal tails";

COMMAND algebra.crossproduct(X_0:bat[:any_1], X_1:bat[:any_2], X_2:bat[:oid], X_3:bat[:oid], X_4:bit) (X_5:bat[:oid], X_6:bat[:oid]);
COMMENT "Compute the cross product of both input bats";

COMMAND algebra.crossproduct(X_0:bat[:any_1], X_1:bat[:any_2], X_2:bat[:oid], X_3:bat[:oid], X_4:bit):bat[:oid];
COMMENT "Compute the cross product of both input bats; but only produce left output";

COMMAND algebra.difference(X_0:bat[:any_1], X_1:bat[:any_1], X_2:bat[:oid], X_3:bat[:oid], X_4:bit, X_5:bit, X_6:lng):bat[:oid];
COMMENT "Difference of l and r with candidate lists";

COMMAND algebra.exist(X_0:bat[:any_1], X_1:any_1):bit;
COMMENT "Returns whether 'val' occurs in b.";

COMMAND algebra.fetch(X_0:bat[:any_1], X_1:oid):any_1;
COMMENT "Returns the value of the BUN at x-th position with 0 <= x < b.count";

COMMAND algebra.find(X_0:bat[:any_1], X_1:any_1):oid;
COMMENT "Returns the index position of a value.  If no such BUN exists return OID-nil.";

PATTERN algebra.firstn(X_0:bat[:any], X_1:bat[:oid], X_2:bat[:oid], X_3:lng, X_4:bit, X_5:bit, X_6:bit) (X_7:bat[:oid], X_8:bat[:oid]);
COMMENT "Calculate first N values of B with candidate list S";

PATTERN algebra.firstn(X_0:bat[:any], X_1:bat[:oid], X_2:bat[:oid], X_3:lng, X_4:bit, X_5:bit, X_6:bit):bat[:oid];
COMMENT "Calculate first N values of B with candidate list S";

COMMAND algebra.groupby(X_0:bat[:oid], X_1:bat[:lng]):bat[:oid];
COMMENT "Produces a new BAT with groups identified by the head column. The result contains tail times the head value, ie the tail contains the result group sizes.";

COMMAND algebra.intersect(X_0:bat[:any_1], X_1:bat[:any_1], X_2:bat[:oid], X_3:bat[:oid], X_4:bit, X_5:bit, X_6:lng):bat[:oid];
COMMENT "Intersection of l and r with candidate lists (i.e. half of semi-join)";

COMMAND algebra.join(X_0:bat[:any_1], X_1:bat[:any_1], X_2:bat[:oid], X_3:bat[:oid], X_4:bit, X_5:lng):bat[:oid];
COMMENT "Join; only produce left output";

COMMAND algebra.join(X_0:bat[:any_1], X_1:bat[:any_1], X_2:bat[:oid], X_3:bat[:oid], X_4:bit, X_5:lng) (X_6:bat[:oid], X_7:bat[:oid]);
COMMENT "Join";

COMMAND algebra.leftjoin(X_0:bat[:any_1], X_1:bat[:any_1], X_2:bat[:oid], X_3:bat[:oid], X_4:bit, X_5:lng):bat[:oid];
COMMENT "Left join with candidate lists; only produce left output";

COMMAND algebra.leftjoin(X_0:bat[:any_1], X_1:bat[:any_1], X_2:bat[:oid], X_3:bat[:oid], X_4:bit, X_5:lng) (X_6:bat[:oid], X_7:bat[:oid]);
COMMENT "Left join with candidate lists";

COMMAND algebra.like(X_0:str, X_1:str, X_2:str, X_3:bit):bit;
COMMENT "";

COMMAND algebra.likejoin(X_0:bat[:str], X_1:bat[:str], X_2:bat[:str], X_3:bat[:bit], X_4:bat[:oid], X_5:bat[:oid], X_6:bit, X_7:lng, X_8:bit):bat[:oid];
COMMENT "The same as LIKEjoin_esc, but only produce one output";

COMMAND algebra.likejoin(X_0:bat[:str], X_1:bat[:str], X_2:bat[:str], X_3:bat[:bit], X_4:bat[:oid], X_5:bat[:oid], X_6:bit, X_7:lng, X_8:bit) (X_9:bat[:oid], X_10:bat[:oid]);
COMMENT "Join the string bat L with the pattern bat R\nwith optional candidate lists SL and SR using pattern escape string ESC\nand doing a case sensitive match.\nThe result is two aligned bats with oids of matching rows.";

COMMAND algebra.likeselect(X_0:bat[:str], X_1:bat[:oid], X_2:str, X_3:str, X_4:bit, X_5:bit):bat[:oid];
COMMENT "Select all head values of the first input BAT for which the\ntail value is \"like\" the given (SQL-style) pattern and for\nwhich the head value occurs in the tail of the second input\nBAT.\nInput is a dense-headed BAT, output is a dense-headed BAT with in\nthe tail the head value of the input BAT for which the\nrelationship holds.  The output BAT is sorted on the tail value.";

COMMAND algebra.not_like(X_0:str, X_1:str, X_2:str, X_3:bit):bit;
COMMENT "";

COMMAND algebra.orderidx(X_0:bat[:any_1], X_1:bit):bat[:any_1];
COMMENT "Create an order index";

COMMAND algebra.outerjoin(X_0:bat[:any_1], X_1:bat[:any_1], X_2:bat[:oid], X_3:bat[:oid], X_4:bit, X_5:bit, X_6:lng):bat[:oid];
COMMENT "Left outer join with candidate lists; only produce left output";

COMMAND algebra.outerjoin(X_0:bat[:any_1], X_1:bat[:any_1], X_2:bat[:oid], X_3:bat[:oid], X_4:bit, X_5:bit, X_6:lng) (X_7:bat[:oid], X_8:bat[:oid]);
COMMENT "Left outer join with candidate lists";

PATTERN algebra.project(X_0:bat[:any_1], X_1:any_3):bat[:any_3];
COMMENT "Fill the tail with a constant";

COMMAND algebra.projection(X_0:bat[:oid], X_1:bat[:any_3], X_2:bat[:any_3]):bat[:any_3];
COMMENT "Project left input onto right inputs which should be consecutive.";

COMMAND algebra.projection(X_0:bat[:oid], X_1:bat[:any_3]):bat[:any_3];
COMMENT "Project left input onto right input.";

PATTERN algebra.projectionpath(X_0:bat[:any]...):bat[:any];
COMMENT "Routine to handle join paths.  The type analysis is rather tricky.";

COMMAND algebra.rangejoin(X_0:bat[:any_1], X_1:bat[:any_1], X_2:bat[:any_1], X_3:bat[:oid], X_4:bat[:oid], X_5:bit, X_6:bit, X_7:bit, X_8:bit, X_9:lng):bat[:oid];
COMMENT "Range join: values in l and r1/r2 match if r1 <[=] l <[=] r2; only produce left output";

COMMAND algebra.rangejoin(X_0:bat[:any_1], X_1:bat[:any_1], X_2:bat[:any_1], X_3:bat[:oid], X_4:bat[:oid], X_5:bit, X_6:bit, X_7:bit, X_8:bit, X_9:lng) (X_10:bat[:oid], X_11:bat[:oid]);
COMMENT "Range join: values in l and r1/r2 match if r1 <[=] l <[=] r2";

COMMAND algebra.reuse(X_0:bat[:any_1]):bat[:any_1];
COMMENT "Reuse a temporary BAT if you can. Otherwise,\nallocate enough storage to accept result of an\noperation (not involving the heap)";

COMMAND algebra.select(X_0:bat[:any_1], X_1:any_1, X_2:any_1, X_3:bit, X_4:bit, X_5:bit):bat[:oid];
COMMENT "Select all head values for which the tail value is in range.\nInput is a dense-headed BAT, output is a dense-headed BAT with in\nthe tail the head value of the input BAT for which the tail value\nis between the values low and high (inclusive if li respectively\nhi is set).  The output BAT is sorted on the tail value.  If low\nor high is nil, the boundary is not considered (effectively - and\n+ infinity).  If anti is set, the result is the complement.  Nil\nvalues in the tail are never matched, unless low=nil, high=nil,\nli=1, hi=1, anti=0.  All non-nil values are returned if low=nil,\nhigh=nil, and li, hi are not both 1, or anti=1.\nNote that the output is suitable as second input for the other\nversion of this function.";

COMMAND algebra.select(X_0:bat[:any_1], X_1:any_1, X_2:any_1, X_3:bit, X_4:bit, X_5:bit, X_6:bit):bat[:oid];
COMMENT "With unknown set, each nil != nil";

COMMAND algebra.select(X_0:bat[:any_1], X_1:bat[:oid], X_2:any_1, X_3:any_1, X_4:bit, X_5:bit, X_6:bit):bat[:oid];
COMMENT "Select all head values of the first input BAT for which the tail value\nis in range and for which the head value occurs in the tail of the\nsecond input BAT.\nThe first input is a dense-headed BAT, the second input is a\ndense-headed BAT with sorted tail, output is a dense-headed BAT\nwith in the tail the head value of the input BAT for which the\ntail value is between the values low and high (inclusive if li\nrespectively hi is set).  The output BAT is sorted on the tail\nvalue.  If low or high is nil, the boundary is not considered\n(effectively - and + infinity).  If anti is set, the result is the\ncomplement.  Nil values in the tail are never matched, unless\nlow=nil, high=nil, li=1, hi=1, anti=0.  All non-nil values are\nreturned if low=nil, high=nil, and li, hi are not both 1, or anti=1.\nNote that the output is suitable as second input for this\nfunction.";

COMMAND algebra.select(X_0:bat[:any_1], X_1:bat[:oid], X_2:any_1, X_3:any_1, X_4:bit, X_5:bit, X_6:bit, X_7:bit):bat[:oid];
COMMENT "With unknown set, each nil != nil";

COMMAND algebra.selectNotNil(X_0:bat[:any_2]):bat[:any_2];
COMMENT "Select all not-nil values";

COMMAND algebra.semijoin(X_0:bat[:any_1], X_1:bat[:any_1], X_2:bat[:oid], X_3:bat[:oid], X_4:bit, X_5:bit, X_6:lng) (X_7:bat[:oid], X_8:bat[:oid]);
COMMENT "Semi join with candidate lists";

COMMAND algebra.slice(X_0:bat[:any_1], X_1:int, X_2:int):bat[:any_1];
COMMENT "Return the slice with the BUNs at position x till y.";

COMMAND algebra.slice(X_0:bat[:any_1], X_1:lng, X_2:lng):bat[:any_1];
COMMENT "Return the slice with the BUNs at position x till y.";

COMMAND algebra.slice(X_0:bat[:any_1], X_1:oid, X_2:oid):bat[:any_1];
COMMENT "Return the slice based on head oid x till y (exclusive).";

COMMAND algebra.sort(X_0:bat[:any_1], X_1:bit, X_2:bit, X_3:bit):bat[:any_1];
COMMENT "Returns a copy of the BAT sorted on tail values.\nThe order is descending if the reverse bit is set.\nThis is a stable sort if the stable bit is set.";

COMMAND algebra.sort(X_0:bat[:any_1], X_1:bit, X_2:bit, X_3:bit) (X_4:bat[:any_1], X_5:bat[:oid]);
COMMENT "Returns a copy of the BAT sorted on tail values and a BAT that\nspecifies how the input was reordered.\nThe order is descending if the reverse bit is set.\nThis is a stable sort if the stable bit is set.";

COMMAND algebra.sort(X_0:bat[:any_1], X_1:bit, X_2:bit, X_3:bit) (X_4:bat[:any_1], X_5:bat[:oid], X_6:bat[:oid]);
COMMENT "Returns a copy of the BAT sorted on tail values, a BAT that specifies\nhow the input was reordered, and a BAT with group information.\nThe order is descending if the reverse bit is set.\nThis is a stable sort if the stable bit is set.";

COMMAND algebra.sort(X_0:bat[:any_1], X_1:bat[:oid], X_2:bit, X_3:bit, X_4:bit):bat[:any_1];
COMMENT "Returns a copy of the BAT sorted on tail values.\nThe order is descending if the reverse bit is set.\nThis is a stable sort if the stable bit is set.";

COMMAND algebra.sort(X_0:bat[:any_1], X_1:bat[:oid], X_2:bit, X_3:bit, X_4:bit) (X_5:bat[:any_1], X_6:bat[:oid]);
COMMENT "Returns a copy of the BAT sorted on tail values and a BAT that\nspecifies how the input was reordered.\nThe order is descending if the reverse bit is set.\nThis is a stable sort if the stable bit is set.";

COMMAND algebra.sort(X_0:bat[:any_1], X_1:bat[:oid], X_2:bit, X_3:bit, X_4:bit) (X_5:bat[:any_1], X_6:bat[:oid], X_7:bat[:oid]);
COMMENT "Returns a copy of the BAT sorted on tail values, a BAT that specifies\nhow the input was reordered, and a BAT with group information.\nThe order is descending if the reverse bit is set.\nThis is a stable sort if the stable bit is set.";

COMMAND algebra.sort(X_0:bat[:any_1], X_1:bat[:oid], X_2:bat[:oid], X_3:bit, X_4:bit, X_5:bit):bat[:any_1];
COMMENT "Returns a copy of the BAT sorted on tail values.\nThe order is descending if the reverse bit is set.\nThis is a stable sort if the stable bit is set.";

COMMAND algebra.sort(X_0:bat[:any_1], X_1:bat[:oid], X_2:bat[:oid], X_3:bit, X_4:bit, X_5:bit) (X_6:bat[:any_1], X_7:bat[:oid]);
COMMENT "Returns a copy of the BAT sorted on tail values and a BAT that\nspecifies how the input was reordered.\nThe order is descending if the reverse bit is set.\nThis is a stable sort if the stable bit is set.";

COMMAND algebra.sort(X_0:bat[:any_1], X_1:bat[:oid], X_2:bat[:oid], X_3:bit, X_4:bit, X_5:bit) (X_6:bat[:any_1], X_7:bat[:oid], X_8:bat[:oid]);
COMMENT "Returns a copy of the BAT sorted on tail values, a BAT that specifies\nhow the input was reordered, and a BAT with group information.\nThe order is descending if the reverse bit is set.\nThis is a stable sort if the stable bit is set.";

COMMAND algebra.subslice(X_0:bat[:any_1], X_1:lng, X_2:lng):bat[:oid];
COMMENT "Return the oids of the slice with the BUNs at position x till y.";

COMMAND algebra.thetajoin(X_0:bat[:any_1], X_1:bat[:any_1], X_2:bat[:oid], X_3:bat[:oid], X_4:int, X_5:bit, X_6:lng):bat[:oid];
COMMENT "Theta join with candidate lists; only produce left output";

COMMAND algebra.thetajoin(X_0:bat[:any_1], X_1:bat[:any_1], X_2:bat[:oid], X_3:bat[:oid], X_4:int, X_5:bit, X_6:lng) (X_7:bat[:oid], X_8:bat[:oid]);
COMMENT "Theta join with candidate lists";

COMMAND algebra.thetaselect(X_0:bat[:any_1], X_1:bat[:oid], X_2:any_1, X_3:str):bat[:oid];
COMMENT "Select all head values of the first input BAT for which the tail value\nobeys the relation value OP VAL and for which the head value occurs in\nthe tail of the second input BAT.\nInput is a dense-headed BAT, output is a dense-headed BAT with in\nthe tail the head value of the input BAT for which the\nrelationship holds.  The output BAT is sorted on the tail value.";

COMMAND algebra.unique(X_0:bat[:any_1], X_1:bat[:oid]):bat[:oid];
COMMENT "Select all unique values from the tail of the first input.\nInput is a dense-headed BAT, the second input is a\ndense-headed BAT with sorted tail, output is a dense-headed\nBAT with in the tail the head value of the input BAT that was\nselected.  The output BAT is sorted on the tail value.  The\nsecond input BAT is a list of candidates.";
``````

## Batalgebra module

``````MODULE batalgebra;

PATTERN batalgebra.like(X_0:bat[:str], X_1:bat[:str], X_2:str, X_3:bit):bat[:bit];
COMMENT "";

PATTERN batalgebra.like(X_0:bat[:str], X_1:str, X_2:str, X_3:bit):bat[:bit];
COMMENT "";

PATTERN batalgebra.like(X_0:str, X_1:bat[:str], X_2:str, X_3:bit):bat[:bit];
COMMENT "";

PATTERN batalgebra.not_like(X_0:bat[:str], X_1:bat[:str], X_2:str, X_3:bit):bat[:bit];
COMMENT "";

PATTERN batalgebra.not_like(X_0:bat[:str], X_1:str, X_2:str, X_3:bit):bat[:bit];
COMMENT "";

PATTERN batalgebra.not_like(X_0:str, X_1:bat[:str], X_2:str, X_3:bit):bat[:bit];
COMMENT "";
``````