Sql Normalization

<!-- status: beta language: sql-normalization -->

SQL normalization and statement hash

One algorithm turns a SQL statement into literal-free text and a stable hash. Every implementation must produce the same bytes, so a span's db.query.hash joins agent DBM samples and the server-side wt.db.query_hash.

• Reference: services/worker/src/sqlnorm.rs (normalize_with). It stamps wt.db.query_hash on spans that arrive with statement text.

• Node SDK: sdks/node/src/sql.ts (normalizeSql, normalizeAndHash, export @watchtowerobservability/node/sql).

• Shared vectors: testdata/sql_normalize_vectors.json. Each entry has input, pg, normalized and hash. Every implementation must pass all of them.

• Note: go/agent/ebpf/trace/sql.go (NormalizeSQL) is an older mirror. It differs on backslash escapes and on Postgres "identifiers", and it emits no hash. Port it to this spec before relying on agent hashes.

Input

• The input is a statement string and the pg dialect flag. pg is true when db.system.name (or the legacy db.system) is postgresql, cockroachdb or redshift.

• Only the first 8192 UTF-8 bytes are read. The cut moves back to a whole character.

• The text is processed as a sequence of Unicode scalar values (Rust char). JS code must iterate by code point, not by UTF-16 unit.

Character classes

• whitespace: Unicode White_Space (Rust char::is_whitespace). This is not JS \s, which also matches U+FEFF.

• alnum: Unicode Alphabetic or general category N* (Rust char::is_alphanumeric).

• word: alnum, _ or $.

• digit: ASCII 0-9 only.

Scan (one left-to-right pass)

The scan keeps an output buffer and a space flag, both starting empty/false. At each position, the first matching rule applies:

1. Whitespace sets space and advances one character.

2. /* opens a comment. Comments nest: /* adds a level and */ closes one. An unclosed comment runs to the end. Sets space.

3. --, or # when not pg, opens a comment that runs to the next \n. The \n itself is left for rule 1. Sets space.

4. Anything else. If space is set and the output is non-empty, write one . Clear space, then read one token:

   • ' (a string). Skip ahead to the next ' that is not part of a doubled '', then write ?. If a string is closed and the character just before the closing quote is \, discard the rest of the statement. The quote may have been a MySQL escape, so stopping here never leaks what follows. Otherwise continue after the closing quote.

   • " or `. The token runs to the next identical quote character, or to the end of the statement if there is none.

     • Keep it verbatim, with both quotes, only when all of these hold: the quote is ", pg is true, the quote is closed, the body is 1-64 characters, and every body character is alnum, _ or $.

     • Otherwise write ?. MySQL and SQLite accept "alice" as a string, so a backtick-quoted identifier also becomes ?.

   • $ followed by a digit. Read $ and all following digits, then write ?.

   • $ opening a dollar-quoted string.

     • Look up to 33 characters past the $ for the next $. The text between the two is the tag. The tag must be empty or contain only alnum or _, and its first character must not be a digit.

     • The token runs past the next occurrence of the full $tag$, or to the end of the statement if there is none. Write ?.

     • If no valid tag is found, the $ is handled as a word character (next rule).

   • digit. Read the run of word characters and ., then write ?. This covers 0x1F and 1.5e3.

   • word character. Copy the run of word characters.

   • any other character. Copy that one character.

IN-list collapse (on the UTF-8 bytes of the scan output)

The collapse runs on the UTF-8 bytes of the scan output, from left to right. At each byte i:

1. The match is checked only when i is at a word start: i is 0, or the previous byte, read as a Latin-1 character (U+0000-U+00FF), is not a word character. This is a byte-level quirk of the reference. The last UTF-8 byte of é is 0xA9 (©), which is not a word character, so éin (1) collapses. Implementations must reproduce this.

2. Bytes i and i+1 must be in, compared case-insensitively. There is no check after in.

3. Then come optional spaces, (, and one or more items, each ? with optional spaces around it. Items are separated by , and the list ends with ).

4. On a match, copy the original bytes up to the ( (the in and any spaces), write (?+), and continue after the ). Otherwise copy byte i and move on.

The result is a normalized text such as SELECT * FROM t WHERE id IN (?+) AND n = ?.

Hash

hash = XXH3_64bits(UTF-8 bytes of the normalized text), with seed 0 and the default 192-byte secret. It is written as an unsigned decimal string, because 64-bit numbers lose precision in JSON. The Node SDK carries its own XXH3 port (BigInt). The vectors cover every length bucket (0, 1-3, 4-8, 9-16, 17-128, 129-240, more than 240, and more than 1024 bytes, which crosses the block scramble).

Span attributes

• db.query.hash: always set on a span that has a statement.

• db.query.summary: <OPERATION> <first table>, taken from the normalized text. It is also the span name.

• db.query.text: the normalized text, only when the user opts in (queryText: true or WATCHTOWER_DB_QUERY_TEXT=1). Raw text and parameters are never recorded.

• N+1 hint: a span gets db.n_plus_one=true and db.query.repeat_count=<n> when the same hash has run more than N times (default 5) under one parent span. Only the repeats after the N-th carry the hint.

Changing the algorithm

The vectors file is the contract. To change the algorithm, update the Rust, the SDKs and the vectors in one change. Old and new hashes do not join, so call the change out in the changelog.