LucidDbSqlConformance

From Eigenpedia

Jump to: navigation, search

This page notes a few cases where LucidDB's SQL implementation diverges from SQL:2003.

Contents

Aggregated types of character strings

See this thread for background. Because the SQL:2003 rules lead to onerous behavior in this case, LucidDB instead uses the rule that the result type is VARCHAR unless all of the aggregated types are CHAR of the exact same precision.

Aggregated types of exact numerics

Given types DECIMAL(p1,s1) and DECIMAL(p2,s2), LucidDB derives a union type having precision (p), scale (s) and number of whole digits (d) as follows:

d = max(p1 - s1, p2 - s2)
s = min(max(s1, s2), 19 - d)
p = s + d

This is in contradiction to SQL:2003 Part 2 Section 9.3 Syntax Rule 3.c, which states "If all of the data types in DTS are exact numeric, then the result data type is exact numeric with implementation-defined precision and with scale equal to the maximum of the scales of the data types in DTS." The LucidDB rule is more pragmatic due to the system's limited max precision (under this constraint, it is preferable to preserve the most significant digits instead of the least).

Result type of multiplication of exact numerics

s = min(s1 + s2, 19)
p = p1 + p2
if (p > 19 && s > 6) then s -= min(p - 19, s - 6)
p = min(p, 19)

In this case, LucidDB chooses to preserve some scale (6 digits), but beyond that gives preference to preserving whole digits.

In cases where p1 + p2 >= 19, LucidDB implements the product via double-precision multiplication to avoid the overflow that can occur with integer multiplication. In other cases, it uses integer multiplication to avoid losing the least significant digits (since the mantissa of a double can only represent 15 decimal digits).

Result type of division of exact numerics

d = min(p1 - s1 + s2, 19)
s = max(6, s1 + p2 + 1)
s = min(s, 6)
d = min(d, 19 - s)
p = d + s

Division is always implemented via a double precision operation.

Row Count for UPDATE and MERGE

According to SQL:2003 Part 2 Section 22.1, General Rule 2.g cases ii and iii, the number of rows affected by a MERGE or UPDATE should be the number of rows matching the search condition (plus any inserted rows for a MERGE), regardless of whether the updates in the SET clause turn out to have no effect due to the source and target values already matching.

LucidDB does not conform with this rule; instead, its returned rowcount is based on the number of rows actually modified, which is arguably much more useful (especially in an ETL context, where it is common to execute incremental updates which potentially affect every row but actually affect only a few).

Duplicate Source Key Detection During MERGE

See LucidDbUniqueConstraints#Bulk_Load_and_Upsert_Semantics for an explanation of the conformance here.

See Also

Personal tools