Skip to content

[SQL] Support for OVER window queries with UNSIGNED and UUID NOT NULL sort orders #5904

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.

Sign up for GitHub

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

Open
mihaibudiu wants to merge 1 commit into
base: main
Choose a base branch
from
Open

[SQL] Support for OVER window queries with UNSIGNED and UUID NOT NULL sort orders #5904

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
1 commit
Select commit
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
816 changes: 803 additions & 13 deletions crates/sqllib/src/aggregates.rs
View file
Open in desktop

Large diffs are not rendered by default.

47 changes: 47 additions & 0 deletions crates/sqllib/src/binary.rs
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -518,3 +518,50 @@ pub fn bin2utf8N(source: Option<ByteArray>) -> Option<SqlString> {
Some(bytes) => bin2utf8_(bytes),
}
}

#[doc(hidden)]
pub fn binary_to_u8(b: ByteArray) -> u8 {
assert!(b.length() <= 1);
b.data[0]
}

#[doc(hidden)]
pub fn binary_to_u16(b: ByteArray) -> u16 {
assert!(b.length() <= 2);
let mut buf = [0u8; 2];
buf[2 - b.length()..].copy_from_slice(&b.data);
u16::from_be_bytes(buf)
}

#[doc(hidden)]
pub fn binary_to_u32(b: ByteArray) -> u32 {
assert!(b.length() <= 4);
let mut buf = [0u8; 4];
buf[4 - b.length()..].copy_from_slice(&b.data);
u32::from_be_bytes(buf)
}

#[doc(hidden)]
pub fn binary_to_u64(b: ByteArray) -> u64 {
assert!(b.length() <= 8);
let mut buf = [0u8; 8];
buf[8 - b.length()..].copy_from_slice(&b.data);
u64::from_be_bytes(buf)
}

#[doc(hidden)]
pub fn binary_to_u128(b: ByteArray) -> u128 {
assert!(b.length() <= 16);
let mut buf = [0u8; 16];
buf[16 - b.length()..].copy_from_slice(&b.data);
u128::from_be_bytes(buf)
}

#[test]
pub fn testBinaryToInteger() {
let bin = ByteArray::new(&[0x12, 0x34]);
assert_eq!(0x1234, binary_to_u16(bin.clone()));
assert_eq!(0x1234, binary_to_u32(bin.clone()));
assert_eq!(0x1234, binary_to_u64(bin.clone()));
assert_eq!(0x1234, binary_to_u128(bin));
}
28 changes: 14 additions & 14 deletions crates/sqllib/src/interval.rs
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -128,55 +128,55 @@ pub fn abs_ShortInterval(value: ShortInterval) -> ShortInterval {
some_polymorphic_function1!(abs, ShortInterval, ShortInterval, ShortInterval);

#[doc(hidden)]
/// This function is used in rolling window computations, which require all
/// values to be expressed using unsigned types.
/// This function is used in rolling window computations, to compute a window bound.
/// Window bounds are always positive.
pub fn to_bound_ShortInterval_ShortInterval_u128(value: &ShortInterval) -> u128 {
value.microseconds as u128
}

#[doc(hidden)]
/// This function is used in rolling window computations, which require all
/// values to be expressed using unsigned types.
/// This function is used in rolling window computations, to compute a window bound.
/// Window bounds are always positive.
pub fn to_bound_ShortInterval_Date_u128(value: &ShortInterval) -> u128 {
// express value in days
(value.microseconds / 1_000_000_i64 / 86400) as u128
}

#[doc(hidden)]
/// This function is used in rolling window computations, which require all
/// values to be expressed using unsigned types.
/// This function is used in rolling window computations, to compute a window bound.
/// Window bounds are always positive.
pub fn to_bound_ShortInterval_Date_u64(value: &ShortInterval) -> u64 {
// express value in days
(value.microseconds / 1_000_000_i64 / 86400) as u64
}

#[doc(hidden)]
/// This function is used in rolling window computations, which require all
/// values to be expressed using unsigned types.
/// This function is used in rolling window computations, to compute a window bound.
/// Window bounds are always positive.
pub fn to_bound_ShortInterval_Timestamp_u128(value: &ShortInterval) -> u128 {
// express value in milliseconds
value.microseconds as u128
}

#[doc(hidden)]
/// This function is used in rolling window computations, which require all
/// values to be expressed using unsigned types.
/// This function is used in rolling window computations, to compute a window bound.
/// Window bounds are always positive.
pub fn to_bound_ShortInterval_Timestamp_u64(value: &ShortInterval) -> u64 {
// express value in milliseconds
value.microseconds as u64
}

#[doc(hidden)]
/// This function is used in rolling window computations, which require all
/// values to be expressed using unsigned types.
/// This function is used in rolling window computations, to compute a window bound.
/// Window bounds are always positive.
pub fn to_bound_ShortInterval_Time_u128(value: &ShortInterval) -> u128 {
// express value in nanoseconds
(value.microseconds * 1_000) as u128
}

#[doc(hidden)]
/// This function is used in rolling window computations, which require all
/// values to be expressed using unsigned types.
/// This function is used in rolling window computations, to compute a window bound.
/// Window bounds are always positive.
pub fn to_bound_ShortInterval_Time_u64(value: &ShortInterval) -> u64 {
// express value in nanoseconds
(value.microseconds * 1_000) as u64
Expand Down
10 changes: 10 additions & 0 deletions crates/sqllib/src/uuid.rs
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -171,3 +171,13 @@ impl Display for Uuid {
Display::fmt(&self.value, f)
}
}

#[doc(hidden)]
pub fn uuid_to_u128(u: Uuid) -> u128 {
u128::from_be_bytes(*u.to_bytes())
}

#[doc(hidden)]
pub fn u128_to_uuid(n: u128) -> Uuid {
Uuid::from_bytes(n.to_be_bytes())
}
24 changes: 15 additions & 9 deletions ...compiler/src/main/java/org/dbsp/sqlCompiler/compiler/backend/rust/ToRustInnerVisitor.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -373,19 +373,23 @@ void codegen(DBSPUnsignedWrapExpression.TypeSequence sequence) {
this.builder.append("<");
// In the type parameter we do not put the Option<>
sequence.dataType.withMayBeNull(false).accept(this);
this.builder.append(", ");
sequence.dataConvertedType.accept(this);
this.builder.append(", ");
sequence.intermediateType.accept(this);
this.builder.append(", ");
sequence.unsignedType.accept(this);
if (sequence.dataConvertedType.signed) {
this.builder.append(", ");
sequence.dataConvertedType.accept(this);
this.builder.append(", ");
sequence.intermediateType.accept(this);
}
if (!sequence.unsignedType.sameType(sequence.dataType)) {
this.builder.append(", ");
sequence.unsignedType.accept(this);
}
this.builder.append(">");
}

@Override
public VisitDecision preorder(DBSPUnsignedWrapExpression expression) {
this.push(expression);
this.builder.append("UnsignedWrapper")
this.builder.append(DBSPUnsignedWrapExpression.RUST_IMPLEMENTATION)
.append("::")
.append(expression.getMethod())
.append("::");
Expand Down Expand Up @@ -415,7 +419,7 @@ public VisitDecision preorder(DBSPLazyExpression expression) {
@Override
public VisitDecision preorder(DBSPUnsignedUnwrapExpression expression) {
this.push(expression);
this.builder.append("UnsignedWrapper")
this.builder.append(DBSPUnsignedWrapExpression.RUST_IMPLEMENTATION)
.append("::")
.append(expression.getMethod())
.append("::");
Expand Down Expand Up @@ -1869,7 +1873,9 @@ public VisitDecision preorder(DBSPUnaryExpression expression) {
this.pop(expression);
return VisitDecision.STOP;
} else if (expression.opcode == DBSPOpcode.INTEGER_TO_SHORT_INTERVAL ||
expression.opcode == DBSPOpcode.SHORT_INTERVAL_TO_INTEGER) {
expression.opcode == DBSPOpcode.SHORT_INTERVAL_TO_INTEGER ||
expression.opcode == DBSPOpcode.INTEGER_TO_UUID ||
expression.opcode == DBSPOpcode.UUID_TO_INTEGER) {
this.builder.append(expression.opcode.toString())
.append("(");
expression.source.accept(this);
Expand Down
16 changes: 13 additions & 3 deletions ...iler/src/main/java/org/dbsp/sqlCompiler/compiler/frontend/aggregates/RangeAggregates.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -41,21 +41,19 @@
import org.dbsp.sqlCompiler.ir.expression.literal.DBSPLiteral;
import org.dbsp.sqlCompiler.ir.type.DBSPType;
import org.dbsp.sqlCompiler.ir.type.DBSPTypeCode;
import org.dbsp.sqlCompiler.ir.type.IsDateType;
import org.dbsp.sqlCompiler.ir.type.IsNumericType;
import org.dbsp.sqlCompiler.ir.type.IsTimeRelatedType;
import org.dbsp.sqlCompiler.ir.type.derived.DBSPTypeRawTuple;
import org.dbsp.sqlCompiler.ir.type.derived.DBSPTypeTuple;
import org.dbsp.sqlCompiler.ir.type.primitive.DBSPTypeBaseType;
import org.dbsp.sqlCompiler.ir.type.primitive.DBSPTypeDate;
import org.dbsp.sqlCompiler.ir.type.primitive.DBSPTypeDecimal;
import org.dbsp.sqlCompiler.ir.type.primitive.DBSPTypeDouble;
import org.dbsp.sqlCompiler.ir.type.primitive.DBSPTypeInteger;
import org.dbsp.sqlCompiler.ir.type.primitive.DBSPTypeLongInterval;
import org.dbsp.sqlCompiler.ir.type.primitive.DBSPTypeReal;
import org.dbsp.sqlCompiler.ir.type.primitive.DBSPTypeShortInterval;
import org.dbsp.sqlCompiler.ir.type.primitive.DBSPTypeTime;
import org.dbsp.sqlCompiler.ir.type.primitive.DBSPTypeTimestamp;
import org.dbsp.sqlCompiler.ir.type.primitive.DBSPTypeUuid;
import org.dbsp.sqlCompiler.ir.type.user.DBSPTypeIndexedZSet;
import org.dbsp.util.Linq;
import org.dbsp.util.Utilities;
Expand Down Expand Up @@ -101,6 +99,7 @@ String intervalConversionFunction(CalciteObject node, DBSPType unsignedType, DBS

// we ignore nullability because window bounds are constants and cannot be null
// BEWARE: this function name structure is hardwired in the monotonicity analysis
// NOTE: This function requires the arguments to be positive
return "to_bound_" +
deltaType.withMayBeNull(false).to(DBSPTypeBaseType.class).shortName() + "_" +
sortType.withMayBeNull(false).to(DBSPTypeBaseType.class).shortName() + "_" +
Expand Down Expand Up @@ -196,6 +195,14 @@ public DBSPSimpleOperator implement(DBSPSimpleOperator input, DBSPSimpleOperator
sortType = DBSPTypeInteger.getType(node, INT64, originalSortType.mayBeNull);
convertToSigned = new DBSPUnaryExpression(
this.node, sortType, DBSPOpcode.SHORT_INTERVAL_TO_INTEGER, var).closure(var);
} else if (originalSortType.is(DBSPTypeUuid.class)) {
if (originalSortType.mayBeNull) {
// This is 128 bits, but we need one more to represent the NULL value.
throw new UnimplementedException("OVER currently cannot sort on columns with type UUID NULL. Can you convert the type to UUID NOT NULL?", 457, node);
}
sortType = DBSPTypeInteger.getType(node, UINT128, originalSortType.mayBeNull);
convertToSigned = new DBSPUnaryExpression(
this.node, sortType, DBSPOpcode.UUID_TO_INTEGER, var.applyClone()).closure(var);
} else {
sortType = originalSortType;
}
Expand Down Expand Up @@ -306,6 +313,9 @@ public DBSPSimpleOperator implement(DBSPSimpleOperator input, DBSPSimpleOperator
} else if (originalSortType.is(DBSPTypeShortInterval.class)) {
unwrap = new DBSPUnaryExpression(this.node, originalSortType,
DBSPOpcode.INTEGER_TO_SHORT_INTERVAL, unwrap);
} else if (originalSortType.is(DBSPTypeUuid.class)) {
unwrap = new DBSPUnaryExpression(this.node, originalSortType,
DBSPOpcode.INTEGER_TO_UUID, unwrap);
}

DBSPExpression ixKey = var.field(0).deref();
Expand Down
4 changes: 3 additions & 1 deletion .../main/java/org/dbsp/sqlCompiler/compiler/visitors/monotone/MonotoneTransferFunctions.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -743,7 +743,9 @@ public void postorder(DBSPUnsignedUnwrapExpression expression) {
static final Set<DBSPOpcode> monotoneUnary = Set.of(
DBSPOpcode.UNARY_PLUS, DBSPOpcode.TYPEDBOX,
DBSPOpcode.DECIMAL_TO_INTEGER, DBSPOpcode.INTEGER_TO_DECIMAL,
DBSPOpcode.SHORT_INTERVAL_TO_INTEGER, DBSPOpcode.INTEGER_TO_SHORT_INTERVAL);
DBSPOpcode.SHORT_INTERVAL_TO_INTEGER, DBSPOpcode.INTEGER_TO_SHORT_INTERVAL,
DBSPOpcode.UUID_TO_INTEGER, DBSPOpcode.INTEGER_TO_UUID
);

@Override
public void postorder(DBSPUnaryExpression expression) {
Expand Down
5 changes: 4 additions & 1 deletion ...sp-compiler/SQL-compiler/src/main/java/org/dbsp/sqlCompiler/ir/expression/DBSPOpcode.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -25,6 +25,9 @@ public enum DBSPOpcode {
// Lossless, order-preserving conversion between short interval and INTEGER, used for range aggregates
SHORT_INTERVAL_TO_INTEGER("short_interval_to_integer", false),
INTEGER_TO_SHORT_INTERVAL("integer_to_short_interval", false),
// Lossless, order-preserving conversion between UUID and INTEGER, used for range aggregates
UUID_TO_INTEGER("uuid_to_u128", false),
INTEGER_TO_UUID("u128_to_uuid", false),

// Binary operations
ADD("+", false),
Expand Down Expand Up @@ -132,7 +135,7 @@ public boolean isStrict() {
AGG_MAX, AGG_XOR, AGG_OR, AGG_AND, IS_DISTINCT, CONCAT, MIN, MAX, OR, AND, IS_NOT_FALSE, IS_NOT_TRUE,
AGG_MAX1, AGG_MIN1, INDICATOR -> false;
case NEG, INTERVAL_DIV, INTERVAL_MUL, DECIMAL_TO_INTEGER, INTEGER_TO_DECIMAL,
SHORT_INTERVAL_TO_INTEGER, INTEGER_TO_SHORT_INTERVAL,
SHORT_INTERVAL_TO_INTEGER, INTEGER_TO_SHORT_INTERVAL, INTEGER_TO_UUID, UUID_TO_INTEGER,
RUST_INDEX, VARIANT_INDEX, MAP_INDEX,
SQL_INDEX, XOR, BW_OR, MUL_WEIGHT, BW_AND, GTE, LTE, GT, LT, NEQ, EQ, MOD, DIV_NULL, DIV, MUL, SUB,
ADD, TYPEDBOX, IS_TRUE, IS_FALSE, NOT, UNARY_PLUS -> true;
Expand Down
4 changes: 3 additions & 1 deletion ...er/SQL-compiler/src/main/java/org/dbsp/sqlCompiler/ir/expression/DBSPUnaryExpression.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -80,7 +80,9 @@ public IIndentStream toString(IIndentStream builder) {
this.opcode == DBSPOpcode.INTEGER_TO_DECIMAL ||
this.opcode == DBSPOpcode.SHORT_INTERVAL_TO_INTEGER ||
this.opcode == DBSPOpcode.INTEGER_TO_SHORT_INTERVAL ||
this.opcode == DBSPOpcode.REINTERPRET) {
this.opcode == DBSPOpcode.REINTERPRET ||
this.opcode == DBSPOpcode.INTEGER_TO_UUID ||
this.opcode == DBSPOpcode.UUID_TO_INTEGER) {
return builder.append(this.opcode.toString())
.append("(")
.append(this.source)
Expand Down
9 changes: 6 additions & 3 deletions ...mpiler/src/main/java/org/dbsp/sqlCompiler/ir/expression/DBSPUnsignedUnwrapExpression.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -11,7 +11,7 @@
import org.dbsp.util.IIndentStream;
import org.dbsp.util.Utilities;

/** This is the dual of the UnsignedWrapExpression: it unwraps an unsigned number */
/** This is the dual of {@link DBSPUnsignedWrapExpression}: it unwraps an unsigned number */
public final class DBSPUnsignedUnwrapExpression extends DBSPExpression {
public final DBSPExpression source;
public final DBSPUnsignedWrapExpression.TypeSequence sequence;
Expand Down Expand Up @@ -51,12 +51,15 @@ public boolean sameFields(IDBSPInnerNode other) {
}

public String getMethod() {
return this.getType().mayBeNull ? "to_signed_option" : "to_signed";
if (this.sequence.dataConvertedType.signed)
return this.getType().mayBeNull ? "to_signed_option" : "to_signed";
else
return this.getType().mayBeNull ? "to_unsigned_option" : "to_unsigned";
}

@Override
public IIndentStream toString(IIndentStream builder) {
return builder.append("UnsignedWrapper")
return builder.append(DBSPUnsignedWrapExpression.RUST_IMPLEMENTATION)
.append("::")
.append(this.getMethod())
.append("(")
Expand Down
29 changes: 20 additions & 9 deletions ...compiler/src/main/java/org/dbsp/sqlCompiler/ir/expression/DBSPUnsignedWrapExpression.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -2,7 +2,7 @@

import com.fasterxml.jackson.databind.JsonNode;
import org.dbsp.sqlCompiler.compiler.backend.JsonDecoder;
import org.dbsp.sqlCompiler.compiler.errors.InternalCompilerError;
import org.dbsp.sqlCompiler.compiler.errors.UnimplementedException;
import org.dbsp.sqlCompiler.compiler.frontend.calciteObject.CalciteObject;
import org.dbsp.sqlCompiler.compiler.visitors.VisitDecision;
import org.dbsp.sqlCompiler.compiler.visitors.inner.EquivalenceContext;
Expand All @@ -15,8 +15,10 @@
import org.dbsp.util.Utilities;

/** An unsigned wrap expression just wraps a signed integer into an unsigned one
* preserving order. */
* preserving order. See also {@link DBSPUnsignedUnwrapExpression} */
public final class DBSPUnsignedWrapExpression extends DBSPExpression {
public static final String RUST_IMPLEMENTATION = "UnsignedWrappers";

public static class TypeSequence {
public final DBSPType dataType;
public final DBSPTypeInteger dataConvertedType;
Expand All @@ -27,13 +29,18 @@ public static class TypeSequence {
this.dataType = dataType;
this.dataConvertedType = getInitialIntegerType(this.dataType);
if (!this.dataConvertedType.signed) {
throw new InternalCompilerError("The data type encoding " + dataType +
" must be signed, but it is " + this.dataConvertedType);
int width = this.dataConvertedType.getWidth();
int useWidth = dataType.mayBeNull ? width * 2 : width;
this.intermediateType = new DBSPTypeInteger(dataType.getNode(), useWidth, false, false);
this.unsignedType = new DBSPTypeInteger(dataType.getNode(), useWidth, false, false);
} else {
int width = this.dataConvertedType.getWidth();
if (width > 64)
if (width > 64) {
throw new UnimplementedException("Not yet supported: OVER sorting over " + dataType,
dataType.getNode());
// This may cause runtime overflows, but this is the best we can do
width = 64;
// width = 64;
}
this.intermediateType = new DBSPTypeInteger(dataType.getNode(), width * 2, true, false);
this.unsignedType = new DBSPTypeInteger(dataType.getNode(), width * 2, false, false);
}
Expand All @@ -50,7 +57,8 @@ static DBSPTypeInteger getInitialIntegerType(DBSPType sourceType) {
case INT8, INT16, INT32, INT64, INT128 -> sourceType.withMayBeNull(false).to(DBSPTypeInteger.class);
case DATE -> DBSPTypeInteger.getType(sourceType.getNode(), DBSPTypeCode.INT32, false);
case TIMESTAMP, TIME -> DBSPTypeInteger.getType(sourceType.getNode(), DBSPTypeCode.INT64, false);
default -> throw new InternalCompilerError("Not yet supported wrappers for " + sourceType, sourceType.getNode());
case UINT8, UINT16, UINT32, UINT64, UINT128 -> sourceType.withMayBeNull(false).to(DBSPTypeInteger.class);
default -> throw new UnimplementedException("OVER sorting over " + sourceType, sourceType.getNode());
};
}
}
Expand Down Expand Up @@ -96,12 +104,15 @@ public boolean sameFields(IDBSPInnerNode other) {
}

public String getMethod() {
return this.source.getType().mayBeNull ? "from_option" : "from_signed";
if (this.sequence.dataConvertedType.signed)
return this.source.getType().mayBeNull ? "from_option" : "from_signed";
else
return this.source.getType().mayBeNull ? "from_unsigned_option" : "from_unsigned";
}

@Override
public IIndentStream toString(IIndentStream builder) {
return builder.append("UnsignedWrapper")
return builder.append(RUST_IMPLEMENTATION)
.append("::")
.append(this.getMethod())
.append("(")
Expand Down
Loading
Loading