- commits - lists.mariadb.org

[Commits] 501444174: Range Locking: do Snapshot Checking on Put/Delete/etc if assume_tracked=false.
by Sergei Petrunia 15 Jun '20

15 Jun '20

revision-id: 50144417486d9d67b8c7f3fa6e15dfd79d88a742 (v5.8-2684-g501444174) parent(s): 8787e79af4ebe931eeb0a1ef6633576996576126 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-06-15 23:07:20 +0300 message: Range Locking: do Snapshot Checking on Put/Delete/etc if assume_tracked=false. Before this patch, Range Locking relied on the caller to obtain a lock on the rowkey before that rowkey is modified with a Transaction::Put/Delete/etc call. This property holds for MyRocks. However a RocksDB user using Transaction with txn->SetSnapshot(); v= txn->Get(k); txn->Put(k, v+1); txn->Commit(); will observe a weaker transactional isolation than with point locking: the Put() will blindly overwrite any changes commited after Get(). Fixed this by doing similar to what point lock manager does. If Get/Update/etc is called with assume_tracked=false (this is the default argument value), then: - Get a point lock - Perform Snapshot Checking of the - then do the write. This will have has extra overhead for MyRocks-like users that get locks before calling Put/Delete/etc. These callers can pass assume_tracked=true and then the extra checks are avoided. --- utilities/transactions/pessimistic_transaction.cc | 9 +++- utilities/transactions/range_locking_test.cc | 61 ++++++++++++++++++++++- utilities/transactions/transaction_base.cc | 42 +++++----------- 3 files changed, 80 insertions(+), 32 deletions(-) diff --git a/utilities/transactions/pessimistic_transaction.cc b/utilities/transactions/pessimistic_transaction.cc index 04f6c6c8d..8f7629bbf 100644 --- a/utilities/transactions/pessimistic_transaction.cc +++ b/utilities/transactions/pessimistic_transaction.cc @@ -558,7 +558,14 @@ Status PessimisticTransaction::TryLock(ColumnFamilyHandle* column_family, // If we are not doing key tracking, just get the lock and return (this // also assumes locks are "idempotent") if (!do_key_tracking_) { - return txn_db_impl_->TryLock(this, cfh_id, key_str, exclusive); + s = txn_db_impl_->TryLock(this, cfh_id, key_str, exclusive); + if (s.ok() && !assume_tracked && snapshot_ != nullptr) { + // Perform validation + SequenceNumber tracked_at_seq = kMaxSequenceNumber; + SetSnapshotIfNeeded(); + s = ValidateSnapshot(column_family, key, &tracked_at_seq); + } + return s; } // lock this key if this transactions hasn't already locked it diff --git a/utilities/transactions/range_locking_test.cc b/utilities/transactions/range_locking_test.cc index ff1ca5aae..1de512079 100644 --- a/utilities/transactions/range_locking_test.cc +++ b/utilities/transactions/range_locking_test.cc @@ -48,7 +48,7 @@ class RangeLockingTest : public ::testing::Test { RangeLockingTest() : db(nullptr) { options.create_if_missing = true; - dbname = test::PerThreadDBPath("transaction_testdb"); + dbname = test::PerThreadDBPath("range_locking_testdb"); DestroyDB(dbname, options); Status s; @@ -70,6 +70,13 @@ class RangeLockingTest : public ::testing::Test { // from attempting to delete such files in DestroyDB. DestroyDB(dbname, options); } + + PessimisticTransaction* NewTxn( + TransactionOptions txn_opt = TransactionOptions()) { + Transaction* txn = db->BeginTransaction(WriteOptions(), txn_opt); + return reinterpret_cast<PessimisticTransaction*>(txn); + } + }; // TODO: set a smaller lock wait timeout so that the test runs faster. @@ -122,6 +129,58 @@ TEST_F(RangeLockingTest, BasicRangeLocking) { delete txn1; } +TEST_F(RangeLockingTest, SnapshotValidation) { + Status s; + Slice key_slice= Slice("k"); + ColumnFamilyHandle *cfh= db->DefaultColumnFamily(); + + auto txn0 = NewTxn(); + txn0->Put(key_slice, Slice("initial")); + txn0->Commit(); + + // txn1 + auto txn1 = NewTxn(); + txn1->SetSnapshot(); + std::string val1; + s = txn1->Get(ReadOptions(), cfh, key_slice, &val1); + ASSERT_TRUE(s.ok()); + val1 = val1 + std::string("-txn1"); + + s = txn1->Put(cfh, key_slice, Slice(val1)); + ASSERT_TRUE(s.ok()); + + // txn2 + auto txn2 = NewTxn(); + txn2->SetSnapshot(); + std::string val2; + // This will see the original value as nothing is committed + // This is also Get, so it is doesn't acquire any locks. + s = txn2->Get(ReadOptions(), cfh, key_slice, &val2); + ASSERT_TRUE(s.ok()); + + // txn1 + s = txn1->Commit(); + ASSERT_TRUE(s.ok()); + + // txn2 + val2 = val2 + std::string("-txn2"); + // Now, this call should do Snapshot Validation and fail: + s = txn2->Put(cfh, key_slice, Slice(val2)); + ASSERT_TRUE(s.IsBusy()); + + s = txn2->Commit(); + ASSERT_TRUE(s.ok()); + + /* + // Not meaningful if s.IsBusy() above is true: + // Examine the result + auto txn3= NewTxn(); + std::string val3; + Status s3 = txn3->Get(ReadOptions(), cfh, key_slice, &val3); + fprintf(stderr, "Final: %s\n", val3.c_str()); + */ +} + } // namespace rocksdb int main(int argc, char** argv) { diff --git a/utilities/transactions/transaction_base.cc b/utilities/transactions/transaction_base.cc index 3a2c75bec..fb79b9d5d 100644 --- a/utilities/transactions/transaction_base.cc +++ b/utilities/transactions/transaction_base.cc @@ -378,11 +378,8 @@ Status TransactionBaseImpl::Put(ColumnFamilyHandle* column_family, const Slice& key, const Slice& value, const bool assume_tracked) { const bool do_validate = !assume_tracked; - Status s; - if (do_key_tracking_) { - s = TryLock(column_family, key, false /* read_only */, - true /* exclusive */, do_validate, assume_tracked); - } + Status s = TryLock(column_family, key, false /* read_only */, + true /* exclusive */, do_validate, assume_tracked); if (s.ok()) { s = GetBatchForWrite()->Put(column_family, key, value); @@ -398,11 +395,8 @@ Status TransactionBaseImpl::Put(ColumnFamilyHandle* column_family, const SliceParts& key, const SliceParts& value, const bool assume_tracked) { const bool do_validate = !assume_tracked; - Status s; - if (do_key_tracking_) { - s = TryLock(column_family, key, false /* read_only */, - true /* exclusive */, do_validate, assume_tracked); - } + Status s = TryLock(column_family, key, false /* read_only */, + true /* exclusive */, do_validate, assume_tracked); if (s.ok()) { s = GetBatchForWrite()->Put(column_family, key, value); @@ -435,11 +429,8 @@ Status TransactionBaseImpl::Delete(ColumnFamilyHandle* column_family, const Slice& key, const bool assume_tracked) { const bool do_validate = !assume_tracked; - Status s; - if (do_key_tracking_) { - s = TryLock(column_family, key, false /* read_only */, - true /* exclusive */, do_validate, assume_tracked); - } + Status s = TryLock(column_family, key, false /* read_only */, + true /* exclusive */, do_validate, assume_tracked); if (s.ok()) { s = GetBatchForWrite()->Delete(column_family, key); @@ -455,11 +446,8 @@ Status TransactionBaseImpl::Delete(ColumnFamilyHandle* column_family, const SliceParts& key, const bool assume_tracked) { const bool do_validate = !assume_tracked; - Status s; - if (do_key_tracking_) { - s = TryLock(column_family, key, false /* read_only */, - true /* exclusive */, do_validate, assume_tracked); - } + Status s = TryLock(column_family, key, false /* read_only */, + true /* exclusive */, do_validate, assume_tracked); if (s.ok()) { s = GetBatchForWrite()->Delete(column_family, key); @@ -475,11 +463,8 @@ Status TransactionBaseImpl::SingleDelete(ColumnFamilyHandle* column_family, const Slice& key, const bool assume_tracked) { const bool do_validate = !assume_tracked; - Status s; - if (do_key_tracking_) { - s = TryLock(column_family, key, false /* read_only */, - true /* exclusive */, do_validate, assume_tracked); - } + Status s = TryLock(column_family, key, false /* read_only */, + true /* exclusive */, do_validate, assume_tracked); if (s.ok()) { s = GetBatchForWrite()->SingleDelete(column_family, key); @@ -495,11 +480,8 @@ Status TransactionBaseImpl::SingleDelete(ColumnFamilyHandle* column_family, const SliceParts& key, const bool assume_tracked) { const bool do_validate = !assume_tracked; - Status s; - if (do_key_tracking_) { - s = TryLock(column_family, key, false /* read_only */, - true /* exclusive */, do_validate, assume_tracked); - } + Status s = TryLock(column_family, key, false /* read_only */, + true /* exclusive */, do_validate, assume_tracked); if (s.ok()) { s = GetBatchForWrite()->SingleDelete(column_family, key);

1 0

[Commits] 99cc5123c: Range Locking: do Snapshot Checking on Put/Delete/etc if assume_tracked=false.
by Sergei Petrunia 15 Jun '20

15 Jun '20

revision-id: 99cc5123c10a4c65007e15e4ae6d06ac51bd2c36 (v5.8-2684-g99cc5123c) parent(s): 8787e79af4ebe931eeb0a1ef6633576996576126 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-06-15 23:04:16 +0300 message: Range Locking: do Snapshot Checking on Put/Delete/etc if assume_tracked=false. Before this patch, Range Locking relied on the caller to obtain a lock on the rowkey before that rowkey is modified with a Transaction::Put/Delete/etc call. This property holds for MyRocks. However a RocksDB user using Transaction with txn->SetSnapshot(); v= txn->Get(k); txn->Put(k, v+1); txn->Commit(); will observe a weaker transactional isolation than with point locking: the Put() will blindly overwrite any changes commited after Get(). Fixed this by doing similar to what point lock manager does. If Get/Update/etc is called with assume_tracked=false (this is the default argument value), then: - Get a point lock - Perform Snapshot Checking of the - then do the write. This will have has extra overhead for MyRocks-like users that get locks before calling Put/Delete/etc. These callers can pass assume_tracked=true and then extra checks are avoided. so this is avloid. --- utilities/transactions/pessimistic_transaction.cc | 9 +++- utilities/transactions/range_locking_test.cc | 61 ++++++++++++++++++++++- utilities/transactions/transaction_base.cc | 42 +++++----------- 3 files changed, 80 insertions(+), 32 deletions(-) diff --git a/utilities/transactions/pessimistic_transaction.cc b/utilities/transactions/pessimistic_transaction.cc index 04f6c6c8d..8f7629bbf 100644 --- a/utilities/transactions/pessimistic_transaction.cc +++ b/utilities/transactions/pessimistic_transaction.cc @@ -558,7 +558,14 @@ Status PessimisticTransaction::TryLock(ColumnFamilyHandle* column_family, // If we are not doing key tracking, just get the lock and return (this // also assumes locks are "idempotent") if (!do_key_tracking_) { - return txn_db_impl_->TryLock(this, cfh_id, key_str, exclusive); + s = txn_db_impl_->TryLock(this, cfh_id, key_str, exclusive); + if (s.ok() && !assume_tracked && snapshot_ != nullptr) { + // Perform validation + SequenceNumber tracked_at_seq = kMaxSequenceNumber; + SetSnapshotIfNeeded(); + s = ValidateSnapshot(column_family, key, &tracked_at_seq); + } + return s; } // lock this key if this transactions hasn't already locked it diff --git a/utilities/transactions/range_locking_test.cc b/utilities/transactions/range_locking_test.cc index ff1ca5aae..1de512079 100644 --- a/utilities/transactions/range_locking_test.cc +++ b/utilities/transactions/range_locking_test.cc @@ -48,7 +48,7 @@ class RangeLockingTest : public ::testing::Test { RangeLockingTest() : db(nullptr) { options.create_if_missing = true; - dbname = test::PerThreadDBPath("transaction_testdb"); + dbname = test::PerThreadDBPath("range_locking_testdb"); DestroyDB(dbname, options); Status s; @@ -70,6 +70,13 @@ class RangeLockingTest : public ::testing::Test { // from attempting to delete such files in DestroyDB. DestroyDB(dbname, options); } + + PessimisticTransaction* NewTxn( + TransactionOptions txn_opt = TransactionOptions()) { + Transaction* txn = db->BeginTransaction(WriteOptions(), txn_opt); + return reinterpret_cast<PessimisticTransaction*>(txn); + } + }; // TODO: set a smaller lock wait timeout so that the test runs faster. @@ -122,6 +129,58 @@ TEST_F(RangeLockingTest, BasicRangeLocking) { delete txn1; } +TEST_F(RangeLockingTest, SnapshotValidation) { + Status s; + Slice key_slice= Slice("k"); + ColumnFamilyHandle *cfh= db->DefaultColumnFamily(); + + auto txn0 = NewTxn(); + txn0->Put(key_slice, Slice("initial")); + txn0->Commit(); + + // txn1 + auto txn1 = NewTxn(); + txn1->SetSnapshot(); + std::string val1; + s = txn1->Get(ReadOptions(), cfh, key_slice, &val1); + ASSERT_TRUE(s.ok()); + val1 = val1 + std::string("-txn1"); + + s = txn1->Put(cfh, key_slice, Slice(val1)); + ASSERT_TRUE(s.ok()); + + // txn2 + auto txn2 = NewTxn(); + txn2->SetSnapshot(); + std::string val2; + // This will see the original value as nothing is committed + // This is also Get, so it is doesn't acquire any locks. + s = txn2->Get(ReadOptions(), cfh, key_slice, &val2); + ASSERT_TRUE(s.ok()); + + // txn1 + s = txn1->Commit(); + ASSERT_TRUE(s.ok()); + + // txn2 + val2 = val2 + std::string("-txn2"); + // Now, this call should do Snapshot Validation and fail: + s = txn2->Put(cfh, key_slice, Slice(val2)); + ASSERT_TRUE(s.IsBusy()); + + s = txn2->Commit(); + ASSERT_TRUE(s.ok()); + + /* + // Not meaningful if s.IsBusy() above is true: + // Examine the result + auto txn3= NewTxn(); + std::string val3; + Status s3 = txn3->Get(ReadOptions(), cfh, key_slice, &val3); + fprintf(stderr, "Final: %s\n", val3.c_str()); + */ +} + } // namespace rocksdb int main(int argc, char** argv) { diff --git a/utilities/transactions/transaction_base.cc b/utilities/transactions/transaction_base.cc index 3a2c75bec..fb79b9d5d 100644 --- a/utilities/transactions/transaction_base.cc +++ b/utilities/transactions/transaction_base.cc @@ -378,11 +378,8 @@ Status TransactionBaseImpl::Put(ColumnFamilyHandle* column_family, const Slice& key, const Slice& value, const bool assume_tracked) { const bool do_validate = !assume_tracked; - Status s; - if (do_key_tracking_) { - s = TryLock(column_family, key, false /* read_only */, - true /* exclusive */, do_validate, assume_tracked); - } + Status s = TryLock(column_family, key, false /* read_only */, + true /* exclusive */, do_validate, assume_tracked); if (s.ok()) { s = GetBatchForWrite()->Put(column_family, key, value); @@ -398,11 +395,8 @@ Status TransactionBaseImpl::Put(ColumnFamilyHandle* column_family, const SliceParts& key, const SliceParts& value, const bool assume_tracked) { const bool do_validate = !assume_tracked; - Status s; - if (do_key_tracking_) { - s = TryLock(column_family, key, false /* read_only */, - true /* exclusive */, do_validate, assume_tracked); - } + Status s = TryLock(column_family, key, false /* read_only */, + true /* exclusive */, do_validate, assume_tracked); if (s.ok()) { s = GetBatchForWrite()->Put(column_family, key, value); @@ -435,11 +429,8 @@ Status TransactionBaseImpl::Delete(ColumnFamilyHandle* column_family, const Slice& key, const bool assume_tracked) { const bool do_validate = !assume_tracked; - Status s; - if (do_key_tracking_) { - s = TryLock(column_family, key, false /* read_only */, - true /* exclusive */, do_validate, assume_tracked); - } + Status s = TryLock(column_family, key, false /* read_only */, + true /* exclusive */, do_validate, assume_tracked); if (s.ok()) { s = GetBatchForWrite()->Delete(column_family, key); @@ -455,11 +446,8 @@ Status TransactionBaseImpl::Delete(ColumnFamilyHandle* column_family, const SliceParts& key, const bool assume_tracked) { const bool do_validate = !assume_tracked; - Status s; - if (do_key_tracking_) { - s = TryLock(column_family, key, false /* read_only */, - true /* exclusive */, do_validate, assume_tracked); - } + Status s = TryLock(column_family, key, false /* read_only */, + true /* exclusive */, do_validate, assume_tracked); if (s.ok()) { s = GetBatchForWrite()->Delete(column_family, key); @@ -475,11 +463,8 @@ Status TransactionBaseImpl::SingleDelete(ColumnFamilyHandle* column_family, const Slice& key, const bool assume_tracked) { const bool do_validate = !assume_tracked; - Status s; - if (do_key_tracking_) { - s = TryLock(column_family, key, false /* read_only */, - true /* exclusive */, do_validate, assume_tracked); - } + Status s = TryLock(column_family, key, false /* read_only */, + true /* exclusive */, do_validate, assume_tracked); if (s.ok()) { s = GetBatchForWrite()->SingleDelete(column_family, key); @@ -495,11 +480,8 @@ Status TransactionBaseImpl::SingleDelete(ColumnFamilyHandle* column_family, const SliceParts& key, const bool assume_tracked) { const bool do_validate = !assume_tracked; - Status s; - if (do_key_tracking_) { - s = TryLock(column_family, key, false /* read_only */, - true /* exclusive */, do_validate, assume_tracked); - } + Status s = TryLock(column_family, key, false /* read_only */, + true /* exclusive */, do_validate, assume_tracked); if (s.ok()) { s = GetBatchForWrite()->SingleDelete(column_family, key);

1 0

[Commits] b6b5429e49b: MDEV-22891: Optimizer trace: const tables are not clearly visible
by Sergei Petrunia 15 Jun '20

15 Jun '20

revision-id: b6b5429e49bc652c5344cbf48c66aa1790e24598 (mariadb-10.5.2-419-gb6b5429e49b) parent(s): 9ed08f357693875c9d2376651305aa66199d2c35 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-06-15 12:59:53 +0300 message: MDEV-22891: Optimizer trace: const tables are not clearly visible Make mark_join_nest_as_const() print its action into the trace. --- mysql-test/main/opt_trace.result | 31 +++++++++++++++++++++++++++++++ mysql-test/main/opt_trace.test | 23 +++++++++++++++++++++++ sql/sql_select.cc | 6 ++++++ 3 files changed, 60 insertions(+) diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index ad7bce44445..6af1c2afe9e 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -8530,5 +8530,36 @@ select count(*) from seq_1_to_10000000 { } ] } 0 0 +# +# MDEV-22891: Optimizer trace: const tables are not clearly visible +# +create table t0(a int primary key); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (pk int primary key, a int); +insert into t1 select a,a from t0; +create table t2 (pk int primary key, a int); +insert into t2 select a,a from t0; +create table t3 (pk int primary key, a int); +insert into t3 select a,a from t0; +explain +select * from t1 left join (t2 join t3 on t3.pk=1000) on t2.a=t1.a and t2.pk is null; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 const PRIMARY NULL NULL NULL 1 Impossible ON condition +1 SIMPLE t2 const PRIMARY NULL NULL NULL 1 Impossible ON condition +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 +select JSON_DETAILED(JSON_EXTRACT(trace, '$**.mark_join_nest_as_const')) +from information_schema.optimizer_trace; +JSON_DETAILED(JSON_EXTRACT(trace, '$**.mark_join_nest_as_const')) +[ + + { + "members": + [ + "t3", + "t2" + ] + } +] +drop table t0, t1, t2, t3; # End of 10.5 tests set optimizer_trace='enabled=off'; diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test index f2d7b67983d..f7f19e38eac 100644 --- a/mysql-test/main/opt_trace.test +++ b/mysql-test/main/opt_trace.test @@ -604,5 +604,28 @@ DROP TABLE t1,t2; select count(*) from seq_1_to_10000000; select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; +--echo # +--echo # MDEV-22891: Optimizer trace: const tables are not clearly visible +--echo # +create table t0(a int primary key); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t1 (pk int primary key, a int); +insert into t1 select a,a from t0; + +create table t2 (pk int primary key, a int); +insert into t2 select a,a from t0; + +create table t3 (pk int primary key, a int); +insert into t3 select a,a from t0; + +explain +select * from t1 left join (t2 join t3 on t3.pk=1000) on t2.a=t1.a and t2.pk is null; + +select JSON_DETAILED(JSON_EXTRACT(trace, '$**.mark_join_nest_as_const')) +from information_schema.optimizer_trace; + +drop table t0, t1, t2, t3; + --echo # End of 10.5 tests set optimizer_trace='enabled=off'; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 76cb96b93fd..fc4aec08302 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -4737,6 +4737,10 @@ void mark_join_nest_as_const(JOIN *join, { List_iterator<TABLE_LIST> it(join_nest->nested_join->join_list); TABLE_LIST *tbl; + Json_writer_object emb_obj(join->thd); + Json_writer_object trace_obj(join->thd, "mark_join_nest_as_const"); + Json_writer_array trace_array(join->thd, "members"); + while ((tbl= it++)) { if (tbl->nested_join) @@ -4756,6 +4760,8 @@ void mark_join_nest_as_const(JOIN *join, *found_const_table_map|= tab->table->map; set_position(join,(*const_count)++,tab,(KEYUSE*) 0); mark_as_null_row(tab->table); // All fields are NULL + + trace_array.add_table_name(tab->table); } } }

1 0

[Commits] 104772e7ccd: MDEV-22779: Crash: Prepared Statement ..., part #2.
by Sergei Petrunia 13 Jun '20

13 Jun '20

revision-id: 104772e7ccdc302fc25a206b1c7b0da186b198a1 (mariadb-10.2.31-274-g104772e7ccd) parent(s): 0f9542ee33c6f0582d60b2244db8733281ecbb46 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-06-13 23:28:09 +0300 message: MDEV-22779: Crash: Prepared Statement ..., part #2. For the sake of completeness, call sync_clones in reset_stmt_params, too. --- sql/sql_prepare.cc | 3 +++ 1 file changed, 3 insertions(+) diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index 79d18fcb799..f0c9f818f87 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -3125,7 +3125,10 @@ static void reset_stmt_params(Prepared_statement *stmt) Item_param **item= stmt->param_array; Item_param **end= item + stmt->param_count; for (;item < end ; ++item) + { (**item).reset(); + (**item).sync_clones(); + } }

1 0

[Commits] 0f9542ee33c: MDEV-22779: Crash: Prepared Statement with a '?' parameter inside a re-used CTE
by Sergei Petrunia 13 Jun '20

13 Jun '20

revision-id: 0f9542ee33c6f0582d60b2244db8733281ecbb46 (mariadb-10.2.31-273-g0f9542ee33c) parent(s): b68f1d847f1fc00eed795e20162effc8fbc4119b author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-06-13 16:45:55 +0300 message: MDEV-22779: Crash: Prepared Statement with a '?' parameter inside a re-used CTE When a prepared statement parameter '?' is used in a CTE that is used multiple times, the following happens: - The CTE definition is re-parsed multiple times. - There are multiple Item_param objects referring to the same "?" in the original query. - Prepared_statement::param has a pointer to the first of them, the others are "clones". - When prepared statement parameter gets the value, it should be passed over to clones with param->sync_clones() call. This call is made in insert_params(), etc. It was not made in insert_params_with_log(). This would cause Item_param to not have any value which would confuse the query optimizer. Added the missing call. --- sql/sql_prepare.cc | 2 ++ sql/sql_select.cc | 1 + tests/mysql_client_test.c | 52 +++++++++++++++++++++++++++++++++++++++++++++++ 3 files changed, 55 insertions(+) diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index 6ac85f1331e..79d18fcb799 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -903,6 +903,8 @@ static bool insert_params_with_log(Prepared_statement *stmt, uchar *null_array, if (param->convert_str_value(thd)) DBUG_RETURN(1); /* out of memory */ + + param->sync_clones(); } if (acc.finalize()) DBUG_RETURN(1); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index b558445540d..4cca2d67eb8 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -5997,6 +5997,7 @@ static void optimize_keyuse(JOIN *join, DYNAMIC_ARRAY *keyuse_array) uint n_tables= my_count_bits(map); if (n_tables == 1) // Only one table { + DBUG_ASSERT(!(map & PSEUDO_TABLE_BITS)); // Must be a real table Table_map_iterator it(map); int tablenr= it.next_bit(); DBUG_ASSERT(tablenr != Table_map_iterator::BITMAP_END); diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c index c544e20f2fe..33655b80662 100644 --- a/tests/mysql_client_test.c +++ b/tests/mysql_client_test.c @@ -19843,6 +19843,57 @@ static void test_bulk_replace() } #endif + +static void test_ps_params_in_ctes() +{ + int rc; + const char *query; + MYSQL_BIND ps_params[1]; + int int_data[1]; + MYSQL_STMT *stmt; + + rc= mysql_query(mysql, "create table t1(a int, b int, key(a))"); + myquery(rc); + + rc= mysql_query(mysql, "insert into t1 (a) values " + "(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)"); + myquery(rc); + + query= + "explain " + "with T as " + "( " + " select * from t1 where t1.a=? limit 2 " + ") " + "select * from T as TA, T as TB;"; + + stmt= mysql_stmt_init(mysql); + check_stmt(stmt); + + rc= mysql_stmt_prepare(stmt, query, (uint) strlen(query)); + check_execute(stmt, rc); + + int_data[0]=2; + + ps_params[0].buffer_type= MYSQL_TYPE_LONG; + ps_params[0].buffer= (char *) &int_data[0]; + ps_params[0].length= 0; + ps_params[0].is_null= 0; + + rc= mysql_stmt_bind_param(stmt, ps_params); + check_execute(stmt, rc); + + rc= mysql_stmt_execute(stmt); + check_execute(stmt, rc); + + rc= mysql_stmt_store_result(stmt); + check_execute(stmt, rc); + + rc= mysql_query(mysql, "drop table t1"); + myquery(rc); +} + + static struct my_tests_st my_tests[]= { { "disable_query_logs", disable_query_logs }, { "test_view_sp_list_fields", test_view_sp_list_fields }, @@ -20127,6 +20178,7 @@ static struct my_tests_st my_tests[]= { { "test_bulk_delete", test_bulk_delete }, { "test_bulk_replace", test_bulk_replace }, #endif + { "test_ps_params_in_ctes", test_ps_params_in_ctes }, { 0, 0 } };

1 0

[Commits] 7478fb3: MDEV-22748 MariaDB crash on WITH RECURSIVE large query
by IgorBabaev 06 Jun '20

06 Jun '20

revision-id: 7478fb361f9c2c85757d08955ff594c124d5e42d (mariadb-10.2.31-263-g7478fb3) parent(s): 187b9c924ebaff8f02fb4e2139a01fd1512e3dc9 author: Igor Babaev committer: Igor Babaev timestamp: 2020-06-06 11:38:38 -0700 message: MDEV-22748 MariaDB crash on WITH RECURSIVE large query This bug is the same as the bug MDEV-17024. The crashes caused by these bugs were due to premature cleanups of the unit specifying recursive CTEs that happened in some cases when there were several outer references the same recursive CTE. The problem of premature cleanups for recursive CTEs could be already resolved by the correction in TABLE_LIST::set_as_with_table() introduced in this patch. ALL other changes introduced by the patches for MDEV-17024 and MDEV-22748 guarantee that this clean-ups are performed as soon as possible: when the select containing the last outer reference to a recursive CTE is being cleaned up the specification of the recursive CTE should be cleaned up as well. --- mysql-test/r/cte_recursive.result | 170 ++++++++++++++++++++++++++++++++++++-- mysql-test/t/cte_recursive.test | 70 +++++++++++++++- sql/sql_cte.cc | 1 + sql/sql_derived.cc | 6 +- sql/sql_union.cc | 13 +++ 5 files changed, 247 insertions(+), 13 deletions(-) diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result index 0b8bc3f..6404931 100644 --- a/mysql-test/r/cte_recursive.result +++ b/mysql-test/r/cte_recursive.result @@ -691,13 +691,13 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where 1 PRIMARY <derived3> ref key0 key0 5 c.h_id 2 100.00 1 PRIMARY <derived3> ref key0 key0 5 c.w_id 2 100.00 -2 DERIVED <derived3> ALL NULL NULL NULL NULL 12 100.00 Using where 3 DERIVED folks ALL NULL NULL NULL NULL 12 100.00 Using where 4 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 4 RECURSIVE UNION p ALL NULL NULL NULL NULL 12 100.00 Using where; Using join buffer (flat, BNL join) 5 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 5 RECURSIVE UNION p ALL NULL NULL NULL NULL 12 100.00 Using where; Using join buffer (flat, BNL join) NULL UNION RESULT <union3,4,5> ALL NULL NULL NULL NULL NULL NULL +2 DERIVED <derived3> ALL NULL NULL NULL NULL 12 100.00 Using where Warnings: Note 1003 with recursive ancestor_couple_ids as (select `a`.`father` AS `h_id`,`a`.`mother` AS `w_id` from `coupled_ancestors` `a` where `a`.`father` is not null and `a`.`mother` is not null), coupled_ancestors as (select `test`.`folks`.`id` AS `id`,`test`.`folks`.`name` AS `name`,`test`.`folks`.`dob` AS `dob`,`test`.`folks`.`father` AS `father`,`test`.`folks`.`mother` AS `mother` from `test`.`folks` where `test`.`folks`.`name` = 'Me' union all select `test`.`p`.`id` AS `id`,`test`.`p`.`name` AS `name`,`test`.`p`.`dob` AS `dob`,`test`.`p`.`father` AS `father`,`test`.`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestor_couple_ids` `fa` where `test`.`p`.`id` = `fa`.`h_id` union all select `test`.`p`.`id` AS `id`,`test`.`p`.`name` AS `name`,`test`.`p`.`dob` AS `dob`,`test`.`p`.`father` AS `father`,`test`.`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestor_couple_ids` `ma` where `test`.`p`.`id` = `ma`.`w_id`)select `h`.`name` AS `name`,`h`.`dob` AS `dob`,`w`.`name ` AS `name`,`w`.`dob` AS `dob` from `ancestor_couple_ids` `c` join `coupled_ancestors` `h` join `coupled_ancestors` `w` where `h`.`id` = `c`.`h_id` and `w`.`id` = `c`.`w_id` # simple mutual recursion @@ -1302,12 +1302,12 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived4> ALL NULL NULL NULL NULL 24 4 DERIVED folks ALL NULL NULL NULL NULL 12 Using where 6 RECURSIVE UNION <derived3> ALL NULL NULL NULL NULL 12 +5 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 24 +NULL UNION RESULT <union4,6,5> ALL NULL NULL NULL NULL NULL 3 DERIVED folks ALL NULL NULL NULL NULL 12 Using where 2 RECURSIVE UNION folks ALL PRIMARY NULL NULL NULL 12 2 RECURSIVE UNION <derived3> ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) NULL UNION RESULT <union3,2> ALL NULL NULL NULL NULL NULL -5 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 24 -NULL UNION RESULT <union4,6,5> ALL NULL NULL NULL NULL NULL explain FORMAT=JSON with recursive prev_gen @@ -3326,13 +3326,13 @@ select * from cte1, cte2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived4> ALL NULL NULL NULL NULL 6 100.00 1 PRIMARY <derived5> ALL NULL NULL NULL NULL 6 100.00 Using join buffer (flat, BNL join) +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL 4 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where 4 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 5 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where 5 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) -2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used -3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where -NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 with recursive rcte as (select 1 AS `a` union select cast(`rcte`.`a` + 1 as unsigned) AS `cast(a+1 as unsigned)` from `rcte` where `rcte`.`a` < 10), cte1 as (select count(0) AS `c1` from `rcte` join `test`.`t1` where `rcte`.`a` between 3 and 5 and `test`.`t1`.`id` = `rcte`.`a` - 3), cte2 as (select count(0) AS `c2` from `rcte` join `test`.`t1` where `rcte`.`a` between 7 and 8 and `test`.`t1`.`id` = `rcte`.`a` - 7)select `cte1`.`c1` AS `c1`,`cte2`.`c2` AS `c2` from `cte1` join `cte2` prepare stmt from "with recursive @@ -3391,6 +3391,65 @@ cte2 as (select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) select * from cte1, cte2 where cte1.c1 = 3; c1 c2 +with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte2, cte1; +c2 c1 +1 2 +explain extended with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte2, cte1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived5> ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <derived4> ALL NULL NULL NULL NULL 6 100.00 Using join buffer (flat, BNL join) +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL +5 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +5 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +4 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +4 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 with recursive rcte as (select 1 AS `a` union select cast(`rcte`.`a` + 1 as unsigned) AS `cast(a+1 as unsigned)` from `rcte` where `rcte`.`a` < 10), cte1 as (select count(0) AS `c1` from `rcte` join `test`.`t1` where `rcte`.`a` between 3 and 5 and `test`.`t1`.`id` = `rcte`.`a` - 3), cte2 as (select count(0) AS `c2` from `rcte` join `test`.`t1` where `rcte`.`a` between 7 and 8 and `test`.`t1`.`id` = `rcte`.`a` - 7)select `cte2`.`c2` AS `c2`,`cte1`.`c1` AS `c1` from `cte2` join `cte1` +prepare stmt from "with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte2, cte1"; +execute stmt; +c2 c1 +1 2 +execute stmt; +c2 c1 +1 2 +drop procedure p; +drop table t2; +create table t2 (c1 int, c2 int); +create procedure p() insert into t2 with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte2, cte1; +call p(); +select * from t2; +c1 c2 +1 2 drop procedure p; drop table t1,t2; # @@ -3809,4 +3868,101 @@ ANALYZE } drop function f1; drop table t1,t2; -End of 10.2 tests +# +# MDEV-22748: two materialized CTEs using the same recursive CTE +# (see also test case for MDEV-17024) +# +CREATE TABLE t1 (YEAR int(4), d1 date , d2 date) ; +INSERT INTO t1 VALUES (2018,'2018-01-01','2018-09-20'); +CREATE TABLE t2 (id int, tm date); +INSERT INTO t2 VALUES (1,'2018-08-30'),(2,'2018-08-30'),(3,'2018-08-30'); +CREATE TABLE t3 (id int, tm date); +INSERT INTO t3 VALUES (1,'2018-08-30'),(2,'2018-08-30'); +WITH RECURSIVE +cte AS +(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn +FROM t1 +UNION ALL +SELECT YEAR(cte.st + INTERVAL 1 MONTH), +cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY +FROM cte JOIN t1 +WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ), +cte2 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), +cte3 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) +SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR); +YEAR d1 d2 +2018 2018-01-01 2018-09-20 +EXPLAIN EXTENDED WITH RECURSIVE +cte AS +(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn +FROM t1 +UNION ALL +SELECT YEAR(cte.st + INTERVAL 1 MONTH), +cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY +FROM cte JOIN t1 +WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ), +cte2 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), +cte3 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) +SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 +1 PRIMARY <derived5> ref key0 key0 9 const 0 0.00 Using where +1 PRIMARY <derived4> ref key0 key0 9 const 0 0.00 Using where +2 DERIVED t1 system NULL NULL NULL NULL 1 100.00 +3 RECURSIVE UNION t1 system NULL NULL NULL NULL 1 100.00 +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL +4 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 +4 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +5 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 +5 DERIVED t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 with recursive cte as (select year(`test`.`t1`.`d1`) AS `YEAR`,`test`.`t1`.`d1` AS `st`,`test`.`t1`.`d1` + interval 1 month AS `fn` from `test`.`t1` union all select year(`cte`.`st` + interval 1 month) AS `YEAR(cte.st + INTERVAL 1 MONTH)`,`cte`.`st` + interval 1 month AS `cte.st + INTERVAL 1 MONTH`,`test`.`t1`.`d2` + interval 1 day AS `t1.d2 + INTERVAL 1 DAY` from `cte` join `test`.`t1` where `cte`.`st` + interval 1 month < `test`.`t1`.`d2`), cte2 as (select `cte`.`YEAR` AS `YEAR`,count(0) AS `COUNT(*)` from `cte` join `test`.`t2` where `test`.`t2`.`tm` between `cte`.`st` and `cte`.`fn`), cte3 as (select `cte`.`YEAR` AS `YEAR`,count(0) AS `COUNT(*)` from `cte` join `test`.`t3` where `test`.`t3`.`tm` between `cte`.`st` and `cte`.`fn`)select 2018 AS `YEAR`,'2018-01-01' AS `d1`,'2018-09-20' AS `d2` from `cte2` join `cte3` where 2018 = `cte3`.`YEAR` and 2018 = `cte2`.`YEAR` +PREPARE stmt FROM "WITH RECURSIVE +cte AS +(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn +FROM t1 +UNION ALL +SELECT YEAR(cte.st + INTERVAL 1 MONTH), +cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY +FROM cte JOIN t1 +WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ), +cte2 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), +cte3 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) +SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR)"; +EXECUTE stmt; +YEAR d1 d2 +2018 2018-01-01 2018-09-20 +EXECUTE stmt; +YEAR d1 d2 +2018 2018-01-01 2018-09-20 +CREATE TABLE t4 (YEAR int(4), d1 date , d2 date); +CREATE PROCEDURE p() INSERT INTO t4 WITH RECURSIVE +cte AS +(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn +FROM t1 +UNION ALL +SELECT YEAR(cte.st + INTERVAL 1 MONTH), +cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY +FROM cte JOIN t1 +WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ), +cte2 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), +cte3 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) +SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR); +CALL p(); +SELECT * FROM t4; +YEAR d1 d2 +2018 2018-01-01 2018-09-20 +DROP PROCEDURE p; +DROP TABLE t1,t2,t3,t4; +# +# End of 10.2 tests +# diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test index 1c0280f..d190458 100644 --- a/mysql-test/t/cte_recursive.test +++ b/mysql-test/t/cte_recursive.test @@ -2390,6 +2390,30 @@ select * from cte1, cte2 where cte1.c1 = 3; eval $q3; +let $q4= +with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte2, cte1; + +eval $q4; +eval explain extended $q4; +eval prepare stmt from "$q4"; +execute stmt; +execute stmt; + +drop procedure p; +drop table t2; + +create table t2 (c1 int, c2 int); +eval create procedure p() insert into t2 $q4; +call p(); +select * from t2; + drop procedure p; drop table t1,t2; @@ -2574,4 +2598,48 @@ eval analyze format=json $q; drop function f1; drop table t1,t2; ---echo End of 10.2 tests +--echo # +--echo # MDEV-22748: two materialized CTEs using the same recursive CTE +--echo # (see also test case for MDEV-17024) +--echo # + +CREATE TABLE t1 (YEAR int(4), d1 date , d2 date) ; +INSERT INTO t1 VALUES (2018,'2018-01-01','2018-09-20'); +CREATE TABLE t2 (id int, tm date); +INSERT INTO t2 VALUES (1,'2018-08-30'),(2,'2018-08-30'),(3,'2018-08-30'); +CREATE TABLE t3 (id int, tm date); +INSERT INTO t3 VALUES (1,'2018-08-30'),(2,'2018-08-30'); + +let $q= +WITH RECURSIVE +cte AS + (SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn + FROM t1 + UNION ALL + SELECT YEAR(cte.st + INTERVAL 1 MONTH), + cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY + FROM cte JOIN t1 + WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ), +cte2 AS (SELECT YEAR, COUNT(*) + FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), +cte3 AS (SELECT YEAR, COUNT(*) + FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) +SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR); + +eval $q; +eval EXPLAIN EXTENDED $q; +eval PREPARE stmt FROM "$q"; +EXECUTE stmt; +EXECUTE stmt; + +CREATE TABLE t4 (YEAR int(4), d1 date , d2 date); +eval CREATE PROCEDURE p() INSERT INTO t4 $q; +CALL p(); +SELECT * FROM t4; + +DROP PROCEDURE p; +DROP TABLE t1,t2,t3,t4; + +--echo # +--echo # End of 10.2 tests +--echo # diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc index d922a7a..fe8e0de 100644 --- a/sql/sql_cte.cc +++ b/sql/sql_cte.cc @@ -1099,6 +1099,7 @@ bool TABLE_LIST::set_as_with_table(THD *thd, With_element *with_elem) { derived= with_elem->spec; if (derived != select_lex->master_unit() && + !with_elem->is_recursive && !is_with_table_recursive_reference()) { derived->move_as_slave(select_lex); diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index 6785bf8..39499e6 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -1054,7 +1054,6 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived) DBUG_ASSERT(derived->table && derived->table->is_created()); select_union *derived_result= derived->derived_result; SELECT_LEX *save_current_select= lex->current_select; - bool derived_recursive_is_filled= false; if (derived_is_recursive) { @@ -1067,7 +1066,6 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived) { /* In this case all iteration are performed */ res= derived->fill_recursive(thd); - derived_recursive_is_filled= true; } } else if (unit->is_union()) @@ -1123,9 +1121,7 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived) } } - if (res || (!lex->describe && - (!derived_is_recursive || - derived_recursive_is_filled))) + if (res || (!lex->describe && !derived_is_recursive)) unit->cleanup(); lex->current_select= save_current_select; diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 7e4d06b..b2198eb 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -1540,6 +1540,19 @@ bool st_select_lex::cleanup() delete join; join= 0; } + for (TABLE_LIST *tbl= get_table_list(); tbl; tbl= tbl->next_local) + { + if (tbl->is_recursive_with_table() && + !tbl->is_with_table_recursive_reference()) + { + /* + If query is killed before open_and_process_table() for tbl + is called then 'with' is already set, but 'derived' is not. + */ + st_select_lex_unit *unit= tbl->with->spec; + error|= (bool) error | (uint) unit->cleanup(); + } + } for (SELECT_LEX_UNIT *lex_unit= first_inner_unit(); lex_unit ; lex_unit= lex_unit->next_unit()) {

1 0

[Commits] 5aa4431: MDEV-22748 MariaDB crash on WITH RECURSIVE large query
by IgorBabaev 06 Jun '20

06 Jun '20

revision-id: 5aa4431160f9fb9ff96f46f7b898542445d985cd (mariadb-10.2.31-263-g5aa4431) parent(s): 187b9c924ebaff8f02fb4e2139a01fd1512e3dc9 author: Igor Babaev committer: Igor Babaev timestamp: 2020-06-06 10:41:08 -0700 message: MDEV-22748 MariaDB crash on WITH RECURSIVE large query This bug is the same as the bug MDEV-17024. The crashes caused by these bugs were due to premature cleanups of the unit specifying recursive CTEs that happened in some cases when there were several outer references the same recursive CTE. The problem of premature cleanups for recursive CTEs could be already resolved by the correction in TABLE_LIST::set_as_with_table() introduced in this patch. ALL other changes introduced by the patches for MDEV-17024 and MDEV-22748 guarantee that this clean-ups are performed as soon as possible: when the select containing the last outer reference to a recursive CTE is being cleaned up the specification of the recursive CTE should be cleaned up as well. --- mysql-test/r/cte_recursive.result | 170 ++++++++++++++++++++++++++++++++++++-- mysql-test/t/cte_recursive.test | 70 +++++++++++++++- sql/sql_cte.cc | 1 + sql/sql_derived.cc | 6 +- sql/sql_union.cc | 13 +++ 5 files changed, 247 insertions(+), 13 deletions(-) diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result index 0b8bc3f..6404931 100644 --- a/mysql-test/r/cte_recursive.result +++ b/mysql-test/r/cte_recursive.result @@ -691,13 +691,13 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where 1 PRIMARY <derived3> ref key0 key0 5 c.h_id 2 100.00 1 PRIMARY <derived3> ref key0 key0 5 c.w_id 2 100.00 -2 DERIVED <derived3> ALL NULL NULL NULL NULL 12 100.00 Using where 3 DERIVED folks ALL NULL NULL NULL NULL 12 100.00 Using where 4 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 4 RECURSIVE UNION p ALL NULL NULL NULL NULL 12 100.00 Using where; Using join buffer (flat, BNL join) 5 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 5 RECURSIVE UNION p ALL NULL NULL NULL NULL 12 100.00 Using where; Using join buffer (flat, BNL join) NULL UNION RESULT <union3,4,5> ALL NULL NULL NULL NULL NULL NULL +2 DERIVED <derived3> ALL NULL NULL NULL NULL 12 100.00 Using where Warnings: Note 1003 with recursive ancestor_couple_ids as (select `a`.`father` AS `h_id`,`a`.`mother` AS `w_id` from `coupled_ancestors` `a` where `a`.`father` is not null and `a`.`mother` is not null), coupled_ancestors as (select `test`.`folks`.`id` AS `id`,`test`.`folks`.`name` AS `name`,`test`.`folks`.`dob` AS `dob`,`test`.`folks`.`father` AS `father`,`test`.`folks`.`mother` AS `mother` from `test`.`folks` where `test`.`folks`.`name` = 'Me' union all select `test`.`p`.`id` AS `id`,`test`.`p`.`name` AS `name`,`test`.`p`.`dob` AS `dob`,`test`.`p`.`father` AS `father`,`test`.`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestor_couple_ids` `fa` where `test`.`p`.`id` = `fa`.`h_id` union all select `test`.`p`.`id` AS `id`,`test`.`p`.`name` AS `name`,`test`.`p`.`dob` AS `dob`,`test`.`p`.`father` AS `father`,`test`.`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestor_couple_ids` `ma` where `test`.`p`.`id` = `ma`.`w_id`)select `h`.`name` AS `name`,`h`.`dob` AS `dob`,`w`.`name ` AS `name`,`w`.`dob` AS `dob` from `ancestor_couple_ids` `c` join `coupled_ancestors` `h` join `coupled_ancestors` `w` where `h`.`id` = `c`.`h_id` and `w`.`id` = `c`.`w_id` # simple mutual recursion @@ -1302,12 +1302,12 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived4> ALL NULL NULL NULL NULL 24 4 DERIVED folks ALL NULL NULL NULL NULL 12 Using where 6 RECURSIVE UNION <derived3> ALL NULL NULL NULL NULL 12 +5 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 24 +NULL UNION RESULT <union4,6,5> ALL NULL NULL NULL NULL NULL 3 DERIVED folks ALL NULL NULL NULL NULL 12 Using where 2 RECURSIVE UNION folks ALL PRIMARY NULL NULL NULL 12 2 RECURSIVE UNION <derived3> ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) NULL UNION RESULT <union3,2> ALL NULL NULL NULL NULL NULL -5 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 24 -NULL UNION RESULT <union4,6,5> ALL NULL NULL NULL NULL NULL explain FORMAT=JSON with recursive prev_gen @@ -3326,13 +3326,13 @@ select * from cte1, cte2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived4> ALL NULL NULL NULL NULL 6 100.00 1 PRIMARY <derived5> ALL NULL NULL NULL NULL 6 100.00 Using join buffer (flat, BNL join) +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL 4 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where 4 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 5 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where 5 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) -2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used -3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where -NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 with recursive rcte as (select 1 AS `a` union select cast(`rcte`.`a` + 1 as unsigned) AS `cast(a+1 as unsigned)` from `rcte` where `rcte`.`a` < 10), cte1 as (select count(0) AS `c1` from `rcte` join `test`.`t1` where `rcte`.`a` between 3 and 5 and `test`.`t1`.`id` = `rcte`.`a` - 3), cte2 as (select count(0) AS `c2` from `rcte` join `test`.`t1` where `rcte`.`a` between 7 and 8 and `test`.`t1`.`id` = `rcte`.`a` - 7)select `cte1`.`c1` AS `c1`,`cte2`.`c2` AS `c2` from `cte1` join `cte2` prepare stmt from "with recursive @@ -3391,6 +3391,65 @@ cte2 as (select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) select * from cte1, cte2 where cte1.c1 = 3; c1 c2 +with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte2, cte1; +c2 c1 +1 2 +explain extended with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte2, cte1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived5> ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <derived4> ALL NULL NULL NULL NULL 6 100.00 Using join buffer (flat, BNL join) +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL +5 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +5 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +4 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +4 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 with recursive rcte as (select 1 AS `a` union select cast(`rcte`.`a` + 1 as unsigned) AS `cast(a+1 as unsigned)` from `rcte` where `rcte`.`a` < 10), cte1 as (select count(0) AS `c1` from `rcte` join `test`.`t1` where `rcte`.`a` between 3 and 5 and `test`.`t1`.`id` = `rcte`.`a` - 3), cte2 as (select count(0) AS `c2` from `rcte` join `test`.`t1` where `rcte`.`a` between 7 and 8 and `test`.`t1`.`id` = `rcte`.`a` - 7)select `cte2`.`c2` AS `c2`,`cte1`.`c1` AS `c1` from `cte2` join `cte1` +prepare stmt from "with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte2, cte1"; +execute stmt; +c2 c1 +1 2 +execute stmt; +c2 c1 +1 2 +drop procedure p; +drop table t2; +create table t2 (c1 int, c2 int); +create procedure p() insert into t2 with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte2, cte1; +call p(); +select * from t2; +c1 c2 +1 2 drop procedure p; drop table t1,t2; # @@ -3809,4 +3868,101 @@ ANALYZE } drop function f1; drop table t1,t2; -End of 10.2 tests +# +# MDEV-22748: two materialized CTEs using the same recursive CTE +# (see also test case for MDEV-17024) +# +CREATE TABLE t1 (YEAR int(4), d1 date , d2 date) ; +INSERT INTO t1 VALUES (2018,'2018-01-01','2018-09-20'); +CREATE TABLE t2 (id int, tm date); +INSERT INTO t2 VALUES (1,'2018-08-30'),(2,'2018-08-30'),(3,'2018-08-30'); +CREATE TABLE t3 (id int, tm date); +INSERT INTO t3 VALUES (1,'2018-08-30'),(2,'2018-08-30'); +WITH RECURSIVE +cte AS +(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn +FROM t1 +UNION ALL +SELECT YEAR(cte.st + INTERVAL 1 MONTH), +cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY +FROM cte JOIN t1 +WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ), +cte2 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), +cte3 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) +SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR); +YEAR d1 d2 +2018 2018-01-01 2018-09-20 +EXPLAIN EXTENDED WITH RECURSIVE +cte AS +(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn +FROM t1 +UNION ALL +SELECT YEAR(cte.st + INTERVAL 1 MONTH), +cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY +FROM cte JOIN t1 +WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ), +cte2 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), +cte3 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) +SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 +1 PRIMARY <derived5> ref key0 key0 9 const 0 0.00 Using where +1 PRIMARY <derived4> ref key0 key0 9 const 0 0.00 Using where +2 DERIVED t1 system NULL NULL NULL NULL 1 100.00 +3 RECURSIVE UNION t1 system NULL NULL NULL NULL 1 100.00 +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL +4 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 +4 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +5 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 +5 DERIVED t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 with recursive cte as (select year(`test`.`t1`.`d1`) AS `YEAR`,`test`.`t1`.`d1` AS `st`,`test`.`t1`.`d1` + interval 1 month AS `fn` from `test`.`t1` union all select year(`cte`.`st` + interval 1 month) AS `YEAR(cte.st + INTERVAL 1 MONTH)`,`cte`.`st` + interval 1 month AS `cte.st + INTERVAL 1 MONTH`,`test`.`t1`.`d2` + interval 1 day AS `t1.d2 + INTERVAL 1 DAY` from `cte` join `test`.`t1` where `cte`.`st` + interval 1 month < `test`.`t1`.`d2`), cte2 as (select `cte`.`YEAR` AS `YEAR`,count(0) AS `COUNT(*)` from `cte` join `test`.`t2` where `test`.`t2`.`tm` between `cte`.`st` and `cte`.`fn`), cte3 as (select `cte`.`YEAR` AS `YEAR`,count(0) AS `COUNT(*)` from `cte` join `test`.`t3` where `test`.`t3`.`tm` between `cte`.`st` and `cte`.`fn`)select 2018 AS `YEAR`,'2018-01-01' AS `d1`,'2018-09-20' AS `d2` from `cte2` join `cte3` where 2018 = `cte3`.`YEAR` and 2018 = `cte2`.`YEAR` +PREPARE stmt FROM "WITH RECURSIVE +cte AS +(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn +FROM t1 +UNION ALL +SELECT YEAR(cte.st + INTERVAL 1 MONTH), +cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY +FROM cte JOIN t1 +WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ), +cte2 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), +cte3 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) +SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR)"; +EXECUTE stmt; +YEAR d1 d2 +2018 2018-01-01 2018-09-20 +EXECUTE stmt; +YEAR d1 d2 +2018 2018-01-01 2018-09-20 +CREATE TABLE t4 (YEAR int(4), d1 date , d2 date); +CREATE PROCEDURE p() INSERT INTO t4 WITH RECURSIVE +cte AS +(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn +FROM t1 +UNION ALL +SELECT YEAR(cte.st + INTERVAL 1 MONTH), +cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY +FROM cte JOIN t1 +WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ), +cte2 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), +cte3 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) +SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR); +CALL p(); +SELECT * FROM t4; +YEAR d1 d2 +2018 2018-01-01 2018-09-20 +DROP PROCEDURE p; +DROP TABLE t1,t2,t3,t4; +# +# End of 10.2 tests +# diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test index 1c0280f..d190458 100644 --- a/mysql-test/t/cte_recursive.test +++ b/mysql-test/t/cte_recursive.test @@ -2390,6 +2390,30 @@ select * from cte1, cte2 where cte1.c1 = 3; eval $q3; +let $q4= +with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte2, cte1; + +eval $q4; +eval explain extended $q4; +eval prepare stmt from "$q4"; +execute stmt; +execute stmt; + +drop procedure p; +drop table t2; + +create table t2 (c1 int, c2 int); +eval create procedure p() insert into t2 $q4; +call p(); +select * from t2; + drop procedure p; drop table t1,t2; @@ -2574,4 +2598,48 @@ eval analyze format=json $q; drop function f1; drop table t1,t2; ---echo End of 10.2 tests +--echo # +--echo # MDEV-22748: two materialized CTEs using the same recursive CTE +--echo # (see also test case for MDEV-17024) +--echo # + +CREATE TABLE t1 (YEAR int(4), d1 date , d2 date) ; +INSERT INTO t1 VALUES (2018,'2018-01-01','2018-09-20'); +CREATE TABLE t2 (id int, tm date); +INSERT INTO t2 VALUES (1,'2018-08-30'),(2,'2018-08-30'),(3,'2018-08-30'); +CREATE TABLE t3 (id int, tm date); +INSERT INTO t3 VALUES (1,'2018-08-30'),(2,'2018-08-30'); + +let $q= +WITH RECURSIVE +cte AS + (SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn + FROM t1 + UNION ALL + SELECT YEAR(cte.st + INTERVAL 1 MONTH), + cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY + FROM cte JOIN t1 + WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ), +cte2 AS (SELECT YEAR, COUNT(*) + FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), +cte3 AS (SELECT YEAR, COUNT(*) + FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) +SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR); + +eval $q; +eval EXPLAIN EXTENDED $q; +eval PREPARE stmt FROM "$q"; +EXECUTE stmt; +EXECUTE stmt; + +CREATE TABLE t4 (YEAR int(4), d1 date , d2 date); +eval CREATE PROCEDURE p() INSERT INTO t4 $q; +CALL p(); +SELECT * FROM t4; + +DROP PROCEDURE p; +DROP TABLE t1,t2,t3,t4; + +--echo # +--echo # End of 10.2 tests +--echo # diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc index d922a7a..fe8e0de 100644 --- a/sql/sql_cte.cc +++ b/sql/sql_cte.cc @@ -1099,6 +1099,7 @@ bool TABLE_LIST::set_as_with_table(THD *thd, With_element *with_elem) { derived= with_elem->spec; if (derived != select_lex->master_unit() && + !with_elem->is_recursive && !is_with_table_recursive_reference()) { derived->move_as_slave(select_lex); diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index 6785bf8..39499e6 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -1054,7 +1054,6 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived) DBUG_ASSERT(derived->table && derived->table->is_created()); select_union *derived_result= derived->derived_result; SELECT_LEX *save_current_select= lex->current_select; - bool derived_recursive_is_filled= false; if (derived_is_recursive) { @@ -1067,7 +1066,6 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived) { /* In this case all iteration are performed */ res= derived->fill_recursive(thd); - derived_recursive_is_filled= true; } } else if (unit->is_union()) @@ -1123,9 +1121,7 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived) } } - if (res || (!lex->describe && - (!derived_is_recursive || - derived_recursive_is_filled))) + if (res || (!lex->describe && !derived_is_recursive)) unit->cleanup(); lex->current_select= save_current_select; diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 7e4d06b..b2198eb 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -1540,6 +1540,19 @@ bool st_select_lex::cleanup() delete join; join= 0; } + for (TABLE_LIST *tbl= get_table_list(); tbl; tbl= tbl->next_local) + { + if (tbl->is_recursive_with_table() && + !tbl->is_with_table_recursive_reference()) + { + /* + If query is killed before open_and_process_table() for tbl + is called then 'with' is already set, but 'derived' is not. + */ + st_select_lex_unit *unit= tbl->with->spec; + error|= (bool) error | (uint) unit->cleanup(); + } + } for (SELECT_LEX_UNIT *lex_unit= first_inner_unit(); lex_unit ; lex_unit= lex_unit->next_unit()) {

1 0

[Commits] 04b3134: MDEV-22748 MariaDB crash on WITH RECURSIVE large query
by IgorBabaev 06 Jun '20

06 Jun '20

revision-id: 04b31342547907c9b44632510e99c0126a5879ae (mariadb-10.2.31-259-g04b3134) parent(s): a8c200c73c96d35b9e58fdcde0987f0ac5cb9b1a author: Igor Babaev committer: Igor Babaev timestamp: 2020-06-05 22:15:04 -0700 message: MDEV-22748 MariaDB crash on WITH RECURSIVE large query This bug is the same as the bug MDEV-17024. The crashes caused by these bugs were due to premature cleanups of the unit specifying recursive CTEs that happened in some cases when there were several outer references the same recursive CTE. The problem of premature cleanups for recursive CTEs could be already resolved by the correction in TABLE_LIST::set_as_with_table() introduced in this patch. ALL other changes introduced by the patches for MDEV-17024 and MDEV-22748 guarantee that this clean-ups are performed as soon as possible: when the select containing the last outer reference to a recursive CTE is being cleaned up the specification of the recursive CTE should be cleaned up as well. --- mysql-test/r/cte_recursive.result | 170 ++++++++++++++++++++++++++++++++++++-- mysql-test/t/cte_recursive.test | 70 +++++++++++++++- sql/sql_cte.cc | 1 + sql/sql_derived.cc | 6 +- sql/sql_union.cc | 13 +++ 5 files changed, 247 insertions(+), 13 deletions(-) diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result index 0b8bc3f..6404931 100644 --- a/mysql-test/r/cte_recursive.result +++ b/mysql-test/r/cte_recursive.result @@ -691,13 +691,13 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where 1 PRIMARY <derived3> ref key0 key0 5 c.h_id 2 100.00 1 PRIMARY <derived3> ref key0 key0 5 c.w_id 2 100.00 -2 DERIVED <derived3> ALL NULL NULL NULL NULL 12 100.00 Using where 3 DERIVED folks ALL NULL NULL NULL NULL 12 100.00 Using where 4 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 4 RECURSIVE UNION p ALL NULL NULL NULL NULL 12 100.00 Using where; Using join buffer (flat, BNL join) 5 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 5 RECURSIVE UNION p ALL NULL NULL NULL NULL 12 100.00 Using where; Using join buffer (flat, BNL join) NULL UNION RESULT <union3,4,5> ALL NULL NULL NULL NULL NULL NULL +2 DERIVED <derived3> ALL NULL NULL NULL NULL 12 100.00 Using where Warnings: Note 1003 with recursive ancestor_couple_ids as (select `a`.`father` AS `h_id`,`a`.`mother` AS `w_id` from `coupled_ancestors` `a` where `a`.`father` is not null and `a`.`mother` is not null), coupled_ancestors as (select `test`.`folks`.`id` AS `id`,`test`.`folks`.`name` AS `name`,`test`.`folks`.`dob` AS `dob`,`test`.`folks`.`father` AS `father`,`test`.`folks`.`mother` AS `mother` from `test`.`folks` where `test`.`folks`.`name` = 'Me' union all select `test`.`p`.`id` AS `id`,`test`.`p`.`name` AS `name`,`test`.`p`.`dob` AS `dob`,`test`.`p`.`father` AS `father`,`test`.`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestor_couple_ids` `fa` where `test`.`p`.`id` = `fa`.`h_id` union all select `test`.`p`.`id` AS `id`,`test`.`p`.`name` AS `name`,`test`.`p`.`dob` AS `dob`,`test`.`p`.`father` AS `father`,`test`.`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestor_couple_ids` `ma` where `test`.`p`.`id` = `ma`.`w_id`)select `h`.`name` AS `name`,`h`.`dob` AS `dob`,`w`.`name ` AS `name`,`w`.`dob` AS `dob` from `ancestor_couple_ids` `c` join `coupled_ancestors` `h` join `coupled_ancestors` `w` where `h`.`id` = `c`.`h_id` and `w`.`id` = `c`.`w_id` # simple mutual recursion @@ -1302,12 +1302,12 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived4> ALL NULL NULL NULL NULL 24 4 DERIVED folks ALL NULL NULL NULL NULL 12 Using where 6 RECURSIVE UNION <derived3> ALL NULL NULL NULL NULL 12 +5 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 24 +NULL UNION RESULT <union4,6,5> ALL NULL NULL NULL NULL NULL 3 DERIVED folks ALL NULL NULL NULL NULL 12 Using where 2 RECURSIVE UNION folks ALL PRIMARY NULL NULL NULL 12 2 RECURSIVE UNION <derived3> ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) NULL UNION RESULT <union3,2> ALL NULL NULL NULL NULL NULL -5 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 24 -NULL UNION RESULT <union4,6,5> ALL NULL NULL NULL NULL NULL explain FORMAT=JSON with recursive prev_gen @@ -3326,13 +3326,13 @@ select * from cte1, cte2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived4> ALL NULL NULL NULL NULL 6 100.00 1 PRIMARY <derived5> ALL NULL NULL NULL NULL 6 100.00 Using join buffer (flat, BNL join) +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL 4 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where 4 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 5 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where 5 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) -2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used -3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where -NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 with recursive rcte as (select 1 AS `a` union select cast(`rcte`.`a` + 1 as unsigned) AS `cast(a+1 as unsigned)` from `rcte` where `rcte`.`a` < 10), cte1 as (select count(0) AS `c1` from `rcte` join `test`.`t1` where `rcte`.`a` between 3 and 5 and `test`.`t1`.`id` = `rcte`.`a` - 3), cte2 as (select count(0) AS `c2` from `rcte` join `test`.`t1` where `rcte`.`a` between 7 and 8 and `test`.`t1`.`id` = `rcte`.`a` - 7)select `cte1`.`c1` AS `c1`,`cte2`.`c2` AS `c2` from `cte1` join `cte2` prepare stmt from "with recursive @@ -3391,6 +3391,65 @@ cte2 as (select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) select * from cte1, cte2 where cte1.c1 = 3; c1 c2 +with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte2, cte1; +c2 c1 +1 2 +explain extended with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte2, cte1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived5> ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <derived4> ALL NULL NULL NULL NULL 6 100.00 Using join buffer (flat, BNL join) +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL +5 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +5 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +4 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +4 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 with recursive rcte as (select 1 AS `a` union select cast(`rcte`.`a` + 1 as unsigned) AS `cast(a+1 as unsigned)` from `rcte` where `rcte`.`a` < 10), cte1 as (select count(0) AS `c1` from `rcte` join `test`.`t1` where `rcte`.`a` between 3 and 5 and `test`.`t1`.`id` = `rcte`.`a` - 3), cte2 as (select count(0) AS `c2` from `rcte` join `test`.`t1` where `rcte`.`a` between 7 and 8 and `test`.`t1`.`id` = `rcte`.`a` - 7)select `cte2`.`c2` AS `c2`,`cte1`.`c1` AS `c1` from `cte2` join `cte1` +prepare stmt from "with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte2, cte1"; +execute stmt; +c2 c1 +1 2 +execute stmt; +c2 c1 +1 2 +drop procedure p; +drop table t2; +create table t2 (c1 int, c2 int); +create procedure p() insert into t2 with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte2, cte1; +call p(); +select * from t2; +c1 c2 +1 2 drop procedure p; drop table t1,t2; # @@ -3809,4 +3868,101 @@ ANALYZE } drop function f1; drop table t1,t2; -End of 10.2 tests +# +# MDEV-22748: two materialized CTEs using the same recursive CTE +# (see also test case for MDEV-17024) +# +CREATE TABLE t1 (YEAR int(4), d1 date , d2 date) ; +INSERT INTO t1 VALUES (2018,'2018-01-01','2018-09-20'); +CREATE TABLE t2 (id int, tm date); +INSERT INTO t2 VALUES (1,'2018-08-30'),(2,'2018-08-30'),(3,'2018-08-30'); +CREATE TABLE t3 (id int, tm date); +INSERT INTO t3 VALUES (1,'2018-08-30'),(2,'2018-08-30'); +WITH RECURSIVE +cte AS +(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn +FROM t1 +UNION ALL +SELECT YEAR(cte.st + INTERVAL 1 MONTH), +cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY +FROM cte JOIN t1 +WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ), +cte2 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), +cte3 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) +SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR); +YEAR d1 d2 +2018 2018-01-01 2018-09-20 +EXPLAIN EXTENDED WITH RECURSIVE +cte AS +(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn +FROM t1 +UNION ALL +SELECT YEAR(cte.st + INTERVAL 1 MONTH), +cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY +FROM cte JOIN t1 +WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ), +cte2 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), +cte3 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) +SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 +1 PRIMARY <derived5> ref key0 key0 9 const 0 0.00 Using where +1 PRIMARY <derived4> ref key0 key0 9 const 0 0.00 Using where +2 DERIVED t1 system NULL NULL NULL NULL 1 100.00 +3 RECURSIVE UNION t1 system NULL NULL NULL NULL 1 100.00 +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL +4 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 +4 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +5 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 +5 DERIVED t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 with recursive cte as (select year(`test`.`t1`.`d1`) AS `YEAR`,`test`.`t1`.`d1` AS `st`,`test`.`t1`.`d1` + interval 1 month AS `fn` from `test`.`t1` union all select year(`cte`.`st` + interval 1 month) AS `YEAR(cte.st + INTERVAL 1 MONTH)`,`cte`.`st` + interval 1 month AS `cte.st + INTERVAL 1 MONTH`,`test`.`t1`.`d2` + interval 1 day AS `t1.d2 + INTERVAL 1 DAY` from `cte` join `test`.`t1` where `cte`.`st` + interval 1 month < `test`.`t1`.`d2`), cte2 as (select `cte`.`YEAR` AS `YEAR`,count(0) AS `COUNT(*)` from `cte` join `test`.`t2` where `test`.`t2`.`tm` between `cte`.`st` and `cte`.`fn`), cte3 as (select `cte`.`YEAR` AS `YEAR`,count(0) AS `COUNT(*)` from `cte` join `test`.`t3` where `test`.`t3`.`tm` between `cte`.`st` and `cte`.`fn`)select 2018 AS `YEAR`,'2018-01-01' AS `d1`,'2018-09-20' AS `d2` from `cte2` join `cte3` where 2018 = `cte3`.`YEAR` and 2018 = `cte2`.`YEAR` +PREPARE stmt FROM "WITH RECURSIVE +cte AS +(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn +FROM t1 +UNION ALL +SELECT YEAR(cte.st + INTERVAL 1 MONTH), +cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY +FROM cte JOIN t1 +WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ), +cte2 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), +cte3 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) +SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR)"; +EXECUTE stmt; +YEAR d1 d2 +2018 2018-01-01 2018-09-20 +EXECUTE stmt; +YEAR d1 d2 +2018 2018-01-01 2018-09-20 +CREATE TABLE t4 (YEAR int(4), d1 date , d2 date); +CREATE PROCEDURE p() INSERT INTO t4 WITH RECURSIVE +cte AS +(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn +FROM t1 +UNION ALL +SELECT YEAR(cte.st + INTERVAL 1 MONTH), +cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY +FROM cte JOIN t1 +WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ), +cte2 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), +cte3 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) +SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR); +CALL p(); +SELECT * FROM t4; +YEAR d1 d2 +2018 2018-01-01 2018-09-20 +DROP PROCEDURE p; +DROP TABLE t1,t2,t3,t4; +# +# End of 10.2 tests +# diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test index 1c0280f..d190458 100644 --- a/mysql-test/t/cte_recursive.test +++ b/mysql-test/t/cte_recursive.test @@ -2390,6 +2390,30 @@ select * from cte1, cte2 where cte1.c1 = 3; eval $q3; +let $q4= +with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte2, cte1; + +eval $q4; +eval explain extended $q4; +eval prepare stmt from "$q4"; +execute stmt; +execute stmt; + +drop procedure p; +drop table t2; + +create table t2 (c1 int, c2 int); +eval create procedure p() insert into t2 $q4; +call p(); +select * from t2; + drop procedure p; drop table t1,t2; @@ -2574,4 +2598,48 @@ eval analyze format=json $q; drop function f1; drop table t1,t2; ---echo End of 10.2 tests +--echo # +--echo # MDEV-22748: two materialized CTEs using the same recursive CTE +--echo # (see also test case for MDEV-17024) +--echo # + +CREATE TABLE t1 (YEAR int(4), d1 date , d2 date) ; +INSERT INTO t1 VALUES (2018,'2018-01-01','2018-09-20'); +CREATE TABLE t2 (id int, tm date); +INSERT INTO t2 VALUES (1,'2018-08-30'),(2,'2018-08-30'),(3,'2018-08-30'); +CREATE TABLE t3 (id int, tm date); +INSERT INTO t3 VALUES (1,'2018-08-30'),(2,'2018-08-30'); + +let $q= +WITH RECURSIVE +cte AS + (SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn + FROM t1 + UNION ALL + SELECT YEAR(cte.st + INTERVAL 1 MONTH), + cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY + FROM cte JOIN t1 + WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ), +cte2 AS (SELECT YEAR, COUNT(*) + FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), +cte3 AS (SELECT YEAR, COUNT(*) + FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) +SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR); + +eval $q; +eval EXPLAIN EXTENDED $q; +eval PREPARE stmt FROM "$q"; +EXECUTE stmt; +EXECUTE stmt; + +CREATE TABLE t4 (YEAR int(4), d1 date , d2 date); +eval CREATE PROCEDURE p() INSERT INTO t4 $q; +CALL p(); +SELECT * FROM t4; + +DROP PROCEDURE p; +DROP TABLE t1,t2,t3,t4; + +--echo # +--echo # End of 10.2 tests +--echo # diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc index d922a7a..fe8e0de 100644 --- a/sql/sql_cte.cc +++ b/sql/sql_cte.cc @@ -1099,6 +1099,7 @@ bool TABLE_LIST::set_as_with_table(THD *thd, With_element *with_elem) { derived= with_elem->spec; if (derived != select_lex->master_unit() && + !with_elem->is_recursive && !is_with_table_recursive_reference()) { derived->move_as_slave(select_lex); diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index 6785bf8..39499e6 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -1054,7 +1054,6 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived) DBUG_ASSERT(derived->table && derived->table->is_created()); select_union *derived_result= derived->derived_result; SELECT_LEX *save_current_select= lex->current_select; - bool derived_recursive_is_filled= false; if (derived_is_recursive) { @@ -1067,7 +1066,6 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived) { /* In this case all iteration are performed */ res= derived->fill_recursive(thd); - derived_recursive_is_filled= true; } } else if (unit->is_union()) @@ -1123,9 +1121,7 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived) } } - if (res || (!lex->describe && - (!derived_is_recursive || - derived_recursive_is_filled))) + if (res || (!lex->describe && !derived_is_recursive)) unit->cleanup(); lex->current_select= save_current_select; diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 7e4d06b..b2198eb 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -1540,6 +1540,19 @@ bool st_select_lex::cleanup() delete join; join= 0; } + for (TABLE_LIST *tbl= get_table_list(); tbl; tbl= tbl->next_local) + { + if (tbl->is_recursive_with_table() && + !tbl->is_with_table_recursive_reference()) + { + /* + If query is killed before open_and_process_table() for tbl + is called then 'with' is already set, but 'derived' is not. + */ + st_select_lex_unit *unit= tbl->with->spec; + error|= (bool) error | (uint) unit->cleanup(); + } + } for (SELECT_LEX_UNIT *lex_unit= first_inner_unit(); lex_unit ; lex_unit= lex_unit->next_unit()) {

1 0

[Commits] 238c500: MDEV-22748 MariaDB crash on WITH RECURSIVE large query
by IgorBabaev 05 Jun '20

05 Jun '20

revision-id: 238c500f077ed7a77330794defd36bc96d1ba030 (mariadb-10.2.31-259-g238c500) parent(s): a8c200c73c96d35b9e58fdcde0987f0ac5cb9b1a author: Igor Babaev committer: Igor Babaev timestamp: 2020-06-05 13:11:28 -0700 message: MDEV-22748 MariaDB crash on WITH RECURSIVE large query This bug is the same as the bug MDEV-17024. The crashes caused by these bugs were due to premature cleanups of the unit specifying recursive CTEs that happened in some cases when there were several outer references the same recursive CTE. The problem of premature cleanups for recursive CTEs could be already resolved by the correction in TABLE_LIST::set_as_with_table() introduced in this patch. ALL other changes introduced by the patches for MDEV-17024 and MDEV-22748 guarantee that this clean-ups are performed as soon as possible: when the select containing the last outer reference to a recursive CTE is being cleaned up the specification of the recursive CTE should be cleaned up as well. --- mysql-test/r/cte_recursive.result | 170 ++++++++++++++++++++++++++++++++++++-- mysql-test/t/cte_recursive.test | 70 +++++++++++++++- sql/sql_cte.cc | 1 + sql/sql_derived.cc | 6 +- sql/sql_union.cc | 6 ++ 5 files changed, 240 insertions(+), 13 deletions(-) diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result index 0b8bc3f..6404931 100644 --- a/mysql-test/r/cte_recursive.result +++ b/mysql-test/r/cte_recursive.result @@ -691,13 +691,13 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where 1 PRIMARY <derived3> ref key0 key0 5 c.h_id 2 100.00 1 PRIMARY <derived3> ref key0 key0 5 c.w_id 2 100.00 -2 DERIVED <derived3> ALL NULL NULL NULL NULL 12 100.00 Using where 3 DERIVED folks ALL NULL NULL NULL NULL 12 100.00 Using where 4 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 4 RECURSIVE UNION p ALL NULL NULL NULL NULL 12 100.00 Using where; Using join buffer (flat, BNL join) 5 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 5 RECURSIVE UNION p ALL NULL NULL NULL NULL 12 100.00 Using where; Using join buffer (flat, BNL join) NULL UNION RESULT <union3,4,5> ALL NULL NULL NULL NULL NULL NULL +2 DERIVED <derived3> ALL NULL NULL NULL NULL 12 100.00 Using where Warnings: Note 1003 with recursive ancestor_couple_ids as (select `a`.`father` AS `h_id`,`a`.`mother` AS `w_id` from `coupled_ancestors` `a` where `a`.`father` is not null and `a`.`mother` is not null), coupled_ancestors as (select `test`.`folks`.`id` AS `id`,`test`.`folks`.`name` AS `name`,`test`.`folks`.`dob` AS `dob`,`test`.`folks`.`father` AS `father`,`test`.`folks`.`mother` AS `mother` from `test`.`folks` where `test`.`folks`.`name` = 'Me' union all select `test`.`p`.`id` AS `id`,`test`.`p`.`name` AS `name`,`test`.`p`.`dob` AS `dob`,`test`.`p`.`father` AS `father`,`test`.`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestor_couple_ids` `fa` where `test`.`p`.`id` = `fa`.`h_id` union all select `test`.`p`.`id` AS `id`,`test`.`p`.`name` AS `name`,`test`.`p`.`dob` AS `dob`,`test`.`p`.`father` AS `father`,`test`.`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestor_couple_ids` `ma` where `test`.`p`.`id` = `ma`.`w_id`)select `h`.`name` AS `name`,`h`.`dob` AS `dob`,`w`.`name ` AS `name`,`w`.`dob` AS `dob` from `ancestor_couple_ids` `c` join `coupled_ancestors` `h` join `coupled_ancestors` `w` where `h`.`id` = `c`.`h_id` and `w`.`id` = `c`.`w_id` # simple mutual recursion @@ -1302,12 +1302,12 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived4> ALL NULL NULL NULL NULL 24 4 DERIVED folks ALL NULL NULL NULL NULL 12 Using where 6 RECURSIVE UNION <derived3> ALL NULL NULL NULL NULL 12 +5 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 24 +NULL UNION RESULT <union4,6,5> ALL NULL NULL NULL NULL NULL 3 DERIVED folks ALL NULL NULL NULL NULL 12 Using where 2 RECURSIVE UNION folks ALL PRIMARY NULL NULL NULL 12 2 RECURSIVE UNION <derived3> ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) NULL UNION RESULT <union3,2> ALL NULL NULL NULL NULL NULL -5 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 24 -NULL UNION RESULT <union4,6,5> ALL NULL NULL NULL NULL NULL explain FORMAT=JSON with recursive prev_gen @@ -3326,13 +3326,13 @@ select * from cte1, cte2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived4> ALL NULL NULL NULL NULL 6 100.00 1 PRIMARY <derived5> ALL NULL NULL NULL NULL 6 100.00 Using join buffer (flat, BNL join) +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL 4 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where 4 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 5 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where 5 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) -2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used -3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where -NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 with recursive rcte as (select 1 AS `a` union select cast(`rcte`.`a` + 1 as unsigned) AS `cast(a+1 as unsigned)` from `rcte` where `rcte`.`a` < 10), cte1 as (select count(0) AS `c1` from `rcte` join `test`.`t1` where `rcte`.`a` between 3 and 5 and `test`.`t1`.`id` = `rcte`.`a` - 3), cte2 as (select count(0) AS `c2` from `rcte` join `test`.`t1` where `rcte`.`a` between 7 and 8 and `test`.`t1`.`id` = `rcte`.`a` - 7)select `cte1`.`c1` AS `c1`,`cte2`.`c2` AS `c2` from `cte1` join `cte2` prepare stmt from "with recursive @@ -3391,6 +3391,65 @@ cte2 as (select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) select * from cte1, cte2 where cte1.c1 = 3; c1 c2 +with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte2, cte1; +c2 c1 +1 2 +explain extended with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte2, cte1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived5> ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <derived4> ALL NULL NULL NULL NULL 6 100.00 Using join buffer (flat, BNL join) +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL +5 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +5 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +4 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +4 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 with recursive rcte as (select 1 AS `a` union select cast(`rcte`.`a` + 1 as unsigned) AS `cast(a+1 as unsigned)` from `rcte` where `rcte`.`a` < 10), cte1 as (select count(0) AS `c1` from `rcte` join `test`.`t1` where `rcte`.`a` between 3 and 5 and `test`.`t1`.`id` = `rcte`.`a` - 3), cte2 as (select count(0) AS `c2` from `rcte` join `test`.`t1` where `rcte`.`a` between 7 and 8 and `test`.`t1`.`id` = `rcte`.`a` - 7)select `cte2`.`c2` AS `c2`,`cte1`.`c1` AS `c1` from `cte2` join `cte1` +prepare stmt from "with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte2, cte1"; +execute stmt; +c2 c1 +1 2 +execute stmt; +c2 c1 +1 2 +drop procedure p; +drop table t2; +create table t2 (c1 int, c2 int); +create procedure p() insert into t2 with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte2, cte1; +call p(); +select * from t2; +c1 c2 +1 2 drop procedure p; drop table t1,t2; # @@ -3809,4 +3868,101 @@ ANALYZE } drop function f1; drop table t1,t2; -End of 10.2 tests +# +# MDEV-22748: two materialized CTEs using the same recursive CTE +# (see also test case for MDEV-17024) +# +CREATE TABLE t1 (YEAR int(4), d1 date , d2 date) ; +INSERT INTO t1 VALUES (2018,'2018-01-01','2018-09-20'); +CREATE TABLE t2 (id int, tm date); +INSERT INTO t2 VALUES (1,'2018-08-30'),(2,'2018-08-30'),(3,'2018-08-30'); +CREATE TABLE t3 (id int, tm date); +INSERT INTO t3 VALUES (1,'2018-08-30'),(2,'2018-08-30'); +WITH RECURSIVE +cte AS +(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn +FROM t1 +UNION ALL +SELECT YEAR(cte.st + INTERVAL 1 MONTH), +cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY +FROM cte JOIN t1 +WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ), +cte2 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), +cte3 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) +SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR); +YEAR d1 d2 +2018 2018-01-01 2018-09-20 +EXPLAIN EXTENDED WITH RECURSIVE +cte AS +(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn +FROM t1 +UNION ALL +SELECT YEAR(cte.st + INTERVAL 1 MONTH), +cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY +FROM cte JOIN t1 +WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ), +cte2 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), +cte3 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) +SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 +1 PRIMARY <derived5> ref key0 key0 9 const 0 0.00 Using where +1 PRIMARY <derived4> ref key0 key0 9 const 0 0.00 Using where +2 DERIVED t1 system NULL NULL NULL NULL 1 100.00 +3 RECURSIVE UNION t1 system NULL NULL NULL NULL 1 100.00 +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL +4 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 +4 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +5 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 +5 DERIVED t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 with recursive cte as (select year(`test`.`t1`.`d1`) AS `YEAR`,`test`.`t1`.`d1` AS `st`,`test`.`t1`.`d1` + interval 1 month AS `fn` from `test`.`t1` union all select year(`cte`.`st` + interval 1 month) AS `YEAR(cte.st + INTERVAL 1 MONTH)`,`cte`.`st` + interval 1 month AS `cte.st + INTERVAL 1 MONTH`,`test`.`t1`.`d2` + interval 1 day AS `t1.d2 + INTERVAL 1 DAY` from `cte` join `test`.`t1` where `cte`.`st` + interval 1 month < `test`.`t1`.`d2`), cte2 as (select `cte`.`YEAR` AS `YEAR`,count(0) AS `COUNT(*)` from `cte` join `test`.`t2` where `test`.`t2`.`tm` between `cte`.`st` and `cte`.`fn`), cte3 as (select `cte`.`YEAR` AS `YEAR`,count(0) AS `COUNT(*)` from `cte` join `test`.`t3` where `test`.`t3`.`tm` between `cte`.`st` and `cte`.`fn`)select 2018 AS `YEAR`,'2018-01-01' AS `d1`,'2018-09-20' AS `d2` from `cte2` join `cte3` where 2018 = `cte3`.`YEAR` and 2018 = `cte2`.`YEAR` +PREPARE stmt FROM "WITH RECURSIVE +cte AS +(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn +FROM t1 +UNION ALL +SELECT YEAR(cte.st + INTERVAL 1 MONTH), +cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY +FROM cte JOIN t1 +WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ), +cte2 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), +cte3 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) +SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR)"; +EXECUTE stmt; +YEAR d1 d2 +2018 2018-01-01 2018-09-20 +EXECUTE stmt; +YEAR d1 d2 +2018 2018-01-01 2018-09-20 +CREATE TABLE t4 (YEAR int(4), d1 date , d2 date); +CREATE PROCEDURE p() INSERT INTO t4 WITH RECURSIVE +cte AS +(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn +FROM t1 +UNION ALL +SELECT YEAR(cte.st + INTERVAL 1 MONTH), +cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY +FROM cte JOIN t1 +WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ), +cte2 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), +cte3 AS (SELECT YEAR, COUNT(*) +FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) +SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR); +CALL p(); +SELECT * FROM t4; +YEAR d1 d2 +2018 2018-01-01 2018-09-20 +DROP PROCEDURE p; +DROP TABLE t1,t2,t3,t4; +# +# End of 10.2 tests +# diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test index 1c0280f..d190458 100644 --- a/mysql-test/t/cte_recursive.test +++ b/mysql-test/t/cte_recursive.test @@ -2390,6 +2390,30 @@ select * from cte1, cte2 where cte1.c1 = 3; eval $q3; +let $q4= +with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte2, cte1; + +eval $q4; +eval explain extended $q4; +eval prepare stmt from "$q4"; +execute stmt; +execute stmt; + +drop procedure p; +drop table t2; + +create table t2 (c1 int, c2 int); +eval create procedure p() insert into t2 $q4; +call p(); +select * from t2; + drop procedure p; drop table t1,t2; @@ -2574,4 +2598,48 @@ eval analyze format=json $q; drop function f1; drop table t1,t2; ---echo End of 10.2 tests +--echo # +--echo # MDEV-22748: two materialized CTEs using the same recursive CTE +--echo # (see also test case for MDEV-17024) +--echo # + +CREATE TABLE t1 (YEAR int(4), d1 date , d2 date) ; +INSERT INTO t1 VALUES (2018,'2018-01-01','2018-09-20'); +CREATE TABLE t2 (id int, tm date); +INSERT INTO t2 VALUES (1,'2018-08-30'),(2,'2018-08-30'),(3,'2018-08-30'); +CREATE TABLE t3 (id int, tm date); +INSERT INTO t3 VALUES (1,'2018-08-30'),(2,'2018-08-30'); + +let $q= +WITH RECURSIVE +cte AS + (SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn + FROM t1 + UNION ALL + SELECT YEAR(cte.st + INTERVAL 1 MONTH), + cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY + FROM cte JOIN t1 + WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ), +cte2 AS (SELECT YEAR, COUNT(*) + FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), +cte3 AS (SELECT YEAR, COUNT(*) + FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) +SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR); + +eval $q; +eval EXPLAIN EXTENDED $q; +eval PREPARE stmt FROM "$q"; +EXECUTE stmt; +EXECUTE stmt; + +CREATE TABLE t4 (YEAR int(4), d1 date , d2 date); +eval CREATE PROCEDURE p() INSERT INTO t4 $q; +CALL p(); +SELECT * FROM t4; + +DROP PROCEDURE p; +DROP TABLE t1,t2,t3,t4; + +--echo # +--echo # End of 10.2 tests +--echo # diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc index d922a7a..fe8e0de 100644 --- a/sql/sql_cte.cc +++ b/sql/sql_cte.cc @@ -1099,6 +1099,7 @@ bool TABLE_LIST::set_as_with_table(THD *thd, With_element *with_elem) { derived= with_elem->spec; if (derived != select_lex->master_unit() && + !with_elem->is_recursive && !is_with_table_recursive_reference()) { derived->move_as_slave(select_lex); diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index 6785bf8..39499e6 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -1054,7 +1054,6 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived) DBUG_ASSERT(derived->table && derived->table->is_created()); select_union *derived_result= derived->derived_result; SELECT_LEX *save_current_select= lex->current_select; - bool derived_recursive_is_filled= false; if (derived_is_recursive) { @@ -1067,7 +1066,6 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived) { /* In this case all iteration are performed */ res= derived->fill_recursive(thd); - derived_recursive_is_filled= true; } } else if (unit->is_union()) @@ -1123,9 +1121,7 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived) } } - if (res || (!lex->describe && - (!derived_is_recursive || - derived_recursive_is_filled))) + if (res || (!lex->describe && !derived_is_recursive)) unit->cleanup(); lex->current_select= save_current_select; diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 7e4d06b..65e03de 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -1540,6 +1540,12 @@ bool st_select_lex::cleanup() delete join; join= 0; } + for (TABLE_LIST *tbl= get_table_list(); tbl; tbl= tbl->next_local) + { + if (tbl->is_recursive_with_table() && + !tbl->is_with_table_recursive_reference()) + error|= (bool) error | (uint) tbl->get_unit()->cleanup(); + } for (SELECT_LEX_UNIT *lex_unit= first_inner_unit(); lex_unit ; lex_unit= lex_unit->next_unit()) {

1 0

[Commits] 147fd5d: MDEV-22042 Server crash in Item_field::print on ANALYZE FORMAT=JSON
by IgorBabaev 05 Jun '20

05 Jun '20

revision-id: 147fd5df70026648789740e98956d6afde055906 (mariadb-10.2.31-257-g147fd5d) parent(s): 15cdcb2af8d2ed0969e00633c6eed1f414cbd92d author: Igor Babaev committer: Igor Babaev timestamp: 2020-06-05 10:38:40 -0700 message: MDEV-22042 Server crash in Item_field::print on ANALYZE FORMAT=JSON When processing a query with a recursive CTE a temporary table is used for each recursive reference of the CTE. As any temporary table it uses its own mem-root for table definition structures. Due to specifics of the current implementation of ANALYZE stmt command this mem-root can be freed only at the very of query processing. Such deallocation of mem-root memory happens in close_thread_tables(). The function looks through the list of the tmp tables rec_tables attached to the THD of the query and frees corresponding mem-roots. If the query uses a stored function then such list is created for each query of the function. When a new rec_list has to be created the old one has to be saved and then restored at the proper moment. The bug occurred because only one rec_list for the query containing CTE was created. As a result close_thread_tables() freed tmp mem-roots used for rec_tables prematurely destroying some data needed for the output produced by the ANALYZE command. --- mysql-test/r/cte_recursive.result | 162 ++++++++++++++++++++++++++++++++++++++ mysql-test/t/cte_recursive.test | 39 +++++++++ sql/sp_head.cc | 4 + 3 files changed, 205 insertions(+) diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result index 667b8c4..0b8bc3f 100644 --- a/mysql-test/r/cte_recursive.result +++ b/mysql-test/r/cte_recursive.result @@ -3648,3 +3648,165 @@ 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; +# +# MDEV-22042: ANALYZE of query using stored function and recursive CTE +# +create table t1 (a1 varchar(20),a2 varchar(20)) engine=myisam; +insert into t1 values (1,1),(2,2),(3,3); +create table t2 ( +a2 varchar(20) primary key, b1 varchar(20), key (b1) +) engine=myisam; +insert into t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7); +insert into t2 values (11,11),(12,12),(13,13),(14,14),(15,15),(16,16),(17,17); +create function f1(id varchar(20)) returns varchar(50) +begin +declare res varchar (50); +select a2 into res from t2 where a2=id and b1=1 limit 1; +return res; +end$$ +select fv +from (select t1.a1, f1(t1.a2) fv from t1) dt +where (dt.a1) in (with recursive cte as (select a2 from t2 where a2='2' + union select tt2.a2 from t2 tt2 join cte on tt2.b1=cte.a2) +select a2 from cte); +fv +NULL +explain select fv +from (select t1.a1, f1(t1.a2) fv from t1) dt +where (dt.a1) in (with recursive cte as (select a2 from t2 where a2='2' + union select tt2.a2 from t2 tt2 join cte on tt2.b1=cte.a2) +select a2 from cte); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) +3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 +4 DERIVED t2 const PRIMARY PRIMARY 22 const 1 Using index +5 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 2 +5 RECURSIVE UNION tt2 ref b1 b1 23 cte.a2 2 +NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL +analyze format=json select fv +from (select t1.a1, f1(t1.a2) fv from t1) dt +where (dt.a1) in (with recursive cte as (select a2 from t2 where a2='2' + union select tt2.a2 from t2 tt2 join cte on tt2.b1=cte.a2) +select a2 from cte); +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "<subquery3>", + "access_type": "ALL", + "possible_keys": ["distinct_key"], + "r_loops": 1, + "rows": 2, + "r_rows": 1, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 3, + "table": { + "table_name": "<derived4>", + "access_type": "ALL", + "r_loops": 1, + "rows": 2, + "r_rows": 1, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "materialized": { + "query_block": { + "recursive_union": { + "table_name": "<union4,5>", + "access_type": "ALL", + "r_loops": 0, + "r_rows": null, + "query_specifications": [ + { + "query_block": { + "select_id": 4, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "t2", + "access_type": "const", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "22", + "used_key_parts": ["a2"], + "ref": ["const"], + "r_loops": 0, + "rows": 1, + "r_rows": null, + "filtered": 100, + "r_filtered": null, + "using_index": true + } + } + }, + { + "query_block": { + "select_id": 5, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "<derived4>", + "access_type": "ALL", + "r_loops": 1, + "rows": 2, + "r_rows": 1, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + }, + "table": { + "table_name": "tt2", + "access_type": "ref", + "possible_keys": ["b1"], + "key": "b1", + "key_length": "23", + "used_key_parts": ["b1"], + "ref": ["cte.a2"], + "r_loops": 1, + "rows": 2, + "r_rows": 1, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } + } + ] + } + } + } + } + } + } + }, + "block-nl-join": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "r_loops": 1, + "rows": 3, + "r_rows": 3, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "256Kb", + "join_type": "BNL", + "attached_condition": "t1.a1 = cte.a2", + "r_filtered": 33.333 + } + } +} +drop function f1; +drop table t1,t2; +End of 10.2 tests diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test index 2c20e09..1c0280f 100644 --- a/mysql-test/t/cte_recursive.test +++ b/mysql-test/t/cte_recursive.test @@ -2536,3 +2536,42 @@ with recursive cte as select * from t1 as t; drop table t1,t2; + +--echo # +--echo # MDEV-22042: ANALYZE of query using stored function and recursive CTE +--echo # + +create table t1 (a1 varchar(20),a2 varchar(20)) engine=myisam; +insert into t1 values (1,1),(2,2),(3,3); + +create table t2 ( +a2 varchar(20) primary key, b1 varchar(20), key (b1) +) engine=myisam; +insert into t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7); +insert into t2 values (11,11),(12,12),(13,13),(14,14),(15,15),(16,16),(17,17); + +delimiter $$; +create function f1(id varchar(20)) returns varchar(50) +begin + declare res varchar (50); + select a2 into res from t2 where a2=id and b1=1 limit 1; + return res; +end$$ +delimiter ;$$ + +let q= +select fv +from (select t1.a1, f1(t1.a2) fv from t1) dt +where (dt.a1) in (with recursive cte as (select a2 from t2 where a2='2' + union select tt2.a2 from t2 tt2 join cte on tt2.b1=cte.a2) +select a2 from cte); + +eval $q; +eval explain $q; +--source include/analyze-format.inc +eval analyze format=json $q; + +drop function f1; +drop table t1,t2; + +--echo End of 10.2 tests diff --git a/sql/sp_head.cc b/sql/sp_head.cc index 1f5c6e9..1565500 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -1123,6 +1123,7 @@ sp_head::execute(THD *thd, bool merge_da_on_success) backup_arena; query_id_t old_query_id; TABLE *old_derived_tables; + TABLE *old_rec_tables; LEX *old_lex; Item_change_list old_change_list; String old_packet; @@ -1198,6 +1199,8 @@ sp_head::execute(THD *thd, bool merge_da_on_success) old_query_id= thd->query_id; old_derived_tables= thd->derived_tables; thd->derived_tables= 0; + old_rec_tables= thd->rec_tables; + thd->rec_tables= 0; save_sql_mode= thd->variables.sql_mode; thd->variables.sql_mode= m_sql_mode; save_abort_on_warning= thd->abort_on_warning; @@ -1379,6 +1382,7 @@ sp_head::execute(THD *thd, bool merge_da_on_success) thd->set_query_id(old_query_id); DBUG_ASSERT(!thd->derived_tables); thd->derived_tables= old_derived_tables; + thd->rec_tables= old_rec_tables; thd->variables.sql_mode= save_sql_mode; thd->abort_on_warning= save_abort_on_warning; thd->m_reprepare_observer= save_reprepare_observer;

1 0