Materialize/test/sqllogictest/cockroach/prepare.slt

# Copyright 2015 - 2019 The Cockroach Authors. All rights reserved.
# 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.
#
# This file is derived from the logic test suite in CockroachDB. The
# original file was retrieved on June 10, 2019 from:
#
#     https://github.com/cockroachdb/cockroach/blob/d2f7fbf5dd1fc1a099bbad790a2e1f7c60a66cc3/pkg/sql/logictest/testdata/logic_test/prepare
#
# The original source code is subject to the terms of the Apache
# 2.0 license, a copy of which can be found in the LICENSE file at the
# root of this repository.

# not supported yet
halt

mode cockroach

## Tests for ensuring that prepared statements can't get overwritten and for
## deallocate and deallocate all.
statement error prepared statement \"a\" does not exist
DEALLOCATE a

statement
PREPARE a AS SELECT 1

query I
EXECUTE a
----
1

query I
EXECUTE a
----
1

statement error prepared statement \"a\" already exists
PREPARE a AS SELECT 1

statement
DEALLOCATE a

statement error prepared statement \"a\" does not exist
DEALLOCATE a

statement error prepared statement \"a\" does not exist
EXECUTE a

statement
PREPARE a AS SELECT 1

statement
PREPARE b AS SELECT 1

query I
EXECUTE a
----
1

query I
EXECUTE b
----
1

statement ok
DEALLOCATE ALL

statement error prepared statement \"a\" does not exist
DEALLOCATE a

statement error prepared statement \"a\" does not exist
EXECUTE a

statement error prepared statement \"b\" does not exist
DEALLOCATE b

statement error prepared statement \"b\" does not exist
EXECUTE b

## Typing tests - no type hints
#
query error syntax error at or near \"\)\"
PREPARE a as ()

statement error could not determine data type of placeholder \$1
PREPARE a AS SELECT $1

statement error could not determine data type of placeholder \$1
PREPARE a AS SELECT $2:::int

statement error could not determine data type of placeholder \$2
PREPARE a AS SELECT $1:::int, $3:::int

statement ok
PREPARE a AS SELECT $1:::int + $2

query I
EXECUTE a(3, 1)
----
4

query error could not parse "foo" as type int
EXECUTE a('foo', 1)

query error expected EXECUTE parameter expression to have type int, but '3.5' has type decimal
EXECUTE a(3.5, 1)

query error aggregate functions are not allowed in EXECUTE parameter
EXECUTE a(max(3), 1)

query error window functions are not allowed in EXECUTE parameter
EXECUTE a(rank() over (partition by 3), 1)

query error variable sub-expressions are not allowed in EXECUTE parameter
EXECUTE a((SELECT 3), 1)

query error wrong number of parameters for prepared statement \"a\": expected 2, got 3
EXECUTE a(1, 1, 1)

query error wrong number of parameters for prepared statement \"a\": expected 2, got 0
EXECUTE a

# Regression test for cockroach#36153.
statement error unknown signature: array_length\(int, int\)
PREPARE fail AS SELECT array_length($1, 1)

## Type hints

statement
PREPARE b (int) AS SELECT $1

query I
EXECUTE b(3)
----
3

query error could not parse "foo" as type int
EXECUTE b('foo')

statement
PREPARE allTypes(int, float, string, bytea, date, timestamp, timestamptz, bool, decimal) AS
SELECT $1, $2, $3, $4, $5, $6, $7, $8, $9

query IRTTTTTBR
EXECUTE allTypes(0, 0.0, 'foo', 'bar', '2017-08-08', '2015-08-30 03:34:45.34567', '2015-08-30 03:34:45.34567', true, 3.4)
----
0  0  foo  bar  2017-08-08 00:00:00 +0000 +0000  2015-08-30 03:34:45.34567 +0000 +0000  2015-08-30 03:34:45.34567 +0000 UTC  true  3.4

## Other

statement
PREPARE c AS SELECT count(*)

query I
EXECUTE c
----
1

statement
CREATE TABLE t (a int)

statement
PREPARE i AS INSERT INTO t(a) VALUES($1) RETURNING $1 + 1

statement
PREPARE s AS SELECT * FROM t

query I
EXECUTE i(1)
----
2

query I
EXECUTE i(2)
----
3

query error could not parse "foo" as type int
EXECUTE i('foo')

query error expected EXECUTE parameter expression to have type int, but '2.3' has type decimal
EXECUTE i(2.3)

query I
EXECUTE i(3.3::int)
----
4

query I colnames
EXECUTE s
----
a
1
2
3

# DISCARD ROWS drops the results, but does not affect the schema or the
# internal plan.
query I colnames
EXECUTE s DISCARD ROWS
----
a

statement
DEALLOCATE ALL

# Regression test for materialize#15970

statement
PREPARE x AS SELECT avg(column1) OVER (PARTITION BY column2) FROM (VALUES (1, 2), (3, 4))

query R rowsort
EXECUTE x
----
1
3

statement
PREPARE y AS SELECT avg(a.column1) OVER (PARTITION BY a.column2) FROM (VALUES (1, 2), (3, 4)) a

query R rowsort
EXECUTE y
----
1
3

statement
DEALLOCATE ALL

# Regression test for database-issues#4634

statement
CREATE TABLE IF NOT EXISTS f (v INT)

statement
PREPARE x AS SELECT * FROM f

statement
ALTER TABLE f ADD COLUMN u int

statement
INSERT INTO f VALUES (1, 2)

statement error cached plan must not change result type
EXECUTE x

# Ensure that plan changes prevent INSERTs from succeeding.

statement
PREPARE y AS INSERT INTO f VALUES ($1, $2) RETURNING *

statement
EXECUTE y (2, 3)

statement
ALTER TABLE f ADD COLUMN t int

statement error cached plan must not change result type
EXECUTE y (3, 4)

query III
SELECT * FROM f
----
1 2 NULL
2 3 NULL

# Ensure that we have a memory monitor for preparing statements

statement
PREPARE z AS SELECT upper('a')

# Ensure that GROUP BY HAVING doesn't mutate the parsed AST (materialize#16388)
statement
CREATE TABLE foo (a int)

statement
PREPARE groupbyhaving AS SELECT min(1) FROM foo WHERE a = $1 GROUP BY a HAVING count(a) = 0

query I
EXECUTE groupbyhaving(1)
----

# Mismatch between expected and hinted types should prepare, but potentially
# fail to execute if the cast is not possible.
statement
PREPARE wrongTypePossibleCast(float) AS INSERT INTO foo VALUES ($1)

statement
EXECUTE wrongTypePossibleCast(2.3)

statement
PREPARE wrongTypeImpossibleCast(string) AS INSERT INTO foo VALUES ($1)

statement
EXECUTE wrongTypeImpossibleCast('3')

statement error could not parse "crabgas" as type int
EXECUTE wrongTypeImpossibleCast('crabgas')

# Check statement compatibility

statement ok
PREPARE s AS SELECT a FROM t; PREPARE p1 AS UPSERT INTO t(a) VALUES($1) RETURNING a

query I
EXECUTE s
----
1
2
3

query I
EXECUTE p1(123)
----
123

statement ok
PREPARE p2 AS UPDATE t SET a = a + $1 RETURNING a

query I
EXECUTE s
----
1
2
3
123

query I
EXECUTE p2(123)
----
124
125
126
246

statement ok
PREPARE p3 AS DELETE FROM t WHERE a = $1 RETURNING a

query I
EXECUTE s
----
124
125
126
246

query I
EXECUTE p3(124)
----
124

statement ok
PREPARE p4 AS CANCEL JOB $1

query error pq: job with ID 123 does not exist
EXECUTE p4(123)

statement ok
PREPARE p5 AS PAUSE JOB $1

query error pq: job with ID 123 does not exist
EXECUTE p5(123)

statement ok
PREPARE p6 AS RESUME JOB $1

query error pq: job with ID 123 does not exist
EXECUTE p6(123)

# Ensure that SET / SET CLUSTER SETTING know about placeholders
statement ok
PREPARE setp(string) AS SET application_name = $1

query T
SET application_name = 'foo'; SHOW application_name
----
foo

query T
EXECUTE setp('hello'); SHOW application_name
----
hello

# Note: we can't check the result of SET CLUSTER SETTING synchronously
# because it doesn't propagate immediately.

statement ok
PREPARE sets(string) AS SET CLUSTER SETTING cluster.organization = $1

statement ok
EXECUTE sets('hello')

# materialize#19597

statement error could not determine data type of placeholder
PREPARE x19597 AS SELECT $1 IN ($2, null);

statement error multiple conflicting type annotations around \$1
PREPARE invalid AS SELECT $1:::int + $1:::float

statement error type annotation around \$1 conflicts with specified type int
PREPARE invalid (int) AS SELECT $1:::float

statement ok
PREPARE innerStmt AS SELECT $1:::int i, 'foo' t

statement error syntax error at or near "execute"
PREPARE outerStmt AS SELECT * FROM [EXECUTE innerStmt(3)] WHERE t = $1

query error syntax error at or near "execute"
SELECT * FROM [EXECUTE innerStmt(1)] CROSS JOIN [EXECUTE x]

statement ok
PREPARE selectin AS SELECT 1 in ($1, $2)

statement ok
PREPARE selectin2 AS SELECT $1::int in ($2, $3)

query B
EXECUTE selectin(5, 1)
----
true

query B
EXECUTE selectin2(1, 5, 1)
----
true

# Regression tests for materialize#21701.
statement ok
CREATE TABLE kv (k INT PRIMARY KEY, v INT)

statement ok
INSERT INTO kv VALUES (1, 1), (2, 2), (3, 3)

statement ok
PREPARE x21701a AS SELECT * FROM kv WHERE k = $1

query II
EXECUTE x21701a(NULL)
----

statement ok
PREPARE x21701b AS SELECT * FROM kv WHERE k IS DISTINCT FROM $1

query II
EXECUTE x21701b(NULL)
----
1  1
2  2
3  3

statement ok
PREPARE x21701c AS SELECT * FROM kv WHERE k IS NOT DISTINCT FROM $1

query II
EXECUTE x21701c(NULL)
----

statement ok
DROP TABLE kv

# Test that a PREPARE statement after a CREATE TABLE in the same TRANSACTION
# doesn't hang.
subtest 24578

statement ok
BEGIN TRANSACTION

statement ok
create table bar (id integer)

statement ok
PREPARE forbar AS insert into bar (id) VALUES (1)

statement ok
COMMIT TRANSACTION

# Test placeholder in aggregate.
statement ok
CREATE TABLE aggtab (a INT PRIMARY KEY);
INSERT INTO aggtab (a) VALUES (1)

statement ok
PREPARE aggprep AS SELECT max(a + $1:::int) FROM aggtab

query I
EXECUTE aggprep(10)
----
11

query I
EXECUTE aggprep(20)
----
21

# Test placeholder in subquery, where the placeholder will be constant folded
# and then used to select an index.
statement ok
CREATE TABLE abc (a INT PRIMARY KEY, b INT, c INT);
CREATE TABLE xyz (x INT PRIMARY KEY, y INT, z INT, INDEX(y));
INSERT INTO abc (a, b, c) VALUES (1, 10, 100);
INSERT INTO xyz (x, y, z) VALUES (1, 5, 50);
INSERT INTO xyz (x, y, z) VALUES (2, 6, 60);

statement ok
PREPARE subqueryprep AS SELECT * FROM abc WHERE EXISTS(SELECT * FROM xyz WHERE y IN ($1 + 1))

query III
EXECUTE subqueryprep(4)
----
1  10  100

query III
EXECUTE subqueryprep(5)
----
1  10  100

query III
EXECUTE subqueryprep(6)
----

#
# Test prepared statements that rely on context, and ensure they are invalidated
# when that context changes.
#

statement ok
CREATE DATABASE otherdb

statement ok
USE otherdb

statement ok
CREATE TABLE othertable (a INT PRIMARY KEY, b INT); INSERT INTO othertable (a, b) VALUES (1, 10)

## Current database change: Use current_database function, and ensure its return
## value changes when current database changes.
statement ok
PREPARE change_db AS SELECT current_database()

query T
EXECUTE change_db
----
otherdb

statement ok
USE test

query T
EXECUTE change_db
----
test

statement ok
USE otherdb

## Name resolution change: Query table in current database. Ensure that it is
## not visible in another database.
statement ok
PREPARE change_db_2 AS SELECT * FROM othertable

query II
EXECUTE change_db_2
----
1  10

statement ok
USE test

query error pq: relation "othertable" does not exist
EXECUTE change_db_2

statement ok
CREATE TABLE othertable (a INT PRIMARY KEY, b INT); INSERT INTO othertable (a, b) VALUES (2, 20)

query II
EXECUTE change_db_2
----
2  20

# Same test with a query which refers to the same table twice initially, but
# later the two tables are different.
statement ok
PREPARE change_db_3 AS SELECT * from othertable AS t1, test.othertable AS t2

query IIII
EXECUTE change_db_3
----
2 20 2 20

statement ok
USE otherdb

query IIII
EXECUTE change_db_3
----
1 10 2 20

statement ok
DROP TABLE test.othertable

## Search path change: Change the search path and ensure that the prepared plan
## is invalidated.
statement ok
PREPARE change_search_path AS SELECT * FROM othertable

query II
EXECUTE change_search_path
----
1  10

statement ok
SET search_path = pg_catalog

query error pq: relation "othertable" does not exist
EXECUTE change_search_path

## New table in search path: check tricky case where originally resolved table
## still exists but re-resolving with new search path yields another table.
statement ok
SET search_path=public,pg_catalog

# During prepare, pg_type resolves to pg_catalog.pg_type.
statement ok
PREPARE new_table_in_search_path AS SELECT typname FROM pg_type

statement ok
CREATE TABLE pg_type(typname STRING); INSERT INTO pg_type VALUES('test')

# Now, it should resolve to the table we just created.
query T
EXECUTE new_table_in_search_path
----
test

statement ok
DROP TABLE pg_type

## Even more tricky case: the query has two table references that resolve to
## the same table now, but later resolve to separate tables.
statement ok
PREPARE new_table_in_search_path_2 AS
  SELECT a.typname, b.typname FROM pg_type AS a, pg_catalog.pg_type AS b ORDER BY a.typname, b.typname LIMIT 1

query TT
EXECUTE new_table_in_search_path_2
----
_bit _bit

statement ok
CREATE TABLE pg_type(typname STRING); INSERT INTO pg_type VALUES('test')

query TT
EXECUTE new_table_in_search_path_2
----
test _bit

statement ok
DROP TABLE pg_type

statement ok
RESET search_path

## Functions: Use function which depends on context, and which is constant-
## folded by the heuristic planner. Ensure that it's not constant folded when
## part of prepared plan.
query B
SELECT has_column_privilege('testuser', 'othertable', 1, 'SELECT')
----
false

statement ok
GRANT ALL ON othertable TO testuser

query B
SELECT has_column_privilege('testuser', 'othertable', 1, 'SELECT')
----
true

statement ok
REVOKE ALL ON othertable FROM testuser

## Location change: Change the current location (affects timezone) and make
## sure the query is invalidated.
statement ok
PREPARE change_loc AS SELECT '2000-01-01 18:05:10.123'::timestamptz

query T
EXECUTE change_loc
----
2000-01-01 18:05:10.123 +0000 UTC

statement ok
SET TIME ZONE 'EST';

query T
EXECUTE change_loc
----
2000-01-01 18:05:10.123 -0500 -0500

statement ok
SET TIME ZONE 'UTC';

## Permissions: Grant and then revoke permission to select from a table. The
## prepared plan should be invalidated.
statement ok
GRANT ALL ON othertable TO testuser

user testuser

statement ok
USE otherdb

statement ok
PREPARE change_privileges AS SELECT * FROM othertable

query II
EXECUTE change_privileges
----
1  10

user root

statement ok
REVOKE ALL ON othertable FROM testuser

user testuser

query error pq: user testuser does not have SELECT privilege on relation othertable
EXECUTE change_privileges

user root

## Permissions: Use UPDATE statement that requires both UPDATE and SELECT
## privileges.
statement ok
GRANT ALL ON othertable TO testuser

user testuser

statement ok
USE otherdb

statement ok
PREPARE update_privileges AS UPDATE othertable SET b=$1

user root

statement ok
REVOKE UPDATE ON othertable FROM testuser

user testuser

query error pq: user testuser does not have UPDATE privilege on relation othertable
EXECUTE update_privileges(5)

user root

statement ok
GRANT UPDATE ON othertable TO testuser

statement ok
REVOKE SELECT ON othertable FROM testuser

user testuser

query error pq: user testuser does not have SELECT privilege on relation othertable
EXECUTE update_privileges(5)

user root

query II
SELECT * FROM othertable
----
1  10

user root

## Schema change (rename): Rename column in table and ensure that the prepared
## statement is updated to incorporate it.
statement ok
PREPARE change_rename AS SELECT * FROM othertable

query II colnames
EXECUTE change_rename
----
a  b
1  10

statement ok
ALTER TABLE othertable RENAME COLUMN b TO c

query II colnames
EXECUTE change_rename
----
a  c
1  10

statement ok
ALTER TABLE othertable RENAME COLUMN c TO b

query II colnames
EXECUTE change_rename
----
a  b
1  10

## Schema change (placeholders): Similar to previous case, but with placeholder
## present.
statement ok
PREPARE change_placeholders AS SELECT * FROM othertable WHERE a=$1

query II colnames
EXECUTE change_placeholders(1)
----
a  b
1  10

statement ok
ALTER TABLE othertable RENAME COLUMN b TO c

query II colnames
EXECUTE change_placeholders(1)
----
a  c
1  10

statement ok
ALTER TABLE othertable RENAME COLUMN c TO b

query II colnames
EXECUTE change_placeholders(1)
----
a  b
1  10

## Schema change (view): Change view name and ensure that prepared query is
## invalidated.
statement ok
CREATE VIEW otherview AS SELECT a, b FROM othertable

statement ok
PREPARE change_view AS SELECT * FROM otherview

query II
EXECUTE change_view
----
1  10

statement ok
ALTER VIEW otherview RENAME TO otherview2

# HP and CBO return slightly different errors, so accept both.
query error pq: relation "(otherdb.public.)?otherview" does not exist
EXECUTE change_view

statement ok
DROP VIEW otherview2

## Schema change: Drop column and ensure that correct error is reported.
statement ok
PREPARE change_drop AS SELECT * FROM othertable WHERE b=10

query II
EXECUTE change_drop
----
1  10

statement ok
ALTER TABLE othertable DROP COLUMN b

query error pq: column "b" does not exist
EXECUTE change_drop

statement ok
ALTER TABLE othertable ADD COLUMN b INT; UPDATE othertable SET b=10

query II
EXECUTE change_drop
----
1  10

## Uncommitted schema change: Rename column in table in same transaction as
## execution of prepared statement and make prepared statement incorporates it.
statement ok
PREPARE change_schema_uncommitted AS SELECT * FROM othertable

statement ok
BEGIN TRANSACTION

query II colnames
EXECUTE change_schema_uncommitted
----
a  b
1  10

statement ok
ALTER TABLE othertable RENAME COLUMN b TO c

query II colnames
EXECUTE change_schema_uncommitted
----
a  c
1  10

# Change the schema again and verify that the previously prepared plan is not
# reused. Testing this is important because the second schema change won't
# bump the table descriptor version again.
statement ok
ALTER TABLE othertable RENAME COLUMN c TO d

query II colnames
EXECUTE change_schema_uncommitted
----
a  d
1  10

statement ok
ROLLBACK TRANSACTION

# Same virtual table in different catalogs (these virtual table instances have
# the same table ID).
statement ok
CREATE SEQUENCE seq

statement ok
PREPARE pg_catalog_query AS SELECT * FROM pg_catalog.pg_sequence

query OOIIIIIB colnames
EXECUTE pg_catalog_query
----
seqrelid  seqtypid  seqstart  seqincrement  seqmax               seqmin  seqcache  seqcycle
67        20        1         1             9223372036854775807  1       1         false

statement ok
USE test

query OOIIIIIB colnames
EXECUTE pg_catalog_query
----
seqrelid    seqtypid  seqstart  seqincrement  seqmax               seqmin  seqcache  seqcycle

# Verify error when placeholders are used without prepare.
statement error no value provided for placeholder: \$1
SELECT $1:::int

# Verify sequences get re-resolved.
statement ok
CREATE SEQUENCE seq

statement ok
PREPARE seqsel AS SELECT * FROM seq

query I
SELECT nextval('seq')
----
1

query IIB
EXECUTE seqsel
----
1  0  true

statement ok
DROP SEQUENCE seq

statement ok
CREATE SEQUENCE seq

query IIB
EXECUTE seqsel
----
0  0  true

# Null placeholder values need to be assigned static types. Otherwise, we won't
# be able to disambiguate the concat function overloads.
statement ok
PREPARE foobar AS VALUES ($1:::string || $2:::string)

query T
EXECUTE foobar(NULL, NULL)
----
NULL

subtest regression_35145

# Verify db-independent query behaves properly even when db does not exist

statement ok
SET application_name = ap35145

# Prepare in custom db

statement ok
CREATE DATABASE d35145; SET database = d35145;

statement ok
PREPARE display_appname AS SELECT setting FROM pg_settings WHERE name = 'application_name'

query T
EXECUTE display_appname
----
ap35145

# Check what happens when the db where the stmt was prepared disappears "underneath".

statement ok
DROP DATABASE d35145

query error database "d35145" does not exist
EXECUTE display_appname

statement ok
CREATE DATABASE d35145

query T
EXECUTE display_appname
----
ap35145

# Check what happens when the stmt is executed over a non-existent, unrelated db.

statement ok
CREATE DATABASE d35145_2; SET database = d35145_2; DROP DATABASE d35145_2

query error database "d35145_2" does not exist
EXECUTE display_appname

# Check what happens when the stmt is executed over no db whatsoever.

statement ok
SET database = ''

query error  cannot access virtual schema in anonymous database
EXECUTE display_appname