diff options
| -rw-r--r-- | mysql-test/suite/versioning/r/derived_tables.result | 129 | ||||
| -rw-r--r-- | mysql-test/suite/versioning/t/derived_tables.test | 91 | ||||
| -rw-r--r-- | sql/item.cc | 9 | ||||
| -rw-r--r-- | sql/item.h | 2 | ||||
| -rw-r--r-- | sql/sql_derived.cc | 20 | ||||
| -rw-r--r-- | sql/sql_select.cc | 28 |
6 files changed, 272 insertions, 7 deletions
diff --git a/mysql-test/suite/versioning/r/derived_tables.result b/mysql-test/suite/versioning/r/derived_tables.result new file mode 100644 index 00000000000..638f5da665a --- /dev/null +++ b/mysql-test/suite/versioning/r/derived_tables.result @@ -0,0 +1,129 @@ +create table emp +( +emp_id int, +name varchar(127), +mgr int +) with system versioning; +insert into emp values (1, 'bill', 0), +(2, 'bill', 1), +(3, 'kate', 1); +set @ts=now(6); +delete from emp; +insert into emp values (4, 'john', 1); +with ancestors as (select * from emp) select * from ancestors; +emp_id name mgr +4 john 1 +set @tmp= "with ancestors as (select * from emp) select * from ancestors"; +prepare stmt from @tmp; +execute stmt; +emp_id name mgr +4 john 1 +drop prepare stmt; +with ancestors as (select * from emp for system_time all) select * from ancestors for system_time all; +emp_id name mgr +1 bill 0 +2 bill 1 +3 kate 1 +4 john 1 +set @tmp= "with ancestors as (select * from emp for system_time all) select * from ancestors for system_time all"; +prepare stmt from @tmp; +execute stmt; +emp_id name mgr +1 bill 0 +2 bill 1 +3 kate 1 +4 john 1 +drop prepare stmt; +with recursive ancestors as (select * from emp) select * from ancestors; +emp_id name mgr +4 john 1 +set @tmp= "with recursive ancestors as (select * from emp) select * from ancestors"; +prepare stmt from @tmp; +execute stmt; +emp_id name mgr +4 john 1 +drop prepare stmt; +select emp_id from (select emp_id from emp where sys_trx_end>'2031-1-1') as tmp; +emp_id +4 +set @tmp= "select emp_id from (select emp_id from emp where sys_trx_end>'2031-1-1') as tmp"; +prepare stmt from @tmp; +execute stmt; +emp_id +4 +drop prepare stmt; +with recursive +ancestors +as +( +select e.emp_id, e.name, e.mgr +from emp as e +where name = 'john' + union +select ee.emp_id, ee.name, ee.mgr +from emp as ee, ancestors as a +where ee.mgr = a.emp_id +) +select * from ancestors; +emp_id name mgr +4 john 1 +set @tmp= " +with recursive +ancestors +as +( + select e.emp_id, e.name, e.mgr + from emp as e + where name = 'john' + union + select ee.emp_id, ee.name, ee.mgr + from emp as ee, ancestors as a + where ee.mgr = a.emp_id +) +select * from ancestors +"; +prepare stmt from @tmp; +execute stmt; +emp_id name mgr +4 john 1 +drop prepare stmt; +with recursive +ancestors +as +( +select e.emp_id, e.name, e.mgr +from emp as e for system_time as of timestamp @ts +where name = 'bill' + union +select ee.emp_id, ee.name, ee.mgr +from emp as ee for system_time as of timestamp @ts, ancestors as a for system_time as of timestamp @ts +where ee.mgr = a.emp_id +) +select * from ancestors for system_time as of timestamp @ts; +emp_id name mgr +1 bill 0 +2 bill 1 +3 kate 1 +set @tmp= " +with recursive +ancestors +as +( + select e.emp_id, e.name, e.mgr + from emp as e for system_time as of timestamp @ts + where name = 'bill' + union + select ee.emp_id, ee.name, ee.mgr + from emp as ee for system_time as of timestamp @ts, ancestors as a for system_time as of timestamp @ts + where ee.mgr = a.emp_id +) +select * from ancestors for system_time as of timestamp @ts; +"; +prepare stmt from @tmp; +execute stmt; +emp_id name mgr +1 bill 0 +2 bill 1 +3 kate 1 +drop prepare stmt; +drop table emp; diff --git a/mysql-test/suite/versioning/t/derived_tables.test b/mysql-test/suite/versioning/t/derived_tables.test new file mode 100644 index 00000000000..e847a126ea4 --- /dev/null +++ b/mysql-test/suite/versioning/t/derived_tables.test @@ -0,0 +1,91 @@ +create table emp +( + emp_id int, + name varchar(127), + mgr int +) with system versioning; + +insert into emp values (1, 'bill', 0), + (2, 'bill', 1), + (3, 'kate', 1); +set @ts=now(6); +delete from emp; +insert into emp values (4, 'john', 1); + +with ancestors as (select * from emp) select * from ancestors; +set @tmp= "with ancestors as (select * from emp) select * from ancestors"; +prepare stmt from @tmp; execute stmt; drop prepare stmt; + +with ancestors as (select * from emp for system_time all) select * from ancestors for system_time all; +set @tmp= "with ancestors as (select * from emp for system_time all) select * from ancestors for system_time all"; +prepare stmt from @tmp; execute stmt; drop prepare stmt; + +with recursive ancestors as (select * from emp) select * from ancestors; +set @tmp= "with recursive ancestors as (select * from emp) select * from ancestors"; +prepare stmt from @tmp; execute stmt; drop prepare stmt; + +select emp_id from (select emp_id from emp where sys_trx_end>'2031-1-1') as tmp; +set @tmp= "select emp_id from (select emp_id from emp where sys_trx_end>'2031-1-1') as tmp"; +prepare stmt from @tmp; execute stmt; drop prepare stmt; + +with recursive +ancestors +as +( + select e.emp_id, e.name, e.mgr + from emp as e + where name = 'john' + union + select ee.emp_id, ee.name, ee.mgr + from emp as ee, ancestors as a + where ee.mgr = a.emp_id +) +select * from ancestors; +set @tmp= " +with recursive +ancestors +as +( + select e.emp_id, e.name, e.mgr + from emp as e + where name = 'john' + union + select ee.emp_id, ee.name, ee.mgr + from emp as ee, ancestors as a + where ee.mgr = a.emp_id +) +select * from ancestors +"; +prepare stmt from @tmp; execute stmt; drop prepare stmt; + +with recursive +ancestors +as +( + select e.emp_id, e.name, e.mgr + from emp as e for system_time as of timestamp @ts + where name = 'bill' + union + select ee.emp_id, ee.name, ee.mgr + from emp as ee for system_time as of timestamp @ts, ancestors as a for system_time as of timestamp @ts + where ee.mgr = a.emp_id +) +select * from ancestors for system_time as of timestamp @ts; +set @tmp= " +with recursive +ancestors +as +( + select e.emp_id, e.name, e.mgr + from emp as e for system_time as of timestamp @ts + where name = 'bill' + union + select ee.emp_id, ee.name, ee.mgr + from emp as ee for system_time as of timestamp @ts, ancestors as a for system_time as of timestamp @ts + where ee.mgr = a.emp_id +) +select * from ancestors for system_time as of timestamp @ts; +"; +prepare stmt from @tmp; execute stmt; drop prepare stmt; + +drop table emp; diff --git a/sql/item.cc b/sql/item.cc index 410fa32f234..80a38e4d1d2 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -10271,7 +10271,8 @@ void Item_cache_row::set_null() Item_type_holder::Item_type_holder(THD *thd, Item *item) :Item(thd, item), Type_handler_hybrid_field_type(item->real_type_handler()), - enum_set_typelib(0) + enum_set_typelib(0), + flags(0) { DBUG_ASSERT(item->fixed); maybe_null= item->maybe_null; @@ -10282,6 +10283,12 @@ Item_type_holder::Item_type_holder(THD *thd, Item *item) if (item->field_type() == MYSQL_TYPE_GEOMETRY) geometry_type= item->get_geometry_type(); #endif /* HAVE_SPATIAL */ + if (item->real_type() == Item::FIELD_ITEM) + { + Item_field *item_field= (Item_field*)item->real_item(); + flags|= + (item_field->field->flags & (VERS_SYS_START_FLAG | VERS_SYS_END_FLAG)); + } } diff --git a/sql/item.h b/sql/item.h index 357eabd72e1..f7bc1110898 100644 --- a/sql/item.h +++ b/sql/item.h @@ -5892,6 +5892,8 @@ public: Field *make_field_by_type(TABLE *table); Field::geometry_type get_geometry_type() const { return geometry_type; }; Item* get_copy(THD *thd, MEM_ROOT *mem_root) { return 0; } + + uint flags; }; diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index 827fb21edf4..43302498d57 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -713,6 +713,26 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived) cursor= cursor->next_local) cursor->outer_join|= JOIN_TYPE_OUTER; } + if ((thd->stmt_arena->is_stmt_prepare() || + !thd->stmt_arena->is_stmt_execute()) && + !derived->is_view() && sl->table_list.elements > 0) + { + TABLE_LIST *tl= sl->table_list.first; + if (tl->table && tl->table->versioned()) + { + TABLE_SHARE *s= tl->table->s; + const char *db= tl->db; + const char *alias= tl->alias; + Query_arena backup; + Query_arena *arena= thd->activate_stmt_arena_if_needed(&backup); + sl->item_list.push_back(new (thd->mem_root) Item_field( + thd, &sl->context, db, alias, s->vers_start_field()->field_name)); + sl->item_list.push_back(new (thd->mem_root) Item_field( + thd, &sl->context, db, alias, s->vers_end_field()->field_name)); + if (arena) + thd->restore_active_arena(arena, &backup); + } + } } unit->derived= derived; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index c41801401e7..2ba2f2e7234 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -807,12 +807,20 @@ int vers_setup_select(THD *thd, TABLE_LIST *tables, COND **where_expr, Field *fstart= table->table->vers_start_field(); Field *fend= table->table->vers_end_field(); - DBUG_ASSERT(slex->parent_lex); - Name_resolution_context *context= slex->parent_lex->current_context(); - DBUG_ASSERT(context); - - Item *row_start= newx Item_field(thd, context, fstart); - Item *row_end= newx Item_field(thd, context, fend); + Item *row_start= NULL; + Item *row_end= NULL; + if (table->is_derived() && !table->is_recursive_with_table()) + { + row_start= newx Item_field(thd, &slex->context, NULL, NULL, + fstart->field_name); + row_end= + newx Item_field(thd, &slex->context, NULL, NULL, fend->field_name); + } + else + { + row_start= newx Item_field(thd, &slex->context, fstart); + row_end= newx Item_field(thd, &slex->context, fend); + } Item *row_end2= row_end; if (table->table->versioned_by_sql()) @@ -16859,6 +16867,14 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields, sys_trx_end= new_field; } } + if (type == Item::TYPE_HOLDER) + { + Item_type_holder *ith= (Item_type_holder*)item; + if (ith->flags & VERS_SYS_START_FLAG) + sys_trx_start= new_field; + else if (ith->flags & VERS_SYS_END_FLAG) + sys_trx_end= new_field; + } if (type == Item::SUM_FUNC_ITEM) { Item_sum *agg_item= (Item_sum *) item; |