Description:
A component sometimes has to process values in its own code, not only pass
them on. Examples:
- A table function that deduplicates, groups, joins or sorts the rows
it builds (Bug #120978).
- A component that bridges an external engine or file format into
SQL, and has to merge or filter values the way the server does.
- A stored program in another language that compares its arguments.
- A user-defined type built on a built-in type, which needs the
compare and hash of that base type.
In each case the component must reach the same answer as SQL. If it
does not, its GROUP BY, DISTINCT, join or ORDER BY gives a different
result from the server on the same data. Today the component services
let a component read and write some values, but not compare or hash
them with SQL semantics:
V1 Strings. mysql_string_compare compares under the collation of
the first string only and ignores the collation of the second.
So compare(a, b) and compare(b, a) can disagree, and the result
is not an order. SQL refuses such a comparison (ER 1267, illegal
mix of collations). There is no hash service. A component that
builds a hash table cannot hash by collation weights, so strings
that SQL finds equal ('abc' and 'ABC ' under
utf8mb4_general_ci) fall into different buckets.
V2 JSON. There is no JSON value service. A component gets a JSON
value only as text (UDF arguments, stored program arguments,
statement results). The text loses the typed scalars of the
binary form: a DATE, a TIME or a DECIMAL inside a document becomes
a string or a double. The component has to parse the text again,
and it cannot compare or hash as SQL does: numbers by value
whatever their type, object members in no order, values of
different types ordered by type.
V3 DECIMAL. The only C forms are text (statement service) and, in
the table function prototype, mle_decimal (unscaled integer plus
scale). 1.0 is {10, scale 1} and 1.00 is {100, scale 2}. SQL finds
them equal, but byte equality does not. Each component would have
to rescale 256-bit integers itself.
V4 Temporal values. mle_time is the ABI copy of MYSQL_TIME, and it
is good for reading and writing. But field-by-field comparison is
wrong for it:
- A negative TIME keeps its sign in neg, not in any part.
- A DATETIME with a time zone displacement
(MYSQL_TIMESTAMP_TYPE_DATETIME_TZ) can be the same moment as
another DATETIME with different parts.
- Comparing a TIME with a DATETIME depends on CURRENT_DATE.
Even the server helper my_time_compare() (mysys/my_time.cc)
ignores neg and time_type: it returns 0 for -00:00:01 and
00:00:01. SQL itself compares the packed form
(TIME_to_longlong_packed()).
V5 The value forms are not used the same way in all services. The
stored program runtime services have no DECIMAL or JSON (only
text), and the statement service binds DECIMAL as text.
This is a feature request for compare and hash services for each SQL
type family, with the semantics the server uses for the same type,
and the rule that values that compare equal hash alike.
How to repeat:
1. What SQL answers. A component that receives these values through a
component service has no service that gives the same answers:
-- V1: strings, PAD SPACE and case-insensitive
SELECT _utf8mb4'abc' COLLATE utf8mb4_general_ci = _utf8mb4'ABC ';
-- 1
SELECT _utf8mb4'abc' COLLATE utf8mb4_bin =
_utf8mb4'ABC' COLLATE utf8mb4_0900_ai_ci;
-- ERROR 1267 (HY000): Illegal mix of collations
-- mysql_string_compare(s1, s2) with s1 = 'abc' (utf8mb4_bin) and
-- s2 = 'ABC' (utf8mb4_0900_ai_ci) compares under utf8mb4_bin and
-- returns non-zero; with the arguments swapped it compares under
-- utf8mb4_0900_ai_ci and returns 0.
-- V2: JSON
SELECT CAST(1 AS JSON) = CAST(1.0 AS JSON); -- 1
SELECT CAST('{"x": 1, "y": 2}' AS JSON) =
CAST('{"y": 2, "x": 1}' AS JSON); -- 1
SELECT JSON_TYPE(JSON_EXTRACT(
JSON_OBJECT('d', DATE'2026-11-01'), '$.d')); -- DATE
-- V3: DECIMAL
SELECT CAST(1.0 AS DECIMAL(5,1)) = CAST(1.00 AS DECIMAL(6,2)); -- 1
-- V4: temporal
SELECT TIME'-00:00:01' < TIME'00:00:01'; -- 1
CREATE TABLE t (d DATETIME);
INSERT INTO t VALUES ('2026-11-01 08:00:00+08:00'),
('2026-11-01 00:00:00+00:00');
SELECT COUNT(DISTINCT d) FROM t; -- 1
2. The services that exist. In include/mysql/components/services/:
- mysql_string.h: mysql_string_compare is the only comparison, and
there is no hash. Its implementation
(sql/server_component/mysql_string_service.cc) calls
sortcmp(str1, str2, str1->charset()).
- No header declares a JSON value, or a compare or hash for
DECIMAL or temporal values.
- bits/mle_time_bits.h declares mle_time, and nothing compares it.
Suggested fix:
Use one value form for each type family, and give each family a
compare and a hash service.
- A value that has a C form is passed by value: int64_t / uint64_t,
double, mle_decimal, mle_time.
- A value that has no C form, or that carries its own semantics, is
an opaque handle: my_h_string (existing) and my_h_json (new).
- compare(a, b, &cmp) returns negative, zero or positive, as SQL
orders the two values.
- hash(v, &hash) mixes the value into a 64-bit seed, so a component
can hash several columns into one key. Values that compare equal
hash alike. The hash is for an in-memory table and may change
between versions, so it must not be stored.
- Two operands that SQL has no single order for are refused with an
error. The service does not choose one.
Integers and doubles need no service: C comparison gives the SQL
order. The documentation states the two exceptions: signed vs.
unsigned, and -0.0 equal to 0.0.
A prototype of 1 to 3 exists on top of the Bug #120978 branch, and is
covered by MTR (test_json_services; json_table_c, which rewrites
JSON_TABLE as a component and checks it against the built-in one).
1. JSON services (new header mysql_json.h)
mysql_json_factory create / destroy / reset (my_h_json)
mysql_json_parse parse(json, text, length, &valid, ...)
mysql_json_value type / length / element / member / find
mysql_json_scalar get_integer / get_unsigned_integer /
get_double / get_boolean / get_string /
get_decimal (mle_decimal) /
get_temporal (mle_time) / get_opaque
mysql_json_compare compare(a, b, &cmp)
mysql_json_hash hash(json, &hash)
mysql_json_path_factory, mysql_json_path_parse, mysql_json_seek
- compare() is Json_wrapper::compare(), the comparison SQL uses for
JSON. hash() is Json_wrapper::make_hash_key().
- A handle keeps its memory when it is filled again, so a component
needs one handle, not one for each row.
- A part of a value (element(), member(), find(), seek()) shares the
storage of the value it is taken from, so a document can be walked
without copies.
- Every failure raises an error before the method returns.
2. String services (added to mysql_string.h)
mysql_string_collation_compare compare(s1, s2, &cmp)
mysql_string_hash hash(s, &hash)
- collation_compare() uses sortcmp() under the collation the two
strings share. If the collations differ, it raises
ER_CANT_AGGREGATE_2COLLATIONS. Trailing spaces count as the
collation says (PAD SPACE or NO PAD).
- hash() uses the collation's hash_sort(), the function hash
indexes and hash joins use. It hashes the weights the collation
compares.
- mysql_string_compare is not changed, so existing components keep
their behavior.
3. Reading and writing in table functions (table_function_registration.h)
- table_function_argument_json: get() copies the value; get_ref()
borrows the record of a JSON column without a copy, for the length
of the callback.
- table_function_field_json: writes a JSON column.
- table_function_argument_decimal / _field_decimal on mle_decimal,
and table_function_argument_temporal / _field_temporal on mle_time.
4. DECIMAL services (proposed)
mysql_decimal_compare compare(const mle_decimal *a,
const mle_decimal *b, int *cmp)
mysql_decimal_hash hash(const mle_decimal *v, uint64_t *hash)
- compare() converts to my_decimal and calls my_decimal_cmp().
- hash() removes trailing fractional zeros first, so 1.0 and 1.00
hash alike.
5. Temporal services (proposed)
mysql_temporal_compare compare(const mle_time *a,
const mle_time *b, int *cmp)
mysql_temporal_hash hash(const mle_time *v, uint64_t *hash)
- A DATETIME_TZ value is converted to the session time zone first,
as the server does when it stores such a value.
- DATE and DATETIME are compared through the packed form
(TIME_to_longlong_packed()), and so are two TIMEs. Comparing a
TIME with a DATE or DATETIME is refused, because SQL makes that
comparison depend on CURRENT_DATE.
- hash() hashes the same packed value.
- my_time_compare() is not used: it ignores neg and time_type.
6. The same value forms in other services (follow-up)
- Add DECIMAL (mle_decimal) and JSON (my_h_json) to the stored
program runtime argument and return value services.
- Allow mle_decimal in mysql_stmt_bind::bind_param().
Also: mysql_string_imp::compare() asserts str1 != nullptr twice and
never checks str2.
Open questions for discussion:
- Temporal values: handle or struct. (A) Keep mle_time and add the
stateless services in 5. mle_time already has the layout of
MYSQL_TIME, and the statement service, JSON scalars and table
functions already exchange it. (B) Add a handle on the server's
MYSQL_TIME / Datetime_val. It hides the layout and can cache the
packed form, but it needs a create and a destroy for each value of
a small struct, and a new getter in every service that returns
mle_time today. The prototype direction is (A).
- A memcmp-comparable key form per type (the form that filesort and
hash join build: strnxfrm(), my_decimal2binary(), packed temporal),
so a component can sort and hash bytes without a call for each
comparison. This would make those formats part of the interface.
- Comparison across families (INTEGER vs. DECIMAL, string vs.
number) is out of scope. The component casts first, as hash join
does.
© 2026, Oracle Corporation and/or its affiliates
Description: A component sometimes has to process values in its own code, not only pass them on. Examples: - A table function that deduplicates, groups, joins or sorts the rows it builds (Bug #120978). - A component that bridges an external engine or file format into SQL, and has to merge or filter values the way the server does. - A stored program in another language that compares its arguments. - A user-defined type built on a built-in type, which needs the compare and hash of that base type. In each case the component must reach the same answer as SQL. If it does not, its GROUP BY, DISTINCT, join or ORDER BY gives a different result from the server on the same data. Today the component services let a component read and write some values, but not compare or hash them with SQL semantics: V1 Strings. mysql_string_compare compares under the collation of the first string only and ignores the collation of the second. So compare(a, b) and compare(b, a) can disagree, and the result is not an order. SQL refuses such a comparison (ER 1267, illegal mix of collations). There is no hash service. A component that builds a hash table cannot hash by collation weights, so strings that SQL finds equal ('abc' and 'ABC ' under utf8mb4_general_ci) fall into different buckets. V2 JSON. There is no JSON value service. A component gets a JSON value only as text (UDF arguments, stored program arguments, statement results). The text loses the typed scalars of the binary form: a DATE, a TIME or a DECIMAL inside a document becomes a string or a double. The component has to parse the text again, and it cannot compare or hash as SQL does: numbers by value whatever their type, object members in no order, values of different types ordered by type. V3 DECIMAL. The only C forms are text (statement service) and, in the table function prototype, mle_decimal (unscaled integer plus scale). 1.0 is {10, scale 1} and 1.00 is {100, scale 2}. SQL finds them equal, but byte equality does not. Each component would have to rescale 256-bit integers itself. V4 Temporal values. mle_time is the ABI copy of MYSQL_TIME, and it is good for reading and writing. But field-by-field comparison is wrong for it: - A negative TIME keeps its sign in neg, not in any part. - A DATETIME with a time zone displacement (MYSQL_TIMESTAMP_TYPE_DATETIME_TZ) can be the same moment as another DATETIME with different parts. - Comparing a TIME with a DATETIME depends on CURRENT_DATE. Even the server helper my_time_compare() (mysys/my_time.cc) ignores neg and time_type: it returns 0 for -00:00:01 and 00:00:01. SQL itself compares the packed form (TIME_to_longlong_packed()). V5 The value forms are not used the same way in all services. The stored program runtime services have no DECIMAL or JSON (only text), and the statement service binds DECIMAL as text. This is a feature request for compare and hash services for each SQL type family, with the semantics the server uses for the same type, and the rule that values that compare equal hash alike. How to repeat: 1. What SQL answers. A component that receives these values through a component service has no service that gives the same answers: -- V1: strings, PAD SPACE and case-insensitive SELECT _utf8mb4'abc' COLLATE utf8mb4_general_ci = _utf8mb4'ABC '; -- 1 SELECT _utf8mb4'abc' COLLATE utf8mb4_bin = _utf8mb4'ABC' COLLATE utf8mb4_0900_ai_ci; -- ERROR 1267 (HY000): Illegal mix of collations -- mysql_string_compare(s1, s2) with s1 = 'abc' (utf8mb4_bin) and -- s2 = 'ABC' (utf8mb4_0900_ai_ci) compares under utf8mb4_bin and -- returns non-zero; with the arguments swapped it compares under -- utf8mb4_0900_ai_ci and returns 0. -- V2: JSON SELECT CAST(1 AS JSON) = CAST(1.0 AS JSON); -- 1 SELECT CAST('{"x": 1, "y": 2}' AS JSON) = CAST('{"y": 2, "x": 1}' AS JSON); -- 1 SELECT JSON_TYPE(JSON_EXTRACT( JSON_OBJECT('d', DATE'2026-11-01'), '$.d')); -- DATE -- V3: DECIMAL SELECT CAST(1.0 AS DECIMAL(5,1)) = CAST(1.00 AS DECIMAL(6,2)); -- 1 -- V4: temporal SELECT TIME'-00:00:01' < TIME'00:00:01'; -- 1 CREATE TABLE t (d DATETIME); INSERT INTO t VALUES ('2026-11-01 08:00:00+08:00'), ('2026-11-01 00:00:00+00:00'); SELECT COUNT(DISTINCT d) FROM t; -- 1 2. The services that exist. In include/mysql/components/services/: - mysql_string.h: mysql_string_compare is the only comparison, and there is no hash. Its implementation (sql/server_component/mysql_string_service.cc) calls sortcmp(str1, str2, str1->charset()). - No header declares a JSON value, or a compare or hash for DECIMAL or temporal values. - bits/mle_time_bits.h declares mle_time, and nothing compares it. Suggested fix: Use one value form for each type family, and give each family a compare and a hash service. - A value that has a C form is passed by value: int64_t / uint64_t, double, mle_decimal, mle_time. - A value that has no C form, or that carries its own semantics, is an opaque handle: my_h_string (existing) and my_h_json (new). - compare(a, b, &cmp) returns negative, zero or positive, as SQL orders the two values. - hash(v, &hash) mixes the value into a 64-bit seed, so a component can hash several columns into one key. Values that compare equal hash alike. The hash is for an in-memory table and may change between versions, so it must not be stored. - Two operands that SQL has no single order for are refused with an error. The service does not choose one. Integers and doubles need no service: C comparison gives the SQL order. The documentation states the two exceptions: signed vs. unsigned, and -0.0 equal to 0.0. A prototype of 1 to 3 exists on top of the Bug #120978 branch, and is covered by MTR (test_json_services; json_table_c, which rewrites JSON_TABLE as a component and checks it against the built-in one). 1. JSON services (new header mysql_json.h) mysql_json_factory create / destroy / reset (my_h_json) mysql_json_parse parse(json, text, length, &valid, ...) mysql_json_value type / length / element / member / find mysql_json_scalar get_integer / get_unsigned_integer / get_double / get_boolean / get_string / get_decimal (mle_decimal) / get_temporal (mle_time) / get_opaque mysql_json_compare compare(a, b, &cmp) mysql_json_hash hash(json, &hash) mysql_json_path_factory, mysql_json_path_parse, mysql_json_seek - compare() is Json_wrapper::compare(), the comparison SQL uses for JSON. hash() is Json_wrapper::make_hash_key(). - A handle keeps its memory when it is filled again, so a component needs one handle, not one for each row. - A part of a value (element(), member(), find(), seek()) shares the storage of the value it is taken from, so a document can be walked without copies. - Every failure raises an error before the method returns. 2. String services (added to mysql_string.h) mysql_string_collation_compare compare(s1, s2, &cmp) mysql_string_hash hash(s, &hash) - collation_compare() uses sortcmp() under the collation the two strings share. If the collations differ, it raises ER_CANT_AGGREGATE_2COLLATIONS. Trailing spaces count as the collation says (PAD SPACE or NO PAD). - hash() uses the collation's hash_sort(), the function hash indexes and hash joins use. It hashes the weights the collation compares. - mysql_string_compare is not changed, so existing components keep their behavior. 3. Reading and writing in table functions (table_function_registration.h) - table_function_argument_json: get() copies the value; get_ref() borrows the record of a JSON column without a copy, for the length of the callback. - table_function_field_json: writes a JSON column. - table_function_argument_decimal / _field_decimal on mle_decimal, and table_function_argument_temporal / _field_temporal on mle_time. 4. DECIMAL services (proposed) mysql_decimal_compare compare(const mle_decimal *a, const mle_decimal *b, int *cmp) mysql_decimal_hash hash(const mle_decimal *v, uint64_t *hash) - compare() converts to my_decimal and calls my_decimal_cmp(). - hash() removes trailing fractional zeros first, so 1.0 and 1.00 hash alike. 5. Temporal services (proposed) mysql_temporal_compare compare(const mle_time *a, const mle_time *b, int *cmp) mysql_temporal_hash hash(const mle_time *v, uint64_t *hash) - A DATETIME_TZ value is converted to the session time zone first, as the server does when it stores such a value. - DATE and DATETIME are compared through the packed form (TIME_to_longlong_packed()), and so are two TIMEs. Comparing a TIME with a DATE or DATETIME is refused, because SQL makes that comparison depend on CURRENT_DATE. - hash() hashes the same packed value. - my_time_compare() is not used: it ignores neg and time_type. 6. The same value forms in other services (follow-up) - Add DECIMAL (mle_decimal) and JSON (my_h_json) to the stored program runtime argument and return value services. - Allow mle_decimal in mysql_stmt_bind::bind_param(). Also: mysql_string_imp::compare() asserts str1 != nullptr twice and never checks str2. Open questions for discussion: - Temporal values: handle or struct. (A) Keep mle_time and add the stateless services in 5. mle_time already has the layout of MYSQL_TIME, and the statement service, JSON scalars and table functions already exchange it. (B) Add a handle on the server's MYSQL_TIME / Datetime_val. It hides the layout and can cache the packed form, but it needs a create and a destroy for each value of a small struct, and a new getter in every service that returns mle_time today. The prototype direction is (A). - A memcmp-comparable key form per type (the form that filesort and hash join build: strnxfrm(), my_decimal2binary(), packed temporal), so a component can sort and hash bytes without a call for each comparison. This would make those formats part of the interface. - Comparison across families (INTEGER vs. DECIMAL, string vs. number) is out of scope. The component casts first, as hash join does.