summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/main/cte_recursive.result48
-rw-r--r--mysql-test/main/cte_recursive.test39
-rw-r--r--mysql-test/suite/galera/disabled.def9
-rw-r--r--mysql-test/suite/innodb/r/table_flags.result5
-rw-r--r--mysql-test/suite/innodb/t/table_flags.test6
-rw-r--r--sql/sql_select.cc11
-rw-r--r--sql/sql_union.cc7
-rw-r--r--storage/innobase/fil/fil0crypt.cc17
-rw-r--r--storage/innobase/handler/ha_innodb.cc3
9 files changed, 128 insertions, 17 deletions
diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result
index 9e934b19efc..0417cf0a7f0 100644
--- a/mysql-test/main/cte_recursive.result
+++ b/mysql-test/main/cte_recursive.result
@@ -3606,6 +3606,54 @@ Mandelbrot Set
..............................................................................
...........................................................................
........................................................................
+#
+# MDEV-17871: EXPLAIN for query with not used recursive cte
+#
+create table t1 (a int);
+insert into t1 values (2), (1), (4), (3);
+explain extended
+with recursive cte as
+(select * from t1 where a=1 union select a+1 from cte where a<3)
+select * from cte as t;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 100.00
+2 DERIVED t1 ALL NULL NULL NULL NULL 4 100.00 Using where
+3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 4 100.00 Using where
+NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
+Warnings:
+Note 1003 with recursive cte as (/* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 union /* select#3 */ select `cte`.`a` + 1 AS `a+1` from `cte` where `cte`.`a` < 3)/* select#1 */ select `t`.`a` AS `a` from `cte` `t`
+with recursive cte as
+(select * from t1 where a=1 union select a+1 from cte where a<3)
+select * from cte as t;
+a
+1
+2
+3
+explain extended
+with recursive cte as
+(select * from t1 where a=1 union select a+1 from cte where a<3)
+select * from t1 as t;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t ALL NULL NULL NULL NULL 4 100.00
+Warnings:
+Note 1003 with recursive cte as (/* select#2 */ select `*` AS `*` from `test`.`t1` where `a` = 1 union /* select#3 */ select `a` + 1 AS `a+1` from `cte` where `a` < 3)/* select#1 */ select `test`.`t`.`a` AS `a` from `test`.`t1` `t`
+with recursive cte as
+(select * from t1 where a=1 union select a+1 from cte where a<3)
+select * from t1 as t;
+a
+2
+1
+4
+3
+create table t2 ( i1 int, i2 int);
+insert into t2 values (1,1),(2,2);
+explain
+with recursive cte as
+( select * from t1 union select s1.* from t1 as s1, cte where s1.i1 = cte.i2 )
+select * from t1 as t;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t ALL NULL NULL NULL NULL 4
+drop table t1,t2;
# End of 10.2 tests
#
# MDEV-14217 [db crash] Recursive CTE when SELECT includes new field
diff --git a/mysql-test/main/cte_recursive.test b/mysql-test/main/cte_recursive.test
index 79809044c00..0ed9c2d56e3 100644
--- a/mysql-test/main/cte_recursive.test
+++ b/mysql-test/main/cte_recursive.test
@@ -2496,6 +2496,45 @@ SELECT GROUP_CONCAT(
GROUP BY Iy
ORDER BY Iy;
+--echo #
+--echo # MDEV-17871: EXPLAIN for query with not used recursive cte
+--echo #
+
+create table t1 (a int);
+insert into t1 values (2), (1), (4), (3);
+
+let $rec_cte =
+with recursive cte as
+ (select * from t1 where a=1 union select a+1 from cte where a<3);
+
+eval
+explain extended
+$rec_cte
+select * from cte as t;
+
+eval
+$rec_cte
+select * from cte as t;
+
+eval
+explain extended
+$rec_cte
+select * from t1 as t;
+
+eval
+$rec_cte
+select * from t1 as t;
+
+create table t2 ( i1 int, i2 int);
+insert into t2 values (1,1),(2,2);
+
+explain
+with recursive cte as
+ ( select * from t1 union select s1.* from t1 as s1, cte where s1.i1 = cte.i2 )
+select * from t1 as t;
+
+drop table t1,t2;
+
--echo # End of 10.2 tests
--echo #
diff --git a/mysql-test/suite/galera/disabled.def b/mysql-test/suite/galera/disabled.def
index 5df9fa72b8f..d1d054b87ad 100644
--- a/mysql-test/suite/galera/disabled.def
+++ b/mysql-test/suite/galera/disabled.def
@@ -29,12 +29,12 @@ MW-416 : MDEV-13549 Galera test failures
MW-388 : MDEV-13549 Galera test failures
MW-44 : MDEV-15809 Test failure on galera.MW-44
galera_pc_ignore_sb : MDEV-15811 Test failure on galera_pc_ignore_sb
+galera_drop_database : MDEV-17421 mtr does not restart the server whose parameters were changed
galera_kill_applier : race condition at the start of the test
galera_ist_progress: MDEV-15236 galera_ist_progress fails when trying to read transfer status
pxc-421: Lock timeout exceeded
galera_sst_mysqldump_with_key : MDEV-16890 Galera test failure
galera_sst_xtrabackup-v2-options : Failed to read uuid:seqno and wsrep_gtid_domain_id from joiner script
-MW-328C : Timeouts
galera_gcs_fc_limit : Timeouts
pool_of_threads: WSREP has not yet prepared node for application use
galera_var_innodb_disallow_writes : Timeout
@@ -47,7 +47,6 @@ galera_gc_fc_limit : MDEV-17061 Test failure on galera.galera_gc_fc_limit
galera_as_slave_replication_budle : MDEV-15785 Test case galera_as_slave_replication_bundle caused debug assertion
galera_wan : MDEV-17259: Test failure on galera.galera_wan
galera_pc_ignore_sb : MDEV-17357 Test failure on galera.galera_pc_ignore_sb
-galera_drop_database : test
-galera.MW-328A : MDEV-17847 Galera test failure on MW-328[A|B|C]
-galera.MW-328B : MDEV-17847 Galera test failure on MW-328[A|B|C]
-galera.MW-328C : MDEV-17847 Galera test failure on MW-328[A|B|C]
+MW-328A : MDEV-17847 Galera test failure on MW-328[A|B|C]
+MW-328B : MDEV-17847 Galera test failure on MW-328[A|B|C]
+MW-328C : MDEV-17847 Galera test failure on MW-328[A|B|C]
diff --git a/mysql-test/suite/innodb/r/table_flags.result b/mysql-test/suite/innodb/r/table_flags.result
index c82cd6ceccb..8c4280738e1 100644
--- a/mysql-test/suite/innodb/r/table_flags.result
+++ b/mysql-test/suite/innodb/r/table_flags.result
@@ -192,3 +192,8 @@ ib_logfile0
ib_logfile1
ibdata1
sys_tables.bin
+call mtr.add_suppression("ERROR HY000: Can't create table `test`.`t1`");
+CREATE TABLE t1(f1 INT, f2 VARCHAR(1), KEY k1(f2),
+FULLTEXT KEY(f2),
+FOREIGN KEY (f2) REFERENCES t1(f3))ENGINE=InnoDB;
+ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
diff --git a/mysql-test/suite/innodb/t/table_flags.test b/mysql-test/suite/innodb/t/table_flags.test
index 284589a17c8..e979b5fffe0 100644
--- a/mysql-test/suite/innodb/t/table_flags.test
+++ b/mysql-test/suite/innodb/t/table_flags.test
@@ -216,3 +216,9 @@ DROP TABLE tr,tc,td,tz,tp;
--list_files $bugdir
--remove_files_wildcard $bugdir
--rmdir $bugdir
+
+call mtr.add_suppression("ERROR HY000: Can't create table `test`.`t1`");
+--error ER_CANT_CREATE_TABLE
+CREATE TABLE t1(f1 INT, f2 VARCHAR(1), KEY k1(f2),
+ FULLTEXT KEY(f2),
+ FOREIGN KEY (f2) REFERENCES t1(f3))ENGINE=InnoDB;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 26c5813e3d9..ce8617cda64 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -25744,13 +25744,13 @@ int JOIN::save_explain_data_intern(Explain_query *output,
(1) they are not parts of ON clauses that were eliminated by table
elimination.
(2) they are not merged derived tables
- (3) they are not unreferenced CTE
+ (3) they are not hanging CTEs (they are needed for execution)
*/
if (!(tmp_unit->item && tmp_unit->item->eliminated) && // (1)
(!tmp_unit->derived ||
tmp_unit->derived->is_materialized_derived()) && // (2)
- !(tmp_unit->with_element &&
- !tmp_unit->with_element->is_referenced())) // (3)
+ !(tmp_unit->with_element &&
+ (!tmp_unit->derived || !tmp_unit->derived->derived_result))) // (3)
{
explain->add_child(tmp_unit->first_select()->select_number);
}
@@ -25811,11 +25811,12 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order,
Save plans for child subqueries, when
(1) they are not parts of eliminated WHERE/ON clauses.
(2) they are not VIEWs that were "merged for INSERT".
- (3) they are not unreferenced CTE.
+ (3) they are not hanging CTEs (they are needed for execution)
*/
if (!(unit->item && unit->item->eliminated) && // (1)
!(unit->derived && unit->derived->merged_for_insert) && // (2)
- !(unit->with_element && !unit->with_element->is_referenced())) // (3)
+ !(unit->with_element &&
+ (!unit->derived || !unit->derived->derived_result))) // (3)
{
if (mysql_explain_union(thd, unit, result))
DBUG_VOID_RETURN;
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index 6368ed8afd8..edf85c2084b 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -1046,8 +1046,11 @@ bool st_select_lex_unit::prepare(TABLE_LIST *derived_arg,
0))
goto err;
if (!derived_arg->table)
- derived_arg->table= derived_arg->derived_result->table=
- with_element->rec_result->rec_tables.head();
+ {
+ derived_arg->table= with_element->rec_result->rec_tables.head();
+ if (derived_arg->derived_result)
+ derived_arg->derived_result->table= derived_arg->table;
+ }
with_element->mark_as_with_prepared_anchor();
is_rec_result_table_created= true;
}
diff --git a/storage/innobase/fil/fil0crypt.cc b/storage/innobase/fil/fil0crypt.cc
index 6a190a35b17..727264573da 100644
--- a/storage/innobase/fil/fil0crypt.cc
+++ b/storage/innobase/fil/fil0crypt.cc
@@ -2562,7 +2562,7 @@ fil_space_verify_crypt_checksum(
return (true);
}
- uint32 cchecksum1, cchecksum2;
+ uint32_t cchecksum1, cchecksum2;
/* Calculate checksums */
if (page_size.is_compressed()) {
@@ -2645,10 +2645,19 @@ fil_space_verify_crypt_checksum(
#else /* UNIV_INNOCHECKSUM */
ib::error()
<< " Page " << space << ":" << offset
- << " may be corrupted."
- " Post encryption checksum " << checksum
- << " stored [" << checksum1 << ":" << checksum2
+ << " may be corrupted.";
+ ib::info()
+ << "If encrypted: stored checksum" << checksum
+ << " calculated checksum [" << cchecksum1 << ":" << cchecksum2
<< "] key_version " << key_version;
+ ib::info()
+ << "If unencrypted: stored checksum [" << checksum1
+ << ":" << checksum2 << "] calculated crc32 ["
+ << buf_calc_page_crc32(page, false) << ":"
+ << buf_calc_page_crc32(page, true) << "] innodb ["
+ << buf_calc_page_old_checksum(page) << ":"
+ << buf_calc_page_new_checksum(page) << "] LSN "
+ << mach_read_from_4(page + FIL_PAGE_LSN);
#endif
encrypted = false;
}
diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
index a747038aed4..ba7a38c7428 100644
--- a/storage/innobase/handler/ha_innodb.cc
+++ b/storage/innobase/handler/ha_innodb.cc
@@ -12596,7 +12596,8 @@ ha_innobase::create(
if (info.drop_before_rollback()) {
trx->error_state = DB_SUCCESS;
row_drop_table_for_mysql(info.table_name(),
- trx, SQLCOM_TRUNCATE, true);
+ trx, SQLCOM_TRUNCATE, true,
+ false);
}
trx_rollback_for_mysql(trx);
row_mysql_unlock_data_dictionary(trx);
View Lorry Controller Status