Materialize/test/sqllogictest/prepare.slt

# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.

# For more prepared statement tests, see `order_by.slt` and `test_bind_params`.

statement ok
CREATE TABLE t (a int);

# INSERT
statement ok
PREPARE i1 AS
INSERT INTO t(a) VALUES($1);

statement ok
EXECUTE i1(5);

query I
SELECT * FROM t;
----
5

# INSERT ... RETURNING
statement ok
PREPARE i2 AS
INSERT INTO t(a) VALUES($1 - 1) RETURNING $1 + 1;

query I
EXECUTE i2(7);
----
8

query I
SELECT * FROM t;
----
5
6

query error db error: ERROR: operator is not unique: unknown \+ unknown
PREPARE i3 AS
INSERT INTO t(a) VALUES(4) RETURNING $1 + $1;

statement ok
PREPARE i3 AS
INSERT INTO t(a) VALUES(4) RETURNING $1;

query T
EXECUTE i3('x');
----
x

query I valuesort
SELECT * FROM t;
----
4
5
6

statement ok
PREPARE p1 AS
SELECT $1 + $1::bigint;

query I
EXECUTE p1(5);
----
10

statement ok
PREPARE p2 AS
SELECT $1::bigint + $1::bigint;

query I
EXECUTE p2(7);
----
14

statement ok
PREPARE p3 AS
SELECT $1 || $1;

query T
EXECUTE p3('abc');
----
abcabc

statement ok
PREPARE p4 AS
SELECT $1::text || $1::bigint::text;

query error db error: ERROR: invalid input syntax for type bigint: invalid digit found in string: "abc"
EXECUTE p4('abc');

query T
EXECUTE p4('123');
----
123123

statement ok
PREPARE p5 AS
SELECT $1, $1::bigint;

query II
EXECUTE p5(7);
----
0
7

query error db error: ERROR: operator does not exist: bigint \|\| bigint
PREPARE p6 AS
SELECT $1 + $1::bigint, $1 || $1;

query error db error: ERROR: operator does not exist: text \+ bigint
PREPARE p7 AS
SELECT $1 || $1, $1 + $1::bigint;

query error db error: ERROR: there are contradicting constraints for the type of parameter \$1: should be both text and integer
PREPARE p AS SELECT repeat($1, $1);