Aggregates lacked the SQL-standard FILTER clause,
forcing CASE-based workarounds that reduced readability across (sum, avg, count, …).
This update introduces the ability to specify a FILTER clause for aggregate functions,
allowing for more granular control over which rows are included in the aggregation.
Also, improves standards compliance and makes queries clearer and more readable.
The FILTER(WHERE ...) condition may contain any expression allowed in regular WHERE clauses,
except window functions, and outer references.
on a truncated nested JSON object with different character sets/collations.
The value->s.c_str and js->s.c_str became invalid once the
function returned which caused the crash.
Across a range of JSON functions taking a path argument
there are SQL expressions that dont' have a character
set. If these expressions don't have a character set
fall back to the character set of the argument of the
json function that represent the document being operated
on. If this doesn't have a character set fall back to
my_charset_utf8mb4_bin.
This covers the 11.4 JSON_KEY_VALUE function also as it
reuses the Json_path_extractor::extract method.
Add nonnull and warn_unused_result to the json path
functions to facilitate compiler and UBSAN catching of the
problem early.
As null values of s_p are incompatible with report_path_error,
jump directly to a null return which is consistent with
the defination of the JSON sql funciton.
JSON_EQUALS didn't check that ::val_str() of its arguments
where not-null before attempting to test their equality.
JSON_OVERLAPS also ensure that ::val_json() isn't null.
don't truncate to a position somewhere between
[old_length, max_length-1] if old_length == max_length.
Simply set the length to old_length in this case.
Also, max_length cannot be zero.
The JSON functions added in 11.4 where not interuptable
with a KILL QUERY or exceeding the max_statement_time.
* JSON_ARRAY_INTERSECT
* JSON_OBJECT_TO_ARRAY
* JSON_SCHEMA_VALID
This behaviour is corrected by setting the killed_ptr
of the json_engine_t structure.
JSON_KEY_VALUE was added in 11.4, however as it doesn't call
json_next_value(), its processing doesn't check the killed pointer
of the json engine. So its quick anyway.
JSON_OBJECT_FILTER_KEYS, has some structural problem. These
will be addressed in MDEV-39941 and the KILL QUERY/max_statement_time
will be implemented then.
Reviewed by: Rucha Deodhar
a90779098b changed the
interface to the json_valid to take a json_engine_t
so that it could be interupted and so that warnings
could become visible.
On merge from that 10.11 to 11.4 it was discovered
that the ColumnStore engine uses this interface.
Restored the original json_valid function exactly
how ColumnStore expects this and added renamed
the function with json_engine_t to be json_valid_engine.
This follows the same convention as in the commit
756fc6fd9a that was
for json_normalize, that ColumnStore also used.
a90779098b changed the
interface to the json_valid to take a json_engine_t
so that it could be interupted and so that warnings
could become visible.
On merge from that 10.11 to 11.4 it was discovered
that the ColumnStore engine uses this interface.
Restored the original json_valid function exactly
how ColumnStore expects this and added renamed
the function with json_engine_t to be json_valid_engine.
This follows the same convention as in the commit
756fc6fd9a that was
for json_normalize, that ColumnStore also used.
14c16e02b2 changed the
interface for json_normalize to take a json_engine_t so that
there could be some error checking.
Unfortunately ColumnStore depended on this interface.
Restored original interface of json_normalize() and
added a json_normalize_engine() that take as engine as the
argument.
14c16e02b2 changed the
interface for json_normalize to take a json_engine_t so that
there could be some error checking.
Unfortunately ColumnStore depended on this interface.
Restored original interface of json_normalize() and
added a json_normalize_engine() that take as engine as the
argument.
JSON_CONTAINS, JSON_EQUALS, and JSON_OVERLAPS used raw byte-level
comparison (memcmp) for JSON string values, which meant semantically
equivalent strings like "A" and "\u0041" were incorrectly treated
as different.
Fix: add json_string_compare() that decodes Unicode escape sequences
before comparing, and fix json_normalize to produce a canonical form
for strings with escapes so JSON_EQUALS works correctly.
All new code of the whole pull request, including one or several files
that are either new files or modified ones, are contributed under the
BSD-new license. I am contributing on behalf of my employer Amazon Web
Services, Inc.
Or max_statement_time limit.
Change the json_valid to take a json_engine_t argument.
Adjust the Item_json_valid to have a json_engine_t and
to report an error.
Note this means that invalid json syntax now has a
note on any json error. Raising this to a warning can
cause check constraints of JSON_VALID() to result
in ER_JSON_SYNTAX rather than ER_CONSTRAINT_FAILED
(valided in main.type_json test).
Consequential changes in debug build to json acl
testing validation and unit test having a dummy json_engine_t.
json_normalize also uses the same engine that was
passed as its argument to test json_valid. Because
the character set may have changed, if there's an error
adjust the je->s.c_str pointer. Its only the offset that
is currently used in the error messages anyway so there
may be offset errors depending on the original character
set.
Or max_statement_time limit.
JSON_EQUAL/JSON_NORMALIZE also didn't report warnings
on invalid JSON inputs.
Change the json_normalize function to take a json_engine_t
as a argument and implement the sql/item_jsonfunc.cc calls
to have a json_engine_t.
To the JSON_EQUALS and JSON_NORMALIZE, add error handling
to produce an error when there was one. compare_nested_object,
part of JSON_OVERLAPS, uses a new json_engine and
copies the error/position to the returning function.
json_normalize expects the incoming je structure to have
a valid, or nullptr, killed_ptr, as json_start will reset this.
Or max_statement_time limit.
Implement interuptability of:
* JSON_OVERLAPS
* JSON_LENGTH
* JSON_DEPTH
As json_report_error now processes the killed there's
no need of thd->check_killed(). Removed this from a few
locations.
Verified that the path after every json_scan_next, the
location of checking the killed_ptr, reports errors
correctly.
The IS JSON predicate in a query wasn't able to be killed,
and could exceed the max_statement_time of a query.
Introduce the mechanics so that a killed query would
result in the correct error.
Or max_statement_time limit or MAX_DEPTH.
Correct interuptability and depth checking of:
* JSON_CONTAINS
* JSON_CONTAINS_PATH
* JSON_EXISTS
* JSON_EXTRACT
* JSON_KEYS
* ST_GeomFromGeoJSON
Populate in the json library the killed as a specific
error message. As the json library has a killed_ptr
is approprate it handles it.
As json_report_error now processes the killed there's
no need of thd->check_killed().
Verified that the path after every json_scan_next, the
location of checking the killed_ptr, reports errors
correctly.
Backport to 10.6 without JSON_OVERLAPS
The json_engine killed_ptr was of type uchar however the enum
in the server is dependant on the architecture. On big-endian
architectures like IBM Z, retreiving a uchar* retreived the
unmodifed part of the THD->kill enum location and was always 0.
As C++11 allows enums to inherit a type, used uint32_t as the base
class of killed_state type in the server and used uint32_t in the
json library.
reload_acl_and_cache required an expression change to avoid
the compile error:
sql/sql_reload.cc:472:24: error: enumerated and non-enumerated
type in conditional expression [-Werror=enum-conversion]
472 | return result || (thd ? thd->killed : 0);
Added the kill_ptr assignment the following functions can be
killed:
* Item_func_json_depth::val_int
* Item_func_json_type::val_str
* Item_func_json_length::val_int
Item_func_json_array_append::val_str, check_killed() only applied
to json_error: label and not return_null.
Item_func_json_format::val_str(), corresponding to the SQL,
json_compact, json_detailed, json_loose - add debug instrumentation
for the func_json_notembedded test.
MDEV-26726 (fcd345de48) added a json_pause_execution sync point
however the test case used debug_max_statement_time, which doesn't
exist. Fix the sync point name in the test.
(10.6 backport)
Json test about max statement time fails with freebsd because on some
architectures the test might execute faster and the statement may not fail.
To simulate failure regardless of architecture, introduce a wait of seconds
longer than the max_statement_time.
MDEV-37864 added a length setting to the function
Item_func_json_array_intersect::prepare_json_and_create_hash
however some paths of Item_func_json_array_intersect::fix_length_and_dec
never reach this function.
Leave the length calculation the same but place in the
::fix_length_and_dec function.
Collation is based on the arg[0], but potentially after they
are swapped.
Corrects cursor protocol for the test case added in MDEV-36808.
GROUP_CONCAT is limited by group_concat_max_len,
but also, as a string function it must respect max_allowed_packet.
Let's introduce THD::gconcat_max_len() helper to simplify checks.
Also:
* make max group_concat_max_len value the same as max max_allowed_packet
* use the same MY_MIN((ulonglong) ..., UINT_MAX32) in
Item_func_json_objectagg as in Item_func_group_concat
* use overflow-safe type for lengths in Item_func_quote
(MAX_MAX_ALLOWED_PACKET is 1G so uint cannot overflow yet, but it's
a fragile assumption)
Test func_json_notembedded became unreliable after
509e40e706 where JSON_ARRAY_APPEND
would return NULL rather than:
ERROR 70100: Query execution was interrupted (max_statement_time exceeded)
Restore the original fragment.
The json_engine killed_ptr was of type uchar however the enum
in the server is dependant on the architecture. On big-endian
architectures like IBM Z, retreiving a uchar* retreived the
unmodifed part of the THD->kill enum location and was always 0.
As C++11 allows enums to inherit a type, used uint32_t as the base
class of killed_state type in the server and used uint32_t in the
json library.
reload_acl_and_cache required an expression change to avoid
the compile error:
sql/sql_reload.cc:472:24: error: enumerated and non-enumerated
type in conditional expression [-Werror=enum-conversion]
472 | return result || (thd ? thd->killed : 0);
Added the kill_ptr assignment the following functions can be
killed:
* Item_func_json_depth::val_int
* Item_func_json_type::val_str
* Item_func_json_length::val_int
Item_func_json_array_append::val_str, check_killed() only applied
to json_error: label and not return_null.
Item_func_json_overlaps::val_bool ensure check_killed() is called.
Item_func_json_format::val_str(), corresponding to the SQL,
json_compact, json_detailed, json_loose - add debug instrumentation
for the func_json_notembedded test.
MDEV-26726 (fcd345de48) added a json_pause_execution sync point
however the test case used debug_max_statement_time, which doesn't
exist. Fix the sync point name in the test.
