# tinySQL - Function Examples

Auto-generated from input SQL. Results are shown below; SQL is visible above each result.

tinySQL Function Examples
This file demonstrates all supported SQL functions in tinySQL
Execute these examples to learn the complete SQL dialect

## DATE AND TIME FUNCTIONS

Current date and time
SELECT NOW() as current_timestamp;

current_timestamp
2026-08-04T15:09:08.3860223+02:00

SELECT CURRENT_DATE() as today;

today
2026-08-04T00:00:00+02:00

SELECT CURRENT_TIME() as current_time;

CURRENT_TIME
2026-08-04T15:09:08.5999875+02:00

Date part extraction
SELECT YEAR(NOW()) as current_year;

current_year
2026

SELECT MONTH(NOW()) as current_month;

current_month
8

SELECT DAY(NOW()) as current_day;

current_day
4

SELECT HOUR(NOW()) as current_hour;

current_hour
15

SELECT MINUTE(NOW()) as current_minute;

current_minute
9

SELECT SECOND(NOW()) as current_second;

current_second
8

SELECT QUARTER(NOW()) as current_quarter;

current_quarter
3

SELECT DAYOFWEEK(NOW()) as day_of_week;

day_of_week
3

SELECT DAYOFYEAR(NOW()) as day_of_year;

day_of_year
216

SELECT WEEKOFYEAR(NOW()) as week_of_year;

week_of_year
32

NEW: EXTRACT function (alternative to individual functions)
SELECT EXTRACT('YEAR', NOW()) as year;

YEAR
2026

SELECT EXTRACT('MONTH', NOW()) as month;

MONTH
8

SELECT EXTRACT('DAY', NOW()) as day;

DAY
4

SELECT EXTRACT('QUARTER', NOW()) as quarter;

QUARTER
3

SELECT EXTRACT('WEEK', NOW()) as week;

week
32

SELECT EXTRACT('DAYOFWEEK', NOW()) as dow;

dow
2

Date arithmetic
SELECT DATE_ADD(CURRENT_DATE(), 7, 'DAY') as next_week;

next_week
2026-08-11T00:00:00+02:00

SELECT DATE_SUB(CURRENT_DATE(), 30, 'DAY') as last_month;

last_month
2026-07-05T00:00:00+02:00

SELECT DATEDIFF('DAYS', '2024-01-01', '2024-12-31') as days_in_2024;

days_in_2024
365

NEW: ADD_MONTHS function
SELECT ADD_MONTHS(CURRENT_DATE(), 3) as three_months_later;

three_months_later
2026-11-04T00:00:00+01:00

SELECT ADD_MONTHS(CURRENT_DATE(), -6) as six_months_ago;

six_months_ago
2026-02-04T00:00:00+01:00

NEW: DATE_TRUNC function (truncate to period start)
SELECT DATE_TRUNC('YEAR', NOW()) as start_of_year;

start_of_year
2026-01-01T00:00:00+01:00

SELECT DATE_TRUNC('QUARTER', NOW()) as start_of_quarter;

start_of_quarter
2026-07-01T00:00:00+02:00

SELECT DATE_TRUNC('MONTH', NOW()) as start_of_month;

start_of_month
2026-08-01T00:00:00+02:00

SELECT DATE_TRUNC('WEEK', NOW()) as start_of_week;

start_of_week
2026-08-03T02:00:00+02:00

SELECT DATE_TRUNC('DAY', NOW()) as start_of_day;

start_of_day
2026-08-04T02:00:00+02:00

SELECT DATE_TRUNC('HOUR', NOW()) as start_of_hour;

start_of_hour
2026-08-04T15:00:00+02:00

NEW: EOMONTH function (end of month)
SELECT EOMONTH(CURRENT_DATE()) as end_of_current_month;

end_of_current_month
2026-08-31T00:00:00+02:00

SELECT EOMONTH(CURRENT_DATE(), 1) as end_of_next_month;

end_of_next_month
2026-09-30T00:00:00+02:00

SELECT EOMONTH(CURRENT_DATE(), -1) as end_of_last_month;

end_of_last_month
2026-07-31T00:00:00+02:00

NEW: IN_PERIOD function (period membership checks)
SELECT IN_PERIOD('TODAY', CURRENT_DATE()) as is_today;

is_today
true

SELECT IN_PERIOD('YTD', '2024-06-15') as is_year_to_date;

is_year_to_date
false

SELECT IN_PERIOD('MTD', CURRENT_DATE()) as is_month_to_date;

is_month_to_date
true

SELECT IN_PERIOD('QTD', CURRENT_DATE()) as is_quarter_to_date;

is_quarter_to_date
true

SELECT IN_PERIOD('L12M', DATE_SUB(CURRENT_DATE(), 180, 'DAY')) as is_last_12_months;

is_last_12_months
true

SELECT IN_PERIOD('CURRENT_QUARTER', CURRENT_DATE()) as is_current_quarter;

is_current_quarter
true

SELECT IN_PERIOD('PREVIOUS_QUARTER', DATE_SUB(CURRENT_DATE(), 120, 'DAY')) as was_previous_quarter;

was_previous_quarter
true

SELECT IN_PERIOD('CURRENT_YEAR_FULL', CURRENT_DATE()) as is_current_year;

is_current_year
true

Date formatting
SELECT STRFTIME('%Y-%m-%d', NOW()) as iso_date;

iso_date
2026-08-04

SELECT STRFTIME('%d.%m.%Y', NOW()) as german_date;

german_date
04.08.2026

SELECT STRFTIME('%A, %B %d, %Y', NOW()) as long_date;

long_date
%A, %B 04, 2026

SELECT DATE(NOW()) as date_only;

date_only
2026-08-04

SELECT TIME(NOW()) as time_only;

time_only
15:09:08

## STRING FUNCTIONS

Case conversion
SELECT UPPER('hello world') as uppercase;

uppercase
HELLO WORLD

SELECT LOWER('HELLO WORLD') as lowercase;

lowercase
hello world

SELECT INITCAP('hello world from tinysql') as title_case;

title_case
Hello World From Tinysql

String manipulation
SELECT CONCAT('Hello', ' ', 'World') as concatenated;

concatenated
Hello World

SELECT CONCAT_WS(', ', 'Apple', 'Banana', 'Cherry') as joined_with_separator;

joined_with_separator
Apple, Banana, Cherry

SELECT LENGTH('Hello World') as string_length;

string_length
11

SELECT CHAR_LENGTH('Hello') as char_length;

CHAR_LENGTH
5

SELECT SUBSTRING('Hello World', 7, 5) as substring_result;

substring_result
World

SELECT SUBSTR('Hello World', 1, 5) as substr_result;

substr_result
Hello

SELECT LEFT('Hello World', 5) as left_chars;

left_chars
Hello

SELECT RIGHT('Hello World', 5) as right_chars;

right_chars
World

SELECT REVERSE('Hello') as reversed;

reversed
olleH

SELECT REPEAT('Ha', 3) as repeated;

repeated
HaHaHa

String trimming
SELECT TRIM(' Hello ') as trimmed;

trimmed
Hello

SELECT LTRIM(' Hello') as left_trimmed;

left_trimmed
Hello

SELECT RTRIM('Hello ') as right_trimmed;

right_trimmed
Hello

String padding
SELECT LPAD('42', 5, '0') as left_padded;

left_padded
00042

SELECT RPAD('42', 5, '0') as right_padded;

right_padded
42000

String replacement
SELECT REPLACE('Hello World', 'World', 'Universe') as replaced;

replaced
Hello Universe

String search
SELECT INSTR('Hello World', 'World') as position;

POSITION
7

SELECT LOCATE('World', 'Hello World') as locate_position;

locate_position
0

String formatting
SELECT PRINTF('Value: %d, Name: %s', 42, 'Test') as formatted;

formatted
Value: 42, Name: Test

SELECT FORMAT('Pi is approximately %.2f', 3.14159) as formatted_number;

formatted_number
Pi is approximately 3.14

NEW: SPLIT function (returns array)
SELECT SPLIT('apple,banana,cherry', ',') as fruit_array;

fruit_array
["apple","banana","cherry"]

Split and extract part (existing function)
SELECT SPLIT_PART('apple,banana,cherry', ',', 2) as second_fruit;

second_fruit
banana

Special string functions
SELECT SPACE(10) as ten_spaces;

ten_spaces

SELECT ASCII('A') as ascii_value;

ascii_value
65

SELECT CHAR(65) as char_from_ascii;

char_from_ascii
A

SELECT SOUNDEX('Smith') as soundex_code;

soundex_code
S530

SELECT QUOTE('It''s a test') as quoted_string;

quoted_string
'It''s a test'

## NEW: REGEX FUNCTIONS

Pattern matching
SELECT REGEXP_MATCH('test@example.com', '.*@.*\.com') as is_email;

is_email
true

SELECT REGEXP_MATCH('12345', '^\d+$') as is_numeric;

is_numeric
true

Pattern extraction
SELECT REGEXP_EXTRACT('Order #12345 processed', '#\d+') as order_number;

order_number
#12345

SELECT REGEXP_EXTRACT('Email: test@example.com', '\w+@\w+\.\w+') as email;

email
test@example.com

Pattern replacement
SELECT REGEXP_REPLACE('2024-11-27', '-', '/') as date_with_slashes;

date_with_slashes
2024/11/27

SELECT REGEXP_REPLACE('Hello123World456', '\d+', 'X') as numbers_replaced;

numbers_replaced
HelloXWorldX

## FULL-TEXT SEARCH FUNCTIONS

### `FTS_MATCH` — boolean match against a query (term, phrase, boolean, wildcard)

SELECT FTS_MATCH('the quick brown fox', 'fox') as has_fox;

has_fox
true

SELECT FTS_MATCH('the quick brown fox', 'quick AND fox') as has_both;

has_both
true

SELECT FTS_MATCH('the quick brown fox', 'cat OR fox') as has_either;

has_either
true

SELECT FTS_MATCH('the quick brown fox', 'NOT cat') as lacks_cat;

lacks_cat
true

SELECT FTS_MATCH('the quick brown fox jumps', '"quick brown"') as has_phrase;

has_phrase
true

SELECT FTS_MATCH('the database stores information', 'inform*') as has_prefix;

has_prefix
true

? / _ match one character; * / % match zero or more characters.
SELECT FTS_MATCH('colour palette', 'colo?r') as single_char_wildcard;

single_char_wildcard
true

SELECT FTS_MATCH('database systems', 'data%') as multi_char_wildcard;

multi_char_wildcard
true

FTS_RANK / BM25: relevance score for the same query syntax (higher = more relevant)
SELECT FTS_RANK('the quick brown fox', 'fox AND quick') as relevance;

relevance
2

SELECT BM25('the quick brown fox', 'fox AND quick') as relevance_alias;

relevance_alias
2

FTS_SNIPPET / FTS_HIGHLIGHT: return the text with matches wrapped in markers
SELECT FTS_SNIPPET('the quick brown fox', 'fox', '<em>', '</em>', '', 10) as excerpt;

excerpt
the quick brown <em>fox</em>

### `FTS_WORD_COUNT` — number of (stop-word-filtered, stemmed) tokens

SELECT FTS_WORD_COUNT('hello world foo') as token_count;

token_count
3

CREATE TABLE fts_demo_articles (id INT, title TEXT, body TEXT)
(ok)
INSERT INTO fts_demo_articles VALUES (1, 'Go Programming', 'Go is a fast compiled language for systems programming')
(ok)
INSERT INTO fts_demo_articles VALUES (2, 'Python Tutorial', 'Python is a dynamic scripting language popular for data science')
(ok)
INSERT INTO fts_demo_articles VALUES (3, 'Database Design', 'Relational databases store data in tables with relationships')
(ok)
FTS_SEARCH table-valued function: ranked search across a real table.
With no column arguments it searches the WHOLE ROW (every column, not
just TEXT ones); pass explicit column names to restrict the search.
Repeated calls against an unchanged table reuse a cached tokenization,
so it's cheap for interactive/repeated queries.
CREATE TABLE fts_demo_articles (id INT, title TEXT, body TEXT); INSERT INTO fts_demo_articles VALUES (1, 'Go Programming', 'Go is a fast compiled language for systems programming'); INSERT INTO fts_demo_articles VALUES (2, 'Python Tutorial', 'Python is a dynamic scripting language popular for data science'); INSERT INTO fts_demo_articles VALUES (3, 'Database Design', 'Relational databases store data in tables with relationships'); SELECT id, title, _fts_score, _fts_rank FROM FTS_SEARCH('fts_demo_articles', 'programming language', 2) ORDER BY _fts_rank;

id | title | _fts_score | _fts_rank
--- | --- | --- | ---
1 | Go Programming | 1.8418075983526219 | 1

Restrict the search to a single column
SELECT id, title FROM FTS_SEARCH('fts_demo_articles', 'relational', 5, 'body');

id | title
--- | ---
3 | Database Design

DROP TABLE fts_demo_articles
(ok)
CREATE TABLE row_to_text_demo (id INT, customer TEXT, status TEXT)
(ok)
INSERT INTO row_to_text_demo VALUES (1, 'Acme Corp', 'open')
(ok)
INSERT INTO row_to_text_demo VALUES (2, 'Globex', 'closed')
(ok)

### `ROW_TO_TEXT` — ad-hoc whole-row substring search inside an ordinary WHERE

clause, combinable with other conditions. Cheaper to set up than
FTS_SEARCH (no ranking, no table function) but also less precise; good
for a quick "search everywhere" filter.
DROP TABLE fts_demo_articles; CREATE TABLE row_to_text_demo (id INT, customer TEXT, status TEXT); INSERT INTO row_to_text_demo VALUES (1, 'Acme Corp', 'open'); INSERT INTO row_to_text_demo VALUES (2, 'Globex', 'closed'); SELECT id FROM row_to_text_demo WHERE ROW_TO_TEXT() LIKE '%Acme%' AND status = 'open';

id
1

DROP TABLE row_to_text_demo
(ok)
CREATE TABLE contains_demo (id INT, description TEXT)
(ok)
INSERT INTO contains_demo VALUES (1, 'Widget A2000 - blue, waterproof')
(ok)
INSERT INTO contains_demo VALUES (2, 'Widget B300 - red')
(ok)
INSERT INTO contains_demo VALUES (3, 'Gadget C100 - blue, waterproof, wireless')
(ok)
CONTAINS_ALL / CONTAINS_ANY / CONTAINS_SCORE: a friendlier, case-insensitive
alternative to chaining "ROW_TO_TEXT() LIKE '%term%' AND ROW_TO_TEXT() LIKE
'%other%' ..." for each term. Terms are literal substrings, not patterns —
unlike LIKE, '%' and '_' have no wildcard meaning here.

### `CONTAINS_ALL` — every term must be present (case-insensitive substring match)

DROP TABLE row_to_text_demo; CREATE TABLE contains_demo (id INT, description TEXT); INSERT INTO contains_demo VALUES (1, 'Widget A2000 - blue, waterproof'); INSERT INTO contains_demo VALUES (2, 'Widget B300 - red'); INSERT INTO contains_demo VALUES (3, 'Gadget C100 - blue, waterproof, wireless'); SELECT id, description FROM contains_demo WHERE CONTAINS_ALL(description, 'blue', 'waterproof');

id | description
--- | ---
1 | Widget A2000 - blue, waterproof
3 | Gadget C100 - blue, waterproof, wireless

### `CONTAINS_ANY` — at least one term must be present

SELECT id, description FROM contains_demo WHERE CONTAINS_ANY(description, 'wireless', 'red');

id | description
--- | ---
2 | Widget B300 - red
3 | Gadget C100 - blue, waterproof, wireless

### `CONTAINS_SCORE` — count of matched terms (0..N) - use in ORDER BY to rank

rows by how many search terms they contain
SELECT id, description, CONTAINS_SCORE(description, 'blue', 'waterproof', 'wireless') as match_count FROM contains_demo ORDER BY match_count DESC;

id | description | match_count
--- | --- | ---
3 | Gadget C100 - blue, waterproof, wireless | 3
1 | Widget A2000 - blue, waterproof | 2
2 | Widget B300 - red | 0

DROP TABLE contains_demo
(ok)

## NUMERIC FUNCTIONS

Basic math
DROP TABLE contains_demo; SELECT ABS(-42) as absolute_value;

absolute_value
42

SELECT SIGN(-15) as sign_of_number;

sign_of_number
-1

SELECT ROUND(3.14159, 2) as rounded;

rounded
3.14

SELECT FLOOR(3.7) as floor_value;

floor_value
3

SELECT CEIL(3.2) as ceiling_value;

ceiling_value
4

SELECT CEILING(3.2) as ceiling_value2;

ceiling_value2
4

SELECT TRUNCATE(3.14159, 2) as truncated;

truncated
3.14

SELECT TRUNC(3.14159, 2) as trunc_value;

trunc_value
3.14

Advanced math
SELECT POWER(2, 8) as power_of_two;

power_of_two
256

SELECT POW(3, 3) as cube;

cube
27

SELECT SQRT(16) as square_root;

square_root
4

SELECT MOD(17, 5) as modulo;

modulo
2

Logarithms and exponentials
SELECT LOG(100) as natural_log;

natural_log
4.605170185988092

SELECT LN(2.71828) as ln_value;

ln_value
0.999999327347282

SELECT LOG10(1000) as log_base_10;

log_base_10
3

SELECT LOG2(1024) as log_base_2;

log_base_2
10

SELECT EXP(1) as euler_number;

euler_number
2.718281828459045

Trigonometry
SELECT PI() as pi_value;

pi_value
3.141592653589793

SELECT SIN(PI() / 2) as sine_90_degrees;

sine_90_degrees
1

SELECT COS(0) as cosine_0_degrees;

cosine_0_degrees
1

SELECT TAN(PI() / 4) as tangent_45_degrees;

tangent_45_degrees
1

SELECT ASIN(1) as arcsine;

arcsine
1.5707963267948966

SELECT ACOS(0) as arccosine;

arccosine
1.5707963267948966

SELECT ATAN(1) as arctangent;

arctangent
0.7853981633974483

SELECT ATAN2(1, 1) as atan2_value;

atan2_value
0.7853981633974483

SELECT DEGREES(PI()) as radians_to_degrees;

radians_to_degrees
180

SELECT RADIANS(180) as degrees_to_radians;

degrees_to_radians
3.141592653589793

Random numbers
SELECT RANDOM() as random_value;

random_value
0.09334275200814703

SELECT RAND() as rand_value;

rand_value
0.8887558635612671

## NEW: ARRAY FUNCTIONS

Create arrays
SELECT SPLIT('red,green,blue', ',') as colors;

colors
["red","green","blue"]

Array element access
SELECT FIRST(SPLIT('alpha,beta,gamma', ',')) as first_element;

first_element
alpha

SELECT LAST(SPLIT('alpha,beta,gamma', ',')) as last_element;

last_element
gamma

Array properties
SELECT ARRAY_LENGTH(SPLIT('one,two,three,four', ',')) as array_size;

array_size
4

Array membership
SELECT ARRAY_CONTAINS(SPLIT('apple,banana,cherry', ','), 'banana') as has_banana;

has_banana
true

SELECT IN_ARRAY(SPLIT('1,2,3,4,5', ','), '3') as has_three;

has_three
true

Array to string
SELECT ARRAY_JOIN(SPLIT('hello,world', ','), ' ') as joined_array;

joined_array
hello world

SELECT ARRAY_JOIN(SPLIT('a,b,c', ','), ' - ') as joined_with_dash;

joined_with_dash
a - b - c

Array manipulation
SELECT ARRAY_DISTINCT(SPLIT('a,b,a,c,b,d', ',')) as unique_elements;

unique_elements
["a","b","c","d"]

SELECT ARRAY_SORT(SPLIT('zebra,apple,mango,banana', ',')) as sorted_array;

sorted_array
["apple","banana","mango","zebra"]

CREATE TABLE temp_numbers (group_id INT, value INT)
(ok)
INSERT INTO temp_numbers VALUES (1, 10), (1, 20), (1, 30)
(ok)
INSERT INTO temp_numbers VALUES (2, 5), (2, 15), (2, 25)
(ok)
INSERT INTO temp_numbers VALUES (3, 100), (3, 200)
(ok)

## AGGREGATE FUNCTIONS

Create a sample table for aggregate examples

## COUNT

CREATE TABLE temp_numbers (group_id INT, value INT); INSERT INTO temp_numbers VALUES (1, 10), (1, 20), (1, 30); INSERT INTO temp_numbers VALUES (2, 5), (2, 15), (2, 25); INSERT INTO temp_numbers VALUES (3, 100), (3, 200); SELECT group_id, COUNT(*) as total_count FROM temp_numbers GROUP BY group_id;

group_id | total_count
--- | ---
1 | 3
2 | 3
3 | 2

SELECT group_id, COUNT(value) as non_null_count FROM temp_numbers GROUP BY group_id;

group_id | non_null_count
--- | ---
1 | 3
2 | 3
3 | 2

CREATE TABLE temp_sales (product TEXT, sale_date TEXT, amount INT)
(ok)
INSERT INTO temp_sales VALUES ('Laptop', '2025-01-15', 1200)
(ok)
INSERT INTO temp_sales VALUES ('Mouse', '2025-03-20', 25)
(ok)
INSERT INTO temp_sales VALUES ('Keyboard', '2025-02-10', 75)
(ok)
INSERT INTO temp_sales VALUES ('Monitor', '2025-01-05', 350)
(ok)
Basic aggregates (use with GROUP BY in real queries)
SELECT COUNT(*) as total_count FROM table;
SELECT COUNT(column) as non_null_count FROM table;
SELECT SUM(amount) as total FROM table;
SELECT AVG(value) as average FROM table;
SELECT MIN(price) as minimum FROM table;
SELECT MAX(price) as maximum FROM table;
NEW: MIN_BY and MAX_BY - Get value from row with min/max of another column
These are useful for time-series data and finding associated values
Create a sample sales table
Get the product name from the row with the earliest date
CREATE TABLE temp_sales (product TEXT, sale_date TEXT, amount INT); INSERT INTO temp_sales VALUES ('Laptop', '2025-01-15', 1200); INSERT INTO temp_sales VALUES ('Mouse', '2025-03-20', 25); INSERT INTO temp_sales VALUES ('Keyboard', '2025-02-10', 75); INSERT INTO temp_sales VALUES ('Monitor', '2025-01-05', 350); SELECT MIN_BY(product, sale_date) as earliest_product FROM temp_sales;

earliest_product
Monitor

Get the product name from the row with the latest date
SELECT MAX_BY(product, sale_date) as latest_product FROM temp_sales;

latest_product
Mouse

Get the product name with the lowest amount
SELECT MIN_BY(product, amount) as cheapest FROM temp_sales;

cheapest
Mouse

Get the product name with the highest amount
SELECT MAX_BY(product, amount) as most_expensive FROM temp_sales;

most_expensive
Laptop

Get the amount from the row with the earliest date
SELECT MIN_BY(amount, sale_date) as earliest_amount FROM temp_sales;

earliest_amount
350

Get the amount from the row with the latest date
SELECT MAX_BY(amount, sale_date) as latest_amount FROM temp_sales;

latest_amount
25

Aliases ARG_MIN and ARG_MAX are also supported
SELECT ARG_MIN(product, amount) as cheapest_via_argmin FROM temp_sales;

cheapest_via_argmin
Mouse

SELECT ARG_MAX(product, amount) as most_expensive_via_argmax FROM temp_sales;

most_expensive_via_argmax
Laptop

DROP TABLE temp_sales
(ok)
DROP TABLE temp_numbers
(ok)
Clean up

## COMPARISON AND RANGE PREDICATES

### `BETWEEN` — inclusive range check, equivalent to (x >= lo AND x <= hi)

DROP TABLE temp_sales; DROP TABLE temp_numbers; SELECT 5 BETWEEN 1 AND 10 as in_range;

in_range
true

SELECT 15 BETWEEN 1 AND 10 as out_of_range;

out_of_range
false

SELECT 5 NOT BETWEEN 1 AND 10 as not_in_range;

not_in_range
false

BETWEEN also works on dates and strings (lexicographic range for strings)
SELECT '2024-06-15' BETWEEN '2024-01-01' AND '2024-12-31' as in_2024;

in_2024
true

SELECT 'mango' BETWEEN 'apple' AND 'peach' as alphabetically_between;

alphabetically_between
true

The comparand is evaluated exactly once even for expensive/non-deterministic
expressions, e.g. RANDOM() BETWEEN 0.0 AND 1.0 is always true, never
comparing two different random draws against each other.
SELECT RANDOM() BETWEEN 0.0 AND 1.0 as random_is_always_in_unit_range;

random_is_always_in_unit_range
true

CREATE TABLE limit_demo (id INT)
(ok)
INSERT INTO limit_demo VALUES (1)
(ok)
INSERT INTO limit_demo VALUES (2)
(ok)
INSERT INTO limit_demo VALUES (3)
(ok)
INSERT INTO limit_demo VALUES (4)
(ok)
INSERT INTO limit_demo VALUES (5)
(ok)

## LIMIT / OFFSET

LIMIT ALL means "no limit" (same as omitting LIMIT entirely)
CREATE TABLE limit_demo (id INT); INSERT INTO limit_demo VALUES (1); INSERT INTO limit_demo VALUES (2); INSERT INTO limit_demo VALUES (3); INSERT INTO limit_demo VALUES (4); INSERT INTO limit_demo VALUES (5); SELECT * FROM limit_demo ORDER BY id LIMIT ALL;

id
1
2
3
4
5

LIMIT/OFFSET accept any constant expression, not just a bare literal
SELECT * FROM limit_demo ORDER BY id LIMIT 2 + 1 OFFSET 1 * 2;

id
3
4
5

### `SQL` — 2008 standard syntax: OFFSET n ROWS FETCH {FIRST|NEXT} m ROWS ONLY

SELECT * FROM limit_demo ORDER BY id OFFSET 1 ROWS FETCH FIRST 2 ROWS ONLY;

id
2
3

DROP TABLE limit_demo
(ok)

## CONDITIONAL AND NULL HANDLING

NULL handling
DROP TABLE limit_demo; SELECT COALESCE(NULL, NULL, 'default') as first_non_null;

first_non_null
default

SELECT NVL(NULL, 'replacement') as nvl_result;

nvl_result
replacement

SELECT IFNULL(NULL, 'value') as ifnull_result;

ifnull_result
value

SELECT NULLIF(5, 5) as nullif_equal;

nullif_equal
NULL

SELECT NULLIF(5, 3) as nullif_different;

nullif_different
5

Conditional
SELECT IF(10 > 5, 'yes', 'no') as if_result;

if_result
yes

SELECT IIF(2 + 2 = 4, 'correct', 'wrong') as iif_result;

iif_result
correct

SELECT GREATEST(10, 25, 15, 30) as greatest_value;

greatest_value
30

SELECT LEAST(10, 25, 15, 30) as least_value;

least_value
10

CASE expressions
SELECT CASE WHEN 10 > 20 THEN 'impossible' WHEN 10 > 5 THEN 'correct' ELSE 'wrong' END as case_result;

case_result
correct

## CRYPTOGRAPHIC AND ENCODING FUNCTIONS

Hashing
SELECT MD5('password') as md5_hash;

md5_hash
5f4dcc3b5aa765d61d8327deb882cf99

SELECT SHA1('password') as sha1_hash;

sha1_hash
5baa61e4c9b93f3f0682250b6cf8331b7ee68fd8

SELECT SHA256('password') as sha256_hash;

sha256_hash
5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8

SELECT SHA512('password') as sha512_hash;

sha512_hash
b109f3bbbc244eb82441917ed06d618b9008dd09b3befd1b5e07394c706a8bb980b1d7785e5976ec049b46df5f1326af5a2ea6d103fd07c95385ffab0cacbc86

Encoding
SELECT BASE64('Hello World') as base64_encoded;

base64_encoded
SGVsbG8gV29ybGQ=

SELECT BASE64_DECODE('SGVsbG8gV29ybGQ=') as base64_decoded;

base64_decoded
Hello World

SELECT HEX('Hello') as hex_encoded;

hex_encoded
48656C6C6F

SELECT UNHEX('48656C6C6F') as hex_decoded;

hex_decoded
Hello

## JSON FUNCTIONS

JSON extraction
SELECT JSON_GET('{"name":"John","age":30}', 'name') as json_name;
SELECT JSON_EXTRACT('{"user":{"name":"Jane"}}', 'user.name') as nested_value;

## TYPE CONVERSION AND INTROSPECTION

Type casting
SELECT CAST('123' AS INTEGER) as string_to_int;

string_to_int
123

SELECT CAST(3.14 AS INTEGER) as float_to_int;

float_to_int
3

SELECT CAST(42 AS TEXT) as int_to_string;

int_to_string
42

Type checking
SELECT TYPEOF(42) as integer_type;

integer_type
integer

SELECT TYPEOF('text') as string_type;

string_type
text

SELECT TYPEOF(3.14) as float_type;

float_type
real

## UTILITY FUNCTIONS

UUID generation
SELECT UUID() as unique_id;

unique_id
6c69f735-2079-4959-b6c0-e98a5f089bf0

Version info
SELECT VERSION() as tinysql_version;

tinysql_version
tinySQL 1.0

CREATE TABLE window_demo ( product TEXT, category TEXT, sale_date TEXT, amount INT )
(ok)
INSERT INTO window_demo VALUES ('Laptop', 'Electronics', '2025-01-15', 1200)
(ok)
INSERT INTO window_demo VALUES ('Mouse', 'Electronics', '2025-01-20', 25)
(ok)
INSERT INTO window_demo VALUES ('Desk', 'Furniture', '2025-01-25', 350)
(ok)
INSERT INTO window_demo VALUES ('Keyboard', 'Electronics', '2025-02-10', 75)
(ok)
INSERT INTO window_demo VALUES ('Chair', 'Furniture', '2025-02-15', 200)
(ok)

## WINDOW FUNCTIONS - FULLY IMPLEMENTED

Window functions operate on a set of rows (window) relative to the current row
Syntax: function() OVER (PARTITION BY ... ORDER BY ... frame_clause)
Create sample table for window function examples

### `ROW_NUMBER` — Assign unique row numbers

CREATE TABLE window_demo ( product TEXT, category TEXT, sale_date TEXT, amount INT ); INSERT INTO window_demo VALUES ('Laptop', 'Electronics', '2025-01-15', 1200); INSERT INTO window_demo VALUES ('Mouse', 'Electronics', '2025-01-20', 25); INSERT INTO window_demo VALUES ('Desk', 'Furniture', '2025-01-25', 350); INSERT INTO window_demo VALUES ('Keyboard', 'Electronics', '2025-02-10', 75); INSERT INTO window_demo VALUES ('Chair', 'Furniture', '2025-02-15', 200); SELECT product, amount, ROW_NUMBER() OVER (ORDER BY amount) as row_num FROM window_demo ORDER BY amount;

product | amount | row_num
--- | --- | ---
Mouse | 25 | 1
Keyboard | 75 | 2
Chair | 200 | 3
Desk | 350 | 4
Laptop | 1200 | 5

ROW_NUMBER with PARTITION BY: Restart numbering per partition
SELECT product, category, amount, ROW_NUMBER() OVER (PARTITION BY category ORDER BY amount) as row_in_category FROM window_demo ORDER BY category, amount;

product | category | amount | row_in_category
--- | --- | --- | ---
Mouse | Electronics | 25 | 1
Keyboard | Electronics | 75 | 2
Laptop | Electronics | 1200 | 3
Chair | Furniture | 200 | 1
Desk | Furniture | 350 | 2

### `RANK` — like ROW_NUMBER, but tied rows share a rank and the next rank

skips ahead by the tie-group size (e.g. 1, 1, 3)
SELECT product, amount, RANK() OVER (ORDER BY amount DESC) as rank_by_amount FROM window_demo ORDER BY amount DESC;

product | amount | rank_by_amount
--- | --- | ---
Laptop | 1200 | 1
Desk | 350 | 2
Chair | 200 | 3
Keyboard | 75 | 4
Mouse | 25 | 5

### `DENSE_RANK` — like RANK, but no gaps after ties (e.g. 1, 1, 2)

SELECT product, amount, DENSE_RANK() OVER (ORDER BY amount DESC) as dense_rank_by_amount FROM window_demo ORDER BY amount DESC;

product | amount | dense_rank_by_amount
--- | --- | ---
Laptop | 1200 | 1
Desk | 350 | 2
Chair | 200 | 3
Keyboard | 75 | 4
Mouse | 25 | 5

PERCENT_RANK and CUME_DIST: relative standing within the partition, as a
fraction in [0, 1]
SELECT product, amount, PERCENT_RANK() OVER (ORDER BY amount) as percent_rank, CUME_DIST() OVER (ORDER BY amount) as cume_dist FROM window_demo ORDER BY amount;

product | amount | percent_rank | cume_dist
--- | --- | --- | ---
Mouse | 25 | 0 | 0.2
Keyboard | 75 | 0.25 | 0.4
Chair | 200 | 0.5 | 0.6
Desk | 350 | 0.75 | 0.8
Laptop | 1200 | 1 | 1

### `NTILE` — split the partition into N (approximately) equal-sized buckets

SELECT product, amount, NTILE(3) OVER (ORDER BY amount) as tercile FROM window_demo ORDER BY amount;

product | amount | tercile
--- | --- | ---
Mouse | 25 | 1
Keyboard | 75 | 1
Chair | 200 | 2
Desk | 350 | 2
Laptop | 1200 | 3

### `LAG` — Access previous row value

SELECT product, amount, LAG(amount, 1) OVER (ORDER BY sale_date) as previous_amount FROM window_demo ORDER BY sale_date;

product | amount | previous_amount
--- | --- | ---
Laptop | 1200 | NULL
Mouse | 25 | 1200
Desk | 350 | 25
Keyboard | 75 | 350
Chair | 200 | 75

LAG with default value for first row
SELECT product, amount, LAG(amount, 1, 0) OVER (ORDER BY sale_date) as previous_or_zero FROM window_demo ORDER BY sale_date;

product | amount | previous_or_zero
--- | --- | ---
Laptop | 1200 | 0
Mouse | 25 | 1200
Desk | 350 | 25
Keyboard | 75 | 350
Chair | 200 | 75

### `LEAD` — Access next row value

SELECT product, amount, LEAD(amount, 1) OVER (ORDER BY sale_date) as next_amount FROM window_demo ORDER BY sale_date;

product | amount | next_amount
--- | --- | ---
Laptop | 1200 | 25
Mouse | 25 | 350
Desk | 350 | 75
Keyboard | 75 | 200
Chair | 200 | NULL

### `FIRST_VALUE` — Get first value in window

SELECT product, amount, FIRST_VALUE(product) OVER (ORDER BY amount) as cheapest_product FROM window_demo ORDER BY amount;

product | amount | cheapest_product
--- | --- | ---
Mouse | 25 | Mouse
Keyboard | 75 | Mouse
Chair | 200 | Mouse
Desk | 350 | Mouse
Laptop | 1200 | Mouse

FIRST_VALUE with PARTITION BY
SELECT product, category, amount, FIRST_VALUE(product) OVER (PARTITION BY category ORDER BY amount) as cheapest_in_category FROM window_demo ORDER BY category, amount;

product | category | amount | cheapest_in_category
--- | --- | --- | ---
Mouse | Electronics | 25 | Mouse
Keyboard | Electronics | 75 | Mouse
Laptop | Electronics | 1200 | Mouse
Chair | Furniture | 200 | Chair
Desk | Furniture | 350 | Chair

### `LAST_VALUE` — Get last value in window (up to current row by default)

SELECT product, amount, LAST_VALUE(product) OVER (ORDER BY amount) as most_expensive_so_far FROM window_demo ORDER BY amount;

product | amount | most_expensive_so_far
--- | --- | ---
Mouse | 25 | Laptop
Keyboard | 75 | Laptop
Chair | 200 | Laptop
Desk | 350 | Laptop
Laptop | 1200 | Laptop

### `MOVING_SUM` — Calculate moving/rolling sum

SELECT product, sale_date, amount, MOVING_SUM(3, amount) OVER (ORDER BY sale_date) as rolling_sum_3days FROM window_demo ORDER BY sale_date;

product | sale_date | amount | rolling_sum_3days
--- | --- | --- | ---
Laptop | 2025-01-15 | 1200 | 1200
Mouse | 2025-01-20 | 25 | 1225
Desk | 2025-01-25 | 350 | 1575
Keyboard | 2025-02-10 | 75 | 450
Chair | 2025-02-15 | 200 | 625

### `MOVING_AVG` — Calculate moving/rolling average

SELECT product, sale_date, amount, MOVING_AVG(3, amount) OVER (ORDER BY sale_date) as rolling_avg_3days FROM window_demo ORDER BY sale_date;

product | sale_date | amount | rolling_avg_3days
--- | --- | --- | ---
Laptop | 2025-01-15 | 1200 | 1200
Mouse | 2025-01-20 | 25 | 612.5
Desk | 2025-01-25 | 350 | 525
Keyboard | 2025-02-10 | 75 | 150
Chair | 2025-02-15 | 200 | 208.33333333333334

Complex example: LAG with PARTITION BY
SELECT product, category, amount, LAG(amount, 1) OVER (PARTITION BY category ORDER BY sale_date) as prev_in_category FROM window_demo ORDER BY category, sale_date;

product | category | amount | prev_in_category
--- | --- | --- | ---
Laptop | Electronics | 1200 | NULL
Mouse | Electronics | 25 | 1200
Keyboard | Electronics | 75 | 25
Desk | Furniture | 350 | NULL
Chair | Furniture | 200 | 350

DROP TABLE window_demo
(ok)
Clean up

## COMPLEX QUERIES COMBINING MULTIPLE FUNCTIONS

Example 1: Date range analysis with period check
DROP TABLE window_demo; SELECT CURRENT_DATE() as today, DATE_TRUNC('MONTH', CURRENT_DATE()) as month_start, EOMONTH(CURRENT_DATE()) as month_end, IN_PERIOD('MTD', CURRENT_DATE()) as is_month_to_date, DATEDIFF('DAYS', DATE_TRUNC('MONTH', CURRENT_DATE()), CURRENT_DATE()) as days_into_month;

today | month_start | month_end | is_month_to_date | days_into_month
--- | --- | --- | --- | ---
2026-08-04T00:00:00+02:00 | 2026-08-01T00:00:00+02:00 | 2026-08-31T00:00:00+02:00 | true | 3

Example 2: String processing with regex
SELECT REGEXP_EXTRACT('Order #12345', '#(\d+)') as order_id, UPPER(REGEXP_REPLACE('test@EXAMPLE.com', '@.*', '')) as username, ARRAY_LENGTH(SPLIT('item1,item2,item3', ',')) as item_count;

order_id | username | item_count
--- | --- | ---
#12345 | TEST | 3

Example 3: Numeric calculations
SELECT ROUND(PI() * POWER(5, 2), 2) as circle_area_r5, DEGREES(ATAN2(3, 4)) as angle_degrees, ABS(SIGN(-42) * 100) as absolute_signed;

circle_area_r5 | angle_degrees | absolute_signed
--- | --- | ---
78.54 | 36.86989764584402 | 100

Example 4: Array operations
SELECT FIRST(ARRAY_SORT(ARRAY_DISTINCT(SPLIT('zebra,apple,zebra,mango', ',')))) as first_sorted_unique, ARRAY_JOIN(ARRAY_SORT(SPLIT('3,1,4,1,5,9', ',')), ' -> ') as sorted_numbers;

first_sorted_unique | sorted_numbers
--- | ---
apple | 1 -> 1 -> 3 -> 4 -> 5 -> 9

Example 5: Conditional logic with dates
SELECT CASE WHEN IN_PERIOD('TODAY', CURRENT_DATE()) THEN 'Today' WHEN IN_PERIOD('MTD', CURRENT_DATE()) THEN 'This Month' WHEN IN_PERIOD('QTD', CURRENT_DATE()) THEN 'This Quarter' WHEN IN_PERIOD('YTD', CURRENT_DATE()) THEN 'This Year' ELSE 'Historical' END as period_classification;

period_classification
Today

Example 6: NULL handling with coalesce chain
SELECT COALESCE( NULL, NULLIF('', ''), REGEXP_EXTRACT('No match here', 'PATTERN'), 'Final Default' ) as coalesce_result;

coalesce_result
Final Default

CREATE TABLE pivot_demo (region TEXT, category TEXT, amount INT)
(ok)
INSERT INTO pivot_demo VALUES ('East', 'Electronics', 100)
(ok)
INSERT INTO pivot_demo VALUES ('East', 'Furniture', 50)
(ok)
INSERT INTO pivot_demo VALUES ('West', 'Electronics', 200)
(ok)
INSERT INTO pivot_demo VALUES ('West', 'Furniture', 75)
(ok)

## PIVOT

PIVOT spreads the distinct values of one column into new output columns,
aggregating another column into each. Every other selected column becomes
an implicit GROUP BY key. Scope: one aggregate function and a static
(literal) value list.
One row per region, one column per category, aliased for clean output names
CREATE TABLE pivot_demo (region TEXT, category TEXT, amount INT); INSERT INTO pivot_demo VALUES ('East', 'Electronics', 100); INSERT INTO pivot_demo VALUES ('East', 'Furniture', 50); INSERT INTO pivot_demo VALUES ('West', 'Electronics', 200); INSERT INTO pivot_demo VALUES ('West', 'Furniture', 75); SELECT * FROM pivot_demo PIVOT (SUM(amount) FOR category IN ('Electronics' AS electronics, 'Furniture' AS furniture));

region | electronics | furniture
--- | --- | ---
East | 100 | 50
West | 200 | 75

WHERE filters the source rows before pivoting
SELECT * FROM pivot_demo WHERE amount >= 100 PIVOT (SUM(amount) FOR category IN ('Electronics' AS electronics));

region | electronics
--- | ---
East | 100
West | 200

COUNT works too, not just SUM
SELECT * FROM pivot_demo PIVOT (COUNT(amount) FOR category IN ('Electronics' AS electronics_count, 'Furniture' AS furniture_count));

region | electronics_count | furniture_count
--- | --- | ---
East | 1 | 1
West | 1 | 1

DROP TABLE pivot_demo
(ok)

## PRACTICAL USE CASES

Use Case 1: Email validation and extraction
DROP TABLE pivot_demo; SELECT REGEXP_MATCH('user@example.com', '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$') as is_valid_email, SPLIT_PART('user@example.com', '@', 1) as username, SPLIT_PART('user@example.com', '@', 2) as domain;

is_valid_email | username | domain
--- | --- | ---
true | user | example.com

Use Case 2: Date-based filtering helper
SELECT IN_PERIOD('LAST_12_MONTHS', ADD_MONTHS(CURRENT_DATE(), -6)) as in_last_year, IN_PERIOD('CURRENT_QUARTER', CURRENT_DATE()) as in_current_quarter, EXTRACT('QUARTER', CURRENT_DATE()) as current_quarter_number;

in_last_year | in_current_quarter | current_quarter_number
--- | --- | ---
true | true | 3

CREATE TABLE demo_orders ( order_id INT, customer TEXT, order_date TEXT, total INT, "customer name" TEXT )
(ok)
INSERT INTO demo_orders VALUES (1, 'alice', '2025-01-10 09:15:00', 120, 'Alice A'), (2, 'alice', '2025-02-05 14:20:00', 200, 'Alice A'), (3, 'bob', '2025-01-12 11:05:00', 75, 'Robert B'), (4, 'bob', '2025-03-01 08:30:00', 50, 'Robert B'), (5, 'carol', '2025-02-20 16:45:00', 300, 'Carol C')
(ok)
Complex Example: DISTINCT ON + ROW_NUMBER, string concatenation,
column names with spaces, and timestamp helpers.
Use DISTINCT ON to pick the most recent order per customer.
Note: DISTINCT ON keeps the first row per key; ORDER BY controls which row is first.
STRING CONCAT via + operator (also works with CONCAT)
ROW_NUMBER demonstrates window function numbering per PARTITION
TIMESTAMP helpers
CREATE TABLE demo_orders ( order_id INT, customer TEXT, order_date TEXT, total INT, "customer name" TEXT ); INSERT INTO demo_orders VALUES (1, 'alice', '2025-01-10 09:15:00', 120, 'Alice A'), (2, 'alice', '2025-02-05 14:20:00', 200, 'Alice A'), (3, 'bob', '2025-01-12 11:05:00', 75, 'Robert B'), (4, 'bob', '2025-03-01 08:30:00', 50, 'Robert B'), (5, 'carol', '2025-02-20 16:45:00', 300, 'Carol C'); SELECT DISTINCT ON (customer) customer, "customer name", total, order_date, customer + ' - ' + CAST(total AS TEXT) AS summary, ROW_NUMBER() OVER (PARTITION BY customer ORDER BY order_date DESC) AS rn, GETDATE() AS now_ts, TODAY() AS today_date, FROM_TIMESTAMP(0) AS epoch_zero, TIMESTAMP('2023-05-01 00:00:00') AS ts_epoch FROM demo_orders ORDER BY customer, order_date DESC;

customer | customer name | total | order_date | summary | rn | now_ts | today_date | epoch_zero | ts_epoch
--- | --- | --- | --- | --- | --- | --- | --- | --- | ---
alice | Alice A | 200 | 2025-02-05 14:20:00 | alice - 200 | 1 | 2026-08-04T15:09:08.6093595+02:00 | 2026-08-04T00:00:00+02:00 | 1970-01-01T01:00:00+01:00 | 1682899200
bob | Robert B | 50 | 2025-03-01 08:30:00 | bob - 50 | 1 | 2026-08-04T15:09:08.6093595+02:00 | 2026-08-04T00:00:00+02:00 | 1970-01-01T01:00:00+01:00 | 1682899200
carol | Carol C | 300 | 2025-02-20 16:45:00 | carol - 300 | 1 | 2026-08-04T15:09:08.6093595+02:00 | 2026-08-04T00:00:00+02:00 | 1970-01-01T01:00:00+01:00 | 1682899200

Show all rows with ROW_NUMBER and LAG for context
SELECT order_id, customer, "customer name", order_date, total, ROW_NUMBER() OVER (PARTITION BY customer ORDER BY order_date) AS rn_asc, LAG(total, 1) OVER (PARTITION BY customer ORDER BY order_date) AS prev_total FROM demo_orders ORDER BY customer, order_date;

order_id | customer | customer name | order_date | total | rn_asc | prev_total
--- | --- | --- | --- | --- | --- | ---
1 | alice | Alice A | 2025-01-10 09:15:00 | 120 | 1 | NULL
2 | alice | Alice A | 2025-02-05 14:20:00 | 200 | 2 | 120
3 | bob | Robert B | 2025-01-12 11:05:00 | 75 | 1 | NULL
4 | bob | Robert B | 2025-03-01 08:30:00 | 50 | 2 | 75
5 | carol | Carol C | 2025-02-20 16:45:00 | 300 | 1 | NULL

DROP TABLE demo_orders
(ok)
Cleanup demo table
Use Case 3: String normalization
DROP TABLE demo_orders; SELECT UPPER(TRIM(REGEXP_REPLACE(' Hello World ', '\s+', ' '))) as normalized_string;

normalized_string
HELLO WORLD

Use Case 4: Array processing from CSV
SELECT ARRAY_LENGTH(SPLIT('red,green,blue,yellow', ',')) as color_count, ARRAY_CONTAINS(SPLIT('red,green,blue', ','), 'green') as has_green, ARRAY_JOIN(ARRAY_SORT(ARRAY_DISTINCT(SPLIT('b,a,c,a,b', ','))), ',') as sorted_unique;

color_count | has_green | sorted_unique
--- | --- | ---
4 | true | a,b,c

Use Case 5: Complex date arithmetic
SELECT ADD_MONTHS(DATE_TRUNC('YEAR', CURRENT_DATE()), 6) as mid_year, EOMONTH(ADD_MONTHS(CURRENT_DATE(), 3), 0) as end_of_quarter, DATEDIFF('DAYS', DATE_TRUNC('YEAR', CURRENT_DATE()), EOMONTH(DATE_TRUNC('YEAR', CURRENT_DATE()), 11) ) as days_in_year;

mid_year | end_of_quarter | days_in_year
--- | --- | ---
2026-07-01T00:00:00+02:00 | 2026-11-30T00:00:00+01:00 | 364

CTE (Common Table Expression) EXAMPLES
Non-recursive CTE: useful for organizing complex subqueries
WITH recent_sales AS ( SELECT * FROM (SELECT '2025-01-01' AS sale_date, 'A' AS product) AS t ) SELECT * FROM recent_sales;

sale_date | product
--- | ---
2025-01-01 | A

Recursive CTE example: generate numbers 1..5
WITH RECURSIVE nums AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM nums WHERE n < 5 ) SELECT * FROM nums ORDER BY n;

n
1
2
3
4
5

Fibonacci via recursive CTE: generate first 10 Fibonacci numbers
n: index, a: current fib value, b: next fib value
WITH RECURSIVE fib AS ( SELECT 0 AS n, 0 AS a, 1 AS b UNION ALL SELECT n + 1, b, a + b FROM fib WHERE n < 9 ) SELECT n, a AS fib_value FROM fib ORDER BY n;

n | fib_value
--- | ---
0 | 0
1 | 1
2 | 1
3 | 2
4 | 3
5 | 5
6 | 8
7 | 13
8 | 21
9 | 34

IO AND TRANSFORM FUNCTIONS (safe runnable examples enabled)
GZIP/GUNZIP: Compression functions (safe examples)
SELECT LENGTH(GZIP('tinySQL gzip test string')) as gzip_compressed_length;

gzip_compressed_length
48

SELECT GUNZIP(GZIP('roundtrip test')) as gunzip_roundtrip;

gunzip_roundtrip
roundtrip test

BASE64 encoding/decoding (safe examples)
SELECT BASE64_ENCODE('hello world') as base64_encoded_example;

base64_encoded_example
aGVsbG8gd29ybGQ=

SELECT BASE64_DECODE(BASE64_ENCODE('hello world')) as base64_decoded_roundtrip;

base64_decoded_roundtrip
hello world

SELECT FILE('./data/data.json') as file_content;

file_content
[ {"id": 1, "name": "Alice", "email": "alice@example.com"}, {"id": 2, "name": "Bob", "email": "bob@example.com"}, {"id": 3, "name": "Carol", "email": "carol@example.com"} ]

### `HTTP` — Fetch HTTP GET response (30 second timeout)

SELECT HTTP('https://jsonplaceholder.typicode.com/todos/1') as http_response;

http_response
{ "userId": 1, "id": 1, "title": "delectus aut autem", "completed": false }

Combined example: Read and decompress a gzipped file
SELECT GUNZIP(FILE('./data/data.csv.gz')) as decompressed_content;

decompressed_content
id,name,email 1,Alice,alice@example.com 2,Bob,bob@example.com 3,Carol,carol@example.com

Combined example: Fetch and decode base64 data from HTTP
SELECT HTTP('https://jsonplaceholder.typicode.com/todos/1') as decoded_api_response;

decoded_api_response
{ "userId": 1, "id": 1, "title": "delectus aut autem", "completed": false }

SELECT * FROM TABLE_FROM_JSON(FILE('data/data.json')) ;

id | name | email
--- | --- | ---
1 | Alice | alice@example.com
2 | Bob | bob@example.com
3 | Carol | carol@example.com

SELECT * FROM TABLE_FROM_CSV(FILE('data/sample.csv'), '{"delimiter":",","header":true}');

id | name | email
--- | --- | ---
1 | Alice | alice@example.com
2 | Bob | bob@example.com
3 | Carol | carol@example.com

## TABLE-VALUED FUNCTIONS (TVF)

Note: Table-valued functions are currently in development.
The infrastructure is implemented, but parser integration is pending.
These will be available in a future release:

### `TABLE_FROM_JSON` — Parse JSON array into table

SELECT * FROM TABLE_FROM_JSON('[{"id":1,"name":"Alice"},{"id":2,"name":"Bob"}]');

name | id
--- | ---
Alice | 1
Bob | 2

### `TABLE_FROM_JSON_LINES` — Parse JSON Lines (JSONL) format

SELECT * FROM TABLE_FROM_JSON_LINES(FILE('data/data.jsonl'));

id | name | email
--- | --- | ---
1 | Alice | alice@example.com
2 | Bob | bob@example.com
3 | Carol | carol@example.com

### `TABLE_FROM_CSV` — Parse CSV data with configurable delimiter

SELECT * FROM TABLE_FROM_CSV(FILE('data/sample.csv'), '{"delimiter":",","header":true}');

email | id | name
--- | --- | ---
alice@example.com | 1 | Alice
bob@example.com | 2 | Bob
carol@example.com | 3 | Carol

Combined example: Query JSON data from HTTP endpoint
Use local demo JSON instead of external HTTP endpoint
SELECT * FROM TABLE_FROM_JSON(FILE('data/data.json')) WHERE id > 0;

email | id | name
--- | --- | ---
alice@example.com | 1 | Alice
bob@example.com | 2 | Bob
carol@example.com | 3 | Carol

CREATE TABLE IF NOT EXISTS users ( id INT, name TEXT, email TEXT )
(ok)
Combined example: Join CSV file with database table
Ensure `users` demo table exists before JOIN examples
CREATE TABLE IF NOT EXISTS users ( id INT, name TEXT, email TEXT ); INSERT INTO users VALUES (1, 'Alice', 'alice@example.com') ON CONFLICT DO NOTHING;
ERR: parse error near "ON": unexpected token after statement
INSERT INTO users VALUES (2, 'Bob', 'bob@example.com') ON CONFLICT DO NOTHING;
ERR: parse error near "ON": unexpected token after statement
INSERT INTO users VALUES (3, 'Carol', 'carol@example.com') ON CONFLICT DO NOTHING;
ERR: parse error near "ON": unexpected token after statement
Combined example: Join CSV file with database table
SELECT u.name, o.amount FROM users u JOIN TABLE_FROM_CSV(FILE('./data/orders.csv'), '{"delimiter":",","header":true}') o ON u.id = o.user_id;

## SYSTEM CATALOG

Note: System catalog tables are implemented and available via the Go API.
SQL syntax for querying catalog is planned for future release:
List all tables
SELECT * FROM catalog.tables;

name | full_name | columns | rows | is_temp | version | type | tenant | schema
--- | --- | --- | --- | --- | --- | --- | --- | ---
users | users | 3 | 0 | false | 0 | TABLE | default | main

Find columns for a specific table
SELECT * FROM catalog.columns WHERE table_name = 'users';

View all registered functions
SELECT name, function_type, return_type FROM catalog.functions;
ERR: unknown column "return_type"

Check scheduled jobs
SELECT name, enabled, schedule_type, next_run_at FROM catalog.jobs WHERE enabled = true;

CREATE JOB cleanup_logs SCHEDULE CRON '0 0 3 * * *' -- Daily at 3 AM TIMEZONE 'UTC' MAX_RUNTIME 300000 -- 5 minutes NO_OVERLAP AS DELETE FROM logs WHERE created_at < datetime('now', '-7 days')
(ok)
CREATE JOB refresh_stats SCHEDULE INTERVAL 30000 -- Every 30 seconds MAX_RUNTIME 10000 -- 10 seconds AS INSERT INTO stats_cache SELECT * FROM compute_stats()
(ok)
CREATE JOB send_report SCHEDULE ONCE '2025-12-31 23:59:59' TIMEZONE 'America/New_York' AS SELECT generate_report('annual')
(ok)
ALTER JOB cleanup_logs ENABLE
(ok)
ALTER JOB cleanup_logs DISABLE
(ok)
DROP JOB cleanup_logs
(ok)

## JOB SCHEDULER

Note: Job scheduler is fully implemented and operational via the Go API.
SQL syntax for job management is planned for future release:
Create a CRON job
Create an INTERVAL job
Create a ONCE job
Enable/disable jobs
Drop a job

## PRACTICAL EXAMPLES WITH NEW FUNCTIONS

Example 1: Load and process JSON data from a file
CREATE JOB cleanup_logs SCHEDULE CRON '0 0 3 * * *' -- Daily at 3 AM TIMEZONE 'UTC' MAX_RUNTIME 300000 -- 5 minutes NO_OVERLAP AS DELETE FROM logs WHERE created_at < datetime('now', '-7 days'); CREATE JOB refresh_stats SCHEDULE INTERVAL 30000 -- Every 30 seconds MAX_RUNTIME 10000 -- 10 seconds AS INSERT INTO stats_cache SELECT * FROM compute_stats(); CREATE JOB send_report SCHEDULE ONCE '2025-12-31 23:59:59' TIMEZONE 'America/New_York' AS SELECT generate_report('annual'); ALTER JOB cleanup_logs ENABLE; ALTER JOB cleanup_logs DISABLE; DROP JOB cleanup_logs; SELECT * FROM TABLE_FROM_JSON(FILE('./data/data.json')) WHERE id > 1 ORDER BY name;

name | email | id
--- | --- | ---
Bob | bob@example.com | 2
Carol | carol@example.com | 3

Materialize JSON into a demo table for downstream examples
SELECT * INTO demotable FROM TABLE_FROM_JSON(FILE('./data/data.json'));
ERR: parse error near "INTO": unexpected token after statement

Example 2: Fetch and parse CSV from HTTP endpoint
SELECT product, SUM(quantity) as total FROM TABLE_FROM_CSV(FILE('data/sales.csv'), '{"delimiter":",","header":true}') GROUP BY product;

product | total
--- | ---
Mouse | 2
Keyboard | 1
Laptop | 3
Chair | 1
Desk | 1

Example 3: Process compressed log files
SELECT * FROM TABLE_FROM_JSON_LINES(FILE('./data/data.jsonl')) WHERE id > 1;

id | name | email
--- | --- | ---
2 | Bob | bob@example.com
3 | Carol | carol@example.com

Example 4: Combine multiple data sources
SELECT u.name, o.order_date, p.price FROM users u JOIN TABLE_FROM_CSV(FILE('./data/orders.csv'), '{"delimiter":",","header":true}') o ON u.id = o.user_id JOIN TABLE_FROM_JSON(FILE('./data/prices.json')) p ON o.product = p.product;

Example 5: Base64-encoded JSON data
SELECT * FROM TABLE_FROM_JSON(BASE64_DECODE(FILE('./data/encoded_data.b64')));

id | name | email
--- | --- | ---
1 | Alice | alice@example.com
2 | Bob | bob@example.com

VECTOR / EMBEDDING FUNCTIONS (RAG Vector Database Support)
tinySQL supports a native VECTOR data type for storing float64 embeddings,
plus a rich set of functions for similarity search, distance computation,
and vector manipulation — enabling RAG (Retrieval-Augmented Generation)
workloads directly in SQL.
---- Vector Creation & Conversion ----
Parse a JSON array into a VECTOR
SELECT VEC_FROM_JSON('[1.0, 2.0, 3.0]') as embedding;

EMBEDDING
[1,2,3]

Serialize a VECTOR back to JSON
SELECT VEC_TO_JSON(VEC_FROM_JSON('[1.5, 2.5, 3.5]')) as json_vec;

json_vec
[1.5,2.5,3.5]

---- Vector Introspection ----
Number of dimensions
SELECT VEC_DIM(VEC_FROM_JSON('[1,2,3,4,5]')) as dimensions;

dimensions
5

L2 (Euclidean) norm
SELECT VEC_NORM(VEC_FROM_JSON('[3.0, 4.0]')) as norm_is_5;

norm_is_5
5

---- Vector Normalization ----
Normalize to unit length
SELECT VEC_NORMALIZE(VEC_FROM_JSON('[3.0, 4.0]')) as unit_vec;

unit_vec
[0.6,0.8]

---- Element-wise Arithmetic ----
Addition
SELECT VEC_ADD(VEC_FROM_JSON('[1,2,3]'), VEC_FROM_JSON('[4,5,6]')) as sum_vec;

sum_vec
[5,7,9]

Subtraction
SELECT VEC_SUB(VEC_FROM_JSON('[5,7,9]'), VEC_FROM_JSON('[1,2,3]')) as diff_vec;

diff_vec
[4,5,6]

Hadamard (element-wise) product
SELECT VEC_MUL(VEC_FROM_JSON('[2,3,4]'), VEC_FROM_JSON('[5,6,7]')) as prod_vec;

prod_vec
[10,18,28]

Scalar multiplication
SELECT VEC_SCALE(VEC_FROM_JSON('[1,2,3]'), 2.5) as scaled_vec;

scaled_vec
[2.5,5,7.5]

---- Similarity & Distance Functions ----
Dot product (inner product)
SELECT VEC_DOT(VEC_FROM_JSON('[1,2,3]'), VEC_FROM_JSON('[4,5,6]')) as dot_product;

dot_product
32

Cosine similarity ∈ [-1, 1] (1 = identical direction)
SELECT VEC_COSINE_SIMILARITY(VEC_FROM_JSON('[1,0]'), VEC_FROM_JSON('[1,0]')) as same_dir;

same_dir
1

SELECT VEC_COSINE_SIMILARITY(VEC_FROM_JSON('[1,0]'), VEC_FROM_JSON('[0,1]')) as orthogonal;

orthogonal
0

Cosine distance ∈ [0, 2] (0 = identical)
SELECT VEC_COSINE_DISTANCE(VEC_FROM_JSON('[1,0]'), VEC_FROM_JSON('[1,0]')) as zero_dist;

zero_dist
0

Euclidean (L2) distance
SELECT VEC_L2_DISTANCE(VEC_FROM_JSON('[0,0]'), VEC_FROM_JSON('[3,4]')) as dist_5;

dist_5
5

Manhattan (L1) distance
SELECT VEC_MANHATTAN_DISTANCE(VEC_FROM_JSON('[1,2,3]'), VEC_FROM_JSON('[4,6,8]')) as dist_12;

dist_12
12

Generic distance function with selectable metric
SELECT VEC_DISTANCE(VEC_FROM_JSON('[1,0]'), VEC_FROM_JSON('[0,1]'), 'cosine') as cosine_dist;

cosine_dist
1

SELECT VEC_DISTANCE(VEC_FROM_JSON('[0,0]'), VEC_FROM_JSON('[3,4]'), 'l2') as l2_dist;

l2_dist
5

SELECT VEC_DISTANCE(VEC_FROM_JSON('[0,0]'), VEC_FROM_JSON('[3,4]'), 'manhattan') as l1_dist;

l1_dist
7

SELECT VEC_DISTANCE(VEC_FROM_JSON('[1,0]'), VEC_FROM_JSON('[0,1]'), 'dot') as dot_dist;

dot_dist
-0

---- Vector Manipulation ----
Extract a sub-vector (0-based start, length)
SELECT VEC_SLICE(VEC_FROM_JSON('[10,20,30,40,50]'), 1, 3) as sub_vec;

sub_vec
[20,30,40]

Concatenate two vectors
SELECT VEC_CONCAT(VEC_FROM_JSON('[1,2]'), VEC_FROM_JSON('[3,4,5]')) as concat_vec;

concat_vec
[1,2,3,4,5]

Element-wise average of two vectors
SELECT VEC_AVG(VEC_FROM_JSON('[2,4,6]'), VEC_FROM_JSON('[4,6,8]')) as avg_vec;

avg_vec
[3,5,7]

---- Quantization ----
Simulate 8-bit quantization (reduces precision, saves storage)
SELECT VEC_QUANTIZE(VEC_FROM_JSON('[0.0, 0.5, 1.0]'), 8) as quantized_8bit;

quantized_8bit
[0,0.5019607843137255,1]

Simulate 16-bit quantization
SELECT VEC_QUANTIZE(VEC_FROM_JSON('[0.0, 0.33, 0.67, 1.0]'), 16) as quantized_16bit;

quantized_16bit
[0,0.3300068665598535,0.6699931334401464,1]

---- Random Vector Generation ----
Generate a random unit vector of 5 dimensions
SELECT VEC_RANDOM(5) as random_vec;

random_vec
[-0.14274490614762214,-0.7160125291820697,-0.523714526457873,0.39095634406149465,-0.19956498099185213]

Deterministic random vector with a seed
SELECT VEC_RANDOM(5, 42) as seeded_vec;

seeded_vec
[0.7546115837811923,0.06083794731271071,-0.24012205373772932,0.6042286795046568,0.06410307295949322]

CREATE TABLE vec_demo_docs (id INT, title TEXT, embedding VECTOR)
(ok)
INSERT INTO vec_demo_docs VALUES (1, 'apple', '[1.0, 0.0, 0.0]')
(ok)
INSERT INTO vec_demo_docs VALUES (2, 'banana', '[0.9, 0.1, 0.0]')
(ok)
INSERT INTO vec_demo_docs VALUES (3, 'cherry', '[0.0, 1.0, 0.0]')
(ok)
INSERT INTO vec_demo_docs VALUES (4, 'date fruit', '[0.0, 0.0, 1.0]')
(ok)
INSERT INTO vec_demo_docs VALUES (5, 'elderberry', '[0.8, 0.2, 0.0]')
(ok)
---- VECTOR Column Type ----
Tables can have VECTOR columns for storing embeddings
Query embeddings with inline similarity scoring
CREATE TABLE vec_demo_docs (id INT, title TEXT, embedding VECTOR); INSERT INTO vec_demo_docs VALUES (1, 'apple', '[1.0, 0.0, 0.0]'); INSERT INTO vec_demo_docs VALUES (2, 'banana', '[0.9, 0.1, 0.0]'); INSERT INTO vec_demo_docs VALUES (3, 'cherry', '[0.0, 1.0, 0.0]'); INSERT INTO vec_demo_docs VALUES (4, 'date fruit', '[0.0, 0.0, 1.0]'); INSERT INTO vec_demo_docs VALUES (5, 'elderberry', '[0.8, 0.2, 0.0]'); SELECT title, VEC_COSINE_SIMILARITY(embedding, VEC_FROM_JSON('[1.0, 0.0, 0.0]')) as similarity FROM vec_demo_docs ORDER BY similarity DESC;

title | similarity
--- | ---
apple | 1
banana | 0.9938837346736189
elderberry | 0.9701425001453318
cherry | 0
date fruit | 0

Filter by similarity threshold
SELECT title, VEC_DISTANCE(embedding, VEC_FROM_JSON('[1.0, 0.0, 0.0]'), 'cosine') as distance FROM vec_demo_docs WHERE VEC_DISTANCE(embedding, VEC_FROM_JSON('[1.0, 0.0, 0.0]'), 'cosine') < 0.5 ORDER BY distance;

title | distance
--- | ---
apple | 0
banana | 0.006116265326381098
elderberry | 0.029857499854668235

---- k-NN Vector Search (Table-Valued Function) ----
VEC_SEARCH(table, column, query_vector, k [, metric [, index]])
Returns the k nearest neighbours with _vec_distance and _vec_rank columns
SELECT * FROM VEC_SEARCH('vec_demo_docs', 'embedding', VEC_FROM_JSON('[1.0, 0.0, 0.0]'), 3);

id | title | embedding | _vec_distance | _vec_rank | _vec_similarity
--- | --- | --- | --- | --- | ---
1 | apple | [1,0,0] | 0 | 1 | 1
2 | banana | [0.9,0.1,0] | 0.006116265326381098 | 2 | 0.9938837346736189
5 | elderberry | [0.8,0.2,0] | 0.029857499854668235 | 3 | 0.9701425001453318

Search with L2 distance metric
SELECT * FROM VEC_SEARCH('vec_demo_docs', 'embedding', VEC_FROM_JSON('[1.0, 0.0, 0.0]'), 3, 'l2');

_vec_rank | id | title | embedding | _vec_distance | _vec_similarity
--- | --- | --- | --- | --- | ---
1 | 1 | apple | [1,0,0] | 0 | -0
2 | 2 | banana | [0.9,0.1,0] | 0.1414213562373095 | -0.1414213562373095
3 | 5 | elderberry | [0.8,0.2,0] | 0.282842712474619 | -0.282842712474619

Search with cached approximate ANN indexes for larger RAG tables
SELECT * FROM VEC_SEARCH('vec_demo_docs', 'embedding', VEC_FROM_JSON('[1.0, 0.0, 0.0]'), 3, 'cosine', 'hnsw');

_vec_distance | _vec_similarity | _vec_rank | id | title | embedding
--- | --- | --- | --- | --- | ---
0 | 1 | 1 | 1 | apple | [1,0,0]
0.006116265326381098 | 0.9938837346736189 | 2 | 2 | banana | [0.9,0.1,0]
0.029857499854668235 | 0.9701425001453318 | 3 | 5 | elderberry | [0.8,0.2,0]

SELECT * FROM VEC_SEARCH('vec_demo_docs', 'embedding', VEC_FROM_JSON('[1.0, 0.0, 0.0]'), 3, 'cosine', 'ivf');

id | title | embedding | _vec_distance | _vec_similarity | _vec_rank
--- | --- | --- | --- | --- | ---
1 | apple | [1,0,0] | 0 | 1 | 1
2 | banana | [0.9,0.1,0] | 0.006116265326381098 | 0.9938837346736189 | 2
5 | elderberry | [0.8,0.2,0] | 0.029857499854668235 | 0.9701425001453318 | 3

VEC_WARM(table, column [, metric [, index]]) prebuilds the vector column
cache and the requested ANN index (ivf/hnsw) ahead of time, instead of
paying that one-time O(n log n) build cost on whichever query happens to
run first. Building an HNSW index is real, non-trivial work (each row
insertion does its own approximate-nearest-neighbor search against the
graph built so far) — worth doing explicitly right after a bulk load
(e.g. a nightly re-embed) rather than surprising the first live query with
it. Returns one row describing what was warmed.
SELECT * FROM VEC_WARM('vec_demo_docs', 'embedding', 'cosine', 'hnsw');

dims | distinct_dims | table_name | row_count | column_name | index_mode | vector_count | metric | excluded_rows
--- | --- | --- | --- | --- | --- | --- | --- | ---
3 | 1 | vec_demo_docs | 5 | embedding | hnsw | 5 | cosine | 0

VEC_TOP_K is an alias for VEC_SEARCH
SELECT * FROM VEC_TOP_K('vec_demo_docs', 'embedding', VEC_FROM_JSON('[0.0, 1.0, 0.0]'), 2, 'cosine');

embedding | _vec_distance | _vec_similarity | id | title | _vec_rank
--- | --- | --- | --- | --- | ---
[0,1,0] | 0 | 1 | 3 | cherry | 1
[0.8,0.2,0] | 0.757464374963667 | 0.24253562503633297 | 5 | elderberry | 2

CREATE TABLE vec_demo_kb (id INT, content TEXT, embedding VECTOR)
(ok)
INSERT INTO vec_demo_kb VALUES (1, 'Go is a statically typed language', '[0.9, 0.1, 0.0, 0.0]')
(ok)
INSERT INTO vec_demo_kb VALUES (2, 'Python is dynamically typed', '[0.8, 0.2, 0.1, 0.0]')
(ok)
INSERT INTO vec_demo_kb VALUES (3, 'Databases store structured data', '[0.0, 0.0, 0.9, 0.1]')
(ok)
INSERT INTO vec_demo_kb VALUES (4, 'Vector search enables AI applications', '[0.1, 0.0, 0.1, 0.9]')
(ok)
---- RAG Workflow Example ----
1. Create a knowledge base with embeddings
2. Semantic search: find docs closest to a query embedding
CREATE TABLE vec_demo_kb (id INT, content TEXT, embedding VECTOR); INSERT INTO vec_demo_kb VALUES (1, 'Go is a statically typed language', '[0.9, 0.1, 0.0, 0.0]'); INSERT INTO vec_demo_kb VALUES (2, 'Python is dynamically typed', '[0.8, 0.2, 0.1, 0.0]'); INSERT INTO vec_demo_kb VALUES (3, 'Databases store structured data', '[0.0, 0.0, 0.9, 0.1]'); INSERT INTO vec_demo_kb VALUES (4, 'Vector search enables AI applications', '[0.1, 0.0, 0.1, 0.9]'); SELECT * FROM VEC_SEARCH('vec_demo_kb', 'embedding', VEC_FROM_JSON('[0.85, 0.15, 0.05, 0.0]'), 2);

id | content | _vec_distance | _vec_similarity | _vec_rank | embedding
--- | --- | --- | --- | --- | ---
1 | Go is a statically typed language | 0.003718472373113735 | 0.9962815276268863 | 1 | [0.9,0.1,0,0]
2 | Python is dynamically typed | 0.004420608950979843 | 0.9955793910490202 | 2 | [0.8,0.2,0.1,0]

3. Inline similarity ranking
SELECT content, VEC_COSINE_SIMILARITY(embedding, VEC_FROM_JSON('[0.85, 0.15, 0.05, 0.0]')) as relevance FROM vec_demo_kb ORDER BY relevance DESC;

content | relevance
--- | ---
Go is a statically typed language | 0.9962815276268863
Python is dynamically typed | 0.9955793910490202
Vector search enables AI applications | 0.11426095898687248
Databases store structured data | 0.05747778044001267

4. Vector arithmetic for query expansion (average two embeddings)
SELECT VEC_AVG(VEC_FROM_JSON('[0.9, 0.1, 0.0, 0.0]'), VEC_FROM_JSON('[0.8, 0.2, 0.1, 0.0]')) as expanded_query;

expanded_query
[0.8500000000000001,0.15000000000000002,0.05,0]

DROP TABLE vec_demo_docs
(ok)
DROP TABLE vec_demo_kb
(ok)
CREATE TABLE rag_search_demo ( doc_id TEXT, chunk_index INT, chunk_text TEXT, embedding VECTOR )
(ok)
INSERT INTO rag_search_demo VALUES ('doc-1', 0, 'TinySQL is a lightweight embeddable SQL engine', '[0.9, 0.1, 0.0]'), ('doc-1', 1, 'It supports vector search and full-text search', '[0.85, 0.15, 0.05]'), ('doc-1', 2, 'RAG pipelines combine retrieval with generation', '[0.1, 0.0, 0.9]'), ('doc-2', 0, 'Full-text search uses BM25 ranking', '[0.2, 0.0, 0.8]')
(ok)
Cleanup demo tables
---- RAG_SEARCH: composed retrieval (one call) ----
RAG_SEARCH(table, vector_column, query_vector, k [, options_json]) composes
VEC_SEARCH + FTS_SEARCH (hybrid reciprocal rank fusion) + RAG_CONTEXT_FROM
(neighbor-chunk expansion) into a single table-valued function, instead of
hand-assembling that pipeline from the three primitives above.
1. Vector-only search (equivalent to VEC_SEARCH, one call)
DROP TABLE vec_demo_docs; DROP TABLE vec_demo_kb; CREATE TABLE rag_search_demo ( doc_id TEXT, chunk_index INT, chunk_text TEXT, embedding VECTOR ); INSERT INTO rag_search_demo VALUES ('doc-1', 0, 'TinySQL is a lightweight embeddable SQL engine', '[0.9, 0.1, 0.0]'), ('doc-1', 1, 'It supports vector search and full-text search', '[0.85, 0.15, 0.05]'), ('doc-1', 2, 'RAG pipelines combine retrieval with generation', '[0.1, 0.0, 0.9]'), ('doc-2', 0, 'Full-text search uses BM25 ranking', '[0.2, 0.0, 0.8]'); SELECT doc_id, chunk_index, chunk_text, _vec_distance, _vec_similarity, _vec_rank FROM RAG_SEARCH('rag_search_demo', 'embedding', VEC_FROM_JSON('[0.9, 0.1, 0.0]'), 2);

doc_id | chunk_index | chunk_text | _vec_distance | _vec_similarity | _vec_rank
--- | --- | --- | --- | --- | ---
doc-1 | 0 | TinySQL is a lightweight embeddable SQL engine | 1.1102230246251565e-16 | 0.9999999999999999 | 1
doc-1 | 1 | It supports vector search and full-text search | 0.003718472373113735 | 0.9962815276268863 | 2

2. Hybrid vector + BM25 keyword search fused via reciprocal rank fusion
(RRF). key_columns is required in hybrid mode: it's how RAG_SEARCH matches
a row across the independently-fetched vector and text candidate sets.
SELECT doc_id, chunk_index, chunk_text, _rrf_score, _rrf_rank FROM RAG_SEARCH('rag_search_demo', 'embedding', VEC_FROM_JSON('[0.9, 0.1, 0.0]'), 3, '{ "text_column": "chunk_text", "text_query": "full-text search", "key_columns": ["doc_id", "chunk_index"] }');

doc_id | chunk_index | chunk_text | _rrf_score | _rrf_rank
--- | --- | --- | --- | ---
doc-1 | 1 | It supports vector search and full-text search | 0.03252247488101534 | 1
doc-2 | 0 | Full-text search uses BM25 ranking | 0.03200204813108039 | 2
doc-1 | 0 | TinySQL is a lightweight embeddable SQL engine | 0.01639344262295082 | 3

3. Hybrid search plus neighbor-chunk context expansion, in one call
(replaces wrapping the hybrid result in RAG_CONTEXT_FROM separately).
SELECT doc_id, chunk_index, chunk_text, _hit_rank, _context_offset, _context_rank FROM RAG_SEARCH('rag_search_demo', 'embedding', VEC_FROM_JSON('[0.9, 0.1, 0.0]'), 2, '{ "text_column": "chunk_text", "text_query": "vector search", "key_columns": ["doc_id", "chunk_index"], "expand_before": 1, "expand_after": 1, "doc_id_column": "doc_id", "chunk_index_column": "chunk_index" }') ORDER BY _context_rank;

doc_id | chunk_index | chunk_text | _hit_rank | _context_offset | _context_rank
--- | --- | --- | --- | --- | ---
doc-1 | 0 | TinySQL is a lightweight embeddable SQL engine | 1 | -1 | 1
doc-1 | 1 | It supports vector search and full-text search | 1 | 0 | 2
doc-1 | 2 | RAG pipelines combine retrieval with generation | 1 | 1 | 3
doc-2 | 0 | Full-text search uses BM25 ranking | 2 | 0 | 4

DROP TABLE rag_search_demo
(ok)
CREATE TABLE hybrid_search_demo ( id INT PRIMARY KEY, content TEXT, embedding VECTOR )
(ok)
INSERT INTO hybrid_search_demo VALUES (1, 'database timeout retry guide', '[1.0, 0.0, 0.0]'), (2, 'general systems handbook', '[0.8, 0.2, 0.0]'), (3, 'unrelated cooking notes', '[0.0, 1.0, 0.0]')
(ok)
---- HYBRID_SEARCH: one search term, semantic + full-text ----
The query vector is the embedding of the same search term, generated with
the model used to populate the VECTOR column. The PRIMARY KEY is used
automatically to fuse vector and BM25 candidate lists.
DROP TABLE rag_search_demo; CREATE TABLE hybrid_search_demo ( id INT PRIMARY KEY, content TEXT, embedding VECTOR ); INSERT INTO hybrid_search_demo VALUES (1, 'database timeout retry guide', '[1.0, 0.0, 0.0]'), (2, 'general systems handbook', '[0.8, 0.2, 0.0]'), (3, 'unrelated cooking notes', '[0.0, 1.0, 0.0]'); SELECT id, content, _vec_similarity, _fts_score, _rrf_score, _rrf_rank FROM HYBRID_SEARCH( 'hybrid_search_demo', 'embedding', 'content', 'time?ut retry*', VEC_FROM_JSON('[0.9, 0.1, 0.0]'), 3 ) ORDER BY _rrf_rank;

id | content | _vec_similarity | _fts_score | _rrf_score | _rrf_rank
--- | --- | --- | --- | --- | ---
1 | database timeout retry guide | 0.9938837346736189 | 0.9066488893385708 | 0.03278688524590164 | 1
2 | general systems handbook | 0.9909924304103231 | NULL | 0.016129032258064516 | 2
3 | unrelated cooking notes | 0.11043152607484652 | NULL | 0.015873015873015872 | 3

DROP TABLE hybrid_search_demo
(ok)
CREATE TABLE geo_demo (name TEXT, geometry GEOMETRY)
(ok)
INSERT INTO geo_demo VALUES ('Berlin', '{"type":"Point","coordinates":[13.4050,52.5200]}')
(ok)
INSERT INTO geo_demo VALUES ('Munich', '{"type":"Point","coordinates":[11.5755,48.1372]}')
(ok)
VEC_HYBRID_SEARCH is an alias. Tables without a PRIMARY KEY provide the
identity columns via the optional JSON object:
'{"key_columns":["doc_id","chunk_index"],"index":"hnsw","candidate_k":50}'

## GEOSPATIAL FUNCTIONS

Geometry is stored and returned as GeoJSON, either in a plain TEXT/JSON
column or in the dedicated GEOMETRY column type (validated on write and
canonicalized to stable text). Raw four-number coordinate arguments use
(lat, lon, lat, lon); GeoJSON itself stays [lon, lat].

### `GEO_POINT` — build a GeoJSON Point from (lon, lat[, z])

DROP TABLE hybrid_search_demo; CREATE TABLE geo_demo (name TEXT, geometry GEOMETRY); INSERT INTO geo_demo VALUES ('Berlin', '{"type":"Point","coordinates":[13.4050,52.5200]}'); INSERT INTO geo_demo VALUES ('Munich', '{"type":"Point","coordinates":[11.5755,48.1372]}'); SELECT GEO_POINT(13.4050, 52.5200) AS berlin;

berlin
{"coordinates":[13.405,52.52],"type":"Point"}

ST_MAKEPOINT / ST_POINT: aliases of GEO_POINT
SELECT ST_MAKEPOINT(13.4050, 52.5200) AS berlin, ST_POINT(13.4050, 52.5200) AS also_berlin;

berlin | also_berlin
--- | ---
{"coordinates":[13.405,52.52],"type":"Point"} | {"coordinates":[13.405,52.52],"type":"Point"}

GEO_LON / GEO_X: read a Point's longitude. ST_X is the alias.
SELECT GEO_LON(geometry) AS lon, ST_X(geometry) AS lon_alias FROM geo_demo WHERE name = 'Berlin';

lon | lon_alias
--- | ---
13.405 | 13.405

GEO_LAT / GEO_Y: read a Point's latitude. ST_Y is the alias.
SELECT GEO_LAT(geometry) AS lat, ST_Y(geometry) AS lat_alias FROM geo_demo WHERE name = 'Berlin';

lat | lat_alias
--- | ---
52.52 | 52.52

### `GEO_DISTANCE` — great-circle distance in meters between two points.

HAVERSINE and ST_DISTANCE are aliases.
SELECT a.name, b.name, GEO_DISTANCE(a.geometry, b.geometry) AS meters FROM geo_demo a JOIN geo_demo b ON a.name = 'Berlin' AND b.name = 'Munich';

a.name | b.name | meters
--- | --- | ---
Berlin | Munich | 504307.226096815

GEO_DISTANCE also accepts four raw (lat1, lon1, lat2, lon2) numbers
SELECT GEO_DISTANCE(52.5200, 13.4050, 48.1372, 11.5755) AS meters;

meters
504307.226096815

### `GEO_DWITHIN` — true if two points are within a given radius (meters).

ST_DWITHIN is the alias.
SELECT GEO_DWITHIN( '{"type":"Point","coordinates":[13.4050,52.5200]}', '{"type":"Point","coordinates":[13.4060,52.5205]}', 200) AS within_200m;

within_200m
true

### `GEO_WITHIN_BBOX` — true if a point falls inside [minLon, minLat, maxLon, maxLat].

ST_WITHIN_BBOX is the alias.
SELECT GEO_WITHIN_BBOX(geometry, 5, 47, 15, 55) AS in_germany_ish FROM geo_demo WHERE name = 'Berlin';

in_germany_ish
true

### `GEO_BEARING` — initial compass bearing (0-360 clockwise from north) from the

first point toward the second. ST_AZIMUTH is the alias.
SELECT a.name, b.name, GEO_BEARING(a.geometry, b.geometry) AS bearing_deg FROM geo_demo a JOIN geo_demo b ON a.name = 'Berlin' AND b.name = 'Munich';

a.name | b.name | bearing_deg
--- | --- | ---
Berlin | Munich | 195.63069050650938

### `GEO_MIDPOINT` — great-circle midpoint between two points. ST_MIDPOINT is the alias.

SELECT a.name, b.name, GEO_MIDPOINT(a.geometry, b.geometry) AS midpoint FROM geo_demo a JOIN geo_demo b ON a.name = 'Berlin' AND b.name = 'Munich';

a.name | b.name | midpoint
--- | --- | ---
Berlin | Munich | {"coordinates":[12.448041477751076,50.33218058133119],"type":"Point"}

### `GEO_DESTINATION` — project a point along a bearing for a distance (meters).

ST_PROJECT is the alias.
SELECT GEO_DESTINATION(geometry, 90, 10000) AS ten_km_east FROM geo_demo WHERE name = 'Berlin';

ten_km_east
{"coordinates":[13.552796765540279,52.51990795285367],"type":"Point"}

### `GEO_WITHIN_POLYGON` — point-in-polygon(-or-multipolygon) test.

ST_WITHIN is the alias; ST_CONTAINS takes the same two arguments reversed
(polygon, point), matching PostGIS's ST_Contains(A, B) = "A contains B".
SELECT GEO_WITHIN_POLYGON( '{"type":"Point","coordinates":[0.5,0.5]}', '{"type":"Polygon","coordinates":[[[0,0],[1,0],[1,1],[0,1],[0,0]]]}' ) AS point_inside, ST_CONTAINS( '{"type":"Polygon","coordinates":[[[0,0],[1,0],[1,1],[0,1],[0,0]]]}', '{"type":"Point","coordinates":[9,9]}' ) AS point_outside;

point_inside | point_outside
--- | ---
true | false

### `GEO_POLYGON_AREA` — area in square meters, exterior ring minus holes.

ST_AREA is the alias. Accepts a Polygon or MultiPolygon.
SELECT GEO_POLYGON_AREA( '{"type":"Polygon","coordinates":[[[13.0,52.0],[13.1,52.0],[13.1,52.1],[13.0,52.1],[13.0,52.0]]]}' ) AS square_meters;

square_meters
7.603723971920487e+07

### `GEO_LENGTH` — sum of great-circle segment lengths of a LineString, in meters.

ST_LENGTH is the alias.
SELECT GEO_LENGTH( '{"type":"LineString","coordinates":[[13.40,52.52],[13.41,52.52],[13.41,52.53]]}' ) AS meters;

meters
1788.5531141424217

### `GEO_INTERSECTS` — true if two geometries (point/line/polygon, any combination)

share at least one point. ST_INTERSECTS is the alias. Respects polygon
holes: a shape nested in another's hole is NOT reported as intersecting.
SELECT GEO_INTERSECTS( '{"type":"Polygon","coordinates":[[[0,0],[1,0],[1,1],[0,1],[0,0]]]}', '{"type":"Polygon","coordinates":[[[0.5,0.5],[1.5,0.5],[1.5,1.5],[0.5,1.5],[0.5,0.5]]]}' ) AS overlapping_squares;

overlapping_squares
true

### `GEO_DISJOINT` — the exact negation of GEO_INTERSECTS. ST_DISJOINT is the alias.

SELECT GEO_DISJOINT( '{"type":"Polygon","coordinates":[[[0,0],[1,0],[1,1],[0,1],[0,0]]]}', '{"type":"Polygon","coordinates":[[[10,10],[11,10],[11,11],[10,11],[10,10]]]}' ) AS far_apart_squares;

far_apart_squares
true

### `GEO_EQUALS` — same coordinates, allowing for a different start vertex,

winding direction, or Polygon-vs-single-part-MultiPolygon wrapping (NOT
full OGC point-set equality -- two differently-vertexized polygons
covering the same area are not detected as equal). ST_EQUALS is the alias.
SELECT GEO_EQUALS( '{"type":"Polygon","coordinates":[[[0,0],[1,0],[1,1],[0,1],[0,0]]]}', '{"type":"Polygon","coordinates":[[[1,0],[1,1],[0,1],[0,0],[1,0]]]}' ) AS same_square_rotated_start;

same_square_rotated_start
true

### `GEO_BUFFER` — approximate a circular buffer (meters) around a point as a

regular polygon; the optional 3rd argument sets the vertex count (8-256,
default 32). ST_BUFFER is the alias.
SELECT GEO_BUFFER(geometry, 500, 16) AS half_km_circle FROM geo_demo WHERE name = 'Berlin';

half_km_circle
{"coordinates":[[[13.404999999999973,52.524496608029594],[13.407828240070671,52.524154290420434],[13.410225790262416,52.523179466961025],[13.411827597276556,52.52172058096743],[13.412389848573184,52.51999976988185],[13.41182706244581,52.51827902619631],[13.410225033897405,52.51682030292081],[13.407827705239924,52.515845642179514],[13.404999999999973,52.515503391970405],[13.402172294760021,52.515845642179514],[13.399774966102541,52.51682030292081],[13.39817293755425,52.51827902619631],[13.397610151426761,52.51999976988185],[13.39817240272339,52.52172058096743],[13.399774209737643,52.523179466961025],[13.402171759929274,52.524154290420434],[13.404999999999973,52.524496608029594]]],"type":"Polygon"}

### `GEO_CONVEX_HULL` — convex hull of every vertex in a geometry, as a Polygon.

ST_CONVEXHULL is the alias. Computed in plain lon/lat space (a standard
planar approximation, not a rigorous spherical hull).
SELECT GEO_CONVEX_HULL( '{"type":"MultiPoint","coordinates":[[0,0],[2,0],[2,2],[0,2],[1,1]]}' ) AS hull;

hull
{"coordinates":[[[0,0],[2,0],[2,2],[0,2],[0,0]]],"type":"Polygon"}

### `GEO_ENVELOPE` — a geometry's bounding box as a Polygon (vs. GEO_BBOX's plain

array). ST_ENVELOPE is the alias.
SELECT GEO_ENVELOPE( '{"type":"LineString","coordinates":[[0,0],[3,1],[1,4]]}' ) AS bbox_polygon;

bbox_polygon
{"coordinates":[[[0,0],[3,0],[3,4],[0,4],[0,0]]],"type":"Polygon"}

### `GEO_LINE_INTERPOLATE` — the point a fraction (0-1) of the way along a

LineString, by actual distance, not vertex count. ST_LINE_INTERPOLATE_POINT
is the alias.
SELECT GEO_LINE_INTERPOLATE( '{"type":"LineString","coordinates":[[13.40,52.52],[13.50,52.60]]}', 0.5 ) AS midway;

midway
{"coordinates":[13.45,52.56],"type":"Point"}

### `GEO_CLIP` — Sutherland-Hodgman clip of a geometry to a convex boundary

polygon. ST_CLIP is the alias. Rejects a non-convex boundary unless the
optional 3rd argument (allow_nonconvex) is true -- a best-effort,
not-guaranteed-correct result in that case.
SELECT GEO_CLIP( '{"type":"Polygon","coordinates":[[[0,0],[4,0],[4,4],[0,4],[0,0]]]}', '{"type":"Polygon","coordinates":[[[1,1],[3,1],[3,3],[1,3],[1,1]]]}' ) AS clipped_to_inner_square;

clipped_to_inner_square
{"coordinates":[[[1,3],[1,1],[3,1],[3,3],[1,3]]],"type":"Polygon"}

### `GEOSPATIAL` — EDITING AND QUALITY

### `GEO_SIMPLIFY` — reduce vertex count. Accepts Douglas-Peucker ('dp', the

default), 'visvalingam-effective', and 'visvalingam-weighted'.
ST_SIMPLIFY is the alias.
SELECT GEO_SIMPLIFY( '{"type":"LineString","coordinates":[[0,0],[1,0.05],[2,0],[3,2],[4,2]]}', 0.2, 'dp' ) AS simplified;

simplified
{"coordinates":[[0,0],[2,0],[3,2],[4,2]],"type":"LineString"}

### `GEO_BBOX` — bounding box as [minLon, minLat, maxLon, maxLat]. ST_BBOX is the alias.

SELECT GEO_BBOX( '{"type":"Polygon","coordinates":[[[-2,-2],[-2,2],[2,2],[2,-2],[-2,-2]]]}' ) AS bbox;

bbox
[-2,-2,2,2]

### `GEO_CENTROID` — area/length-weighted centroid of a geometry. ST_CENTROID is the alias.

SELECT GEO_CENTROID( '{"type":"Polygon","coordinates":[[[-2,-2],[-2,2],[2,2],[2,-2],[-2,-2]]]}' ) AS centroid;

centroid
{"coordinates":[0,0],"type":"Point"}

### `GEO_AFFINE` — shift, scale, and rotate a geometry around an anchor (default

anchor: the geometry's own bbox center; here given explicitly as the last
two arguments). ST_AFFINE is the alias.
SELECT GEO_AFFINE( '{"type":"Point","coordinates":[1,0]}', 0, 0, 1.0, 90, 0, 0 ) AS rotated_90deg_around_origin;

rotated_90deg_around_origin
{"coordinates":[6.123233995736757e-17,1],"type":"Point"}

### `GEO_SMOOTH` — Chaikin corner-cutting smoothing, 0-8 iterations. ST_SMOOTH is the alias.

SELECT GEO_SMOOTH( '{"type":"LineString","coordinates":[[0,0],[1,1],[2,0]]}', 1 ) AS smoothed;

smoothed
{"coordinates":[[0,0],[0.25,0.25],[0.75,0.75],[1.25,0.75],[1.75,0.25],[2,0]],"type":"LineString"}

### `GEO_DROP_HOLES` — remove every hole from a Polygon/MultiPolygon, keeping only

exterior rings. ST_REMOVE_HOLES is the alias.
SELECT GEO_DROP_HOLES( '{"type":"Polygon","coordinates":[[[-2,-2],[-2,2],[2,2],[2,-2],[-2,-2]],[[-1,-1],[-1,1],[1,1],[1,-1],[-1,-1]]]}' ) AS outer_ring_only;

outer_ring_only
{"coordinates":[[[-2,-2],[-2,2],[2,2],[2,-2],[-2,-2]]],"type":"Polygon"}

### `GEO_CLEAN` — remove repeated consecutive vertices and normalize ring closure.

ST_CLEAN is the alias. Rejects a result collapsing below the GeoJSON
minimum vertex count instead of returning invalid output.
SELECT GEO_CLEAN( '{"type":"LineString","coordinates":[[0,0],[0,0],[1,1],[2,2]]}' ) AS deduplicated;

deduplicated
{"coordinates":[[0,0],[1,1],[2,2]],"type":"LineString"}

### `GEO_SNAP` — round coordinates to a grid, then clean. ST_SNAPTOGRID is the alias.

SELECT GEO_SNAP( '{"type":"Point","coordinates":[13.40473,52.52019]}', 0.001 ) AS snapped;

snapped
{"coordinates":[13.405000000000001,52.52],"type":"Point"}

### `GEO_IS_VALID` — structural GeoJSON check (not full topology validation --

no self-intersection detection). ST_ISVALID is the alias.
SELECT GEO_IS_VALID('{"type":"Point","coordinates":[13.40,52.52]}') AS valid, GEO_IS_VALID('{"type":"Polygon","coordinates":[[[0,0],[1,1]]]}') AS invalid_too_few_points;

valid | invalid_too_few_points
--- | ---
true | false

CREATE TABLE geo_typed_demo (id INT, shape GEOMETRY)
(ok)
INSERT INTO geo_typed_demo VALUES (1, '{"type":"Point","coordinates":[13.40,52.52]}')
(ok)
CREATE TABLE ... GEOMETRY: a first-class, validated, canonicalizing column
type (not just TEXT/JSON). A bare number or a Feature/FeatureCollection is
rejected -- a GEOMETRY column holds a Geometry.
CREATE TABLE geo_typed_demo (id INT, shape GEOMETRY); INSERT INTO geo_typed_demo VALUES (1, '{"type":"Point","coordinates":[13.40,52.52]}'); SELECT id, shape FROM geo_typed_demo;

id | shape
--- | ---
1 | {"coordinates":[13.4,52.52],"type":"Point"}

CAST(x AS GEOMETRY) validates and canonicalizes the same way a column write does
SELECT CAST('{"coordinates":[1,2],"type":"Point"}' AS GEOMETRY) AS canonical;

canonical
{"coordinates":[1,2],"type":"Point"}

DROP TABLE geo_typed_demo
(ok)
CREATE TABLE geo_region_demo (region TEXT, footprint GEOMETRY, population FLOAT)
(ok)
INSERT INTO geo_region_demo VALUES ('north', '{"type":"Polygon","coordinates":[[[0,0],[1,0],[1,1],[0,1],[0,0]]]}', 100)
(ok)
INSERT INTO geo_region_demo VALUES ('north', '{"type":"Polygon","coordinates":[[[1,0],[2,0],[2,1],[1,1],[1,0]]]}', 300)
(ok)
INSERT INTO geo_region_demo VALUES ('south', '{"type":"Polygon","coordinates":[[[10,10],[11,10],[11,11],[10,11],[10,10]]]}', 50)
(ok)

### `GEOSPATIAL` — REGION OPERATIONS, SEARCH, AND CLASSIFICATION

Mapshaper-inspired region-editing verbs and BI-oriented helpers for
turning raw geometry into location-based KPIs and choropleth dashboards.

### `GEO_DISSOLVE` — merge every geometry in a group into one, by cancelling

shared directed edges between adjacent polygons. GEO_UNION_AGG and
ST_UNION are the same operation under aggregate-style names. Correct for
topologically-clean, vertex-aligned adjacent input (e.g. this project's
own output, or real GIS boundary data) -- NOT a general polygon-boolean-
union for overlapping-but-misaligned input.
DROP TABLE geo_typed_demo; CREATE TABLE geo_region_demo (region TEXT, footprint GEOMETRY, population FLOAT); INSERT INTO geo_region_demo VALUES ('north', '{"type":"Polygon","coordinates":[[[0,0],[1,0],[1,1],[0,1],[0,0]]]}', 100); INSERT INTO geo_region_demo VALUES ('north', '{"type":"Polygon","coordinates":[[[1,0],[2,0],[2,1],[1,1],[1,0]]]}', 300); INSERT INTO geo_region_demo VALUES ('south', '{"type":"Polygon","coordinates":[[[10,10],[11,10],[11,11],[10,11],[10,10]]]}', 50); SELECT region, GEO_DISSOLVE(footprint) AS boundary FROM geo_region_demo GROUP BY region ORDER BY region;

region | boundary
--- | ---
north | {"coordinates":[[[0,0],[1,0],[2,0],[2,1],[1,1],[0,1],[0,0]]],"type":"Polygon"}
south | {"coordinates":[[[10,10],[11,10],[11,11],[10,11],[10,10]]],"type":"Polygon"}

GEO_UNION_AGG / ST_UNION: aliases of GEO_DISSOLVE
SELECT region, GEO_UNION_AGG(footprint) AS boundary FROM geo_region_demo GROUP BY region ORDER BY region;

region | boundary
--- | ---
north | {"coordinates":[[[0,0],[1,0],[2,0],[2,1],[1,1],[0,1],[0,0]]],"type":"Polygon"}
south | {"coordinates":[[[10,10],[11,10],[11,11],[10,11],[10,10]]],"type":"Polygon"}

### `GEO_BBOX_AGG` — bounding box across every geometry in a group.

SELECT region, GEO_BBOX_AGG(footprint) AS bbox FROM geo_region_demo GROUP BY region ORDER BY region;

region | bbox
--- | ---
north | [0,0,2,1]
south | [10,10,11,11]

### `GEO_CENTROID_AGG` — (optionally weighted) centroid across a group. The

optional weight combines with GEO_CENTROID's own area/length weighting --
here, a population-weighted centroid of already-area-weighted per-row centroids.
SELECT region, GEO_CENTROID_AGG(footprint, population) AS weighted_centroid FROM geo_region_demo GROUP BY region ORDER BY region;

region | weighted_centroid
--- | ---
north | {"coordinates":[1.25,0.5],"type":"Point"}
south | {"coordinates":[10.5,10.5],"type":"Point"}

### `GEO_SEARCH` — an indexed bbox/radius search over a table, backed by a lazy,

per-table grid index invalidated automatically on writes. Exact for Point
columns; for polygon/line columns it indexes by centroid (a large shape
whose edge -- not centroid -- clips into the window is a false negative;
use GEO_INTERSECTS for exact shape overlap).
SELECT region FROM GEO_SEARCH('geo_region_demo', 'footprint', 'bbox', -1, -1, 3, 3);

region
north
north

SELECT region FROM GEO_SEARCH('geo_region_demo', 'footprint', 'radius', 0.5, 0.5, 200000);

region
north
north

DROP TABLE geo_region_demo
(ok)
CREATE TABLE geo_kpi_demo (district TEXT, buildings INT)
(ok)
INSERT INTO geo_kpi_demo VALUES ('a', 5)
(ok)
INSERT INTO geo_kpi_demo VALUES ('b', 12)
(ok)
INSERT INTO geo_kpi_demo VALUES ('c', 48)
(ok)
INSERT INTO geo_kpi_demo VALUES ('d', 51)
(ok)
INSERT INTO geo_kpi_demo VALUES ('e', 95)
(ok)
EQUAL_INTERVAL / NATURAL_BREAKS: choropleth classification window
functions, bucketing a KPI column into N legend classes. Quantile
classification needs no new function -- NTILE(n) OVER (ORDER BY kpi)
already does that.
DROP TABLE geo_region_demo; CREATE TABLE geo_kpi_demo (district TEXT, buildings INT); INSERT INTO geo_kpi_demo VALUES ('a', 5); INSERT INTO geo_kpi_demo VALUES ('b', 12); INSERT INTO geo_kpi_demo VALUES ('c', 48); INSERT INTO geo_kpi_demo VALUES ('d', 51); INSERT INTO geo_kpi_demo VALUES ('e', 95); SELECT district, buildings, EQUAL_INTERVAL(3) OVER (ORDER BY buildings) AS equal_interval_class, NATURAL_BREAKS(3) OVER (ORDER BY buildings) AS natural_breaks_class, NTILE(3) OVER (ORDER BY buildings) AS quantile_class FROM geo_kpi_demo ORDER BY buildings;

district | buildings | equal_interval_class | natural_breaks_class | quantile_class
--- | --- | --- | --- | ---
a | 5 | 1 | 1 | 1
b | 12 | 1 | 1 | 1
c | 48 | 2 | 2 | 2
d | 51 | 2 | 2 | 2
e | 95 | 3 | 3 | 3

DROP TABLE geo_kpi_demo
(ok)
DROP TABLE geo_demo
(ok)

## TILE FUNCTIONS

Web Mercator XYZ tile addressing, for working with MBTiles tilesets in
SQL. XYZ (web clients, /{z}/{x}/{y}.png) counts rows from the top; TMS
(what MBTiles stores in tiles.tile_row) counts rows from the bottom.
TILE_X / TILE_Y: the XYZ tile column/row containing (lon, zoom) / (lat, zoom)
DROP TABLE geo_kpi_demo; DROP TABLE geo_demo; SELECT TILE_X(13.405, 14) AS col, TILE_Y(52.520, 14) AS row;

col | ROW
--- | ---
8802 | 5373

### `TILE_ZXY` — the covering tile at one call, including the MBTiles TMS row

SELECT TILE_ZXY(13.405, 52.520, 14) AS tile;

tile
{"tile_row":11010,"x":8802,"y":5373,"z":14}

### `TILE_FLIP_Y` — convert an XYZ row to/from the MBTiles TMS tile_row (its own

inverse). TILE_ROW_TMS is the alias.
SELECT TILE_FLIP_Y(TILE_Y(52.520, 14), 14) AS tms_row;

tms_row
11010

TILE_LON / TILE_LAT: the (west, north) edge of a tile
SELECT TILE_LON(TILE_X(13.405, 14), 14) AS west_edge, TILE_LAT(TILE_Y(52.520, 14), 14) AS north_edge;

west_edge | north_edge
--- | ---
13.4033203125 | 52.522905940278065

### `TILE_BBOX` — a tile's geographic bounds as [west, south, east, north]

SELECT TILE_BBOX(14, TILE_X(13.405, 14), TILE_Y(52.520, 14)) AS bounds;

bounds
[13.4033203125,52.50953477032728,13.42529296875,52.522905940278065]

TILE_QUADKEY / TILE_FROM_QUADKEY: Bing Maps quadkey encoding, and its inverse
SELECT TILE_QUADKEY(14, TILE_X(13.405, 14), TILE_Y(52.520, 14)) AS quadkey;

quadkey
12021023322212

SELECT TILE_FROM_QUADKEY('12020120310320') AS tile;

tile
{"x":8500,"y":5286,"z":14}

### `TILE_PARENT` — the containing tile one zoom level up (NULL at zoom 0)

SELECT TILE_PARENT(14, TILE_X(13.405, 14), TILE_Y(52.520, 14)) AS parent;

parent
{"x":4401,"y":2686,"z":13}

### `TILE_COUNT` — how many tiles a fully populated zoom level holds (4^zoom)

SELECT TILE_COUNT(10) AS tiles_at_zoom_10;

tiles_at_zoom_10
1048576

### `TILE_CONTAINS` — whether a tile covers a point (edges belong to the tile on

their north/west sides, matching TILE_X/TILE_Y's own assignment)
SELECT TILE_CONTAINS(14, TILE_X(13.405, 14), TILE_Y(52.520, 14), 13.405, 52.520) AS covers_point;

covers_point
true

CREATE TABLE tile_lookup_demo (zoom_level INT, tile_column INT, tile_row INT, tile_data BLOB)
(ok)
INSERT INTO tile_lookup_demo VALUES (14, TILE_X(13.405, 14), TILE_FLIP_Y(TILE_Y(52.520, 14), 14), BLOB_FROM_HEX('00'))
(ok)
A tiles table lookup, converting the client's XYZ row to the MBTiles TMS
row stored on disk:
CREATE TABLE tile_lookup_demo (zoom_level INT, tile_column INT, tile_row INT, tile_data BLOB); INSERT INTO tile_lookup_demo VALUES (14, TILE_X(13.405, 14), TILE_FLIP_Y(TILE_Y(52.520, 14), 14), BLOB_FROM_HEX('00')); SELECT tile_data FROM tile_lookup_demo WHERE zoom_level = 14 AND tile_column = TILE_X(13.405, 14) AND tile_row = TILE_FLIP_Y(TILE_Y(52.520, 14), 14);

tile_data
[0]

DROP TABLE tile_lookup_demo
(ok)