This merges the patch series that fixes a lot of missing validation
checks for read_log_event(). The merge includes a few added fixes that
are needed in new code introduced in 12.3.
Signed-off-by: Kristian Nielsen <knielsen@knielsen-hq.org>
This SQL script failed:
SET NAMES latin1 COLLATE latin1_swedish_ci;
CREATE OR REPLACE VIEW v1 AS SELECT 'Y' AS c1;
SET NAMES big5 COLLATE big5_chinese_ci;
SELECT * FROM v1 WHERE c1='y';
with the following error:
ERROR 1267 (HY000): Illegal mix of collations
(latin1_swedish_ci,COERCIBLE) and (big5_chinese_ci,COERCIBLE)
for operation '='
Note, latin1_swedish_ci and big5_chinese_ci are used here as examples.
The error also happened with different collation combinations.
Fix main idea:
If two collations have equal comparison rules (known as "tailoring")
on a given character repertoire,
like latin1_swedish_ci and big5_chinese_ci on ASCII letters,
then the "Illegal mix of collations" error can be avoided in
a comparison operator. We can choose any of the sides as the operation
effective collation - the result will be equal.
This optimization is not applied when at least one side has an explicit
COLLATE clause. Two explicit COLLATE clauses in one comparison are
already illegal when the character sets are the same, so for
consistency this stays illegal when the character sets differ too.
Most important details:
- Splitting enum_repertoire_t into smaller subsets,
for better repertoire granularity.
A variable holding a repertoire value can now have multiple
MY_REPERTOIRE_XXX flags set.
This patch implements detecting tailoring equality
on these repertoires:
* MY_REPERTOIRE_ASCII_DIGITS - [0..9]
* MY_REPERTOIRE_ASCII_MINUS_DOT_DIGITS - [-.0..9], e.g. '-10.5'.
It consists of the new bits MY_REPERTOIRE_ASCII_MINUS and
MY_REPERTOIRE_ASCII_DOT. They were a part of
MY_REPERTOIRE_ASCII_NOT_IDENT before. As a result, the numeric values
of MY_REPERTOIRE_ASCII and MY_REPERTOIRE_UNICODE30 changed
(223 and 255).
* MY_REPERTOIRE_ASCII_ALNUM - [A..Z,a..z,0..9].
* MY_REPERTOIRE_ASCII_IDENT - ALNUM + underscore
* MY_REPERTOIRE_ASCII - the entire range U+0000..U+007F
- Adding a new virtual function "tailoring" in my_collation_handler_st.
It returns the tailoring on the given repertoire for the given
collation.
If cs1->coll->tailoring(cs1, some_repertoire) returns {0,0},
it means the illegal mix optimization cannot be used
for this collation on the given repertoire.
If these calls:
tr1= cs1->coll->tailoring(cs1, some_repertoire);
tr2= cs2->coll->tailoring(cs2, some_repertoire);
return both non-NULL results and tr1.str==tr2.str,
then these collations are equal on the given repertoire
and are mutually replaceable for a comparison operator,
so "Illegal mix of collations" can be avoided.
- Tailoring strings are shared constants my_tailoring_str_*
(strings/strings_def.h). Tailorings are compared by the string
pointer, so tailoring() implementations return strings only from
these constants. The strings do not depend on PAD/NOPAD.
- my_string_repertoire() for ucs2, utf16, utf32 and NONASCII 8bit
character sets no longer stops at the first bad byte sequence with
the repertoire found so far, which could be MY_REPERTOIRE_NONE
(compatible with everything). It returns MY_REPERTOIRE_EXTENDED
for such strings. A unit test was added into unittest/strings.
- Adding CHARSET_INFO::is_ascii_superset(). It tells if a character set
can store all ASCII characters (it is false for swe7).
It is used in Item_func_conv_charset to calculate "safe", and in
left_is_algorithmically_simpler() to prefer other character sets
to those where ASCII strings cannot be converted safely.
- DBUG_ASSERTs were added into mysys/charset.c, to check that
the "tailoring" method is set in collation handlers when a collation
is added: compiled-in, from ctype-extra.c, or loaded from Index.xml.
- Adding a new function my_collations_equal_on_repertoire(cs1, cs2,
repertoire) in strings/. It tells if two collations are equal on
the repertoire, taking tailoring() results and PAD/NOPAD and the UCA
version into account. Callers do not compare tailoring() results
directly, so the way tailorings are compared (currently by the pointer
to the shared constant string) is private to strings/ and can be
extended later, e.g. for UCA collations on MY_REPERTOIRE_UNICODE30.
- Adding a new method DTCollation::aggregate_by_tailoring().
Collations with different MY_CS_NOPAD are never compatible.
UCA collations of different versions are not compatible on
the repertoire with ASCII punctuation, as the UCA version
affects its order (e.g. UCA-6.2.0 moved GRAVE ACCENT
and CIRCUMFLEX ACCENT after PERCENT SIGN).
- DTCollation::aggregate() now makes the repertoire of the result cover
the repertoires of both sides in all successful branches.
Before, set(dt) replaced the repertoire with the repertoire of the
winner side only, so the result could have a too narrow repertoire,
e.g. IF(1, 1.5, HEX(255)) had MY_REPERTOIRE_ASCII_ALNUM although the
value '1.5' has a punctuation character. With the more granular
repertoires this could wrongly allow mixing collations which are
equal on ALNUM but different on punctuation.
- Adding a new flag MY_COLL_ALLOW_BY_TAILORING.
It indicates to DTCollation::aggregate() that the illegal
mix optimization by repertoire can be used in the given context.
MY_COLL_CMP_CONV now includes MY_COLL_ALLOW_BY_TAILORING.
Note, only comparison operators pass this flag.
Functions returning a string result do not pass this flag,
because in operations like CONCAT(a,b) we still need to evaluate
precisely the collation of the result - we cannot just choose
a collation of one of the sides (even if they are compatible
on the given repertoire). This also applies to ExtractValue()
and UpdateXML(), which now aggregate their arguments without
this flag.
- As in my_repertoire_t the value MY_REPERTOIRE_ASCII is now a set of
bits rather than a single bit, the way to detect "is only ASCII"
repertoires has changed in the code.
For example:
// repertoire *IS* ascii
if (repertoire == MY_REPERTOIRE_ASCII)
has changed in multiple places in the code to
// repertoire *HAS* only ascii characters
if (my_repertoire_is_subset_of(repertoire, MY_REPERTOIRE_ASCII))
The new function my_repertoire_is_subset_of() in m_ctype.h
is used for this purpose in C code. In C++ code
DTCollation::repertoire_is_subset_of() is used, e.g.:
if (collation.repertoire_is_subset_of(MY_REPERTOIRE_ASCII))
- Repertoire of some expressions was adjusted to the new meaning:
* Item_null now has MY_REPERTOIRE_NONE (was ASCII).
* MY_LOCALE::repertoire() now returns MY_REPERTOIRE_UNICODE30
(was EXTENDED).
* Lex_string_with_metadata_st::repertoire(cs) now scans the string
contents to detect the actual repertoire.
* HEX() now has MY_REPERTOIRE_ASCII_ALNUM.
* DATE_FORMAT() decides on MY_REPERTOIRE_EXTENDED using the locale
which is actually used: the explicit third argument, or
@@lc_time_names. It was always @@lc_time_names before.
A non-constant locale argument is assumed to be non-ASCII.
* QUOTE, MAKE_SET, EXPORT_SET, LPAD, RPAD, GROUP_CONCAT, JSON_ARRAY,
JSON_OBJECT and JSON_OBJECTAGG add the repertoire of the extra
characters they put into the result (quotes, separators, padding,
brackets).
* LOWER() and UPPER() add the letters of both cases to the repertoire,
because the result can contain letters of the opposite case.
In Turkish collations they also add MY_REPERTOIRE_EXTENDED, as an
ASCII letter can be converted to a non-ASCII one (I -> dotless i).
Unicode collations are detected by the new member
casefold_info_st::can_convert_to_non_ascii_on_casefolding,
simple 8bit collations by to_lower['I'] and to_upper['i'].
- Lex_string_with_metadata_st::repertoire(cs) now scans a string literal
for any byte oriented character set (mbminlen==1), not only for
ASCII based ones. It was assumed to have the full repertoire for swe7,
which is not ASCII based. The scan is done by Unicode code points, so
swe7 strings with digits and letters get a narrow repertoire, while
e.g. 0x5B (LATIN CAPITAL LETTER A WITH DIAERESIS in swe7) is non-ASCII.
Therefore swe7_swedish_ci can be mixed with other collations now,
in the same way as other collations with MY_CS_IDENT_CASEUP_CI.
The test func_time was adjusted: a swe7 literal ' ull' with ASCII
characters is now safe to convert, the error is still tested for '[ull'.
- cp1250_czech_cs sorts -, . and 0..9 in the code point order, so it gets
the MINUS_DOT_DIGITS tailoring. latin2_czech_cs gets only DIGITS,
as it sorts the minus and the dot differently. Both were verified
with ctype_digits_verify.
- The tis620 collations now set CHARSET_INFO::tab_to_uni (was NULL),
so my_charset_is_ascii_based() is true for tis620.
It was false for tis620, although tis620 is ASCII compatible.
Therefore tis620 string literals were always considered to have
a non-ASCII repertoire, and the repertoire optimizations did not work
for them. Now ASCII-only tis620 literals are detected as ASCII.
This changes the result of subselect_extra_no_semijoin
from an error to success.
- tis620_thai_ci and tis620_thai_nopad_ci fold letters to lower case
and then compare by the code point on the entire ASCII range
(the Thai specific rules affect only non-ASCII characters).
A new function my_tailoring_ascii_casedn_ci() returns tailorings
for such collations on ALNUM, IDENT and ASCII. For example, the
underscore sorts before the letters, unlike in the caseup tailorings.
- New flags were added for CHARSET_INFO::state
* MY_CS_ASCII_CASEUP_CI - for simple 8bit case insensitive collations.
It means that this collation has no irregularities on the ASCII
repertoire, and converts lower case letters to upper case ones,
so the underscore sorts after the letters (with an upper to lower
case conversion it would sort before the letters).
* MY_CS_IDENT_CASEUP_CI - for simple 8bit case insensitive collations.
It means that this collation has no irregularities on the IDENT
repertoire (but can have irregularities say on punctuation).
* MY_CS_ASCII_STD_UCA - for UCA collations.
It means that a UCA collation does not reorder ASCII characters.
* MY_CS_DIGITS_STD - for any collation. It means that the ASCII digits
0..9 are sorted in the code point order and are not tailored, and,
for PAD collations, the space sorts before the digits. Strings of
digits are compared in the same way in all such collations, even if
they have irregularities on letters (latin7, cp866, czech, danish,
turkish UCA, etc.), or differ by case sensitivity. It is detected
from sort_order for 8bit collations, and from the tailoring rules
for UCA collations. Collations with MY_CS_DIGITS_STD still must have
the same PAD/NOPAD attribute.
* MY_CS_ASCII_MINUS_DOT_DIGITS - for any collation. Means
MY_CS_DIGITS_STD, and additionally the minus sorts before the dot,
which sorts before the digits (and the space sorts before the minus
in PAD collations). Strings like '-10.5' are compared in the same
way in all such collations. It is detected from sort_order for simple
8bit collations (my_coll_init_simple()), and from the tailoring rules
for UCA collations. For the multi-byte collations big5_chinese_ci,
gbk_chinese_ci, gb2312_chinese_ci and sjis_japanese_ci it is declared
by my_tailoring_ident_caseup_ci_generic(). The flags MY_CS_DIGITS_STD
and MY_CS_ASCII_MINUS_DOT_DIGITS are stored in ctype-extra.c for
compiled collations (conf_to_src.c prints them), and the detection is
skipped for a collation which already has any of the repertoire flags
(MY_CS_REPERTOIRE_FLAGS).
- strings/conf_to_src.c was modified to detect and print
MY_CS_ASCII_CASEUP_CI and MY_CS_IDENT_CASEUP_CI flags.
- strings/ctype-extra.c was regenerated with new flags.
- 8bit collations are not given MY_CS_ASCII_CASEUP_CI and
MY_CS_IDENT_CASEUP_CI if they are PAD and the space does not sort
before the digits. A shorter string is padded with spaces for
comparison, so the position of the space matters even for strings
without spaces ("ab" is compared to "abc" as "ab " to "abc").
A test collation latin1_space_after_letters_ci was added to
mysql-test/std_data/ldml/ for this. ctype-extra.c does not change.
- The initializer of my_collation_cs_handler in ctype-utf8.c
(utf8mb3_general_cs, under HAVE_UTF8_GENERAL_CS) was fixed to match
my_collation_handler_st: the missing get_id and get_collation_name
members were added, so that eq_collation was not initialized in their
place, and the new member "tailoring" is now set to my_tailoring_none.
- Results of the existing tests func_str, func_test, ps, view
and subselect_sj changed from "Illegal mix of collations" to success.
In these tests ASCII-only strings were compared using collations
with equal tailoring on ASCII, e.g. latin1_swedish_ci with
latin2_general_ci (func_str, ps), koi8r_general_ci with
latin1_swedish_ci (func_test), latin1_general_ci with
latin1_swedish_ci on letters and digits (view), cp932_japanese_ci
with latin1_swedish_ci (subselect_sj).
Such comparisons are now allowed.
Cases with collations that are still incompatible on ASCII,
e.g. latin7_general_ci, were added to the tests and still fail.
- Adding a number of MTR tests in
plugin/func_test/mysql-test/func_test/.
They display a tailoring by collation name and repertoire as
returned by:
cs->coll->tailoring(cs, some_repertoire)
A dynamically linked plugin function collation_tailoring() was added
for the purpose of these tests. Tailorings do not depend on PAD/NOPAD
and on the UCA version, so the function shows "[nopad]" for NOPAD
collations, and "[version X.Y.Z]" for UCA collations on the ASCII
repertoire. The version is formatted by a new class UCAVersion,
which uses a new method CharBuffer::append_uint8().
- Adding the test plugin/func_test/mysql-test/func_test/
ctype_caseup_ci_verify.test. It checks the order of ASCII characters
for all collations declared "caseup CI" on the ASCII or IDENT range.
- Adding the test plugin/func_test/mysql-test/func_test/
collation_tailoring_args.test. It checks the arguments of the plugin
function collation_tailoring(): the repertoire passed by name or by
number, and the NULL results for unknown names.
- Adding the test plugin/func_test/mysql-test/func_test/ctype_ldml.test.
It displays the tailorings of collations loaded at runtime from
Index.xml: simple 8bit collations (flags detected from the weights)
and Unicode collations (LDML rules which do or do not reorder ASCII).
- Adding a number of MTR tests mysql-test/main/ctype_xxx_tailoring.test
They display two-dimensional charts showing which collations
are compatible on which repertoires.
Tests for DATE_FORMAT() with an explicit locale were added to
ctype_big5_tailoring.test, ctype_cp932_tailoring.test and
ctype_latin5_tailoring.test, which also has the chart for latin5.
- Adding a number of MTR tests in the form of the originally
reported script for various collations:
SELECT Insert_priv FROM mysql.user WHERE Insert_priv='...';
The repeated blocks of queries are shared through
mysql-test/include/
ctype_tailoring_0{1,2,3}_mysql_user_Insert_priv*.inc.
- Adding tests to ctype_cp932.test checking that ExtractValue() and
UpdateXML() still raise "Illegal mix of collations".
Problem: SUM(DISTINCT b'1100') returns 9 instead of 12. The hybrid
hex/bit literal handler uses the default DOUBLE temporary field, so
its byte length becomes a DOUBLE(M,0) display width and clips the
numeric value.
Fix: Override numeric DISTINCT field construction for hybrid hex and
bit literals to allocate an unsigned Field_longlong directly. Preserve
argument-based field selection for other types so DISTINCT retains the
input expression's precision and scale, including decimal AVG inputs.
Extend main.sum_distinct to cover bit and hex literals, zero, byte and
unsigned 64-bit boundaries, duplicates, AVG, COUNT and decimal scale.
Recreated-by: Georgi "Joro" Kodinov <joro@mariadb.org>
A 0xHHHH / b'..' literal keeps its hex_hybrid type through COALESCE/IF/CASE/...,
but was evaluated, sized and stored as a string, so it behaved differently from
a bare literal (10+COALESCE(0x31) gave 11, not 59).
Type_handler_hex_hybrid now implements the numeric side to match the bare
literal: val_int/val_real/val_decimal read bytes as a hybrid, Item_decimal_precision
returns the integer precision, Item_save_in_field stores like the bare literal.
The traditional type merge keeps the hybrid through a typeless NULL (as for BIT),
and Item_hybrid_func_fix_attributes attributes it binary/unsigned.
When setup_copy_fields() needs to copy a non-aggregate function value,
it doesn't construct an Item_copy directly. Instead, it calls
Type_handler::create_item_copy, which is a kind of factory. The base
Type_handler::create_item_copy returns Item_copy_string. Some type
handlers override it, like timestamp and fixed binary. However, the
numeric type handlers (e.g., float, double, int, decimal) did not, so
they fell through to that base and got Item_copy_string.
A SELECT that aggregates will copy each non aggregate function value
into a temporary table through an Item_copy object, whose concrete
type is chosen by the create_item_copy method on the value's type
handler. For numeric types that method returned Item_copy_string,
which stores the value as text. A FLOAT keeps only FLT_DIG
significant digits as text, too few to reproduce its 24 bit mantissa,
so the copied value differed from the original. With one row per
group, CAST(c1 AS FLOAT) - MIN(CAST(c1 AS FLOAT)) returned a large
number instead of zero.
Add Item_copy_real with Item_copy_float and Item_copy_double variants
that keep the value as a double, the same way Item_cache_real does, and
let the float and double type handlers create them. This mirrors the
existing copy items for timestamp and fixed binary types.
New optimizations were introduced that substitute virtual column
expressions (abbr. vcol expr) with index virtual columns (abbr. vcol
field) in ORDER BY and GROUP BY.
In this patch we introduce checks that the type of a vcol field is the
supertype to its vcol expr, and if not, do not proceed the
optimization. This ensures that the substitution is safe and does not
for example lose information due to truncation.
For simplicity, the version of the check implemented is strict with
100% precision, meaning that there are instances where vcol field is a
supertype to vcol expr, but the check returns false.
Type not covered in tests: Type_handler_null
Thanks to Jarir Khan <kjarir23@gmail.com> for initial patches
addressing this issue.
Type_handler_int_result::Item_decimal_precision assumes that
max_length includes space for a sign, but Item_int literals don't
include such a space. When aggregate_attributes_int propagates these
into functions via a Item_type_holder, the formula computes
precision-1 (or 0 for 1-digit cases), producing the incorrect
calculation with AVG.
This fix changes aggregate_attributes_int to follow the convention the
Type_handler expects, that max_length = max digit-count + (signed ? 1
: 0). This matches the TODO comment that begins with "TODO: rewrite
aggregate_attributes_int()... " which suggested this idea.
Year::year_precision(): Make static, so that VYear::VYear
and VYear_op::VYear_op() will avoid invoking this function with
an unnecessary "this" pointer to an uninitialized object.
Fixing the return type of Type_handler::Item_save_in_value() from
bool to void.
Adding a new method st_value::is_null().
This makes the code less confusing.
PARTITION BY [LINEAR] KEY ALGORITHM={MYSQL51|MYSQL55|BASE31|CRC32C|XXH32|XXH3}
- The MYSQL5X algorithms are the existing algorithms, with MYSQL55
being the default
- The BASE31 algorithm uses a base-31 representation of the bytes, see
Modular hashing in https://algs4.cs.princeton.edu/34hash/. It serves
as a simple baseline that distributes better than the old mysql5x
algorithms
- CRC32C uses my_crc32c.
- XXH32 and XXH3 are xxhash algorithms - xxhash.h copied from latest
release (0.8.3) of https://github.com/Cyan4973/xxHash
For performance (esp. xxh) we use one-shot hash functions in binary
hash_sort, and streaming hash function otherwise for byte-by-byte
hashing. XXH is the only stateful hash function. The other hash
algorithms are stateless and homomorphic, so streaming and one-shot
functions are identical.
Also updated the columnstore hash due to the change of MY_HASH_ADD
signature.
The following testing commands were run at an earlier version of the
patch with the following patch that changes the default algorithm from
MYSQL55 to CRC32C, and XXH32 (changing the patch accordingly)
mtr --suite main --do-test=.*partition
mtr --suite parts
modified sql/ha_partition.cc
@@ -10336,6 +10336,8 @@ uint32 ha_partition::calculate_key_hash_value(Field **field_array)
switch ((*field_array)->table->part_info->key_algorithm)
{
case partition_info::KEY_ALGORITHM_NONE:
+ hasher.set_algorithm(HASH_ALGORITHM_CRC32C);
+ break;
case partition_info::KEY_ALGORITHM_55:
/* Hasher default to mysql55 */
break;
modified sql/partition_info.cc
@@ -2328,7 +2328,7 @@ bool partition_info::fix_parser_data(THD *thd)
if ((thd_sql_command(thd) == SQLCOM_CREATE_TABLE ||
thd_sql_command(thd) == SQLCOM_ALTER_TABLE) &&
key_algorithm == KEY_ALGORITHM_NONE)
- key_algorithm= KEY_ALGORITHM_55;
+ key_algorithm= PARTITION_INFO_DEFAULT_ALGORITHM;
}
DBUG_RETURN(FALSE);
}
@@ -2344,7 +2344,7 @@ bool partition_info::fix_parser_data(THD *thd)
if ((thd_sql_command(thd) == SQLCOM_CREATE_TABLE ||
thd_sql_command(thd) == SQLCOM_ALTER_TABLE) &&
key_algorithm == KEY_ALGORITHM_NONE)
- key_algorithm= KEY_ALGORITHM_55;
+ key_algorithm= PARTITION_INFO_DEFAULT_ALGORITHM;
}
defined_max_value= FALSE; // in case it already set (CREATE TABLE LIKE)
do
modified sql/partition_info.h
@@ -446,6 +446,8 @@ class partition_info : public DDL_LOG_STATE, public Sql_alloc
int gen_part_type(THD *thd, String *str) const;
};
+#define PARTITION_INFO_DEFAULT_ALGORITHM partition_info::KEY_ALGORITHM_CRC32C
+
void part_type_error(THD *thd, partition_info *work_part_info,
const char *part_type, partition_info *tab_part_info);
modified sql/sql_partition.cc
@@ -2471,7 +2471,7 @@ static int add_key_with_algorithm(String *str, const partition_info *part_info)
err+= str->append(STRING_WITH_LEN("KEY "));
if (part_info->key_algorithm != partition_info::KEY_ALGORITHM_NONE &&
- part_info->key_algorithm != partition_info::KEY_ALGORITHM_55)
+ part_info->key_algorithm != PARTITION_INFO_DEFAULT_ALGORITHM)
{
err+= str->append(STRING_WITH_LEN("ALGORITHM = "));
switch (part_info->key_algorithm)
@@ -2479,6 +2479,9 @@ static int add_key_with_algorithm(String *str, const partition_info *part_info)
case partition_info::KEY_ALGORITHM_51:
err+= str->append(STRING_WITH_LEN("MYSQL51"));
break;
+ case partition_info::KEY_ALGORITHM_55:
+ err+= str->append(STRING_WITH_LEN("MYSQL55"));
+ break;
case partition_info::KEY_ALGORITHM_BASE31:
err+= str->append(STRING_WITH_LEN("BASE31"));
break;
Item_singerow_subselect::fix_length_and_dec() incorrectly calculated
its Item::max_length when the underlying expression was Item_int.
The reason of the problem:
Item_int has an optimized max_length to make CONCAT(1) create a VARCHAR(1)
column rather than a VARCHAR(2) column. Its max_length does not include one
extra character for the sign in case the value is positive but the value
is not marked as Item::unsigned==true.
So copying max_length from the underlying Item_int (with value==9)
in cases like this:
SELECT CONCAT((SELECT 9 FROM t0));
was not correct.
Implementing a new virtual method
Type_handler::Item_type_std_attributes_generic(const Item *item)
- The default implementation just copies attributes from "item" as is.
- In case of Type_handler_int_result it evaluates max_length
using item->decimal_precision() rather than item->max_length.
This works correctly for both "optimized" items like Item_int and
non-"optimized" Items whose max_length includes +1 for the sign
in case of signed expressions.
all params are initialized to use Type_handler_null.
NULL params do not have a value to read from the packet buffer,
so their Item_param_set_param_func() should do nothing.
Type_handler_null inherits from Type_handler_string_result,
that is, Type_handler_null *must* override parent's
Item_param_set_param_func().
This patch implements an Oracle style function to_number()
with the following signatures:
- to_number(number_or_string_subject)
- to_number(string_subject, string_format)
The function is implemented in sql/item_numconvfunc.cc.
The function returns the DOUBLE data type for
all signatures and input data types.
The format parser understands the following components:
- Digits: 0, 9
- Hex digits: X
- Group separators: comma (,) and G
- Decimal delimiers: period (.) and D
- Approximate number signature: EEEE
- Currency/numeric flags: $ and B
- Currency signatures: C, L, U
- Sign signatures: S, MI, PR
- Special format signatures: V, TM, TM9, TME
- Format flag: FM
Note, the parser was implemented assuming that we'll also have the
oppostite function to_char() soon for numeric input.
So it parser all known components.
However, the function to_number() does not support:
- Formats V, TM, TM9, TME. to_number() returns NULL if the format
string has these components.
These componens are supported only by to_char() in Oracle.
Features not inclided into this patch:
- The ON CONVERSION ERROR clause
- The third parameter (nlsparam)
- Internationalized components: G, D, C, L, U.
These features will be implemented later, under terms of MDEV-36978.
Notable changes in the other files:
- item_func.h: adding Item_handled_func::Handler_double
- simple_parser.h: adding a number of *CONTAINER* templates
They help to save on duplicate code when creating classes
suitable for passing into parsing templates such as OPT, OR2C, OR3C, etc
- simple_parser.h: Adding parsing templates OR4C and OR5C
- simple_parser.h: Moving the template "OPT" towars the beginning of the file
Rule parsing templates TOKEN, TokenChoice, AND2, OR2C, OR3C, OR4C, OR5C, LIST
now provide a sub-class Opt, to parse its optional rule.
- simple_parser.h: Adding "explicit" to all "operator bool" definitions
- Renaming Parser_templates::TOKEN to Parser_templates::TokenParser
- Adding "explicit" to all "operator bool()" templates/classes,
to avoid hidden implicit conversion (to int, void*, etc).
- Renaming the LIST template parameter ELEMENT to ELEMENT_PARSER,
to make it clearer what it is for.
- Renaming the OPT template parameter RULE to RULE_PARSER,
to make it clearer what it is for.
It returns the DATETIME data type (which is the closest to Oracle's DATE).
The following Oracle formats are supported:
Truncate to day: DD, DDD,J
Truncate to month: MM,MON,MONTH,RM
Truncate to Year: SYEAR,SYYYY,Y,YEAR,YY,YYY,YYYY
TRUNC(date) is same as TRUNC(date, "DD")
This patch incorporates a fix for:
MDEV-37414 SIGSEGV in Binary_string::c_ptr | Item_func_trunc::get_date
Fixing the problem that the code did not take into
account that args[1] can return SQL NULL.
- Removed duplicate words, like "the the" and "to to"
- Removed duplicate lines (one double sort line found in mysql.cc)
- Fixed some typos found while searching for duplicate words.
Command used to find duplicate words:
egrep -rI "\s([a-zA-Z]+)\s+\1\s" | grep -v param
Thanks to Artjoms Rimdjonoks for the command and pointing out the
spelling errors.
In commit fa7d4abf16
when the 16-bit type alias decimal_digits_t was introduced,
some incompatible use of the 32-bit uint data type was overlooked.
Only for converting Item_result_field::max_length (uint), we will use
explicit conversion.
Fixing a confusing error message:
Unknown column 'assoc_array_var' in 'unknown_method'
to a clearer:
FUNCTION/PROCEDURE assoc_array_var.unknown_method does not exist
- Checking that the key expression is compatible with the INDEX BY data type
for assignment in expressions:
assoc_array_variable(key_expr)
assoc_array_variable(key_expr).field
in all contexts: SELECT, assignment target, INTO target.
Raising an error in case it's not compatible.
- Disallowing non-constant expressions as a key,
as the key is evaluated during the fix_fields() time.
- Disallowing stored functions as a key:
assoc_array(stored_function())
assoc_array(stored_function()).field
The underlying MariaDB code is not ready to call a stored function
during the fix_fields() time. This will be fixed in a separate MDEV.
- Removing the move Assoc_array_data's constructor.
Using the usual constructor instead.
- Setting m_key.thread_specific and m_value.thread_specific to true
in the Assoc_array_data constructor. This is needed to get assoc array
element data counted by the @@session.memory_used status variable.
Adding DBUG_ASSERTs to make sure the thread_specific flag never
disappears in Assoc_array_data members.
- Removing my_free(item) from Field_assoc_array::element_by_key.
It was a remainder from an earlier patch version.
In the current patch version all Items behind an assoc array are
created on a mem_root. It's wrong to use my_free() with them.
- Adding a helper method Field_assoc_array::assoc_tree_search()
- Fixing assoc_array_var.delete() to work as a procedure
rather than a function. It does not need SELECT/DO any more.
- Fixing the crash in a few ctype_xxx tests, caused by the grammar change.
- Fixing compilation failure on Windows
- Adding a new method LEX::set_field_type_udt_or_typedef()
and removing duplicate code from sql_yacc.yy
- Renaming the grammar rule field_type_all_with_composites to
field_type_all_with_typedefs
- Removing the grammar rule assoc_array_index_types.
Changing the grammar to "INDEX_SYM BY field_type".
Removing the grammar rule field_type_all_with_record.
Allow field_type_all_with_typedefs as an assoc array element.
Catching wrong index and element data types has been moved to
Type_handler_assoc_array::Column_definition_set_attributes().
It raises an SQL error on things like:
* assoc array of assoc arrays in TABLE OF
* index by a non-supported types in INDEX BY
- Removing four methods:
* sp_type_def_list::type_defs_add_record()
* sp_type_def_list::type_defs_add_composite2()
* sp_pcontext::type_defs_declare_record()
* sp_type_def_list::type_defs_declare_composite2()
Adding two methods instead:
* sp_type_def_list::type_defs_add()
* sp_pcontext::type_defs_add()
This allows to get rid of the duplicate code detecting data type
declarations with the same name in the same sp_pcontext frame.
- Adding new methods:
* LEX::declare_type_assoc_array()
* LEX::LEX::declare_type_record()
They create a type specific sp_type_def_xxx and the call the generic
sp_pcontext::type_defs_add().
- m_key_def.sp_prepare_create_field() inside
Field_assoc_array::create_fields() is now called for all key data types
(not only for integers)
- Removing the assignment of key_def->charset in
Type_handler_assoc_array::sp_variable_declarations_finalize().
The charset is now evaluated in m_key_def.sp_prepare_create_field().
- Fixing Item_assoc_array::get_key() to set the character set of the "key"
to utf8mb3 instead of binary
- Fixing Field_assoc_array::copy_and_convert_key() to set the key length
limit in terms of the character length as specified in
INDEX BY VARCHAR(N), instead of octet length. This is needed to make
keys with multi-byte characters work correctly.
Also it now raises different errors depending on the reason of the
key conversion failures:
* ER_INVALID_CHARACTER_STRING
* ER_CANNOT_CONVERT_CHARACTER
- Changing the prototype for Type_handler_composite::key_to_lex_cstring() to
virtual LEX_CSTRING key_to_lex_cstring(THD *thd,
const sp_rcontext_addr &var,
Item **key,
String *buffer) const;
* Now it returns a LEX_CSTRING, instead of getting it as an out parameter.
* Gets an sp_rcontext_addr instead of "name" and "def"
* Gets a String buffer which can be used to be passed to val_str(),
or for character set conversion purposes.
- Removing Field_assoc_array::m_key_def, as all required information
is available from Field_assoc_array::m_key_field.
In Field_assoc_array::create_fields turning m_key_def to a local variable
key_def.
- Fixing Field_assoc_array::copy_and_convert_key() to follow MariaDB coding
style: only constants can be passed by-reference, not-constants should
be passed by-pointer.
- Adding DBUG_ASSERTs into Type_handler_assoc_array::get_item()
and Type_handler_assoc_array::get_or_create_item() that the passed
key in "name" is well formed according to the charset of INDEX BY.
- Changing the error ER_TOO_LONG_KEY to ER_WRONG_STRING_LENGTH.
The former prints length limit in bytes, which is not applicable
for INDEX BY values, because its limit is in characters.
Also, the latter is more verbose.
- Fixing the problem that these wrong uses of an assoc array variable:
BEGIN
assoc_var;
assoc_var(1);
END;
raised a weird error message:
ERROR 1054 (42S22): Unknown column 'assoc_var' in '(null)'
Now a more readable parse error is raised.
- Adding a "Duplicate key" warning for the cases when assigning
between two assoc arrays rejects some records due to different
collations in their INDEX BY key definitions.
- Disallow INDEX OF propagation from VARCHAR to TEXT.
The underlying code cannot handle TEXT.
Adding tests.
- Adding a helper class StringBufferKey to pass to val_str() when
a key value is evaluated.
Fixing all val_str() calls to val_str(&buffer), as the former is
not desirable.
- Fixing a wrong use of args[0]->null_value in
Item_func_assoc_array_exists::val_bool()
- Fixing a problem that using TABLE OF TEXT crashed the server.
Thanks to Iqbal Hassan for the proposed patch.
- Changes in Qualified_ident:
* Fixing the Qualified_ident constructors to get all parst as
Lex_ident_cli_st, rather than the first part as Lex_ident_cli_st
with the following parts Lex_ident_sys.
This makes the code more symmetric.
* Fixing the grammar in sql_yacc.yy accordinly.
* Fixing the data type storing the possition in the client query
from "const char *" to Lex_ident_cli.
* Adding a new method Qualified_ident::is_sane().
It allows to reduce the code side in sql_yacc.yy.
Thanks to Iqbal Hassan for the idea.
- Replacing qs_append() to append_ulonglong() in:
* Item_method_func::print()
* Item_splocal_assoc_array_element::print()
* Item_splocal_assoc_array_element_field::print()
These methods do not use reserve()/alloc(), so calling qs_append()
was wrong and caused a crash.
- Changing the output formats of these methods:
* Item_splocal_assoc_array_element::print()
* Item_splocal_assoc_array_element_field::print()
not to print the key two times.
Also moving the `@123` part (the variable offset) immediately
after the variabl name and before the `[key]` part.
- Fixing a memory leak happened when trying to insert a duplicate
key into an assoc array. Also adding a new "THD *" parameter to
Field_assoc_array::insert_element(). Thanks to Iqbal Hassan for the fix.
Adding a test into sp-assoc-array-ctype.test.
- In Field_assoc_array::create_fields: m_element_field->field_name is now
set for all element data types (not only for records).
This fixed a wrong variable name in warnings. Adding tests.
- Adding tests:
* Adding tests for assoc array elements in UNIONs.
* Copying from an assoc array with a varchar key
to an assoc array with a shorter varchar key.
* A relatively big associative array.
* Memory usage for x86_64.
* Package variable as assoc array keys.
* Character set conversion
* TABLE OF TEXT
* TABLE OF VARCHAR(>64k bytes) propagation to TABLE OF TEXT.
* TEXT element fields in an array of records.
* VARCHAR->TEXT propagation in elements in an array of records.
* Some more tests
This patch adds support for associative arrays in stored procedures
for sql_mode=ORACLE.
The syntax follows Oracle's PL/SQL syntax for associative arrays -
TYPE assoc_array_t IS TABLE OF VARCHAR2(100) INDEX BY INTEGER;
or
TYPE assoc_array_t IS TABLE OF record_t INDEX BY VARCHAR2(100);
where record_t is a record type.
The following functions were added for associative arrays:
- COUNT - Retrieve the number of elements within the arra
- EXISTS - Check whether given key exists in the array
- FIRST - Retrieve the first key in the array
- LAST - Retrieve the last key in the array
- PRIOR - Retrieve the key before the given key
- NEXT - Retrieve the key after the given key
- DELETE - Remove the element with the given key or remove all elements
if no key is given
The arrays/elements can be initialized with the following methods:
- Constructor
i.e. array:= assoc_array_t('key1'=>1, 'key2'=>2, 'key3'=>3)
- Assignment
i.e. array(key):= record_t(1, 2)
- SELECT INTO
i.e. SELECT x INTO array(key)
TODOs:
- Nested tables are not supported yet.
i.e. TYPE assoc_array_t IS TABLE OF other_assoc_array_t INDEX BY INTEGER;
- Associative arrays comparisons are not supported yet.
- Moving the definition of "class Type_handler_row" into a new file
sql_type_row.h. Also moving *some* of its methods into sql_type_row.cc.
The rest of the methods will be moved in the patch for MDEV-34319.
Moving the definition of my_var_sp_row_field into sql_type_row.cc.
- Fixing the grammar for function_call_generic to get the first
production as "ident_cli_func" rather than "ident_func".
The upcoming patch needs to know the position of the function name
within the client query.
- Adding new data types to store data types defined by "TYPE" declarations:
* sp_type_def
* sp_type_def_list
sp_pcontext now derives from sp_type_def_list
- A new virtual method in Field:
virtual Item_field *make_item_field_spvar(THD *thd,
const Spvar_definition &def);
Using it in sp_rcontext::init_var_items().
- Fixing my_var_sp to get sp_rcontext_addr in the parameter
instead of two separate parameters (rcontext_handler + offset).
- Adding new virtual methods in my_var:
virtual bool set_row(THD *thd, List<Item> &select_list);
It's used when a select_list record is assigned to a
single composite variable, such as ROW, specified in the INTO clause.
Using it in select_dumpvar::send_data().
virtual bool check_assignability(THD *thd,
const List<Item> &select_list,
bool *assign_as_row) const;
It's used to check if the select_list is compatible with
a single INTO variable, in select_dumpvar::prepare().
- Fixing LEX methods create_outvar() to get identifiers
a Lex_ident_sys_st values instead of generic LEX_CSTRING values.
- Adding virtual methods in Type_handler:
// Used in Item_func_null_predicate::check_arguments()
virtual bool has_null_predicate() const;
// Used in LEX::sp_variable_declarations_finalize()
virtual bool sp_variable_declarations_finalize(THD *thd,
LEX *lex, int nvars,
const Column_definition &def)
const;
// Handle SELECT 1 INTO spvar;
virtual my_var *make_outvar(THD *thd,
const Lex_ident_sys_st &name,
const sp_rcontext_addr &addr,
sp_head *sphead,
bool validate_only) const;
// Handle SELECT 1 INTO spvar.field;
virtual my_var *make_outvar_field(THD *thd,
const Lex_ident_sys_st &name,
const sp_rcontext_addr &addr,
const Lex_ident_sys_st &field,
sp_head *sphead,
bool validate_only) const;
// create the value in: DECLARE var rec_t DEFAULT rec_t(1,'c');
virtual Item *make_typedef_constructor_item(THD *thd,
const sp_type_def &def,
List<Item> *arg_list) const;
- A new helper method:
Row_definition_list *Row_definition_list::deep_copy(THD *thd) const;
