Project "JSON Phoenix" - redundant computation elimination

Summary

This project eliminates revalidation, reparsing and re-nicing in the JSON retrieval and modification pipeline. It is largely accomplished by carrying attestations across the call stack for three properties: validity, niceness and depth (MariaDB bounds nesting depth).

The work uncovered many defects in the JSON function implementations. They vary in severity and span a broad area of functionality: invalid output, unvalidated input, length overruns and depth overruns. Defects that directly affect the functionality this project modifies are fixed here. The rest can be fixed independently and remain open. All are filed as split-offs from this MDEV.

Intended rules

Output fidelity is the primary rule of this project. As much as possible, every output and behavior that is documented, or recorded by the baseline tests, is preserved. The exception is corrupted or erroneous output: malformed JSON, improperly encoded strings, improperly converted character sets, and NULL returned where a valid value was obtainable.

Attestation. The result of a direct observation of a value, made by a validator, a parser, a nice formatter or a depth measurement. Every JSON value carries three attestation marks: validity, niceness and maximum observed depth.

Where no such observation has been made the marks default to false. The value is then not known to be valid – which is not to be confused with invalid – not known to be nice, and its maximum observed depth is unknown.

All nice values are valid. A valid value need not be nice.

Static attestation. An attestation about an operation rather than about a single value. An operation known to return only valid, nice or depth-limited JSON, or NULL, carries one, and every value it returns is thereby known to be valid.

Important note #1: because the user can turn off constraint checks on JSON columns, there is no way to establish whether a value stored in a JSON column is in fact valid. A json_valid check executed against the value during a DML leaves no record of its outcome. Values retrieved from JSON columns in user-controlled MariaDB tables therefore carry no positive attestation marks. This is a deliberate choice, taken to avoid undefined behavior with existing data that cannot be known to be valid. If, however, product management or project leadership decides that values stored in JSON columns can be interpreted as valid by default, such behavior is easy to add and will produce additional performance benefits.

Internal temporary tables are the exception. The server creates and destroys them within a single query (GROUP BY, derived tables, UNIONs, window materialization). A column of such a table is trusted when its producer carries a static attestation and every store into the column confirms it. A producer carrying only a per-evaluation attestation grants the column nothing.

The constraint-disable argument above does not reach these tables: no user statement can address them, no constraint toggling is possible, and no replication or import path writes to them. Stored-program variables follow the same reasoning. They reside in a server-internal table that no user statement can address, and the assignment path maintains their attestation. User-created CREATE TEMPORARY TABLE tables are NOT internal and are untrusted like base tables.

Important note #2: four functions that would ordinarily be expected to return only valid JSON – JSON_ARRAY, JSON_OBJECT, JSON_ARRAYAGG and JSON_OBJECTAGG – permit invalid JSON input in their JSON-typed arguments and splice it into the output verbatim, raising only a note. This weakens their potential for static validity attestation. It is done for compatibility, to minimize disruption of existing functionality in stable MariaDB versions. This is not ideal. If product management decides that it is permissible to change such behavior to only accept valid JSON in input arguments, further optimizations related to JSON value attestations stored in internal temporary tables become possible. The behavior is filed as MDEV-40700. An example of currently accepted invalid input:

SET SESSION check_constraint_checks=OFF;

CREATE TABLE tj (id INT, c JSON);

INSERT INTO tj VALUES (1,'{"a":1 "b":2}'),(2,'[1,2');

SET SESSION check_constraint_checks=ON;

SELECT id, JSON_ARRAY(c) AS v, JSON_VALID(JSON_ARRAY(c)) AS ok

FROM tj ORDER BY id;

id  v                  ok

1   [{"a":1 "b":2}]    0

2   [[1,2]             0

In the second row the argument's dangling bracket has consumed the constructor's own closing bracket: the array constructor opened one bracket and wrote one closing bracket, yet the result has two opening brackets and one closing.

First Principles

JSON function. A function returning a JSON value or NULL.

A JSON function returning a JSON value SHOULD attest to its validity; declining to attest a qualifying value is permitted and costs the reader one validation. A JSON function MUST NOT return malformed JSON. A JSON function forced to truncate a return value, by the target size for instance, MUST raise a note or a warning along with the return value, or return NULL while raising a warning or error.

A JSON function MAY accept JSON values as arguments, and such arguments MAY carry positive attestations. A JSON function SHOULD honor an attestation by eliminating the parsing, formatting, validation or mutation step it renders redundant. Such elimination MUST NOT occur where the attestation is absent. Arguments and other inputs not attested valid MUST be validated before use.

A JSON function combining or otherwise transforming JSON arguments SHOULD preserve the niceness of its output if and only if niceness is a requirement of the function's documented contract and all of the arguments are attested nice.

The validity and niceness attestations MUST NOT be preserved during character set conversion unless the conversion is lossless and completes fully without error, insofar as the markup of the JSON document is preserved. For the avoidance of doubt, if a JSON document is undergoing a character set conversion that results in the loss of data in string literals, the document SHALL remain a valid JSON document as long as such data losses do not affect the JSON syntactical markup.

All of the above principles are subject to the attempt to preserve current behavior where possible (see Important note #2) and MAY be violated here for that purpose. If possible such violations SHOULD be eliminated prior to the final merge to stable.

What was modified

Defects found in the course of the work are filed as separate MDEVs. Those that directly affect the modified functionality are fixed as separate commits within this PR and are not described further here.

Item: per-evaluation attestation

sql/item.h, sql/item.cc, sql/item_func.h

Three virtual methods are added to Item:

virtual bool is_valid_json() const;      /* false */

virtual bool is_nice_json() const;       /* false */

virtual uint last_depth() const;         /* JSON_DEPTH_UNKNOWN */

Each concerns the value the last evaluation returned. is_valid_json() attests that the value is a JSON document. is_nice_json() attests that it carries the formatting json_nice() writes for the loose form. last_depth() reports its nesting depth.

A fourth method, is_valid_json_static(), attests about the class: every evaluation yields a document or SQL NULL. It MAY be asked before any evaluation has occurred.

All four default to the non-attesting answer. A caller told nothing MUST read the value. False attestation MUST NOT occur; declining to attest a qualifying value is permitted and costs one reading.

JSON_DEPTH_UNKNOWN is UINT_MAX. An unattested depth therefore exceeds every reachable depth and fails every limit comparison.

Json_result_marks holds the three per-evaluation answers of an item that composes its value. set() accepts the value being attested and, in assertion-enabled builds, reads it back and aborts on a false attestation. The copy constructor attests nothing: a copy has not been evaluated.

Result side

An item with a result field holds two byte sequences: the composed value and a copy in a record. Only the first is what the item attested. Which of the two a reference reads depends on the reference, not on the referent.

Three result-side methods are added: is_valid_json_result(), is_nice_json_result() and last_depth_result(). Each class attests from whichever byte sequence its own str_result() reads: Item_field from result_field, Item_ref by the same condition its val_str() uses, Item_direct_ref from the value side, Item_direct_view_ref by forwarding. Item_func_set_user_var attests nothing on either side.

Item_sp_variable

A stored-program variable forwards the three answers through this_item(). The same item serves both read and store: reading a variable goes through this_item(), and assigning one variable to another delivers the value through the same field, so a single attestation covers both directions without explicit propagation.

is_valid_json_static() is NOT answered here. A variable holds what the last assignment stored; the next assignment can store anything.

Item_field

Item_field answers the three from the field it reads and, on the result side, from result_field. A field holding SQL NULL attests nothing regardless of column metadata.

Six debug detectors – reads_back_as_document(), reads_back_as_nice(), reads_back_no_deeper_than_claimed() and their result-side twins – re-read the field to verify the answers. They are compiled wherever DBUG_ASSERT compiles its expression, which includes DBUG_ASSERT_AS_PRINTF builds where DBUG_OFF is set. Members read only by such assertions are therefore guarded with #ifdef DBUG_ASSERT_EXISTS. Item_copy::copied_in is moved to the same guard.

Pass-through items

A pass-through item returns one of its arguments unchanged. CASE and its abbreviations – COALESCE, IFNULL, IF, NVL2, NULLIF and DECODE – are pass-through items, as are a scalar subquery and the expression cache in front of one. The bytes belong to the argument; the item transmits them.

None of these items attested to what they returned. A document read and attested by one JSON function, passed through a COALESCE, reached the next JSON function unattested and was read a second time to establish what was already known.

Each item now records which argument supplied the value at the moment it reads that argument, and puts the three questions to that argument. The argument cannot be identified afterwards: COALESCE and IFNULL determine theirs by evaluating arguments in turn, and the others are directed to one by a condition that may contain a subquery or a stored program. Asking a second time would evaluate that condition twice.

The same read carries the caller's request for a document down to the argument. Without it an argument returning a view of a value it owns – a stored program variable, for one – handed out that view where a copy was owed, the request having stopped at the item in front of it.

is_valid_json_static() is answered as well, a temporary table being written before any row exists and taking what can be stated in advance. The answer is the conjunction over the arguments that can be returned. WHEN conditions are excluded: reorder_args() has already moved them to the front, so the returnable arguments are the tail of the list.

Item_cache_str holds a copy of the item it was built over and takes that item's answers alongside the copy, as Item_copy_string does.

A debug build checks a forwarded answer against the bytes it describes while both are in hand. That is the reading Json_result_marks::set() performs wherever an answer is first given.

Field: per-record attestation

sql/field.h, sql/field.cc

Two independent channels carry attestation through a record.

Per-row channel

Field::set_is_valid_json() marks the value just stored as a document. It is set only where Field::is_attestation_preserved() returns true. That requires all five of:

1. the item attested is_valid_json();
2. Field::store() returned 0 (no truncation);
3. the field preserves the bytes it receives – Field::is_character_preserving(), dispatched on real_type() so that a type MUST be named to be trusted, and asked about compression first, a compressed class tested as the real_type() it derives from;
4. String_copier::conversion_keeps_characters();
5. no error has occurred in the statement.

Any other condition clears the mark.

Per-column channel

Field::set_is_valid_json_static() accepts the item that will fill the column. It requires is_valid_json_static() from the item and a character-preserving field. Character sets are NOT checked here: the branches attest what they always attest, and the column's character set is not yet settled.

Field::confirm_is_valid_json_static() runs after every store into a granted column and converts the grant into an answer, checking how much was kept and whether conversion_keeps_characters(). Field::confirm_is_valid_json_static_from() does the same for a field filled from another field. Both resolve through Field::confirm_json_static_value(), which settles the formatting and the depth.

Formatting and depth are granted once and spent thereafter. A value formatted differently clears is_nice_json_static() permanently. Depth runs the opposite way: it starts at the minimum (no rows) and is raised by each value via Field::raise_json_static_depth(). An uncounted value takes the column to JSON_DEPTH_UNKNOWN via Field::forget_json_static_depth(), where it stays.

Per-variable channel

Field::set_json_held_marks() records the three answers exactly. A variable holds one value, the producing item is in hand, and no future rows constrain the answer. The same is_attestation_preserved() rule applies. SQL NULL clears.

Reader interface

Field::attests_is_valid_json(), Field::attests_is_nice_json() and Field::attested_json_depth() are the reader methods. Each checks that the field holds a value before reading, and reads both channels. A reader need not know which channel carries the answer. The depth returns the smaller of the two, both being upper bounds on the same quantity.

TABLE: storage and invalidation of attestation state

sql/table.h, sql/table.cc

Per-row state:

• is_valid_json_set – a bitmap, one bit per field, for ONE row image.

Per-column state (temporary tables only):

• is_valid_json_static_set, is_nice_json_static_set, json_static_depth – allocated only by Create_tmp_table::start(), so base tables and user-created tables have no per-column channel and attest nothing through it.

Per-variable state:

• json_held_marks – an array of Json_result_marks, allocated only where a stored program's variables are built.

Discriminator:

• has_own_json_valid_check – whether any column carries a check about itself, computed once at open so that store sites and drains do nothing on a table to which this is inapplicable.

Invalidation:

TABLE::clear_is_valid_json_marks() clears the per-row bitmap. Marks MUST be cleared when a row image is BEGUN, not after something is finished with them: a row whose writing completes before any read would otherwise leave stale marks.

TABLE::restore_record_image() performs the memcpy behind the restore_record() macro (sql/unireg.h) and then clears, so that every wholesale row overwrite drops what was said about the replaced image without any caller having to remember to.

TABLE::verify_constraints() calls run_check_constraints() for the constraint loop and drains the per-row bitmap on every way out.

TABLE::set_filled_by_engine() drops the per-column channel in one place.

TABLE::check_json_valid_mark() is the debug-build check that re-reads the value wherever a check constraint is about to be skipped and aborts on an overclaim.

Virtual_column_info::json_valid_field_index records, once at open, which column a check constraint guards, so that only a check about THIS column can leave its reading undone.

Temporary table and group buffer

sql/sql_select.cc, sql/sql_select.h, sql/sql_class.h, sql/sql_union.cc, sql/sql_tvc.cc

• Create_tmp_table::start() allocates the three per-column structures; setup_tmp_table_column_bitmaps() places them.
• Create_tmp_table::add_fields() calls set_is_valid_json_static() at each of its two field-addition sites.
• copy_fields() confirms across the row that crosses between temporary tables. The entry records whether either side can answer; an entry that records neither starts as non-attesting.
• Virtual_tmp_table::init_json_held_marks(), clear_json_held_marks_from() and clear_all_json_held_marks() give a stored-program table its per-variable channel, allocated where the variables are built, not in the base class.
• JOIN::make_aggr_tables_info() keeps, for a grouping without a temporary table, the per-evaluation answer about the value in the group buffer. A buffer holds one value at a time and is filled beside the item that produced it, so it takes the per-evaluation answer where a column can only take the per-class one.
• Type_holder gains m_is_valid_json_static and get_is_valid_json_static(). The type-agreement walk across a UNION or value list grants the column attestation only when EVERY branch attests. A single non-attesting branch revokes the grant. Recursive CTEs are refused: the recursive parts are not walked.

Store sites

sql/sql_base.cc, sql/sql_insert.cc, sql/sql_update.cc, sql/field_conv.cc

Both fill_record() variants, Delayed_insert::get_local_table() and multi_update::do_updates() set or clear the per-row mark.

field_conv() confirms the per-column channel after its dispatch and before return, covering every route that reaches it. The two routes that bypass field_conv() – the fast copier for grouping keys and the copy_fields() loop – confirm for themselves.

Pushdown

sql/select_handler.cc, sql/derived_handler.cc, sql/group_by_handler.cc, sql/sql_select.cc

An engine writes record[0] with no Field::store() on the way in, so nothing there can confirm or revoke a grant. Every way in drops the per-column channel: both arms of select_handler::prepare() – the one that builds such a table and the one that takes a table the engine brought – derived_handler::set_derived() for a pushed-down derived table, and JOIN::make_aggr_tables_info() where the table is handed to a group-by handler. select_handler::execute(), Pushdown_query::execute() and Pushdown_derived::execute() assert that the way in did.

JSON functions

sql/item_jsonfunc.h, sql/item_jsonfunc.cc, sql/item_strfunc.h, sql/item_strfunc.cc

What each function attests

Per-class (is_valid_json_static()):

• JSON_INSERT, JSON_SET, JSON_REPLACE, JSON_REMOVE, JSON_ARRAY_APPEND, JSON_ARRAY_INSERT, JSON_MERGE, JSON_MERGE_PATCH, JSON_EXTRACT, JSON_QUERY, JSON_SEARCH, JSON_KEYS and JSON_NORMALIZE: each returns either a span cut from valid input or a composition it reads back. Of these, JSON_QUERY preserves input spacing and JSON_NORMALIZE emits compact form, so neither attests formatting.

Per-evaluation:

• JSON_ARRAY, JSON_OBJECT and the two aggregates build their value and attest from what they learned composing it.
• JSON_FORMAT forwards: val_str() attests the document and the formatting its own format writes; val_json() attests exactly what its argument attests.
• Item_func_conv_charset passes its argument's answers through where the conversion substituted and dropped nothing. Conversion to binary is refused: it relabels, it does not convert.
• Everything else attests nothing.

Json_splice_marks carries answers over values spliced into a composition. It starts attesting and is only cleared: an array is a document only if every element was. It takes the starting depth for its reckoning in a dedicated constructor.

Readings eliminated

Where an argument attests, the redundant reading is not made:

• The epilogue that read a composed answer back – json_scan_start() followed by json_nice() – is replaced by returning the document through return_json() where the document attested, the argument attested the formatting, and the character set can write the punctuation. Where any condition fails the epilogue stands and the output is unchanged.
• Item_func_json_extract::read_json() no longer walks the document past the last matched path.
• Item_func_json_contains_path::val_bool() no longer continues past a settled answer.
• Item_func_json_length::val_int() no longer walks past the counted length.
• Item_func_json_type::val_str() calls json_valid_engine() only where nothing attested, preserving the existing order so that a type is never returned for non-JSON text.
• Item_func_json_valid::val_bool() returns true where the item attested.
• A check constraint's re-reading of a stored value is skipped where the item attested and the store preserved characters.

document_arg_composes_final() is the single point where the three conditions are tested. What each function does with the result is local to that function.

Helpers

• return_json() – the single return body for composing functions; buffer-growth failure is returned to the caller, which alone can report it.
• json_value_reads_as_document(), json_value_is_nice() and json_value_depth() – the readings the debug assertions make.
• json_walk_nice_value() – json_nice() factored so that the loose form is written in ONE place. A formatting claim MUST rest on the constants json_loose_comma and json_loose_colon, which are file-scoped and used by every function that writes loose formatting itself.
• append_simple() – copies document bytes already in the result's character set. String::append() converts from latin1 and MUST NOT be used for them.
• json_skip_space() – reads one character and classifies it; a single-byte test is wrong in ucs2, utf16 and utf32.
• append_json_value(), append_json_typed_value(), append_json_keyname() and append_escaped_value() – the depth is recorded before the value is examined and is checked against the limit before every arm; the function name and argument number reach both arms.
• st_append_escaped() returns json_append_result so that a buffer that will not grow is distinguished from a character that cannot be written; report_bad_chr_note() reports the second.
• is_json_compatible_charset() and json_key_span_is_inert() – whether a character set can express JSON punctuation at all, and whether a span went in unconverted.
• deepest_document_argument() – folds an argument's depth one argument at a time; what an argument attests is about the value it has just produced.

Counting

json_scan_start() is the single entry point for every reading and is where the Json_scans status variable (mysqld.cc, STATUS_VAR) is incremented. Counting at the callers would require enumerating them.

A reading made by a debug build to verify an attestation is subtracted: json_scan_start_unbilled() and json_uncount_scan() where the reading starts within item_jsonfunc.cc, Json_scans_unbilled where a whole check constraint is run. json_scan_start_hook in json_lib is the callback that lets the server count a reading the library makes.

Supporting changes

• String_copier::conversion_copies_bytes() and conversion_keeps_characters() (sql/sql_string.h, sql/sql_string.cc) – the arm well_formed_copy() takes, exposed where a caller can ask it so that the two cannot diverge.
• Type_handler_json_common::is_json_valid_of_field() (sql/sql_type_json.h, sql/sql_type_json.cc) – whether a check constraint is json_valid() of the field it guards.
• my_realloc() (mysys/my_malloc.c) gains a debug keyword for failing a buffer being GROWN; the existing keyword fails every allocation including the first and therefore cannot reach an append into a buffer already held.
• unittest/sql/copy_field-t.cc – a Copy_field entry for answering no when it was told about neither field, reading the answer as a byte, not as a value.
• Item::conv_charset_arg() (sql/item.h), overridden by Item_func_conv_charset (sql/item_strfunc.h) – returns the item that the character-set conversion wrapper encloses, and NULL from every other item. is_json_type() must see through that wrapper in order to decide whether a value is spliced or quoted, and it is called once for every value written into a container and once for every argument sized, which together covers nearly every argument the JSON functions receive. Deducing the answer from the object instead is worse in both of the available forms. A dynamic_cast that fails cannot stop early, because absence can only be proved by exhausting the base-class graph, and Item inherits from two classes; nearly every item therefore pays for a multiple-inheritance walk in order to be told no. A dedicated Functype would cost one comparison, but Functype is dispatched on by engines outside the tree, several of which treat UNKNOWN_FUNC as an instruction to print the function by name. A wrapper that stopped reporting UNKNOWN_FUNC would drop out of their dispatch, and the statements they construct would lose the conversion instead of rejecting it. Nothing outside sql/ changes.
• json_error() and json_string_t::error_pos (include/json_lib.h, strings/json_lib.c) – the scanner records the position of a refusal at the moment the refusal is made, instead of deriving it from the read pointer at the moment the refusal is reported. A caller is not required to stop once it has been refused, and one that goes on asking for tokens moves the read pointer past the point of failure, so a position taken later refers to somewhere else entirely. Every site that used to refuse by assigning to error now calls json_error(), which sets the code and the position together and keeps the first refusal. This replaces halting the engine on refusal, which answered the same question but cost a load, a test and a branch on every token of every document the server reads – a cost paid by the documents that never fail, which is nearly all of them.

Tests

Nothing here changes any answer. Tests are of two kinds: what is attested, and what is read.

A behavioral baseline was recorded first across the full JSON surface, so that any subsequent change manifests as a result diff rather than as a judgement call.

Attestation coverage tests exercise every shape a value can travel in: a column, a temporary table, a chain of them, a UNION, a value list, a recursive CTE, stored-program variables, a group buffer and a pushed-down select, with the shapes that MUST NOT be believed beside them.

What is attested produces no visible output – only readings not made – so it is counted: scan-count tests assert Json_scans over otherwise identical statements, with a control beside each that arranges for the attestation to be refused and counts the readings that then occur.

A debug build re-reads every attested value and aborts on an overclaim, turning every JSON statement in the existing suite into a detector. Each detector was proved to fire by introducing a false claim and observing the abort.

Touched tests are additionally run under the prepared-statement, cursor and view protocols.