@terascope/data-mate / function-configs/sql-helpers

function-configs/sql-helpers

Variables

HAS_ASTRAL

const HAS_ASTRAL: "[\x{10000}-\x{10FFFF}]" = '[\x{10000}-\x{10FFFF}]'

Defined in: data-mate/src/function-configs/sql-helpers.ts:102

Values that contain a code point outside the BMP, as an RE2 class.

A guard for functions whose JavaScript implementation counts UTF-16 code units while SQL counts characters. truncate with size 3 over four thumbs-up emoji returns '\u{1F44D}\ud83d' (one emoji and a lone surrogate) in JavaScript, where substring returns three whole emoji. isLength with size 5 counts five emoji as length 10. SQL cannot reproduce either, so astral input goes to the UDF.

---

JS_WHITESPACE

const JS_WHITESPACE: string

Defined in: data-mate/src/function-configs/sql-helpers.ts:30

Every code point String.prototype.trim strips: ECMAScript WhiteSpace and LineTerminator.

In order: TAB, LF, VT, FF, CR, SPACE, NBSP, the Zs category (OGHAM SPACE MARK, EN QUAD through HAIR SPACE, NARROW NO-BREAK SPACE, MEDIUM MATHEMATICAL SPACE, IDEOGRAPHIC SPACE), the line and paragraph separators, and ZERO WIDTH NO-BREAK SPACE.

DuckDB's one-argument trim(x) strips only spaces, so TAB + " x " + LF is "x" in JavaScript and unchanged in SQL. Passing this set as the second argument makes them agree.

U+0085 (NEL) is left out on purpose: it is category Cc, so JavaScript does not trim it.

---

NEEDS_GRAPHEME_REVERSE

const NEEDS_GRAPHEME_REVERSE: "[\p{M}\x{200D}\x{10000}-\x{10FFFF}]" = '[\p{M}\x{200D}\x{10000}-\x{10FFFF}]'

Defined in: data-mate/src/function-configs/sql-helpers.ts:79

Values whose reversal needs grapheme segmentation, as an RE2 class: an astral code point, a zero-width joiner, or a combining mark. The same set as NEEDS_SEGMENTATION in reverse.ts.

---

NUMERIC_FIELD_TYPES

const NUMERIC_FIELD_TYPES: readonly FieldType[]

Defined in: data-mate/src/function-configs/sql-helpers.ts:146

The numeric field types.

---

STRING_FIELD_TYPES

const STRING_FIELD_TYPES: readonly FieldType[]

Defined in: data-mate/src/function-configs/sql-helpers.ts:141

The string-shaped field types, as validateAccepts treats them.

Functions

allowsNumericArgs()

allowsNumericArgs<T>(...names): (args) => boolean

Defined in: data-mate/src/function-configs/sql-helpers.ts:134

An applies for optional arguments that must be numbers when present.

inNumberRange and isLength build their expression from whichever bounds were given, so only a present non-number is a problem.

Type Parameters

Type Parameter
T extends Record<string, any>

Parameters

Parameter	Type
...names	readonly string[]

Returns

(args) => boolean

---

finiteOrNull()

finiteOrNull(expression): string

Defined in: data-mate/src/function-configs/sql-helpers.ts:57

runMathFn's rule in SQL: a non-finite result becomes null.

Every numeric transform goes through runMathFn, which returns null when the result is NaN or infinite, so log(0) and an overflow give null. A bare SQL call could return nan or -inf instead.

Parameters

Parameter	Type
expression	string

Returns

string

---

inDomain()

inDomain(condition, native): string

Defined in: data-mate/src/function-configs/sql-helpers.ts:89

Returns null instead of calling native when condition is false.

DuckDB throws where JavaScript returns NaN: sqrt(-1) raises Out of Range Error, and ln(0) and ln(-1) raise errors too, while Math.sqrt(-1) is NaN and runMathFn turns it into null. An unguarded call would abort the query, so the check must come before the call. finiteOrNull checks the result afterwards, which is too late.

Parameters

Parameter	Type
condition	string
native	string

Returns

string

---

isAsciiSql()

isAsciiSql(value): string

Defined in: data-mate/src/function-configs/sql-helpers.ts:46

True when every code point of value is below 128.

length() counts characters and strlen() counts bytes, so they are equal exactly when the string is ASCII. (octet_length has no VARCHAR overload.)

Case conversion depends on this guard. JavaScript uses full Unicode case mapping and DuckDB uses simple mapping, so they disagree outside ASCII: 'ß'.toUpperCase() is 'SS' in JavaScript and 'ẞ' in SQL, and lower('İstanbul') keeps a combining dot in JavaScript but drops it in SQL. Within ASCII they agree.

Parameters

Parameter	Type
value	string

Returns

string

---

literalMatchesColumn()

literalMatchesColumn(value, type): boolean

Defined in: data-mate/src/function-configs/sql-helpers.ts:177

Whether a FieldType.Any argument is the same kind of value as the column it is compared or coalesced with.

SQL allows mixing them (coalesce(varchar_col, 5) casts), but DuckDB's cast and JavaScript's may not turn 5 into the same string. Only same-kind pairs run as SQL; the rest keep the UDF.

Parameters

Parameter	Type
value	unknown
type	FieldType

Returns

boolean

---

needsNumericArgs()

needsNumericArgs<T>(...names): (args) => boolean

Defined in: data-mate/src/function-configs/sql-helpers.ts:122

An applies for an expression that inserts all of these arguments as numbers.

Use it when the arguments are required, as for add, pow and truncate. subtract uses it too: it does not declare its argument required, but its expression cannot handle a missing one.

Type Parameters

Type Parameter
T extends Record<string, any>

Parameters

Parameter	Type
...names	readonly string[]

Returns

(args) => boolean

---

primitiveLiteral()

primitiveLiteral(value): string | null

Defined in: data-mate/src/function-configs/sql-helpers.ts:163

A SQL literal for an argument declared FieldType.Any, or null when it has none.

setDefault and equals take any value, but only primitives have a literal form. An object or array would need the structural comparison that isDeepEqual does, so it keeps the UDF; the caller's applies checks for null.

Parameters

Parameter	Type
value	unknown

Returns

string | null

---

sqlLiteral()

sqlLiteral(value): string

Defined in: sql-builder/dist/src/quoting.d.ts:30

Quote a value as a SQL string literal.

Doubling single quotes is the only escaping needed. DuckDB and PostgreSQL (with standard_conforming_strings on, the default since 9.1) treat a backslash in a single-quoted string as an ordinary character. An engine that does not can override SQLDialect.stringLiteral.

Do not use core-utils' jsStringEscape here: it escapes for JavaScript, so it would change C:\path and would not escape the single quote.

Parameters

Parameter	Type
value	string

Returns

string

---

withinIntegerRange()

withinIntegerRange(value, native): string

Defined in: data-mate/src/function-configs/sql-helpers.ts:71

Starts a CASE that uses native when value fits in an Integer output. The caller adds the ELSE branch.

Used by ceil, floor and round, whose output_type is Integer. Outside that range the UDF path returns a BIGINT rendered as a string while SQL returns the true value, so those values go to the UDF to keep the results identical.

+ 0 turns -0 into 0, as the UDF path's conversion does.

Parameters

Parameter	Type
value	string
native	string

Returns

string