Applying HEX encoding write writting an ENUM/SET TYPELIB to FRM
if the TYPELIB has 0x00 bytes in the value.
This HEX encoding was earlier used only to write UCS2/UTF16/UTF32 TYPELIBs.
A new flag FIELDFLAG_FRM_HEX_ENCODED_TYPELIB was added to indicate
that the TYPELIB is hex encoded. It's used only inside FRM.
Note, it's mangled with FIELDFLAG_TREAT_BIT_AS_CHAR.
This should not be harmful:
- BIT and ENUM/SET columns are handled by two separate code branches
when opening an FRM
- The flag is unset immediately after decoding TYPELIB, so the rest
of the code does not se an unexpected flag combination.
VECTOR, as a subtype of VARCHAR, has max length 65532, i.e. maximum
dimension of 16383. BLOB/MEDIUMBLOB/LONGBLOB (and corresponding TEXT
types) each has length exceeding 65532. Therefore, when aggregating
VECTOR with one of these BLOB/TEXT types, the aggregated type has
length exceeding the max length of VECTOR, which should result in an
error.
To that end, in this patch we add checks on the resulting vector
length during type aggregation inference
Analysis:
OLD_VALUE() swapped only field->ptr, leaving null_ptr pointing to the
current record. This caused incorrect NULL results.
Fix:
Store null_ptr_old for record[1] and swap it together with ptr to
preserve correct NULL semantics.
The patch introduces the OLD_VALUE() expression to reference the value
of a column before it was updated. The parser is extended to support
RETURNING and OLD_VALUE(), and RETURNING expressions are stored in a
separate returning_list in SELECT_LEX with independent wildcard tracking.
RETURNING is rejected for multi-table UPDATE.
During setup of RETURNING fields, THD::is_setting_returning is used
when resolving fields, particularly for updates through views.
When resolving view fields, Item_direct_view_ref may point to the
view's item_list, losing the information about whether the value
should be old or new. The original item in returning_list still
contains the correct is_old_value_reference flag, which is copied
back to the resolved item.
OLD_VALUE() is implemented by extending Item_field with a new
Item_old_field class. Item_field::set_field() initializes
Field::ptr_old to the corresponding location in record[1],
which stores the old row during UPDATE execution.
When sending result rows, Item_old_field::send() temporarily switches
the field pointer from record[0] (current row) to ptr_old so
OLD_VALUE() returns the value before the update.
The UPDATE execution path is modified to send a result set when
RETURNING is present instead of an OK packet.
Adding support for the strict cursor data types:
Example 1a:
TYPE rec0_t IS RECORD (a INT, VARCHAR(10));
TYPE cur0_t IS REF CURSOR RETURN rec0_t;
Example 1b:
TYPE rec0_t IS RECORD (a t1.a%TYPE, b t1.b%TYPE);
TYPE cur0_t IS REF CURSOR RETURN rec0_t;
Example 1c:
TYPE rec0_t IS RECORD (a INT, VARCHAR(10));
r0 rec0_t;
TYPE cur0_t IS REF CURSOR RETURN r0%TYPE;
Example 1d:
TYPE rec0_t IS RECORD (a t1.a%TYPE, b t1.b%TYPE);
r0 rec0_t;
TYPE cur0_t IS REF CURSOR RETURN r0%TYPE;
Example2a:
TYPE cur0_t IS REF CURSOR RETURN t1%ROWTYPE; -- t1 is a table
Example 2b:
r0 t1%ROWTYPE;
TYPE cur0_t IS REF CURSOR RETURN r0%TYPE;
Example3a:
CURSOR cursor_sample IS SELECT a,b FROM t1;
TYPE cur0_t IS REF CURSOR RETURN cursor_sample%ROWTYPE;
Example3b:
CURSOR cursor_sample IS SELECT a,b FROM t1;
r0 cursor_sample%ROWTYPE;
TYPE cur0_t IS REF CURSOR RETURN r0%TYPE;
If a cursor variable is declared with a RETURN clause then:
1. At OPEN type the data type of the SELECT list row is compared
for compatibility with the cursor RETURN data type.
The SELECT list row must be assignable to the RETURN type row.
If case if assignability is not meet, an error is raised
Assignability means:
- The arity of the SELECT list must be equal to the arity
of the RETURN clause
- Every n-th field of the SELECT list must be assignable to the
n-th field of the RETURN Clause
2. At FETCH time, the data is fetched in two steps:
a. On the first step the data is fetched into a virtual table
with the row type described in the RETURN clause
b. On the second step the data is copied from the virtual table
to the target fetch list. Data type conversion can happen
on this step.
Change details:
Adding new methods:
- sp_cursor::check_assignability_to
- Virtual_tmp_table::check_assignability_from
- Virtual_tmp_table::sp_set_from_select_list
- Virtual_tmp_table::sp_save_in_vtable
- Virtual_tmp_table::sp_save_in_target_list
- LEX::check_ref_cursor_components
- LEX::make_sp_instr_copy_struct_for_last_context_variables
- LEX::declare_type_ref_cursor
- sp_cursor::Select_fetch_into_spvars::send_data_with_return_type
Adding new members:
- sp_instr_copen_by_ref::m_cursor_name
- Select_fetch_into_spvars::m_return_type
- Select_materialize::m_cursor_name
- Select_materialize::m_return_type
Adding new virtual methods:
- Item::resolve_spvar_cursor_rowtype
- Type_handler::Spvar_definition_resolve_type_refs
- Server_side_cursor::check_assignability_to
- Overriding Select_materialize::prepare to raise an error when the cursor
returned data type is not compatible with the RETURN clause
Making these methods virtual:
- Field::check_assignability_from
Adding new classes:
- sp_type_def_ref
- RowTypeBuffer
Adding new constructors to:
- Spvar_definition
Adding new helper methods (e.g. to reuse the code)
- Field::store_field_maybe_null
- ChanBuffer::append_ulonglong
- sp_pcontext::set_type_for_last_context_variables
Minor changes:
- Making TABLE::export_structure const
- Overriding Item_splocal::type_extra_attributes. It was forgotten in earlier changes.
Adding new error messages
- ER_CANNOT_CAST_ON_IDENT1_ASSIGNMENT_FOR_OPERATION
- ER_CANNOT_CAST_ON_IDENT2_ASSIGNMENT_FOR_OPERATION
The first key point of this commit is changing Type_extra_attributes in the way
that the pointer to a generic attribute is now "Type_generic_attributes*" -
a pointer to a new class with vitrual method type_handler().
Before this change the pointer to a generic attribute was
"void *m_attr_const_void_ptr", which was error prone, as allowed
to pass a pointer to a wrong structure without control.
Another key point is that the SET/ENUM data type related structures
are now attributed by the changed class Type_typelib_attributes,
which now looks as follows:
class Type_typelib_attributes: public Sql_alloc,
public Type_generic_attributes,
public TYPELIB
(with virtual method type_handler()), instead of being attributed by
TYPELIB directly.
Details:
- Using Column_definition's method typelib() where the member m_typelib
was used directly. The related lines change anyway, because the member
is renamed m_typelib_attr.
- Adding a new class Type_generic_attributes.
Deriving the following classes from it:
* Type_typelib_attributes
* sp_type_def
- sp_type_def does not derive from Type_handler_hybrid_field_type any more,
because the method type_handler() is now implemented virtually.
- Removing Type_extra_attributes::m_attr_const_void_ptr and
adding Type_extra_attributes::m_attr_const_generic_attributes_ptr instead -
a pointer to the new class Type_generic_attributes.
Renaming methods to set and read it according to the new data type name.
- Type_typelib_attributes now derives from TYPELIB instead of having
a pointer to TYPELIB.
- Adding a new class Type_typelib_ptr_attributes. It's a replacement for
the old implementation of Type_typelib_attributes.
Instead of deriving from Type_typelib_attributes, Field_enum now derives
from Type_typelib_ptr_attributes. The latter can store/read itself into/from
Type_extra_attributes. It's a bridge between the new Type_typelib_attributes
and Type_extra_attributes.
- Changing parameter data type in a few methods in Field_enum from
a pointer to TYPELIB to a pointer to Type_typelib_attributes.
- Removing typelib related methods from Type_extra_attributes.
Moving this functionality into Type_typelib_ptr_attributes and
Column_definition_attributes.
This turns Type_extra_attributes into a data structure independent
from any data type specific structures/methods.
- Adding methods:
Column_definition_attributes::typelib_attr() - Column_definition derives it
Column_definition_attributes::typelib() - Column_definition derives it
Type_typelib_ptr_attributes::typelib_attr() - Field_enum derives it
Type_typelib_ptr_attributes::typelib() - Field_enum derives it
- Renaming the member Create_field::save_interval into
Create_field::save_typelib_attr.
Changing its data type from a pointer to TYPELIB into a pointer
to Type_typelib_attributes pointer.
- Removing the method Type_typelib_attributes::store().
Adding Type_typelib_ptr_attributes::save_in_type_extra_attributes() instead.
The new method name makes the code more readable.
A new assertion added by MDEV-36290 is correct in trying to identify
default_fields that weren't correctly accounted in parse_vcol_defs.
It caught a case for INSERT DELAYED on tables using TIMESTAMP and
DATETIME types with ON UPDATE default values. That is, the insert
delayed logic opens the table in one thread, and then copies the
field info to another table in the user thread. This copy loses most
flag metadata for these fields though. In this case, the flag
ON_UPDATE_NOW_FLAG was dropped, and the parse_col_defs() logic was
unable to properly account for that default field, and the assertion
fired.
This patch fixes this by restoring the ON_UPDATE_NOW_FLAG during the
field copy. Also, a comment is added to Field::make_new_field() to
explain the state of the returned field.
Reviewed-by: Monty <monty@mariadb.com>
Signed-off-by: Brandon Nesterenko <brandon.nesterenko@mariadb.com>
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;
We had a protection against it, by allowing versioned delete if:
trx->id != table->vers_start_id()
For replace this check fails: replace calls ha_delete_row(record[2]), but
table->vers_start_id() returns the value from record[0], which is irrelevant.
The same problem hits Field::is_max, which may have checked the wrong record.
Fix:
* Refactor Field::is_max to optionally accept a pointer as an argument.
* Refactor vers_start_id and vers_end_id to always accept a pointer to the
record. there is a difference with is_max is that is_max accepts the pointer to
the
field data, rather than to the record.
Method val_int() would be too effortful to refactor to accept the argument, so
instead the value in record is fetched directly, like it is done in
Field_longlong.
We had a protection against it, by allowing versioned delete if:
trx->id != table->vers_start_id()
For replace this check fails: replace calls ha_delete_row(record[2]), but
table->vers_start_id() returns the value from record[0], which is irrelevant.
The same problem hits Field::is_max, which may have checked the wrong record.
Fix:
* Refactor Field::is_max to optionally accept a pointer as an argument.
* Refactor vers_start_id and vers_end_id to always accept a pointer to the
record. there is a difference with is_max is that is_max accepts the pointer to
the
field data, rather than to the record.
Method val_int() would be too effortful to refactor to accept the argument, so
instead the value in record is fetched directly, like it is done in
Field_longlong.
Additional small cleanup (forgoten in the previous commit):
- Item_splocal_assoc_array_element_field::fix_fields()
Fixing the error message for:
SELECT marks('a').name; -- marks is a TABLE OF VARCHAR(10)
From: ERROR 42S22: Unknown column '1' in 'SELECT'
To: ERROR 42S22: Unknown column 'name' in 'marks'
Fixing wrong results of packed_col_length(): it returned a wrong result for:
- CHAR(N)
- BINARY(N)
- MEDIUMBLOB
- LONGBLOB
Note, this method was not used. Now it's used by the assoc array data type.
Details:
- Cleanup: adding "const" qualifier to:
* Field::pack()
* Field::max_packed_col_length()
* Field::packed_col_length()
- Removing the "max_length" argument from Field::pack().
The caller passed either UINT_MAX or Field::max_data_length() to it.
So it was never used to limit the destination size and only
made the code more complicated and confusing.
- Removing arguments from packed_col_length().
Now it calculates the value for the Field, using its "ptr",
and assuming the entire value will be packed,
without limiting the destination size.
- Fixing Field_blob::packed_col_length(). It worked fine only for
TINYBLOB and BLOB, and did not work for MEDIUMBLOB and LONGBLOB.
- Overriding Field_char::packed_col_length().
Using the inherited method was wrong - it implemented
variable length data behavior.
- Overriding Field_string::max_data_length(). It was also incorrect.
Implementing fixed size behavior.
Moving the old implementation of Field_longstring::max_data_length()
to Field_varstring::max_data_length().
- Fixing class StringPack:
* Removing the "length" argument from StringPack::packed_col_length().
It now assumes that the packed length for the entire data buffer
is needed, without limiting the destination size.
* Fixing StringPack::packed_col_length() to implement fixed dat size behavior.
It erroneously implemented VARCHAR style behavor
(assumed that the length was stored in the leading 1 or 2 bytes).
* Adding a helper method length_bytes(). Reusing it in packed_col_length()
and max_packed_col_length().
* Moving a part the method pack() into a new method trimmed_length().
Reusing it in pack() and packed_col_length().
* Rewriting the code in trimmed_length() in a more straightforward way.
It was hard to understand what it was doing.
Adding a comment.
- Adding a test sp-assoc-array-pack-debug.test covering packed_col_length()
and pack() for various data types.
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;
main/statistics_json.result is updated for f8ba5ced55 (MDEV-36099)
The test uses 'delete from t1' in many places and then populates
the table again. The natural order of rows in a MyISAM table is well
defined and the test was implicitly relying on that.
before f8ba5ced55 delete was deleting rows one by one, using
ha_myisam::delete_row() because the connection was stuck in rbr mode.
This caused rows to be shown in the reverse insertion order (because of
the delete link list).
MDEV-36099 fixes this bug and the server now correctly uses
ha_myisam::delete_all_rows(). This makes rows to be shown in the
insertion order as expected.
don't construct a "default value field" by moving
field's ptr/null_ptr. Field can have its null_ptr moved to
extra_null_bitmap for BEFORE triggers. Perhaps there can be other
reasons for null_ptr and ptr not to be at the right offset to each other.
Instead, use pointers from TABLE_SHARE::field, which always point
to default values. Except when there's no TABLE_SHARE::field,
which can happen for TEMPTABLE views, for example, but these views
are not updatable anyway.
Add an assert to Field::move_field_offset() to ensure it's only
used for appropriately set ptr/null_ptr pairs.
Lots of different cases, SELECT, SELECT DEFAULT(),
UPDATE t SET x=DEFAULT, prepares statements,
opening of a table for the I_S, prelocking (so TL_WRITE),
insert with subquery (so SQLCOM_SELECT), etc.
Don't check NEXTVAL privileges in fix_fields() anymore, it cannot
possibly handle all the cases correctly. Make a special method
Item_func_nextval::check_access() for that and invoke it from
* fix_fields on explicit SELECT NEXTVAL()
(but not if NEXTVAL() is used in a DEFAULT clause)
* when DEFAULT bareword in used in, say, UPDATE t SET x=DEFAULT
(but not if DEFAULT() itself is used in a DEFAULT clause)
* in CREATE TABLE
* in ALTER TABLE ALGORITHM=INPLACE (that doesn't go CREATE TABLE path)
* on INSERT
helpers
* Virtual_column_info::check_access() to walk the item tree and invoke
Item::check_access()
* TABLE::check_sequence_privileges() to iterate default expressions
and invoke Virtual_column_info::check_access()
also, single-table UPDATE in prepared statements now associates
value items with fields just as multi-update already did, fixes the
case of PREPARE s "UPDATE t SET x=?"; EXECUTE s USING DEFAULT.
temporary table
Compressed field cannot be part of a key by its nature: there is no
data to order, only the compressed data.
For optimizer temporary table we create uncompressed substitute.
In all other cases (MDEV-16808) we don't use key: add_keyuse() is
skipped by !field->compression_method() condition.
Also expand vcol field index coverings to include indexes covering all
the fields in the expression. The reasoning goes as follows: let f(c1,
c2, ..., cn) be a function on applied to columns c1, c2, ..., cn, if
f(...) is covered by an index, so should vc whose expression is
f(...).
For example, if t.vf = t.c1 + t.c2, and t has three indexes (vf), (c1,
c2), (c1).
Before this change, vf's index covering is a singleton {(vf)}. Let's call
that the "conventional" index covering.
After this change vf's index covering is now {(vf), (c1, c2)}, since
(c1, c2) covers both c1 and c2. Let's call (c1, c2) in this case the
"extra" covering.
With the coverings updated, when an index in the "extra" covering is
chosen for keyread, the vcol also needs to be calculated. In this case
we mark vcol in the table read_set, and ensure it is computed.
With these changes, we see various improvements, including from using
full table scan + filesort to full index scan + filesort when ORDER BY
an indexed vcol (here vc = c + 1 is a vcol and both c and vc are
indexes):
explain select c + 1 from t order by vc;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t ALL NULL NULL NULL NULL 10000 Using filesort
+1 SIMPLE t index NULL c 5 NULL 10000 Using index; Using filesort
The substitutions are followed updates to all_fields which include a
copy of the ORDER BY/GROUP BY item pointers, as well as corresponding
updates to ref_pointer_array so that the all_fields and
ref_pointer_array remain in sync.
Another, related change is the recomputation of table index covering
on substitutions. It not only reflects the correct table index
covering after the substitutions, but also improve executions where
the vcol index can be chosen, such as this example (here vc = c + 1
and vc is the only index in the table), from full table scan +
filesort to full index scan:
select vc from t order by c + 1;
We do it in SELECT as well as in single table DELETE/UPDATE.
This patch adds support for SYS_REFCURSOR (a weakly typed cursor)
for both sql_mode=ORACLE and sql_mode=DEFAULT.
Works as a regular stored routine variable, parameter and return value:
- can be passed as an IN parameter to stored functions and procedures
- can be passed as an INOUT and OUT parameter to stored procedures
- can be returned from a stored function
Note, strongly typed REF CURSOR will be added separately.
Note, to maintain dependencies easier, some parts of sql_class.h
and item.h were moved to new header files:
- select_results.h:
class select_result_sink
class select_result
class select_result_interceptor
- sp_cursor.h:
class sp_cursor_statistics
class sp_cursor
- sp_rcontext_handler.h
class Sp_rcontext_handler and its descendants
The implementation consists of the following parts:
- A new class sp_cursor_array deriving from Dynamic_array
- A new class Statement_rcontext which contains data shared
between sub-statements of a compound statement.
It has a member m_statement_cursors of the sp_cursor_array data type,
as well as open cursor counter. THD inherits from Statement_rcontext.
- A new data type handler Type_handler_sys_refcursor in plugins/type_cursor/
It is designed to store uint16 references -
positions of the cursor in THD::m_statement_cursors.
- Type_handler_sys_refcursor suppresses some derived numeric features.
When a SYS_REFCURSOR variable is used as an integer an error is raised.
- A new abstract class sp_instr_fetch_cursor. It's needed to share
the common code between "OPEN cur" (for static cursors) and
"OPER cur FOR stmt" (for SYS_REFCURSORs).
- New sp_instr classes:
* sp_instr_copen_by_ref - OPEN sys_ref_curor FOR stmt;
* sp_instr_cfetch_by_ref - FETCH sys_ref_cursor INTO targets;
* sp_instr_cclose_by_ref - CLOSE sys_ref_cursor;
* sp_instr_destruct_variable - to destruct SYS_REFCURSOR variables when
the execution goes out of the BEGIN..END block
where SYS_REFCURSOR variables are declared.
- New methods in LEX:
* sp_open_cursor_for_stmt - handles "OPEN sys_ref_cursor FOR stmt".
* sp_add_instr_fetch_cursor - "FETCH cur INTO targets" for both
static cursors and SYS_REFCURSORs.
* sp_close - handles "CLOSE cur" both for static cursors and SYS_REFCURSORs.
- Changes in cursor functions to handle both static cursors and SYS_REFCURSORs:
* Item_func_cursor_isopen
* Item_func_cursor_found
* Item_func_cursor_notfound
* Item_func_cursor_rowcount
- A new system variable @@max_open_cursors - to limit the number
of cursors (static and SYS_REFCURSORs) opened at the same time.
Its allowed range is [0-65536], with 50 by default.
- A new virtual method Type_handler::can_return_bool() telling
if calling item->val_bool() is allowed for Items of this data type,
or if otherwise the "Illegal parameter for operation" error should be raised
at fix_fields() time.
- New methods in Sp_rcontext_handler:
* get_cursor()
* get_cursor_by_ref()
- A new class Sp_rcontext_handler_statement to handle top level statement
wide cursors which are shared by all substatements.
- A new virtual method expr_event_handler() in classes Item and Field.
It's needed to close (and make available for a new OPEN)
unused THD::m_statement_cursors elements which do not have any references
any more. It can happen in various moments in time, e.g.
* after evaluation parameters of an SQL routine
* after assigning a cursor expression into a SYS_REFCURSOR variable
* when leaving a BEGIN..END block with SYS_REFCURSOR variables
* after setting OUT/INOUT routine actual parameters from formal
parameters.
* rpl.rpl_system_versioning_partitions updated for MDEV-32188
* innodb.row_size_error_log_warnings_3 changed error for MDEV-33658
(checks are done in a different order)
normalize_cond() translated `WHERE col` into `WHERE col<>0`
But the opetator "not equal to 0" does not necessarily exists
for all data types.
For example, the query:
SELECT * FROM t1 WHERE inet6col;
was translated to:
SELECT * FROM t1 WHERE inet6col<>0;
which further failed with this error:
ERROR : Illegal parameter data types inet6 and bigint for operation '<>'
This patch changes the translation from `col<>0` to `col IS TRUE`.
So now
SELECT * FROM t1 WHERE inet6col;
gets translated to:
SELECT * FROM t1 WHERE inet6col IS TRUE;
Details:
1. Implementing methods:
- Field_longstr::val_bool()
- Field_string::val_bool()
- Item::val_int_from_val_str()
If the input contains bad data,
these methods raise a better error message:
Truncated incorrect BOOLEAN value
Before the change, the error was:
Truncated incorrect DOUBLE value
2. Fixing normalize_cond() to generate Item_func_istrue/Item_func_isfalse
instances instead of Item_func_ne/Item_func_eq
3. Making Item_func_truth sargable, so it uses the range optimizer.
Implementing the following methods:
- get_mm_tree(), get_mm_leaf(), add_key_fields() in Item_func_truth.
- get_func_mm_tree(), for all Item_func_truth descendants.
4. Implementing the method negated_item() for all Item_func_truth
descendants, so the negated item has a chance to be sargable:
For example,
WHERE NOT col IS NOT FALSE -- this notation is not sargable
is now translated to:
WHERE col IS FALSE -- this notation is sargable
it's incorrect to zero out table->triggers->extra_null_bitmap
before a statement, because if insert uses an explicit field list
and omits a field that has no default value, the field should
get NULL implicitly. So extra_null_bitmap should have 1s for all
fields that have no defaults
* create extra_null_bitmap_init and initialize it as above
* copy extra_null_bitmap_init to extra_null_bitmap for inserts
* still zero out extra_null_bitmap for updates/deletes where
all fields definitely have a value
* make not_null_fields_have_null_values() to send
ER_NO_DEFAULT_FOR_FIELD for fields with no default and no value,
otherwise creation of a trigger with an empty body would change the
error message
