### ----------------------------------------------------
### -- SQL::Abstract
### -- Licenses: Artistic-2.0
### -- Authors: Leon Timmermans
### -- File: t/01-basic.rakutest
### ----------------------------------------------------
### -- Chunk 1 of 2
#! raku

use Test;
use SQL::Abstract :functions;

sub result(Str $sql, @arguments?) {
	SQL::Query.new($sql, @arguments);
}

my $result1 = result('SELECT * FROM foo', [1, 2, 3]);
is $result1.sql, 'SELECT * FROM foo';
is +$result1.arguments, 3;

my $renderer = SQL::Abstract::Renderer::Postgres.new(:placeholders<postgres>);
my $abstract = SQL::Abstract.new(:$renderer);

my $select-simple1 = $abstract.select('my_table');

is-deeply $select-simple1, result('SELECT * FROM my_table', []);

my $select-simple2 = $abstract.select('my_table', <bar baz>);
is-deeply $select-simple2, result('SELECT bar, baz FROM my_table', []);

my $select-simple3 = $abstract.select('my_table', <bar baz>, { :1foo });
is-deeply $select-simple3, result('SELECT bar, baz FROM my_table WHERE foo = $1', [1]);

my $select-simple4 = $abstract.select('my_table', <bar baz>, { :foo(Any) });
is-deeply $select-simple4, result('SELECT bar, baz FROM my_table WHERE foo IS NULL', []);

my $select-simple5 = $abstract.select('my_table', <bar baz>, { :foo(:null) });
is-deeply $select-simple5, result('SELECT bar, baz FROM my_table WHERE foo IS NULL', []);

my $select-simple6 = $abstract.select('my_table', <bar baz>, { :foo('<' => 3) });
is-deeply $select-simple6, result('SELECT bar, baz FROM my_table WHERE foo < $1', [3]);

 my $select-simple7 = $abstract.select('my_table', <bar baz>, { :foo(\(:and('<' => 3, '>' => 0))) });
is-deeply $select-simple7, result('SELECT bar, baz FROM my_table WHERE foo < $1 AND foo > $2', [3, 0]);

my $select-simple8 = $abstract.select('my_table', <bar baz>, { :foo('<' => \'NOW()') });
is-deeply $select-simple8, result('SELECT bar, baz FROM my_table WHERE foo < NOW()', []);

my $select-simple9 = $abstract.select('my_table', <bar baz>, (:1foo));
is-deeply $select-simple9, result('SELECT bar, baz FROM my_table WHERE foo = $1', [1]);

my $select-simple10 = $abstract.select('my_table', <bar baz>, (:1foo, :1bar));
is-deeply $select-simple10, result('SELECT bar, baz FROM my_table WHERE foo = $1 AND bar = $2', [1, 1]);

my $select-simple11 = $abstract.select('my_table', 'bar', (:1foo, :1bar));
is-deeply $select-simple11, result('SELECT bar FROM my_table WHERE foo = $1 AND bar = $2', [1, 1]);

my $like = \(:op[ 'like' , \(:ident<foo>), 'foo%', '\\']);
my $select-simple12 = $abstract.select('my_table', <bar baz>, $like);
is-deeply $select-simple12, result('SELECT bar, baz FROM my_table WHERE foo LIKE $1 ESCAPE $2', ['foo%', '\\']);

my $select-simple13 = $abstract.select('my_table', <bar baz>, { :foo(\(:false)) });
is-deeply $select-simple13, result('SELECT bar, baz FROM my_table WHERE NOT foo', []);

my $select-simple14 = $abstract.select('my_table', <bar baz>, { :foo(\(:null)) });
is-deeply $select-simple14, result('SELECT bar, baz FROM my_table WHERE foo IS NULL', []);

my $select-simple15 = $abstract.select('my_table', <bar baz>, { :foo(\(:op('in', 1, 2, 3))) });
is-deeply $select-simple15, result('SELECT bar, baz FROM my_table WHERE foo IN ($1, $2, $3)', [1, 2, 3]);


my $select-type1 = $abstract.select('my_table', <bar baz>, { :foo });
is-deeply $select-type1, result('SELECT bar, baz FROM my_table WHERE foo = $1', [ True ]);

my $now = DateTime.now;
my $select-type2 = $abstract.select('my_table', <bar baz>, { :foo('<' => $now) });
is-deeply $select-type2, result('SELECT bar, baz FROM my_table WHERE foo < $1', [ $now ]);

my $select-type3 = $abstract.select('my_table', <bar baz>, { :foo(1..42) });
is-deeply $select-type3, result('SELECT bar, baz FROM my_table WHERE foo BETWEEN $1 AND $2', [ 1, 42 ]);


my $select-rename1 = $abstract.select('my_table', (:bar<baz>), { :foo('<' => 3) });
is-deeply $select-rename1, result('SELECT baz AS bar FROM my_table WHERE foo < $1', [3]);

my $select-rename2 = $abstract.select((:my_table<quz>), <bar baz>, { :foo(Any) });
is-deeply $select-rename2, result('SELECT bar, baz FROM quz AS my_table WHERE foo IS NULL', []);


my $select-subselect1 = $abstract.select({ :query{ :from<other_table>, :where{ :bar('>' => 3) } }, :alias<new_name> }, <bar baz>, { :foo('<' => 4) });
is-deeply $select-subselect1, result('SELECT bar, baz FROM (SELECT * FROM other_table WHERE bar > $1) AS new_name WHERE foo < $2', [3, 4]);

my $select-subselect2 = $abstract.select('new.name' => { :from<other_table>, :where{ :bar('>' => 3) } }, <bar baz>, { :foo('<' => 4) });
is-deeply $select-subselect2, result('SELECT bar, baz FROM (SELECT * FROM other_table WHERE bar > $1) AS new.name WHERE foo < $2', [3, 4]);


my $select-named1 = $abstract.select('my_table', <bar baz>, { :1foo }, :order-by<bar baz>);
is-deeply $select-named1, result('SELECT bar, baz FROM my_table WHERE foo = $1 ORDER BY bar, baz', [1]);

my $select-named2 = $abstract.select('my_table', <bar baz>, { :1foo }, :5limit);
is-deeply $select-named2, result('SELECT bar, baz FROM my_table WHERE foo = $1 LIMIT $2', [1, 5]);

my $select-named3 = $abstract.select('my_table', <bar baz>, { :1foo }, :5limit, :10offset);
is-deeply $select-named3, result('SELECT bar, baz FROM my_table WHERE foo = $1 LIMIT $2 OFFSET $3', [1, 5, 10]);

my $select-named4 = $abstract.select('my_table', <bar baz>, { :1foo }, :distinct);
is-deeply $select-named4, result('SELECT DISTINCT bar, baz FROM my_table WHERE foo = $1', [1]);

my $select-named5 = $abstract.select('my_table', <bar baz>, { :1foo }, :distinct<bar>);
is-deeply $select-named5, result('SELECT DISTINCT ON (bar) bar, baz FROM my_table WHERE foo = $1', [1]);

my $select-named6 = $abstract.select('my_table', <bar baz>, { :1foo }, :order-by(:bar<desc>));
is-deeply $select-named6, result('SELECT bar, baz FROM my_table WHERE foo = $1 ORDER BY bar DESC', [1]);

my $select-named7 = $abstract.select('my_table', <bar baz>, { :1foo }, :order-by({:column<bar>, :order<desc>, :nulls<last>}));
is-deeply $select-named7, result('SELECT bar, baz FROM my_table WHERE foo = $1 ORDER BY bar DESC NULLS LAST', [1]);

my $select-window0 = $abstract.select('my_table', { :function<rank> });
is-deeply $select-window0, result('SELECT rank() FROM my_table', []);

my $select-window1 = $abstract.select('my_table', ( bar => { :function<rank>, :over{} }));
is-deeply $select-window1, result('SELECT rank() OVER () AS bar FROM my_table', []);

my $select-window2 = $abstract.select('my_table', ( bar => { :function<rank>, :over{ :partition-by<baz quz> } }));
is-deeply $select-window2, result('SELECT rank() OVER (PARTITION BY baz, quz) AS bar FROM my_table', []);

my $select-window3 = $abstract.select('my_table', ( bar => { :function<rank>, :over{ :order-by<quz> } }));
is-deeply $select-window3, result('SELECT rank() OVER (ORDER BY quz) AS bar FROM my_table', []);

my $select-window4 = $abstract.select('my_table', ( bar => { :function<rank>, :over{ :partition-by<baz quz>, :order-by<quz> } }));
is-deeply $select-window4, result('SELECT rank() OVER (PARTITION BY baz, quz ORDER BY quz) AS bar FROM my_table', []);

my $select-window5 = $abstract.select('my_table', ( bar => { :function<count>, :arguments<foo>, :filter{ :a('>'=> 3) }, :quantifier<distinct>, :order-by<bar>, :over{} }));
is-deeply $select-window5, result('SELECT count(DISTINCT foo ORDER BY bar) FILTER (WHERE a > $1) OVER () AS bar FROM my_table', [3]);

my $select-window6 = $abstract.select('my_table', ( bar => { :function<rank>, :over{ :partition-by<baz quz>, :order-by<quz>, :from<preceding> } }));
is-deeply $select-window6, result('SELECT rank() OVER (PARTITION BY baz, quz ORDER BY quz) AS bar FROM my_table', []);

my $select-window7 = $abstract.select('my_table', ( bar => { :function<rank>, :over{ :partition-by<baz quz>, :order-by<quz>, :from(:1preceding), :to(:2following), :exclude<group>, :mode<range> } }));
is-deeply $select-window7, result('SELECT rank() OVER (PARTITION BY baz, quz ORDER BY quz RANGE BETWEEN $1 PRECEDING AND $2 FOLLOWING EXCLUDE GROUP) AS bar FROM my_table', [1, 2]);

my $select-window8 = $abstract.select('my_table', ( bar => { :function<rank>, :over{ :existing<w>} }), :windows(:w{:partition-by<baz quz>}));
is-deeply $select-window8, result('SELECT rank() OVER w AS bar FROM my_table WINDOW w AS (PARTITION BY baz, quz)', []);


my $select-funcsource1 = $abstract.select({ :function{ :function<some_function> }, :alias<ranking>, :columns<bar baz> }, *);
is-deeply $select-funcsource1, result('SELECT * FROM (some_function()) AS ranking (bar, baz)', []);


my $case = \(:case(\(:ident<bar>), ('no' => 42), 13));
my $select-case1 = $abstract.select('my_table', $case, { :1foo });
is-deeply $select-case1, result('SELECT CASE bar WHEN $1 THEN $2 ELSE $3 END FROM my_table WHERE foo = $4', ['no', 42, 13, 1]);

my $select-case2 = $abstract.select('my_table', *, { :foo(\(:op('case', ('no' => 42), 13))) });
is-deeply $select-case2, result('SELECT * FROM my_table WHERE CASE foo WHEN $1 THEN $2 ELSE $3 END', ['no', 42, 13]);


my $select-junction1 = $abstract.select('my_table', <bar baz>, { :foo(1|2|4) });
is-deeply $select-junction1, result('SELECT bar, baz FROM my_table WHERE foo IN ($1, $2, $3)', [1, 2, 4]);

my $select-junction2 = $abstract.select('my_table', <bar baz>, { :foo(none(1, 2, 4)) });
is-deeply $select-junction2, result('SELECT bar, baz FROM my_table WHERE foo NOT IN ($1, $2, $3)', [1, 2, 4]);

my $select-junction3 = $abstract.select('my_table', <bar baz>, { :foo(any('=' => 1, '=' => 2, '=' => 4)) });
is-deeply $select-junction3, result('SELECT bar, baz FROM my_table WHERE foo IN ($1, $2, $3)', [1, 2, 4]);

my $select-junction4 = $abstract.select('my_table', <bar baz>, { :foo(none('=' => 1, '=' => 2, '=' => 4)) });
is-deeply $select-junction4, result('SELECT bar, baz FROM my_table WHERE foo NOT IN ($1, $2, $3)', [1, 2, 4]);

my $select-junction5 = $abstract.select('my_table', <bar baz>, { :foo(any('=' => 1, '>' => 4)) });
is-deeply $select-junction5, result('SELECT bar, baz FROM my_table WHERE foo = $1 OR foo > $2', [1, 4]);

my $select-junction6 = $abstract.select('my_table', <bar baz>, { :foo(none('<' => 1, '>' => 2)) });
is-deeply $select-junction6, result('SELECT bar, baz FROM my_table WHERE NOT (foo < $1 OR foo > $2)', [1, 2]);

my $select-junction7 = $abstract.select('my_table', <bar baz>, { :foo(none(1..42, Empty)) });
is-deeply $select-junction7, result('SELECT bar, baz FROM my_table WHERE foo NOT BETWEEN $1 AND $2', [1, 42]);

my $select-junction8 = $abstract.select('my_table', <bar baz>, { :foo(1^2^4) });
is-deeply $select-junction8, result('SELECT bar, baz FROM my_table WHERE CAST(foo = $1 AS INTEGER) + CAST(foo = $2 AS INTEGER) + CAST(foo = $3 AS INTEGER) = 1', [1, 2, 4]);


my $select-groupby1 = $abstract.select('my_table', <bar baz>, { :1foo }, :group-by<bar>);
is-deeply $select-groupby1, result('SELECT bar, baz FROM my_table WHERE foo = $1 GROUP BY bar', [1]);

my $select-groupby2 = $abstract.select('my_table', <bar baz>, { :1foo }, :group-by<bar>, :having({:baz('>' => 15)}));
is-deeply $select-groupby2, result('SELECT bar, baz FROM my_table WHERE foo = $1 GROUP BY bar HAVING baz > $2', [1, 15]);

my $select-groupby3 = $abstract.select('my_table', <bar baz>, { :1foo }, :group-by(:CUBE<bar>));
is-deeply $select-groupby3, result('SELECT bar, baz FROM my_table WHERE foo = $1 GROUP BY CUBE(bar)', [1]);

my $select-groupby4 = $abstract.select('my_table', <bar baz>, { :1foo }, :group-by('GROUPING SETS' => (:ROLLUP<bar>)));
is-deeply $select-groupby4, result('SELECT bar, baz FROM my_table WHERE foo = $1 GROUP BY GROUPING SETS(ROLLUP(bar))', [1]);


my $select-locking1 = $abstract.select('my_table', <bar baz>, { :1foo }, :locking<update>);
is-deeply $select-locking1, result('SELECT bar, baz FROM my_table WHERE foo = $1 FOR UPDATE', [1]);

my $select-locking2 = $abstract.select('my_table', <bar baz>, { :1foo }, :locking(:update<foo>));
is-deeply $select-locking2, result('SELECT bar, baz FROM my_table WHERE foo = $1 FOR UPDATE OF foo', [1]);


my $e = \(:op['<', 1, \(:and[1, 2])]);
my $select-precedence1 = $abstract.select('my_table', <bar baz>, $e);
is-deeply $select-precedence1, result('SELECT bar, baz FROM my_table WHERE $1 < ($2 AND $3)', [1, 1, 2]);


my $select-cte1 = $abstract.select('my_table', <bar baz>, { :1foo }, :common[ my_table => 'other_table' ]);
is-deeply $select-cte1, result('WITH my_table AS (SELECT * FROM other_table) SELECT bar, baz FROM my_table WHERE foo = $1', [1]);

my $select-cte2 = $abstract.select('my_table', <bar baz>, { :1foo }, :common[ my_table => { :from<other_table> } ]);
is-deeply $select-cte2, result('WITH my_table AS (SELECT * FROM other_table) SELECT bar, baz FROM my_table WHERE foo = $1', [1]);

my $select-cte3 = $abstract.select('my_table', <bar baz>, { :1foo }, :common{ :alias<my_table>, :table<other_table>, :materialized });
is-deeply $select-cte3, result('WITH my_table AS MATERIALIZED (SELECT * FROM other_table) SELECT bar, baz FROM my_table WHERE foo = $1', [1]);


my $select-union1 = $abstract.select('my_table', <bar baz>, { :1foo }, :compound(union => { :from<other_table> }), :order-by<bar>);
is-deeply $select-union1, result('SELECT bar, baz FROM my_table WHERE foo = $1 UNION SELECT * FROM other_table ORDER BY bar', [1]);

my $select-union2 = $abstract.select('my_table', <bar baz>, { :1foo }, :compound(union-all => { :from<other_table> }), :order-by<bar>);
is-deeply $select-union2, result('SELECT bar, baz FROM my_table WHERE foo = $1 UNION ALL SELECT * FROM other_table ORDER BY bar', [1]);


my $select-join1 = $abstract.select({ :left<my_table>, :right(:quz<other_table>), :using<qaz>}, <bar baz>, { :1foo });
is-deeply $select-join1, result('SELECT bar, baz FROM my_table INNER JOIN other_table AS quz USING (qaz) WHERE foo = $1', [1]);

my $select-join2 = $abstract.select({ :left<my_table>, :right<other_table>, :on{ 'my_table.baz' => 'other_table.baz_id'} }, <bar baz>, { :1foo});
is-deeply $select-join2, result('SELECT bar, baz FROM my_table INNER JOIN other_table ON my_table.baz = other_table.baz_id WHERE foo = $1', [1]);

my $select-join3 = $abstract.select({ :left<my_table>, :right<other_table>, :natural, :type<left> }, <bar baz>, { :1foo});
is-deeply $select-join3, result('SELECT bar, baz FROM my_table NATURAL LEFT JOIN other_table WHERE foo = $1', [1]);

my $select-join4 = $abstract.select({ :left<my_table>, :right<other_table>, :cross }, <bar baz>, { :1foo});
is-deeply $select-join4, result('SELECT bar, baz FROM my_table CROSS JOIN other_table WHERE foo = $1', [1]);


my $select-sub1-any = \(:op['in', \(:ident<foo>), \(:select{ :from<other_table> }) ]);
my $select-sub1 = $abstract.select('my_table', <bar baz>, $select-sub1-any);
is-deeply $select-sub1, result('SELECT bar, baz FROM my_table WHERE foo IN (SELECT * FROM other_table)', []);

my $select-sub2-any = \(:exists{ :from<other_table> });
my $select-sub2 = $abstract.select('my_table', <bar baz>, $select-sub2-any);
is-deeply $select-sub2, result('SELECT bar, baz FROM my_table WHERE EXISTS (SELECT * FROM other_table)', []);



my $insert1 = $abstract.insert('my_table', { bar => 1, baz => 43 });
is-deeply $insert1, result('INSERT INTO my_table (bar, baz) VALUES ($1, $2)', [1, 43]);

my $insert2 = $abstract.insert('my_table', <bar baz>, [ [ 1, 42 ], ]);
is-deeply $insert2, result('INSERT INTO my_table (bar, baz) VALUES ($1, $2)', [1, 42]);

my $insert3 = $abstract.insert('my_table', <bar baz>, [ [ 1, 42 ], [2, 43] ]);
is-deeply $insert3, result('INSERT INTO my_table (bar, baz) VALUES ($1, $2), ($3, $4)', [1, 42, 2, 43]);

my $insert4 = $abstract.insert('my_table', { bar => 1, baz => 42 }, :returning<bar baz>);
is-deeply $insert4, result('INSERT INTO my_table (bar, baz) VALUES ($1, $2) RETURNING bar, baz', [1, 42]);

my $insert5 = $abstract.insert('my_table', { bar => 1, baz => 42 }, :conflicts(<bar> => { bar => 2 }));
is-deeply $insert5, result('INSERT INTO my_table (bar, baz) VALUES ($1, $2) ON CONFLICT (bar) DO UPDATE SET bar = $3', [1, 42, 2]);

my $insert6 = $abstract.insert('my_table', { bar => 1, baz => 42 }, :conflicts<nothing>);
is-deeply $insert6, result('INSERT INTO my_table (bar, baz) VALUES ($1, $2) ON CONFLICT DO NOTHING', [1, 42]);

my $insert7 =  $abstract.insert('my_table', <bar baz>, { :from<other_table>, :columns<bar baz>, :where{ :foo('>' => 3) } });
is-deeply $insert7, result('INSERT INTO my_table (bar, baz) SELECT bar, baz FROM other_table WHERE foo > $1', [3]);

my $insert8 = $abstract.insert((:alias<my_table>), { bar => 1, baz => 43 });
is-deeply $insert8, result('INSERT INTO my_table AS alias (bar, baz) VALUES ($1, $2)', [1, 43]);

my $insert9 = $abstract.insert('my_table', default);
is-deeply $insert9, result('INSERT INTO my_table DEFAULT VALUES', []);

my $insert10 = $abstract.insert('my_table', { bar => 1, baz => 43 }, :overriding<system>);
is-deeply $insert10, result('INSERT INTO my_table (bar, baz) OVERRIDING SYSTEM VALUE VALUES ($1, $2)', [1, 43]);

my $insert11 = $abstract.insert('my_table', List, [ [ 1, 42 ], ]);
is-deeply $insert11, result('INSERT INTO my_table VALUES ($1, $2)', [1, 42]);

my $insert12 = $abstract.insert('my_table', { bar => \(:default), baz => 43 });
is-deeply $insert12, result('INSERT INTO my_table (bar, baz) VALUES (DEFAULT, $1)', [43]);


my $upsert1 = $abstract.upsert('my_table', { bar => 1, baz => 43 }, ['bar']);
is-deeply $upsert1, result('INSERT INTO my_table (bar, baz) VALUES ($1, $2) ON CONFLICT (bar) DO UPDATE SET baz = $3', [1, 43, 43]);

my $upsert2 = $abstract.upsert('my_table', { bar => 1, baz => 42 }, ['bar'], :returning<bar baz>);
is-deeply $upsert2, result('INSERT INTO my_table (bar, baz) VALUES ($1, $2) ON CONFLICT (bar) DO UPDATE SET baz = $3 RETURNING bar, baz', [1, 42, 42]);

my $upsert3 = $abstract.upsert('my_table', { bar => 1 }, ['bar']);
is-deeply $upsert3, result('INSERT INTO my_table (bar) VALUES ($1) ON CONFLICT (bar) DO NOTHING', [1]);


my $update1 = $abstract.update('my_table', { bar => 1, baz => 42 });
is-deeply $update1, result('UPDATE my_table SET bar = $1, baz = $2', [1, 42]);

my $update2 = $abstract.update('my_table', { bar => 1, baz => 42 }, { quz => 13 });
is-deeply $update2, result('UPDATE my_table SET bar = $1, baz = $2 WHERE quz = $3', [1, 42, 13]);

my $update3 = $abstract.update('my_table', { bar => 1, baz => 42 }, { quz => 13 }, :returning<bar baz>);
is-deeply $update3, result('UPDATE my_table SET bar = $1, baz = $2 WHERE quz = $3 RETURNING bar, baz', [1, 42, 13]);

my $update4 = $abstract.update('my_table', { bar => \('bar + 1', :precedence(SQL::Abstract::Precedence::Additive)), baz => 42 }, { quz => 13 });
is-deeply $update4, result('UPDATE my_table SET bar = bar + 1, baz = $1 WHERE quz = $2', [42, 13]);

my $update5 = $abstract.update('my_table', { bar => \('bar + ?', 1), baz => 42 }, { quz => 13 });
is-deeply $update5, result('UPDATE my_table SET bar = bar + $1, baz = $2 WHERE quz = $3', [1, 42, 13]);

my $update6 = $abstract.update('my_table', { bar => 1, baz => 42 }, { quz => 13 }, :from(other => :a<b>));
is-deeply $update6, result('UPDATE my_table SET bar = $1, baz = $2 FROM other WHERE quz = $3 AND a = b', [1, 42, 13]);

my $update7-columns = \(:row(\(:ident<bar>), \(:ident<baz>)));
my $update7-select = \(:select{ :from<other_table>, :where{ :bar('>' => 3) } });
my $update7 = $abstract.update('my_table', :{ $update7-columns => $update7-select }, { quz => 13 });
is-deeply $update7, result('UPDATE my_table SET (bar, baz) = (SELECT * FROM other_table WHERE bar > $1) WHERE quz = $2', [3, 13]);

my $update8 = $abstract.update('my_table', :{ $update7-columns => \(:row(\(:1bind), \(:2bind))) }, { quz => 13 });
is-deeply $update8, result('UPDATE my_table SET (bar, baz) = ($1, $2) WHERE quz = $3', [1, 2, 13]);

my $update9 = $abstract.update('my_table', { bar => 1, baz => 42 }, :from<quz>);
is-deeply $update9, result('UPDATE my_table SET bar = $1, baz = $2 FROM quz', [1, 42]);



my $delete1 = $abstract.delete('my_table', { :foo('<' => 3) });
is-deeply $delete1, result('DELETE FROM my_table WHERE foo < $1', [3]);

my $delete2 = $abstract.delete('my_table', { :foo('<' => 3) }, :returning<baz>);
is-deeply $delete2, result('DELETE FROM my_table WHERE foo < $1 RETURNING baz', [3]);

my $delete3 = $abstract.delete('my_table', { :foo('<' => 3) }, :using<baz>);
is-deeply $delete3, result('DELETE FROM my_table USING baz WHERE foo < $1', [3]);

my $delete4 = $abstract.delete('my_table', \(:current<cursor_name>), :using<baz>);
is-deeply $delete4, result('DELETE FROM my_table USING baz WHERE CURRENT OF cursor_name', []);

my $delete5 = $abstract.delete('my_table', \'CURRENT OF cursor_name', :using<baz>);
is-deeply $delete5, result('DELETE FROM my_table USING baz WHERE CURRENT OF cursor_name', []);


my $values1 = $abstract.values([[1, 2],], :1limit);
is-deeply $values1, result('VALUES ($1, $2) LIMIT $3', [1, 2, 1]);


my $builder = $renderer.builder;
my $on = $builder.on('my_table');

my $build1 = $on.select(*).build;
is-deeply $build1, result('SELECT * FROM my_table', []);

my $build2 = $on.select(<bar baz>).build;
is-deeply $build2 , result('SELECT bar, baz FROM my_table', []);

my $build3 = $on.where({ :foo(\(:null)) }).select(<bar baz>).build;
is-deeply $build3, result('SELECT bar, baz FROM my_table WHERE foo IS NULL', []);

my $build4 = $on.where({ :foo(\(:null)) }).select(<bar baz>).where({ :1a }).build;
is-deeply $build4, result('SELECT bar, baz FROM my_table WHERE foo IS NULL AND a = $1', [1]);

my $build5 = $on.update({ :1a, :b<foo> }).build;
is-deeply $build5, result('UPDATE my_table SET a = $1, b = $2', [1, 'foo']);

my $build6 = $on.where({ :42c }).update({ :1a, :b<foo> }).build;
is-deeply $build6, result('UPDATE my_table SET a = $1, b = $2 WHERE c = $3', [1, 'foo', 42]);

my $build7 = $on.join('other_table', :using<id>).update({ :1a, :b<foo> }).build;
is-deeply $build7, result('UPDATE my_table SET a = $1, b = $2 FROM other_table WHERE my_table.id = other_table.id', [1, 'foo']);

my $build8 = $on.insert({ :1a, :b<foo> }).build;
is-deeply $build8, result('INSERT INTO my_table (a, b) VALUES ($1, $2)', [1, 'foo']);

my $build9 = $on.where({ :3c }).insert({ :1a, :b<foo> }).build;
is-deeply $build9, result('INSERT INTO my_table (a, b, c) VALUES ($1, $2, $3)', [1, 'foo', 3]);

my $build10 = $on.where({ :1a }).delete.build;
is-deeply $build10, result('DELETE FROM my_table WHERE a = $1', [1]);

my $build11 = $on.join('other_table', :using<id>).delete.build;
is-deeply $build11, result('DELETE FROM my_table USING other_table WHERE my_table.id = other_table.id');

my $build12a = $on.select(*).query;
my $build12 = $builder.with([ a => $build12a ]).on('other_table').join('a', :using<id>).select('foo').build;
is-deeply $build12, result('WITH a AS (SELECT * FROM my_table) SELECT foo FROM other_table INNER JOIN a USING (id)');

my $build13 = $on.upsert({ bar => 1, baz => 43 }, ['bar']).build;
is-deeply $build13, result('INSERT INTO my_table (bar, baz) VALUES ($1, $2) ON CONFLICT (bar) DO UPDATE SET baz = $3', [1, 43, 43]);


my $prepared1 = result('', [1, 2, 3]);
is-deeply $prepared1.resolve, (1, 2, 3);
is-deeply $prepared1.type-hints, (Int, Int, Int);

my $prepared2 = result('', [1, 2, delegate('foo')]);
dies-ok { $prepared2.resolve };
is-deeply $prepared2.type-hints, (Int, Int, Any);
is-deeply $prepared2.resolve({ :3foo }), (1, 2, 3);
is-deeply $prepared2.identifiers, ('foo',);

my $prepared3 = result('', [1, 2, delegate('foo', :type(Int))]);
is-deeply $prepared3.type-hints, (Int, Int, Int);

my $prepared4 = result('', [1, 2, delegate('foo', 3)]);
is-deeply $prepared4.type-hints, (Int, Int, Int);
is-deeply $prepared4.resolve, (1, 2, 3);
is-deeply $prepared4.resolve({ :4foo }), (1, 2, 4);



my SQL::Abstract::Conditions() $conditional1 = :1id;
isa-ok $conditional1.expression, SQL::Abstract::Op::Equals;
is $conditional1.expression.left, identifier('id');
cmp-ok $conditional1.expression.left, '===', identifier('id');
is-deeply $conditional1.expression.right, value(1);

my SQL::Abstract::Conditions() $conditional2 = :other(1|2);
isa-ok $conditional2.expression, SQL::Abstract::Op::In;
is-deeply $conditional2.expression.left, identifier('other');
is-deeply $conditional2.expression.elements[0], value(1);
is-deeply $conditional2.expression.elements[1], value(2);

my $conditional3 = $conditional1.merge($conditional2);
isa-ok $conditional3.expression, SQL::Abstract::Op::And;
isa-ok $conditional3.expression.elements[0], SQL::Abstract::Op::Equals;
isa-ok $conditional3.expression.elements[1], SQL::Abstract::Op::In;

my $conditional4 = SQL::Abstract::Join::Conditions.from-using(table('left'), table('right'), identifiers(<shared shared2>));
isa-ok $conditional4.expression, SQL::Abstract::Op::And;
isa-ok $conditional4.expression.elements[0], SQL::Abstract::Op::Equals;
is-deeply $conditional4.expression.elements[0].left, identifier('left.shared');
is-deeply $conditional4.expression.elements[0].right, identifier('right.shared');
isa-ok $conditional4.expression.elements[1], SQL::Abstract::Op::Equals;
is-deeply $conditional4.expression.elements[1