WL#966: Implement CHECKSUM and CHECKSUM_AGG functions
Affects: Server-4.1
—
Status: Assigned
We should implement the following checksum functions (row and aggregate) for use in SQL queries, and emulate SQL Server syntax. Microsoft SQL Syntax: ------------------------------------------------- CHECKSUM BINARY_CHECKSUM Returns the checksum value computed over a row of a table, or over a list of expressions. CHECKSUM is intended for use in building hash indices. Syntax CHECKSUM ( * | expression [ ,...n ] ) BINARY_CHECKSUM ( * | expression [ ,...n ] ) Arguments * Specifies that computation is over all the columns of the table. This should work with all types and any combinations of types. The difference between BINARY_CHECKSUM an CHECKSUM is that CHECKSUM(a)=CHECKSUM(b) if a=b (or both are NULLs) - so for strings it's charset and collation issue. As manual says - CHECKSUM is intented for building hash indexes. BINARY_CHECKSUM's are equal is values are identical byte-wise.It is useful for tracking changes. ---------------------------------------------------- CHECKSUM_AGG Returns the checksum of the values in a group. Syntax CHECKSUM_AGG ( [ ALL | DISTINCT ] * | expression [,...]) Arguments ALL Applies the aggregate function to all values. ALL is the default. DISTINCT Specifies that CHECKSUM_AGG return the checksum of unique values. expression Is a constant, column, or function, and any combination of arithmetic, bitwise and string operators. Aggregate functions and subqueries are not allowed. This function is aggregate version of CHECKSUM. As aggreate binary checksum Microsoft suggests CHECKSUM_AGG(BINARY_CHECKSUM(...)) MySQL Remarks ------------- I don't really see the need of charset-aware CHECKSUM. Probably we'll just do it binary. Also BINARY_CHECKSUM() can be added as an alias for CHECKSUM() if necessary. The order of the rows in the table does not affect the result of CHECKSUM_AGG.
Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.