github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/statement_statistics

# LogicTest: local

# Check that node_statement_statistics report per application

statement ok
SET application_name = hello; SELECT 1

statement ok
SET application_name = world; SELECT 2

query B
SELECT count > 0 FROM crdb_internal.node_statement_statistics WHERE application_name IN ('hello', 'world')
----
true
true
true

# Check that node_statement_statistics report per statement

statement ok
SET application_name = hello; SELECT 1; SELECT 1,2; SELECT 1

# reset for other tests.
statement ok
SET application_name = ''

query TB
SELECT key, count >= 1 FROM crdb_internal.node_statement_statistics WHERE application_name = 'hello' AND key LIKE 'SELECT%' ORDER BY key
----
SELECT _    true
SELECT _, _ true

statement ok
CREATE TABLE test(x INT, y INT, z INT); INSERT INTO test(x, y, z) VALUES (0,0,0);

# Disable DistSQL for most statements, so that they don't get the "+" flag.
statement ok
SET distsql = off

statement ok
SET application_name = 'valuetest'

# Check that shortening goes through functions.

statement ok
SELECT sin(1.23)

# Check stats for query errors.
statement error cannot take square root
SELECT sqrt(-1.0)

# Check that shortened queries can use virtual tables.

statement ok
SELECT key FROM test.crdb_internal.node_statement_statistics

# Check that multi-value clauses are shortened.

statement ok
SELECT x FROM (VALUES (1,2,3), (4,5,6)) AS t(x)

statement ok
INSERT INTO test VALUES (1, 2, 3), (4, 5, 6)

# Check that the RHS of IN comparisons are shortened.

statement ok
SELECT x FROM test WHERE y IN (4, 5, 6, 7, 8)

statement ok
SELECT x FROM test WHERE y NOT IN (4, 5, 6, 7, 8)

# Check that a non-constant prevents shortening.

statement ok
SELECT x FROM test WHERE y IN (4, 5, 6+x, 7, 8)

# Check that tuples in other positions are not shortened.

statement ok
SELECT ROW(1,2,3,4,5) FROM test WHERE FALSE

# Make one query run in distsql mode to test the flag
# and flag combinations

statement ok
set distsql = on

statement ok
SELECT x FROM test WHERE y IN (4, 5, 6, 7, 8)

statement error division by zero
SELECT x FROM test WHERE y = 1/z

# Set a cluster setting to make it show up below. Which one is set
# does not matter.
statement ok
SET CLUSTER SETTING debug.panic_on_failed_assertions = true; RESET CLUSTER SETTING debug.panic_on_failed_assertions

statement ok
SHOW application_name

statement ok
SHOW CLUSTER SETTING debug.panic_on_failed_assertions

statement ok
SET application_name = ''; RESET distsql

query TT colnames
SELECT key,flags
  FROM test.crdb_internal.node_statement_statistics
 WHERE application_name = 'valuetest' ORDER BY key, flags
----
key                                                               flags
INSERT INTO test VALUES (_, _, __more1__), (__more1__)            ·
SELECT (_, _, __more3__) FROM test WHERE _                        ·
SELECT key FROM test.crdb_internal.node_statement_statistics      ·
SELECT sin(_)                                                     ·
SELECT sqrt(_)                                                    !
SELECT x FROM (VALUES (_, _, __more1__), (__more1__)) AS t (x)    ·
SELECT x FROM test WHERE y = (_ / z)                              !+
SELECT x FROM test WHERE y IN (_, _, _ + x, _, _)                 ·
SELECT x FROM test WHERE y IN (_, _, __more3__)                   ·
SELECT x FROM test WHERE y IN (_, _, __more3__)                   +
SELECT x FROM test WHERE y NOT IN (_, _, __more3__)               ·
SET CLUSTER SETTING "debug.panic_on_failed_assertions" = DEFAULT  ·
SET CLUSTER SETTING "debug.panic_on_failed_assertions" = _        ·
SET application_name = _                                          ·
SET distsql = "on"                                                ·
SHOW CLUSTER SETTING "debug.panic_on_failed_assertions"           ·
SHOW application_name                                             ·

# Check that names are anonymized properly:
# - virtual table names are preserved, but not the db prefix (#22700)
# - function names are preserved
query T
SELECT anonymized
  FROM test.crdb_internal.node_statement_statistics
 WHERE application_name = 'valuetest' ORDER BY key
----
INSERT INTO _ VALUES (_, _, __more1__), (__more1__)
SELECT (_, _, __more3__) FROM _ WHERE _
SELECT _ FROM _.crdb_internal.node_statement_statistics
SELECT sin(_)
SELECT sqrt(_)
SELECT _ FROM (VALUES (_, _, __more1__), (__more1__)) AS _ (_)
SELECT _ FROM _ WHERE _ = (_ / _)
SELECT _ FROM _ WHERE _ IN (_, _, _ + _, _, _)
SELECT _ FROM _ WHERE _ IN (_, _, __more3__)
SELECT _ FROM _ WHERE _ IN (_, _, __more3__)
SELECT _ FROM _ WHERE _ NOT IN (_, _, __more3__)
SET CLUSTER SETTING "debug.panic_on_failed_assertions" = DEFAULT
SET CLUSTER SETTING "debug.panic_on_failed_assertions" = _
SET application_name = _
SET distsql = _
SHOW CLUSTER SETTING "debug.panic_on_failed_assertions"
SHOW application_name

# Check that the latency measurements looks reasonable, protecting
# against failure to measure (#22877).

# We use the keys left over by the two unary selects
# performed at the start of this test above.
#
# The latency metrics are expressed in seconds. Check that some time
# was consumed, but not so much to verify that the computation has not
# incorrectly overflowed.
query TBBBBB colnames
SELECT key,
       service_lat_avg > 0 and service_lat_avg < 10 as svc_ok,
       parse_lat_avg > 0   and parse_lat_avg < 10   as parse_ok,
       plan_lat_avg > 0    and plan_lat_avg < 10    as plan_ok,
       run_lat_avg > 0     and run_lat_avg < 10     as run_ok,
                           overhead_lat_avg < 10    as ovh_ok
  FROM crdb_internal.node_statement_statistics
 WHERE key = 'SELECT _'
----
key       svc_ok  parse_ok  plan_ok  run_ok  ovh_ok
SELECT _  true    true      true     true    true
SELECT _  true    true      true     true    true

# Check that statements made in implicit transactions are separated from those
# that are not.

statement ok
SET application_name = 'implicit_txn_test'

statement ok
BEGIN; SELECT x FROM test where y=1; COMMIT;

statement ok
BEGIN;
SELECT x, z FROM test;
SELECT x FROM test where y=1;
COMMIT;

statement ok
SELECT z FROM test where y=2;

statement ok
SELECT x FROM test where y=1;

query TB colnames rowsort
SELECT key, implicit_txn
  FROM crdb_internal.node_statement_statistics
 WHERE application_name = 'implicit_txn_test' ORDER BY key, implicit_txn;
----
key                             implicit_txn
SELECT x FROM test WHERE y = _  false
SELECT x FROM test WHERE y = _  true
SELECT x, z FROM test           false
SELECT z FROM test WHERE y = _  true