func.slt.part

statement ok
SET RW_IMPLICIT_FLUSH TO true;

query I
select nullif(2,2);
----
NULL

query I
select nullif(1,2);
----
1

query I
select mod(5,2);
----
1

query I
select mod(7.7,2.2);
----
1.1

query IIIII
select coalesce(1,2), coalesce(null,2), coalesce(null), coalesce(null,null), coalesce(null,2,3);
----
1 2 NULL NULL 2

query I
select coalesce(1, 1 / (v1-v1)) from generate_series(1, 3, 1) as t(v1) where v1 != 2;
----
1
1

query I
select true or (1 / (v1-v1) > 2) from generate_series(1, 3, 1) as t(v1) where v1 != 2;
----
t
t

query I
select false and (1 / (v1-v1) > 2) from generate_series(1, 3, 1) as t(v1) where v1 != 2;
----
f
f

query I
select case when 1=1 then 9 else 1 / (v1-v1) end from generate_series(1, 3, 1) as t(v1) where v1 != 2;
----
9
9

statement ok
create table t1 (v1 int, v2 int, v3 int);

statement ok
insert into t1 values (1,null,null),(null,2,null),(null,null,3);

query I
select nullif(t1.v1,1) from t1;
----
NULL
NULL
NULL

query I rowsort
select coalesce(v1,v2,v3) from t1;
----
1
2
3

statement ok
drop table t1;

statement ok
create table t (v1 int);

statement ok
create table b (b1 int, b2 int);


statement ok
insert into t values (2);

statement ok
insert into b values (2, 1);

query T
SELECT 1 in (3, 0.5*2, min(v1)) from t;
----
t

query T
SELECT 1 in (3, 0.5*2, min(v1), null) from t;
----
t

query T
SELECT null in (null, 3, 0.5*2, min(v1)) from t;
----
NULL

query T
SELECT 99 in (null, 3, 0.5*2, min(v1)) from t;
----
NULL

query I
SELECT b2 from b where 1 in (3, 1.0, (select min(v1) from t));
----
1

query I
SELECT b2 from b where exists (select 2 from t where v1 in (3, 1.0, b1));
----
1

statement ok
drop table t;

statement ok
drop table b;

query T
select concat_ws(',', 'a', 'b');
----
a,b

query T
select concat_ws(NULL, 'a', 'b');
----
NULL

query T
select concat_ws(',', NULL, 'b');
----
b

query T
select concat_ws(NULL, NULL, 'b');
----
NULL

query T
select concat_ws(',', 1, 1.01, 'A', true, NULL);
----
1,1.01,A,t

statement ok
create table t (v1 varchar, v2 smallint, v3 int, v4 decimal, v5 real, v6 double, v7 bool, v8 string);

statement ok
insert into t values (',', 1, 2, 3.01, 4, 5.01, true, NULL);

query T
select concat_ws(v1, v2, v3, v4, v5, v6, v7, v8) from t;
----
1,2,3.01,4,5.01,t


statement ok
drop table t;

query T
select md5('abcdefghijklmnopqrstuvwxyz');
----
c3fcd3d76192e4007dfb496cca67e13b

query T
select md5('hello RUST');
----
917b821a0a5f23ab0cfdb36056d2eb9d

query T
select md5('hello world');
----
5eb63bbbe01eeed093cb22bb8f5acdc3

query T
select split_part('abc~@~def~@~ghi', '~@~', 2);
----
def

query T
select split_part('abc,def,ghi,jkl', ',', -2);
----
ghi


query I
select char_length('hello world');
----
11

query I
select char_length('abcdefghijklmnopqrstuvwxyz');
----
26

query I
select length('hello world');
----
11

query I
select length('你好hhh');
----
5

query I
select octet_length('hello world');
----
11

query I
select octet_length('你好hhh');
----
9

query I
select bit_length('hello world') / octet_length('hello world');
----
8

query I
select bit_length('你好hhh') / octet_length('你好hhh');
----
8

query I
select ascii('abc');
----
97

query I
select repeat('hello', 3);
----
hellohellohello

query I
select repeat('hello', -1);
----
(empty)

query T
select substr('hello', NULL);
----
NULL

query T
select substr(NULL, 1);
----
NULL

query T
select pg_typeof('123');
----
unknown

query T
select pg_typeof('123'::varchar);
----
character varying

query T
select pg_typeof('123'::int);
----
integer

query T
select pg_typeof(null);
----
unknown

query T
select pg_typeof(null::varchar);
----
character varying

query T
select pg_typeof(null::bool);
----
boolean

query T
select pg_typeof(-9223372036854775808);
----
bigint

query T
select pg_typeof(round(null));
----
double precision

query T
select pg_typeof(row(true, 1, 'hello'));
----
record

query T
select pg_typeof(array[1, 2]);
----
integer[]

# Note that PostgreSQL still displays `integer[]` in this case.
query T
select pg_typeof(array[array[1, 2], array[3, 4]]);
----
integer[][]

query error unexpected arguments number
select pg_typeof();

query I
select count(current_database());
----
1

query T
select regexp_match('abc', 'bc');
----
{bc}

query T
select regexp_match('abc', 'Bc');
----
NULL

query T
select regexp_match('abc', 'Bc', 'i');
----
{bc}

query T
select regexp_match('abc', 'Bc', 'ic');
----
NULL

query T
select regexp_match('abc', 'Bc', 'ici');
----
{bc}

query T
select regexp_match('abc', '(?i)Bc');
----
{bc}

query T
select regexp_match('abc', '(?i)Bc', 'c');
----
{bc}

query T
select regexp_match('abc', 'd');
----
NULL

query T
select regexp_match('abc', NULL);
----
NULL

query T
select regexp_match('abc', 'a', NULL);
----
NULL

query T
select regexp_match('foobarbequebaz', '(bar)(beque)');
----
{bar,beque}

query T
select regexp_match('foobarbequebaz', 'bar(beque)');
----
{beque}

query T
select regexp_match('foobarbequebaz', 'bar((be)|(bo))(que)');
----
{be,be,NULL,que}

query T
select regexp_match('foobarbequebaz', 'bar.*que');
----
{barbeque}

query T
select regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
----
{abc,01234,xyz}

query T
select regexp_match(string, 'a')
from (values
    ('abc'),
    ('def'),
    ('ghi')
) t(string);
----
{a}
NULL
NULL

query T
select regexp_match(string, pattern, flags)
from (values
    ('abc', 'bc', ''),
    ('abc', 'Bc', ''),
    ('abc', 'Bc', 'i')
) t(string, pattern, flags);
----
{bc}
NULL
{bc}

query T
select regexp_matches('foobarbequebazilbarfbonk', '(b[^b]+)(b[^b]+)', 'g');
----
{bar,beque}
{bazil,barf}

query T
select regexp_matches('foobarbequebazfoobarbequebaz', 'bar((be)|(bo))(que)');
----
{be,be,NULL,que}
{be,be,NULL,que}

query T
select regexp_matches('abcabc', 'Bc');
----

query T
select regexp_matches('abcabc', 'Bc', 'i');
----
{bc}
{bc}

query T
select regexp_matches('abcabc', 'Bc', 'ic');
----

query T
select regexp_matches('abcabc', 'Bc', 'ici');
----
{bc}
{bc}

query T
select regexp_matches('abcabc', '(?i)Bc');
----
{bc}
{bc}

query T
select regexp_matches('abcabc', '(?i)Bc', 'c');
----
{bc}
{bc}

query T
select regexp_matches('abcabc', NULL);
----

query T
select regexp_matches('abcabc', 'a', NULL);
----

query T
select regexp_replace('foobarbaz', 'b..', 'X');
----
fooXbaz

query T
select regexp_replace('foobarbaz', 'b..', 'X', 'g');
----
fooXX

query T
select regexp_replace('foobarbaz', 'b(..)', 'X\1Y', 'g');
----
fooXarYXazY

query T
select regexp_replace('foobarbazabc', 'b(..)', 'X\1Y', 'g');
----
fooXarYXazYabc

query T
select regexp_replace('A PostgreSQL function', 'a|e|i|o|u', 'X', 1, 0, 'i');
----
X PXstgrXSQL fXnctXXn

query T
select regexp_replace('A PostgreSQL function', 'a|e|i|o|u', 'X', 1, 3, 'i');
----
A PostgrXSQL function

# Using Parenthesized Subexpression in Replacement
query T
select regexp_replace('hello hello', '(hel)(lo)', '\2\1');
----
lohel hello

# Literal Backslash in Replacement
query T
select regexp_replace('path\to\directory', '\\', '/');
----
path/to\directory

# Multiple Flags
query T
select regexp_replace('HELLO world', 'a|e|i|o|u', 'X', 'ig');
----
HXLLX wXrld

# No Match Found
query T
select regexp_replace('apple', 'orange', 'fruit');
----
apple

# Using `\&` in Replacement
query T
select regexp_replace('abc123', 'abc', 'prefix\&suffix');
----
prefixabcsuffix123

query error invalid input syntax for type integer: "g"
select regexp_replace('foobarbaz', 'b..', 'X', 1, 'g');

query error invalid input syntax for type integer: "g"
select regexp_replace('foobarbaz', 'b..', 'X', 'g', 1);

# With Unicode
query T
select regexp_replace('foo💩barbaz', 'b..', 'X');
----
foo💩Xbaz

query T
select regexp_replace('foo💩bar💩baz', 'b..', 'X');
----
foo💩X💩baz

query T
select regexp_replace('foo💩bar💩baz', 'b..', 'X', 'g');
----
foo💩X💩X

query T
select regexp_replace('foo💩bar💩baz', '💩', 'X');
----
fooXbar💩baz

query T
select regexp_replace('💩💩💩💩💩', '💩', 'X', 'g');
----
XXXXX

query T
select regexp_replace('aAA', 'a|e|i|o|u', 'X', 'ig');
----
XXX

query T
select regexp_replace('💩💩💩💩💩foo🤔️bar亲爱的😭baz这不是爱情❤️‍🔥', 'baz(...)', '这是🥵', 'ic');
----
💩💩💩💩💩foo🤔️bar亲爱的😭这是🥵爱情❤️‍🔥

# Positive Lookahead
query T
select regexp_replace('foobarbaz', 'a(?=r)', 'X');
----
foobXrbaz

# Negative Lookahead
query T
select regexp_replace('chocolate', 'o(?!c)', 'X');
----
chocXlate

# Positive Lookbehind
query T
select regexp_replace('foobarXaz', '(?<=X)a', 'X');
----
foobarXXz

# Negative Lookbehind
query T
select regexp_replace('foobarXaz', '(?<!X)a', 'X');
----
foobXrXaz

query T
select regexp_count('ABCABCAXYaxy', 'A.');
----
3

query T
select regexp_count('ABCABCAX🤔️axy', 'A.');
----
3

query T
select regexp_count('ABCABCAXYaxy', 'A.', 1, 'i');
----
4

query T
select regexp_count('ABCABCAbXYaxy', 'AB');
----
2

query T
select regexp_count('ABCABCAbXYaxy', 'AB', 1, 'i');
----
3

query T
select regexp_count('ABCABCAXYaxy', '[a-c]', 1, 'i');
----
8

query T
select regexp_count('123ABC#456', '\d{3}');
----
2

query T
select regexp_count('foobarbaz', 'b..', 3);
----
2

query error invalid input syntax for type integer: "g"
select regexp_count('foobarbaz', 'b..', 'g');

query error invalid input syntax for type integer: "i"
select regexp_count('foobarbaz', 'b..', 'i');

query error invalid regular expression option: "a"
select regexp_count('foobarbaz', 'b..', 3, 'a');

query error regexp_count\(\) does not support the "global" option
select regexp_count('foobar', 'b..', 1, 'g');

query T
select regexp_count('💩💩💩💩💩foo🤔️bar亲爱的😭baz这不是爱情❤️‍🔥', '亲爱的😭', 1, 'i');
----
1

query T
select regexp_split_to_array('apple,banana,orange', ',');
----
{apple,banana,orange}

query T
select regexp_split_to_array('apple.banana!orange', '[.!]');
----
{apple,banana,orange}

query T
select regexp_split_to_array('applebananaorange', ',');
----
{applebananaorange}

query T
select regexp_split_to_array('', ',');
----
{""}

query T
select regexp_split_to_array('the quick brown fox jumps over the lazy dog', '\s+');
----
{the,quick,brown,fox,jumps,over,the,lazy,dog}

query T
select regexp_split_to_array('the quick brown fox jumps over the lazy dog', '\s*');
----
{t,h,e,q,u,i,c,k,b,r,o,w,n,f,o,x,j,u,m,p,s,o,v,e,r,t,h,e,l,a,z,y,d,o,g}

query T
select regexp_split_to_array('apple\\banana,orange', '\\\\|,');
----
{apple,banana,orange}

query T
select regexp_split_to_array('apple!!!banana?orange???grape', '[!?]+');
----
{apple,banana,orange,grape}

query T
select regexp_split_to_array('💩💩💩💩💩foo🤔️bar亲爱的😭baz这不是爱情❤️‍🔥', '亲爱的😭');
----
{💩💩💩💩💩foo🤔️bar,baz这不是爱情❤️‍🔥}