statement_statistics

# Disable SQL Stats flush to prevents stats from being cleared from the
# in-memory store.
# LogicTest: local 3node-tenant

statement ok
SET CLUSTER SETTING sql.stats.flush.enabled = false;

# Check that node_statement_statistics report per application

statement ok
SET application_name = hello

statement ok
SELECT 1

statement ok
SET application_name = world

statement ok
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;

statement ok
SELECT 1

statement ok
SELECT 1,2

statement ok
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

# Check that multiple stmts in a simple query are executed as one txn.

statement ok
SET application_name = multi_stmts_test;

statement ok
select 1, 2; select 1, 2, 3; select 'ok'

statement ok
SET application_name = ''

query T
SELECT txn_fingerprint_id FROM crdb_internal.node_statement_statistics WHERE application_name = 'multi_stmts_test' ORDER BY txn_fingerprint_id
----
10413021493801724718
10413021493801724718
10413021493801724718
17854018046052698166

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;

statement ok
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 = '';

statement ok
RESET distsql

skipif config 3node-tenant-default-configs #52763
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 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 _ 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;

# Upgraded implicit txn.
statement ok
select 1; BEGIN; select 1; select 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 _                        false
SELECT x FROM test WHERE y = _  false
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

# Test throttling of storing statistics per application

# Creating some helper views.
statement ok
CREATE VIEW txn_fingerprint_view
AS SELECT
  key, statement_ids, count
FROM
  crdb_internal.node_transaction_statistics
WHERE application_name = 'throttle_test'
  AND statement_ids[1] in (
    SELECT
      statement_id
    FROM
      crdb_internal.node_statement_statistics
    WHERE
      key LIKE 'SELECT%'
  )

statement ok
CREATE VIEW app_stmts_view
AS SELECT statement_id, key, count
FROM crdb_internal.node_statement_statistics
WHERE application_name = 'throttle_test'

statement ok
SET application_name = throttle_test

# Create 3 distinct fingerprints.
statement ok
BEGIN; SELECT 1; SELECT 1, 2; SELECT 1, 2, 3; COMMIT

statement ok
BEGIN

query TTI
SELECT * FROM app_stmts_view
----
11619138756491374338  SELECT _        1
12989550133819508941  SELECT _, _     1
10964754960692259604  SELECT _, _, _  1

query TTI
SELECT * FROM txn_fingerprint_view
----
2090134368184080810  {11619138756491374338,12989550133819508941,10964754960692259604}  1

statement OK
COMMIT

# Change the limit for unique fingerprint.
statement ok
SET CLUSTER SETTING sql.metrics.max_mem_stmt_fingerprints = 0

statement ok
SET CLUSTER SETTING sql.metrics.max_mem_txn_fingerprints = 0

# We now have two additional statement fingerprints because of the two queries we ran against the crdb_internal view
# prior to setting the fingerprint limit to 0.
query TTI
SELECT * FROM app_stmts_view
----
7450916591118342552   SELECT * FROM app_stmts_view        1
738932590625820206    SELECT * FROM txn_fingerprint_view  1
11619138756491374338  SELECT _                            1
12989550133819508941  SELECT _, _                         1
10964754960692259604  SELECT _, _, _                      1

query TTI rowsort
SELECT * FROM txn_fingerprint_view
----
2090134368184080810  {11619138756491374338,12989550133819508941,10964754960692259604}  1
9556717914237561995  {7450916591118342552,738932590625820206}                          1

# Run more fingerprints to ensure that we are still updating our existing fingerprint but not creating more
# fingerprints.
statement ok
BEGIN; SELECT 1; SELECT 1, 3; SELECT 1, 2, 3; COMMIT

statement ok
BEGIN; SELECT 1; SELECT 1, 3; SELECT 1, 2, 3, 4; COMMIT

statement ok
BEGIN

query TTI
SELECT * FROM app_stmts_view
----
7450916591118342552   SELECT * FROM app_stmts_view        1
738932590625820206    SELECT * FROM txn_fingerprint_view  1
11619138756491374338  SELECT _                            2
12989550133819508941  SELECT _, _                         2
10964754960692259604  SELECT _, _, _                      2

query TTI rowsort
SELECT * FROM txn_fingerprint_view
----
2090134368184080810  {11619138756491374338,12989550133819508941,10964754960692259604}  2
9556717914237561995  {7450916591118342552,738932590625820206}                          1

statement ok
COMMIT

# Reset cluster setting for fingerprint limit.
statement ok
RESET CLUSTER SETTING sql.metrics.max_mem_stmt_fingerprints

statement ok
RESET CLUSTER SETTING sql.metrics.max_mem_txn_fingerprints

statement ok
BEGIN; SELECT 1; SELECT 1, 3; SELECT 1, 2, 3, 4; COMMIT

statement ok
BEGIN

query TTI
SELECT * FROM app_stmts_view
----
7450916591118342552   SELECT * FROM app_stmts_view                                           2
738932590625820206    SELECT * FROM txn_fingerprint_view                                     2
11619138756491374338  SELECT _                                                               2
11619138756491374338  SELECT _                                                               1
12989550133819508941  SELECT _, _                                                            2
12989550133819508941  SELECT _, _                                                            1
10964754960692259604  SELECT _, _, _                                                         2
13598297958913191023  SELECT _, _, _, _                                                      1
8880410704856970575   SET CLUSTER SETTING "sql.metrics.max_mem_stmt_fingerprints" = DEFAULT  1
6650120188975168745   SET CLUSTER SETTING "sql.metrics.max_mem_txn_fingerprints" = DEFAULT   1

query TTI rowsort
SELECT * FROM txn_fingerprint_view
----
2090134368184080810  {11619138756491374338,12989550133819508941,10964754960692259604}  2
4151720013906157265  {11619138756491374338,12989550133819508941,13598297958913191023}  1
9556717914237561995  {7450916591118342552,738932590625820206}                          2

statement ok
COMMIT

statement ok
BEGIN; SELECT COUNT(1) AS wombat1; COMMIT

query T
SELECT metadata->>'querySummary' FROM crdb_internal.statement_statistics WHERE metadata->>'query' LIKE '%wombat1%'
----
SELECT count(_) AS wom...

statement ok
SELECT COUNT(1) AS wombat2

query T
SELECT metadata->>'querySummary' FROM crdb_internal.statement_statistics WHERE metadata->>'query' LIKE '%wombat2%'
----
SELECT count(_) AS wom...