Nullability is decided in two stages-
1. Based on argument NULL-ness
Problem:
- COALESCE currently uses a generic logic- "Result of a function
is nullable if any of the arguments is nullable", which is wrong.
- IFNULL sets nullability using second argument alone, which incorrectly
sets the result to NULL even when first argument is not null.
Fix:
- Result of COALESCE and IFNULL is set to NULL only if all arguments are
NULL.
2. Based on type conversion safety of fallback value
Problem:
- The generic `Item_hybrid_func_fix_attributes` logic would mark the
function's result as nullable if any argument involved a type
conversion that could yield NULL.
Fix:
- For COALESCE and IFNULL, nullability is set to NOT NULL if the first
non-null argument can be safely converted to function's target return
type.
- For other functions, if any argument's conversion to target type could
result in NULL, the function is marked nullable.
Tests included in `mysql-test/main/func_hybrid_type.test`
UUID::cmp() correctly compared:
- two swapped v1 UUIDs
- two non-swapped v6 UIDs
but v1 vs v6 were not compared correctly.
Adding a new method cmp_swap_noswap() and using
it in UUID::cmp() to compare two value of different swapness.
Problem:
REPAIR TABLE executed for a pre-MDEV-29959 table (with the old UUID format)
updated the server version in the FRM file without rewriting the data,
so it created a new FRM for old UUIDs. After that MariaDB could not
read UUIDs correctly.
Fix:
- Adding a new virtual method in class Type_handler:
virtual bool type_handler_for_implicit_upgrade() const;
* For the up-to-date data types it returns "this".
* For the data types which need to be implicitly upgraded
during REPAIR TABLE or ALTER TABLE, it returns a pointer
to a new replacement data type handler.
Old VARCHAR and old UUID type handlers override this method.
See more comments below.
- Changing the semantics of the method
Type_handler::Column_definition_implicit_upgrade(Column_definition *c)
to the opposite, so now:
* c->type_handler() references the old data type (to upgrade from)
* "this" references the new data type (to upgrade to).
Before this change Column_definition_implicit_upgrade() was supposed
to be called with the old data type handler (to upgrade from).
Renaming the method to Column_definition_implicit_upgrade_to_this(),
to avoid automatic merges in this method.
Reflecting this change in Create_field::upgrade_data_types().
- Replacing the hard-coded data type tests inside handler::check_old_types()
to a call for the new virtual method
Type_handler::type_handler_for_implicit_upgrade()
- Overriding Type_handler_fbt::type_handler_for_implicit_upgrade()
to call a new method FbtImpl::type_handler_for_implicit_upgrade().
Reasoning:
Type_handler_fbt is a template, so it has access only to "this".
So in case of UUID data types, the type handler for old UUID
knows nothing about the type handler of new UUID inside sql_type_fixedbin.h.
So let's have Type_handler_fbt delegate type_handler_for_implicit_upgrade()
to its Type_collection, which knows both new UUID and old UUID.
- Adding Type_collection_uuid::type_handler_for_implicit_upgrade().
It returns a pointer to the new UUID type handler.
- Overriding Type_handler_var_string::type_handler_for_implicit_upgrade()
to return a pointer to type_handler_varchar (true VARCHAR).
- Cleanup: these two methods:
handler::check_old_types()
handler::ha_check_for_upgrade()
were always called consequently.
So moving the call for check_old_types() inside ha_check_for_upgrade(),
and making check_old_types() private.
- Cleanup: removing the "bool varchar" parameter from fill_alter_inplace_info(),
as its not used any more.
According to the standart draft UUIDv6 and UUIDv7 values
must be compared as opaque raw bytes.
Let's only compare with byte-swapping if both values need
byte swapping.
* modify the test to use different and not monotonous timestamps
* rename methods to be unambiguous (for IDE challenged devs)
* move byte swap checks into helpers
The problem was earlier fixed by a patch for MDEV-27207
68403eeda3
and an additional cleanup patch for MDEV-27207
88dd50b80a
The above patches added MTR tests for INET6.
Now adding UUID specific MTR tests only.
on aarch64 `char` by default is unsigned for performance reasons.
let's adjust checks to work for both signed and unsigned `char`
followup for ef84f8137b
optimizer implicitly assumed that if `a` in `a=b` is not NULL,
then it's safe to convert `a` to the type of `b` and search the
result in the index(b).
which is not always the case, as converting a non-null value
to a different type might produce NULL. And searching for NULL
in the index might find NULL there, so NULL will be equal to NULL,
making `a=b` behave as if it was `a<=>b`
* UUIDs version >= 6 are now stored without byte-swapping
* UUIDs with version >=8 and variant=0 are now considered invalid
* old tables are supported
* old (always byte swapped) and new (swapped for version < 6) UUIDs
can be compared and converted transparently
1. Store assignment failures on incompatible data types now raise errors if:
- STRICT_ALL_TABLES or STRICT_TRANS_TABLES sql_mode is used, and
- IGNORE is not used
Otherwise, only a warning is raised and the statement continues.
2. Changing the error/warning test as follows:
-ERROR HY000: Illegal parameter data types inet6 and int for operation 'SET'
+ERROR HY000: Cannot cast 'int' as 'inet6' in assignment of `db`.`t`.`col`
so in case of a big table it's easier to see which column has the problem.
The new error text is also applied to SP variables.
The assuption that Field::is_null() is always false when
Field_fbt::val_native() or Field_fbt::to_fbt() are called
was wrong.
In some cases, e.g. when this helper Field method is called:
inline String *val_str(String *str, const uchar *new_ptr)
we temporarily reset Field::ptr to some alternative record buffer
but don't reset null_ptr, so null_ptr still points to null flags
of the original record. In such cases it's meaningless to test
the original Field::null_ptr when Field::ptr is temporarily reset:
they don't relate to each other.
Removing the DBUG_ASSERT.
Now INSERT, UPDATE, ALTER statements involving incompatible data type pairs, e.g.:
UPDATE TABLE t1 SET col_inet6=col_int;
INSERT INTO t1 (col_inet6) SELECT col_in FROM t2;
ALTER TABLE t1 MODIFY col_inet6 INT;
consistently return an error at the statement preparation time:
ERROR HY000: Illegal parameter data types inet6 and int for operation 'SET'
and abort the statement before starting interating rows.
This error is the same with what is raised for queries like:
SELECT col_inet6 FROM t1 UNION SELECT col_int FROM t2;
SELECT COALESCE(col_inet6, col_int) FROM t1;
Before this change the error was caught only during the execution time,
when a Field_xxx::store_xxx() was called for the very firts row.
The behavior was not consistent between various statements and could do different things:
- abort the statement
- set a column to the data type default value (e.g. '::' for INET6)
- set a column to NULL
A typical old error was:
ERROR 22007: Incorrect inet6 value: '1' for column `test`.`t1`.`a` at row 1
EXCEPTION:
Note, there is an exception: a multi-row INSERT..VALUES, e.g.:
INSERT INTO t1 (col_a,col_b) VALUES (a1,b1),(a2,b2);
checks assignment compability at the preparation time for the very first row only:
(col_a,col_b) vs (a1,b1)
Other rows are still checked at the execution time and return the old warnings
or errors in case of a failure. This is done because catching all rows at the
preparation time would change behavior significantly. So it still works
according to the STRICT_XXX_TABLES sql_mode flags and the table transaction ability.
This is too late to change this behavior in 10.7.
There is no a firm decision yet if a multi-row INSERT..VALUES
behavior will change in later versions.
UUID values
llllllll-mmmm-Vhhh-vsss-nnnnnnnnnnnn
are now stored as
nnnnnnnnnnnn-vsss-Vhhh-mmmm-llllllll
inside the record:
- the groups (segments separated by dash) are reordered right-to-left.
- the bytes inside the groups are not reordered (stored as before,
in big-endian format).
This provides a better sorting order: the earlier UUID was generated,
the higher it appears in the ORDER BY output.
Also, this change enables a good key prefix compression,
because the constant part is now in the beginning, while
the non-constant part (the timestamp) is in the end.
- Adding a new template FixedBinTypeStorage.
- Restoring classes UUID and Inet6
as primitive "storage classes" for their data types.
They derive from FixedBinTypeStorage.
These storage classes have very few server dependencies so they
can later be easily reused in smart engines, e.g. ColumnStore.
- Changing the FixedBinTypeBundle parameter from
<size_t NATIVE_LEN, size_t MAX_CHAR_LEN> to <class FbtImpl>
and fixing UUID and INET6 bundles to get their storage
classes as a parameter.
