WL#11573: Add BIT_AND, BIT_OR and BIT_XOR window aggregates
Affects: Server-8.0
—
Status: Complete
Add windowing support for the MySQL specific aggregate functions BIT_OR, BIT_AND and BIT_XOR. This is a followup of the work done in WL#9236 Add SQL window functions. Please refer to that WL for a general background and specifications.
R-1 They should have the same semantics as the grouped aggregates BIT_AND, BIT_OR and BIT_XOR, that is bitwise AND/OR/XOR of all bits in the expression. We perform pairwise bit operation on all bits in the expression starting with a) the first row in the frame and the default value, then b) the second row in the frame and the result from a) and so on. R-2 They should have the same default values: R-2-1 BIT_AND: all bits set to ones, R-2-2 BIT_OR: all bits set to zeros. R-2-3 BIT_XOR: all bits set to zeros. R-3 They should work for both integer types and the binary strings, see R-9, again as for the grouped aggregates. For integer types, the calculation is performed with 64-bit (BIGINT) precision, see R-10. R-4 Operands must have the same length. R-5 The maximum length of the binary strings is the same as for the grouped aggregates; 511 bytes. R-6 BIT_XXX window functions should be optimizable, that is, not show (multiplicative) complexity increase as the window frame size increases. R-7 NULL values do not affect the result unless all values are NULL. In that case, the result is a neutral (default) value having the same length as the argument values, cf. R-2 above. R-8 Binary-string evaluation occurs when the argument values have a binary string type, and the argument is not a hexadecimal literal, bit literal, or NULL literal. Numeric evaluation occurs otherwise, with argument value conversion to unsigned 64-bit integers as necessary. R-9 BIT_XXX window functions permit binary string type arguments (BINARY, VARBINARY, and the BLOB types) and return a value of like type. R-10 Nonbinary string arguments are converted to BIGINT and processed as such.
To be optimized as other aggregate function like SUM, AVG: using incremental evaluation: inversion of row(s) leaving the window frame, and adding contribution of new row(s) entering the frame. We will maintain counters for each bit position of 1s (or 0s as the case may be) to be able to evaluate the new value under inversion: For AND we need to keep track of the number of 0s in the frame: if the inverted rows contains a 0, we decrement the zeros counter: if it reaches 0, we know the new value is true, if not it is (still) false. As similar technique is used for OR, except we count 1s instead. XOR inversion is trivially the same as addition.
Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.