Make it possible to specify engine-defined attributes on partitions
as well as tables.
If an engine-defined attribute is only specified at the table level,
it applies to all the partitions in the table.
This is a backward-compatible behavior.
If the same attribute is specified both at the table level and the
partition level, the per-partition one takes precedence.
So, we can consider per-table attributes as default values.
One cannot specify engine-defined attributes on subpartitions.
Implementation details:
* We store per-partition attributes in the partition_element class
because we already have the part_comment field, which is for
per-partition comments.
* In the case of ALTER TABLE statements, the partition_elements in
table->part_info is set up by mysql_unpack_partition().
So, we parse per-partition attributes after the call of the function.
Update was skipped (need_update was false) because compare_record()
used HA_PARTIAL_COLUMN_READ branch and it skipped row_start check
has_explicit_value() was false. When we set bit for row_start in
has_value_set the row is updated with new row_start value.
The bug was caused by combination of MDEV-23446 and 3789692d17. The
latter one says:
... But generated columns that are written to the table are always
deterministic and cannot change unless normal non-generated columns
were changed. ...
Since MDEV-23446 generated row_start can change while non-generated
columns are not changed.
Explicit value flag came from HAS_EXPLICIT_DEFAULT which was used to
distinguish default-generated value from user-supplied one.
Re-execution of a query containing subquery in the FROM clause results
in assert failure in case the query is run as part of a stored routine or
as a prepared statement AND derived table merge optimization is off.
As an example, the following test case
CREATE TABLE t1 (a INT) ;
CREATE PROCEDURE sp() SELECT * FROM (SELECT a FROM t1) tb;
CALL sp();
SET optimizer_switch='derived_merge=off';
CALL sp();
results in assert failure on the second invocation of the 'sp' stored routine.
The reason for assertion failure is that the expression
derived->is_excluded()
returns the value true where the value false expected.
The method is_excluded() returns the value true for a derived table
that has been merged to a parent select. Such transformation happens as part
of Derived Table Merge Optimization that is performed on first invocation of
a stored routine or a prepared statement containing a query with subquery
in the FROM clause of the main SELECT.
When the same routine or prepared statement is run the second time and
Derived Table Merge Optimization is OFF the MariaDB server tries to materialize
a derived table specified by the subquery that fails since this subquery
has already been merged to the top-most SELECT. This transformation is permanent
and can't be reverted. That is the reason why the assert
DBUG_ASSERT(!derived->is_excluded());
fails inside the function TABLE_LIST::set_check_materialized().
Similar behaviour can be observed in case a stored routine or prepared statement
containing a SELECT statement with subquery in the FROM clause, first is run
with the optimizer_switch option set to derived_merge=off and re-run after this
option has been switched to derived_merge=on. In this case a derived table for
subquery is materialized on the first execution and marked as merged derived
table on the second execution that results in error with misleading error
message:
MariaDB [test]> CALL sp1();
ERROR 1030 (HY000): Got error 1 "Operation not permitted" from storage engine MEMORY
To fix the issue, a derived table that has been already optimized shouldn't be
re-marked for one more round of optimization.
One significant consequence following from suggested change is that the data
member TABLE_LIST::derived_type is not updated once the table optimization
has been done. This fact should be taken into account when Prepared Statement
being handled since once a table listed in a query has been optimized on
execution of the statement PREPARE FROM it won't be touched anymore on handling
the statement EXECUTE.
One side effect caused by this change could be observed for the following
test case:
CREATE TABLE t1 (s1 INT);
CREATE VIEW v1 AS
SELECT s1,s2 FROM (SELECT s1 as s2 FROM t1 WHERE s1 <100) x, t1 WHERE t1.s1=x.s2;
INSERT INTO v1 (s1) VALUES (-300);
PREPARE stmt FROM "INSERT INTO v1 (s1) VALUES (-300)";
EXECUTE stmt;
Execution of the above EXECUTE statement results in issuing the error
ER_COLUMNACCESS_DENIED_ERROR since table_ref->is_merged_derived() is false
and check_column_grant_in_table_ref() called for a temporary table that
shouldn't be. To fix this issue the function find_field_in_tables has been
modified in such a way that the function check_column_grant_in_table_ref()
is not called for a temporary table.
InnoDB fails to apply buffered insert operation for
'mysql/transaction_registry' table during system versioning DDL.
To avoid this, DDL calls extra(HA_EXTRA_IGNORE_INSERT) to
inform the InnoDB for applying the buffered insert operation.
Long UNIQUE HASH index silently creates virtual column index, which should
be impossible for base columns featuring AUTO_INCREMENT.
Fix: add a relevant check; add new vcol type for a prettier error message.
In commit 1811fd51fb the assertion
should have said error_reported instead of !error_reported.
But, that revised assertion would still fail in main.defaults
where ER_BAD_DATA is reported during CREATE TABLE.
This is a duplicate of MDEV-18278 89936f11e9, but I will add an
additional assertion
Description:
The frm corruption should not be reported during CREATE TABLE. Normally
it doesn't, and the data to fill TABLE is taken by open_table_from_share
call. However, the vcol data is stored as SQL string in
table->s->vcol_defs.str and is anyway parsed on each table open.
It is impossible [or hard] to avoid, because it's hard to clone the
expression tree in general (it's easier to parse).
Normally parse_vcol_defs should only fail on semantic errors. If so,
error_reported is set to true. Any other failure is not expected during
table creation. There is either unhandled/unacknowledged error, or
something went really wrong, like memory reject. This all should be
asserted anyway.
Solution:
* Set *error_reported=true for the forward references check;
* Assert for every unacknowledged error during table creation.
There were two independent problems which lead to the crash
and to the non-relevant records returned in I_S queries:
- The code in the I_S implementation was not secure
about values with 0x00 bytes.
It's fixed by using check_db_name() and check_table_name()
inside make_table_name_list(), and by adding the test for
0x00 inside check_table_name().
- The code in Item_string::print() did not convert
strings without introducers when restoring
the CREATE VIEW statement from an Item tree.
This made wrong literals inside the "query" line in the view FRM file
in cases when the VIEW parse time
character_set_client!=character_set_connection.
That's fixed by adding a proper conversion.
This change also fixed a similar problem in SHOW PROCEDURE CODE -
the literals were displayed in wrong character set in SP instructions
in cases when the SP parse time
character_set_client!=character_set_connection.
in about a hundred of users of MY_BITMAP, only two were using its
built-in mutex, and only one of those two was actually needing it.
Remove the mutex from MY_BITMAP, remove all associated conditions
and checks in bitmap functions. Use an external LOCK_temp_pool
mutex and temp_pool_set_next/temp_pool_clear_bit acccessors.
Remove bitmap_init/bitmap_free, always use my_* versions.
Server crashes in Field::register_field_in_read_map upon select from
partitioned table with indexed by prefix virtual column.
After several read-mark fixes a problem has surfaced:
Since KEY (c(10),a) uses only a prefix of c, a new field is created,
duplicated from table->field[3], with a new length. However,
vcol_inco->expr is not copied.
Therefore, (*key_info)->key_part[i].field->vcol_info->expr was left NULL
in ha_partition::index_init().
Solution: copy vcol_info from table field when it's set up.
Server crashes in Field::register_field_in_read_map upon select from
partitioned table with indexed by prefix virtual column.
After several read-mark fixes a problem has surfaced:
Since KEY (c(10),a) uses only a prefix of c, a new field is created,
duplicated from table->field[3], with a new length. However,
vcol_inco->expr is not copied.
Therefore, (*key_info)->key_part[i].field->vcol_info->expr was left NULL
in ha_partition::index_init().
Solution: initialize vcols before key initialization
Also key initialization is moved to a function.
Reformulate mark_columns_used_by_index* function family in a more laconic
way:
mark_columns_used_by_index -> mark_index_columns
mark_columns_used_by_index_for_read_no_reset -> mark_index_columns_for_read
mark_columns_used_by_index_no_reset -> mark_index_columns_no_reset
static mark_index_columns -> do_mark_index_columns
Several different test cases were failing under the same reason: the
fields in a vcol expression were not marked during marking columns of a key
contatining virtual column for read.
Fix: make marking columns of a key for read a special case where
register_field_in_read_map() is done instead of plain bitmap_set_bit().
Some test cases are only reproducible in 10.4+, but the fix is applicable
to 10.2+
This is a 10.2+ part of a jira task
The two bugs regarding virtual column marking have been fixed:
1. UPDATE of a partitioned table, where the optimizer has chosen a
secondary index to make a filesort;
2. INSERT into a table with a nonblob field generated from a blob, with
binlog enabled and binlog_row_image=noblob.
3. DELETE from a view on a table with virtual column.
Generally the assertion happens from update_virtual_fields() call
These bugs are root-caused by missing field marking for dependant fields
of a virtual column.
Therefore a fix is: mark all the fields involved in the vcol expression by
calling field->register_field_in_read_map() instead just setting a single
bit.
3 was reproducible only on 10.4+, however the problem might has just been
invisible in the earlier versions. The fix is applicable to 10.2-10.3 as
well.
We cannot revert the ALTER, so anything happening after
the point of no return should not be treated as an error. A
very unfortunate condition that a user needs to be warned about - yes,
but we cannot say "ALTER TABLE has failed" if the table was successfully
altered.
This replaces 8711adb786
if a temptable field is created for some json expression (is_json_type()
returns true), make this temptable field a proper json field.
A field is a json field (see Item_field::is_json_type()) if it
has a CHECK constraint of JSON_VALID(field).
Note that it will never be actually checked for temptable fields,
so it won't cause a run-time slowdown.
The ROWNUM() function is for SELECT mapped to JOIN->accepted_rows, which is
incremented for each accepted rows.
For Filesort, update, insert, delete and load data, we map ROWNUM() to
internal variables incremented when the table is changed.
The connection between the row counter and Item_func_rownum is done
in sql_select.cc::fix_items_after_optimize() and
sql_insert.cc::fix_rownum_pointers()
When ROWNUM() is used anywhere in query, the optimization to ignore ORDER
BY in sub queries are disabled. This was done to get the following common
Oracle query to work:
select * from (select * from t1 order by a desc) as t where rownum() <= 2;
MDEV-3926 "Wrong result with GROUP BY ... WITH ROLLUP" contains a discussion
about this topic.
LIMIT optimization is enabled when in a top level WHERE clause comparing
ROWNUM() with a numerical constant using any of the following expressions:
- ROWNUM() < #
- ROWNUM() <= #
- ROWNUM() = 1
ROWNUM() can be also be the right argument to the comparison function.
LIMIT optimization is done in two cases:
- For the current sub query when the ROWNUM comparison is done on the top
level:
SELECT * from t1 WHERE rownum() <= 2 AND t1.a > 0
- For an inner sub query, when the upper level has only a ROWNUM comparison
in the WHERE clause:
SELECT * from (select * from t1) as t WHERE rownum() <= 2
In Oracle mode, one can also use ROWNUM without parentheses.
Other things:
- Fixed bug where the optimizer tries to optimize away sub queries
with RAND_TABLE_BIT set (non-deterministic queries). Now these
sub queries will not be converted to joins. This bug fix was also
needed to get rownum() working inside subqueries.
- In remove_const() remove setting simple_order to FALSE if ROLLUP is
USED. This code was disable a long time ago because of wrong assignment
in the following code. Instead we set simple_order to false if
RAND_TABLE_BIT was used in the SELECT list. This ensures that
we don't delete ORDER BY if the result set is not deterministic, like
in 'SELECT RAND() AS 'r' FROM t1 ORDER BY r';
- Updated parameters for Sort_param::init_for_filesort() to be able
to provide filesort with information where the number of accepted
rows should be stored
- Reordered fields in class Filesort to optimize storage layout
- Added new error messsage to tell that a function can't be used in HAVING
- Added field 'with_rownum' to THD to mark that ROWNUM() is used in the
query.
Co-author: Oleksandr Byelkin <sanja@mariadb.com>
LIMIT optimization for sub query
