@terascope/sql-builder / dialects/duckdb / DuckDBDialect

Class: DuckDBDialect

Defined in: dialects/duckdb.ts:40

The DuckDB dialect.

It assumes the column types that @terascope/data-types' toDuckDB produces:

xLucene type	DuckDB column
geo-point, geo	STRUCT(lat DOUBLE, lon DOUBLE)
geo-json	GEOMETRY
ip, ip_range	VARCHAR
date	STRUCT(ms BIGINT, tz_offset SMALLINT) - compared on ms

IP queries need the inet extension, which autoloads on first use. Geo queries need the spatial extension, which does not autoload; run LOAD spatial at startup, or a geo query fails with Scalar Function with name "st_within" is not in the catalog.

Extends

• BaseSQLDialect

Constructors

Constructor

new DuckDBDialect(): DuckDBDialect

Returns

DuckDBDialect

Inherited from

BaseSQLDialect.constructor

Properties

Property	Modifier	Type	Default value	Overrides	Defined in
name	readonly	duckdb	SQLDialectName.duckdb	BaseSQLDialect.name	dialects/duckdb.ts:41

Methods

and()

and(expressions): string

Defined in: dialects/base.ts:189

All of the expressions.

Parameters

Parameter	Type
expressions	string[]

Returns

string

Inherited from

BaseSQLDialect.and

---

blockEnd()

protected blockEnd(fieldExpr): string

Defined in: dialects/base.ts:340

Parameters

Parameter	Type
fieldExpr	string

Returns

string

Inherited from

BaseSQLDialect.blockEnd

---

blockStart()

protected blockStart(fieldExpr): string

Defined in: dialects/base.ts:336

The first and last addresses of the block an ip_range column holds.

host() strips the mask. Comparing a masked INET with a bare one also compares the masks, which gives the wrong answer for overlapping blocks.

Parameters

Parameter	Type
fieldExpr	string

Returns

string

Inherited from

BaseSQLDialect.blockStart

---

compare()

compare(fieldExpr, operator, value, fieldType?): string

Defined in: dialects/duckdb.ts:70

One side of a range.

Parameters

Parameter	Type
fieldExpr	string
operator	"gt" | "gte" | "lt" | "lte"
value	unknown
fieldType?	xLuceneFieldType

Returns

string

Overrides

BaseSQLDialect.compare

---

equals()

equals(fieldExpr, value, fieldType?): string

Defined in: dialects/duckdb.ts:63

A date column is STRUCT(ms, tz_offset), so it is compared on ms (epoch milliseconds). An integer comparison also lets Parquet min/max statistics skip row groups.

Parameters

Parameter	Type
fieldExpr	string
value	unknown
fieldType?	xLuceneFieldType

Returns

string

Overrides

BaseSQLDialect.equals

---

exists()

exists(fieldExpr): string

Defined in: dialects/base.ts:174

The field has a value.

Parameters

Parameter	Type
fieldExpr	string

Returns

string

Inherited from

BaseSQLDialect.exists

---

fieldRef()

fieldRef(field): string

Defined in: dialects/duckdb.ts:50

A dotted xLucene field is a STRUCT path.

toDuckDB stores dotted DataType paths as nested STRUCTs, so foo.bar is member bar of column foo. Quoting each part is safe because DuckDB matches quoted identifiers case-insensitively (unlike PostgreSQL).

Parameters

Parameter	Type
field	string

Returns

string

Overrides

BaseSQLDialect.fieldRef

---

geoContainsPoint()

geoContainsPoint(fieldExpr, point, isPointColumn): string

Defined in: dialects/duckdb.ts:168

Uses ST_Intersects rather than ST_Contains so that a point on a polygon's edge matches. Elasticsearch's intersects relation and geoContainsFP both include the boundary; DuckDB's ST_Contains excludes it.

Parameters

Parameter	Type
fieldExpr	string
point	GeoPoint
isPointColumn	boolean

Returns

string

Overrides

BaseSQLDialect.geoContainsPoint

---

geoPointDistance()

geoPointDistance(fieldExpr, point): string

Defined in: dialects/duckdb.ts:121

Distance in meters. ST_Distance_Sphere expects points in (latitude, longitude) order, the reverse of the (x, y) order used elsewhere in this file. Swapping them returns a wrong distance rather than an error.

Parameters

Parameter	Type
fieldExpr	string
point	GeoPoint

Returns

string

Overrides

BaseSQLDialect.geoPointDistance

---

geoPointEquals()

protected geoPointEquals(fieldExpr, point): string

Defined in: dialects/base.ts:287

A geo-point column equal to one point, which is what a bare term on it means.

Parameters

Parameter	Type
fieldExpr	string
point	GeoPoint

Returns

string

Inherited from

BaseSQLDialect.geoPointEquals

---

geoPointInBoundingBox()

geoPointInBoundingBox(fieldExpr, topLeft, bottomRight): string

Defined in: dialects/duckdb.ts:136

A bounding box as two inclusive range checks, which needs no spatial extension.

ST_Within and ST_Contains exclude the boundary, while geoBox includes it.

Parameters

Parameter	Type
fieldExpr	string
topLeft	GeoPoint
bottomRight	GeoPoint

Returns

string

Overrides

BaseSQLDialect.geoPointInBoundingBox

---

geoPointParts()

geoPointParts(fieldExpr): SQLGeoPointColumn

Defined in: dialects/duckdb.ts:109

The lat and lon members of a geo-point column.

Parameters

Parameter	Type
fieldExpr	string

Returns

SQLGeoPointColumn

Overrides

BaseSQLDialect.geoPointParts

---

geoPointWithinDistance()

geoPointWithinDistance(fieldExpr, point, meters): string

Defined in: dialects/duckdb.ts:127

A geo-point field within distance meters of a point.

Parameters

Parameter	Type
fieldExpr	string
point	GeoPoint
meters	number

Returns

string

Overrides

BaseSQLDialect.geoPointWithinDistance

---

geoRelation()

geoRelation(fieldExpr, shape, relation, isPointColumn): string

Defined in: dialects/duckdb.ts:146

A geo field related to a shape.

isPointColumn says whether the field holds a point or a shape, which decides how the column becomes a geometry.

Parameters

Parameter	Type
fieldExpr	string
shape	GeoShape
relation	GeoShapeRelation
isPointColumn	boolean

Returns

string

Overrides

BaseSQLDialect.geoRelation

---

inetLiteral()

protected inetLiteral(value): string

Defined in: dialects/duckdb.ts:195

A literal IP address or CIDR block.

Parameters

Parameter	Type
value	string

Returns

string

Overrides

BaseSQLDialect.inetLiteral

---

ipComparison()

protected ipComparison(fieldExpr, operator, value): string

Defined in: dialects/duckdb.ts:186

One side of an IP range comparison, wrapped in IS TRUE.

DuckDB's optimizer rewrites ip >= a AND ip <= b into ip BETWEEN a AND b, and BETWEEN does not support INET, so the query fails with Invalid type for BETWEEN. Wrapping each comparison in IS TRUE prevents that rewrite. It does not change the result: a NULL comparison becomes FALSE, and WHERE drops both the same way.

ip_range queries do not need this, because they compare two different expressions (the block's first and last address).

Parameters

Parameter	Type
fieldExpr	string
operator	string
value	string

Returns

string

Overrides

BaseSQLDialect.ipComparison

---

ipEquals()

ipEquals(fieldExpr, address): string

Defined in: dialects/base.ts:205

Equality over the mapped form, so ::ffff:8.8.8.8 and 8.8.8.8 are one address.

Parameters

Parameter	Type
fieldExpr	string
address	string

Returns

string

Inherited from

BaseSQLDialect.ipEquals

---

ipInCIDR()

ipInCIDR(fieldExpr, cidr): string

Defined in: dialects/base.ts:198

CIDR containment, comparing both sides in IPv4-mapped IPv6 form (see mappedInet).

Parameters

Parameter	Type
fieldExpr	string
cidr	string

Returns

string

Inherited from

BaseSQLDialect.ipInCIDR

---

ipRangeContains()

ipRangeContains(fieldExpr, value): string

Defined in: dialects/base.ts:220

An ip_range column containing a queried address or block.

Here the column holds the block and the query holds the address, the reverse of ipInCIDR. It is written as an overlap check against the block's endpoints rather than with <<=, because <<= returns false across address families ('::ffff:10.0.0.1'::INET <<= '10.0.0.0/30'::INET is FALSE), while the mapped endpoints compare the way Elasticsearch does.

Parameters

Parameter	Type
fieldExpr	string
value	string

Returns

string

Inherited from

BaseSQLDialect.ipRangeContains

---

ipRangeIntersects()

ipRangeIntersects(fieldExpr, start?, end?): string

Defined in: dialects/base.ts:240

Two blocks overlap when each one starts at or before the other one ends.

An exclusive bound changes the operator, not the value: {a TO b} overlaps a stored block only if the block starts before b and ends after a, so a block that only touches a bound does not match.

Parameters

Parameter	Type
fieldExpr	string
start?	SQLIPRangeBound
end?	SQLIPRangeBound

Returns

string

Inherited from

BaseSQLDialect.ipRangeIntersects

---

join()

protected join(expressions, operator): string

Defined in: dialects/base.ts:361

Parameters

Parameter	Type
expressions	string[]
operator	"AND" | "OR"

Returns

string

Inherited from

BaseSQLDialect.join

---

limitOffset()

limitOffset(size?, offset?): string

Defined in: dialects/base.ts:101

LIMIT/OFFSET, which DuckDB and PostgreSQL write the same way.

Both numbers come from the request and are written into the statement, so they are validated as non-negative integers to prevent SQL injection.

Parameters

Parameter	Type
size?	number
offset?	number

Returns

string

Inherited from

BaseSQLDialect.limitOffset

---

literal()

literal(value, fieldType?): string

Defined in: dialects/base.ts:64

A value rendered as a literal of the given xLucene field type.

Parameters

Parameter	Type
value	unknown
fieldType?	xLuceneFieldType

Returns

string

Inherited from

BaseSQLDialect.literal

---

mappedInet()

protected mappedInet(fieldExpr): string

Defined in: dialects/base.ts:306

The column as an address that compares correctly with any other address.

Both engines order INET by family first, so every IPv4 address sorts before every IPv6 one. Elasticsearch stores every ip as 128 bits with IPv4 mapped into IPv6 and orders numerically. Mapping IPv4 values into ::ffff: form puts everything in one family, so ranges compare numerically and ::ffff:8.8.8.8 matches a stored 8.8.8.8, as in Elasticsearch.

Parameters

Parameter	Type
fieldExpr	string

Returns

string

Inherited from

BaseSQLDialect.mappedInet

---

mappedInetLiteral()

protected mappedInetLiteral(value): string

Defined in: dialects/base.ts:319

The same mapping for a literal.

A CIDR prefix length grows by 96 bits with the mapping, so 8.8.8.0/24 becomes ::ffff:8.8.8.0/120.

Parameters

Parameter	Type
value	string

Returns

string

Inherited from

BaseSQLDialect.mappedInetLiteral

---

matchAll()

matchAll(): string

Defined in: dialects/base.ts:120

TRUE - an empty query matches everything.

Returns

string

Inherited from

BaseSQLDialect.matchAll

---

matchNone()

matchNone(): string

Defined in: dialects/base.ts:124

FALSE - a query that cannot match anything.

Returns

string

Inherited from

BaseSQLDialect.matchNone

---

not()

not(expression): string

Defined in: dialects/base.ts:185

Negate an expression so a missing field matches, as in Elasticsearch.

Elasticsearch's must_not matches a document whose field is absent. In SQL, NOT (col = 'x') is NULL when col is NULL, and WHERE drops that row, so the COALESCE turns the NULL into TRUE.

Parameters

Parameter	Type
expression	string

Returns

string

Inherited from

BaseSQLDialect.not

---

or()

or(expressions): string

Defined in: dialects/base.ts:193

Any of the expressions.

Parameters

Parameter	Type
expressions	string[]

Returns

string

Inherited from

BaseSQLDialect.or

---

orderBy()

orderBy(sort?): string

Defined in: dialects/base.ts:116

The ORDER BY terms, with direction and null placement both written out.

NULLS FIRST / NULLS LAST is standard SQL, so this is shared. It is always written because no engine's default matches DataFrame's null order (see defaultNullOrder).

Parameters

Parameter	Type
sort?	readonly SQLSort[]

Returns

string

Inherited from

BaseSQLDialect.orderBy

---

projection()

projection(readable, all): string

Defined in: dialects/duckdb.ts:91

The SELECT list, rebuilding any column that is only partly readable.

A dotted field is a STRUCT member, not a column. To exclude nested.name the nested column is rebuilt with struct_pack from its readable members only, which matches the shape Elasticsearch's _source filtering returns.

Parameters

Parameter	Type
readable	readonly string[]
all	readonly string[]

Returns

string

Overrides

BaseSQLDialect.projection

---

quoteIdentifier()

quoteIdentifier(name): string

Defined in: dialects/base.ts:54

Quote an identifier for use as a column reference.

Parameters

Parameter	Type
name	string

Returns

string

Inherited from

BaseSQLDialect.quoteIdentifier

---

regexp()

regexp(fieldExpr, value, fieldType?): string

Defined in: dialects/base.ts:169

An anchored regular expression match, as the Elasticsearch regexp query is.

Parameters

Parameter	Type
fieldExpr	string
value	string
fieldType?	xLuceneFieldType

Returns

string

Inherited from

BaseSQLDialect.regexp

---

regexpMatch()

protected regexpMatch(expr, value): string

Defined in: dialects/duckdb.ts:207

regexp_full_match rather than regexp_matches, because Elasticsearch's regexp query is anchored: bar:/h.*o/ matches hello and not xhellox.

Parameters

Parameter	Type
expr	string
value	string

Returns

string

Overrides

BaseSQLDialect.regexpMatch

---

stringLiteral()

stringLiteral(value): string

Defined in: dialects/base.ts:60

A SQL string literal, escaped for this engine.

Parameters

Parameter	Type
value	string

Returns

string

Inherited from

BaseSQLDialect.stringLiteral

---

textContains()

protected textContains(expr, substring): string

Defined in: dialects/duckdb.ts:199

Whether a text expression contains a substring; the engines write this differently.

Parameters

Parameter	Type
expr	string
substring	string

Returns

string

Overrides

BaseSQLDialect.textContains

---

timestampLiteral()

protected timestampLiteral(value): string

Defined in: dialects/base.ts:357

A literal timestamp, always UTC.

Parameters

Parameter	Type
value	unknown

Returns

string

Inherited from

BaseSQLDialect.timestampLiteral

---

toInet()

protected toInet(fieldExpr): string

Defined in: dialects/duckdb.ts:191

The column as an IP address value.

Parameters

Parameter	Type
fieldExpr	string

Returns

string

Overrides

BaseSQLDialect.toInet

---

toText()

toText(fieldExpr): string

Defined in: dialects/duckdb.ts:55

The value cast to text, for a query with no field.

Parameters

Parameter	Type
fieldExpr	string

Returns

string

Overrides

BaseSQLDialect.toText

---

validCIDR()

protected validCIDR(value): string

Defined in: dialects/base.ts:383

Parameters

Parameter	Type
value	unknown

Returns

string

Inherited from

BaseSQLDialect.validCIDR

---

validIP()

protected validIP(value): string

Defined in: dialects/base.ts:372

Validate an IP address or CIDR block before it reaches the engine.

Otherwise the engine's parse error would refer to generated SQL the caller never wrote.

Parameters

Parameter	Type
value	unknown

Returns

string

Inherited from

BaseSQLDialect.validIP

---

wildcard()

wildcard(fieldExpr, value, fieldType?): string

Defined in: dialects/base.ts:159

A wildcard match, given an xLucene wildcard value (* and ?).

Parameters

Parameter	Type
fieldExpr	string
value	string
fieldType?	xLuceneFieldType

Returns

string

Inherited from

BaseSQLDialect.wildcard