MDEV-25529 Auto-create: Pre-existing historical data is not partitioned as specified by ALTER

midenok / mariadb

MariaDB server is a community developed fork of MySQL server. Started by core members of the original MySQL team, MariaDB actively works with outside developers to deliver the most featureful, stable, and sanely licensed open SQL server in the industry.

GNU General Public License v2.0

0 stars 0 forks source link

MDEV-25529 Auto-create: Pre-existing historical data is not partitioned as specified by ALTER #101

Open midenok opened 2 years ago

midenok commented 2 years ago

TODO

[ ] 1. with explicit starts: warn if record is back in past more than interval for first partition.
[ ] 2. auto-detect starts?
[ ] 3. with auto: add required number of partitions

Reproduce

--source include/have_partition.inc

create or replace table t1 (x int) with system versioning
partition by system_time limit 1000 partitions 6;
show create table t1;
set timestamp= unix_timestamp('2000-01-01 00:00:00');
insert t1 values (0);
set timestamp= unix_timestamp('2000-01-01 00:10:00');
update t1 set x= x + 1;
set timestamp= unix_timestamp('2000-01-01 01:00:00');
update t1 set x= x + 1;
set timestamp= unix_timestamp('2000-01-01 01:30:00');
update t1 set x= x + 1;
set timestamp= unix_timestamp('2000-01-01 02:00:00');
update t1 set x= x + 1;

select * from t1 partition (p0);
select * from t1 partition (p1);
select * from t1 partition (p2);
select * from t1 partition (p3);
select * from t1 partition (p4);
select * from t1 partition (pn);
set timestamp= default;

alter table t1 partition by system_time
interval 1 hour starts '2000-01-01 00:00:00';
select * from t1 partition (p0);
select * from t1 partition (p1);
select * from t1 partition (p2);
select * from t1 partition (p3);
select * from t1 partition (p4);
select * from t1 partition (pn);

alter table t1 partition by system_time
interval 1 hour;
select * from t1 partition (p0);
select * from t1 partition (p1);
select * from t1 partition (p2);
select * from t1 partition (p3);
select * from t1 partition (p4);
select * from t1 partition (pn);

drop table t1;

Getting partition id for written row

#0  vers_get_partition_id (part_info=0x7ff728038bf8, part_id=0x7ff73ee2ac5c, func_value=0x7ff73ee2ac50) at /home/midenok/src/mariadb/10.6/src/sql/sql_partition.cc:3444
#1  0x00000000010ff39b in ha_partition::write_row (this=0x7ff728034cb0, buf=0x7ff72809a0f0 <incomplete sequence \375>) at ../src/sql/ha_partition.cc:4445
#2  0x0000000000da067b in handler::ha_write_row (this=0x7ff728034cb0, buf=0x7ff72809a0f0 <incomplete sequence \375>) at ../src/sql/handler.cc:7517
#3  0x0000000000aae154 in copy_data_between_tables (thd=0x7ff728000d48, from=0x7ff728029a68, to=0x7ff728096368, create=..., ignore=false, order_num=0, order=0x0, copied=0x7ff73ee2e858, deleted=0x7ff73ee2e850, keys_onoff=Alter_info::LEAVE_AS_IS, alter_ctx=0x7ff73ee2d1d0) at ../src/sql/sql_table.cc:11218
#4  0x0000000000aa3ed3 in mysql_alter_table (thd=0x7ff728000d48, new_db=0x7ff7280059c8, new_name=0x7ff728005de0, create_info=0x7ff73ee2f278, table_list=0x7ff728016470, alter_info=0x7ff73ee2f190, order_num=0, order=0x0, ignore=false, if_exists=false) at ../src/sql/sql_table.cc:10493
#5  0x0000000000b79b49 in Sql_cmd_alter_table::execute (this=0x7ff728016e78, thd=0x7ff728000d48) at ../src/sql/sql_alter.cc:540
#6  0x0000000000972f33 in mysql_execute_command (thd=0x7ff728000d48, is_called_from_prepared_stmt=false) at ../src/sql/sql_parse.cc:5990
#7  0x0000000000963ae3 in mysql_parse (thd=0x7ff728000d48, rawbuf=0x7ff728016360 "alter table t1 partition by system_time\ninterval 1 hour", length=55, parser_state=0x7ff73ee31338) at ../src/sql/sql_parse.cc:8029

frame 1

(rr) pts thd->query_start()
Sat Dec 25 02:49:10 MSK 2021

frame 0

(rr) pts vers_info->interval.start
Sat Dec 25 02:00:00 MSK 2021
(rr) pts range_value[0]
Sat Dec 25 03:00:00 MSK 2021

midenok commented 2 years ago

Pruning selects partition 0 even for long ago history

#0  get_partition_id_range_for_endpoint (part_info=0x7f0f200da9f8, left_endpoint=true, include_endpoint=false) at /home/midenok/src/mariadb/10.6/src/sql/sql_partition.cc:3625
#1  0x000000000099c328 in get_part_iter_for_interval_via_mapping (part_info=0x7f0f200da9f8, is_subpart=false, store_length_array=0x7f0f3c0bb070, min_value=0x7f0f200451e8 "8m\033\250", max_value=0x7f0f200451f0 "\245\245\245\245\245\245\245\245\003", min_len=7, max_len=0, flags=6, part_iter=0x7f0f3c0bb6a0) at /home/midenok/src/mariadb/10.6/src/sql/sql_partition.cc:8491
#2  0x00000000007c0c48 in find_used_partitions (ppar=0x7f0f3c0bb2d8, key_tree=0x7f0f20045280) at ../src/sql/opt_range.cc:4452
#3  0x00000000007bfd61 in prune_partitions (thd=0x7f0f20000d48, table=0x7f0f20029a68, pprune_cond=0x7f0f20018778) at ../src/sql/opt_range.cc:3969
#4  0x00000000009e8dd7 in JOIN::optimize_inner (this=0x7f0f20017b28) at ../src/sql/sql_select.cc:2341
#5  0x00000000009e3a91 in JOIN::optimize (this=0x7f0f20017b28) at ../src/sql/sql_select.cc:1809
#6  0x00000000009dabd8 in mysql_select (thd=0x7f0f20000d48, tables=0x7f0f20016ad8, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f0f20017b00, unit=0x7f0f20005128, select_lex=0x7f0f20016438) at ../src/sql/sql_select.cc:4979
#7  0x00000000009da44d in handle_select (thd=0x7f0f20000d48, lex=0x7f0f20005050, result=0x7f0f20017b00, setup_tables_done_option=0) at ../src/sql/sql_select.cc:545
#8  0x0000000000975223 in execute_sqlcom_select (thd=0x7f0f20000d48, all_tables=0x7f0f20016ad8) at ../src/sql/sql_parse.cc:6253
#9  0x000000000096ae90 in mysql_execute_command (thd=0x7f0f20000d48, is_called_from_prepared_stmt=false) at ../src/sql/sql_parse.cc:3945
#10 0x0000000000963ae3 in mysql_parse (thd=0x7f0f20000d48, rawbuf=0x7f0f20016360 "select * from t1 for system_time as of '2000-01-01 00:10:00'", length=60, parser_state=0x7f0f3c0be338) at ../src/sql/sql_parse.cc:8029

3610      /*
3611        Search for the partition containing part_func_value
3612        (including the right endpoint).
3613      */
3614      while (max_part_id > min_part_id)
3615      {
3616        loc_part_id= (max_part_id + min_part_id) / 2;
3617        if (range_array[loc_part_id] < part_func_value)
3618          min_part_id= loc_part_id + 1;
3619        else
3620          max_part_id= loc_part_id;
3621      }
3622      loc_part_id= max_part_id;

SYSTEM_TIME partitioning like RANGE partitioning works by "less than" condition, so first history partition can include timeframe of any length back to past.

midenok commented 2 years ago

Getting MIN-row info

When index is used and the table is optimized out

#0  ha_partition::index_first (this=0x7fbaa00267d0, buf=0x7fbaa00263d0 "\377") at /home/midenok/src/mariadb/10.6/src/sql/ha_partition.cc:5883
#1  0x0000000000d933d1 in handler::ha_index_first (this=0x7fbaa00267d0, buf=0x7fbaa00263d0 "\377") at /home/midenok/src/mariadb/10.6/src/sql/handler.cc:3545
#2  0x00000000007ed9ce in get_index_min_value (table=0x7fbaa00145b8, ref=0x7fbb040eb980, item_field=0x7fbaa0016898, range_fl=3, prefix_len=0) at /home/midenok/src/mariadb/10.6/src/sql/opt_sum.cc:117
#3  0x00000000007ecd7e in opt_sum_query (thd=0x7fbaa0000d48, tables=..., all_fields=..., conds=0x0) at /home/midenok/src/mariadb/10.6/src/sql/opt_sum.cc:411
#4  0x00000000009e8e76 in JOIN::optimize_inner (this=0x7fbaa0017b70) at /home/midenok/src/mariadb/10.6/src/sql/sql_select.cc:2364
#5  0x00000000009e3a91 in JOIN::optimize (this=0x7fbaa0017b70) at /home/midenok/src/mariadb/10.6/src/sql/sql_select.cc:1809
#6  0x00000000009dabd8 in mysql_select (thd=0x7fbaa0000d48, tables=0x7fbaa0016b60, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fbaa0017b48, unit=0x7fbaa0005128, select_lex=0x7fbaa0016418) at /home/midenok/src/mariadb/10.6/src/sql/sql_select.cc:4979
#7  0x00000000009da44d in handle_select (thd=0x7fbaa0000d48, lex=0x7fbaa0005050, result=0x7fbaa0017b48, setup_tables_done_option=0) at /home/midenok/src/mariadb/10.6/src/sql/sql_select.cc:545
#8  0x0000000000975223 in execute_sqlcom_select (thd=0x7fbaa0000d48, all_tables=0x7fbaa0016b60) at /home/midenok/src/mariadb/10.6/src/sql/sql_parse.cc:6253
#9  0x000000000096ae90 in mysql_execute_command (thd=0x7fbaa0000d48, is_called_from_prepared_stmt=false) at /home/midenok/src/mariadb/10.6/src/sql/sql_parse.cc:3945
#10 0x0000000000963ae3 in mysql_parse (thd=0x7fbaa0000d48, rawbuf=0x7fbaa0016360 "select min(row_end) from t1 for system_time all", length=47, parser_state=0x7fbb040ef338) at /home/midenok/src/mariadb/10.6/src/sql/sql_parse.cc:8029

frame 3

(rr) p dbug_print_item ((Item *)all_fields->first->info)
$6 = 0x2292440 <dbug_item_print_buf> "min(t1.row_end)"

When there is no index

#0  Item_sum_min::add (this=0x7f597c016980) at ../src/sql/item_sum.cc:2520
#1  0x0000000000ef55ad in Aggregator_simple::add (this=0x7f597c019c60) at ../src/sql/item_sum.h:720
#2  0x00000000007e97ed in Item_sum::aggregator_add (this=0x7f597c016980) at ../src/sql/item_sum.h:564
#3  0x0000000000a2925a in update_sum_func (func_ptr=0x7f597c018470) at ../src/sql/sql_select.cc:26256
#4  0x0000000000a25fe7 in end_send_group (join=0x7f597c017b20, join_tab=0x7f597c019460, end_of_records=false) at ../src/sql/sql_select.cc:22536
#5  0x0000000000a26ee2 in evaluate_join_record (join=0x7f597c017b20, join_tab=0x7f597c0190b0, error=0) at ../src/sql/sql_select.cc:21300
#6  0x00000000009da15e in sub_select (join=0x7f597c017b20, join_tab=0x7f597c0190b0, end_of_records=false) at ../src/sql/sql_select.cc:21109
#7  0x0000000000a060f4 in do_select (join=0x7f597c017b20, procedure=0x0) at ../src/sql/sql_select.cc:20617
#8  0x0000000000a04d49 in JOIN::exec_inner (this=0x7f597c017b20) at ../src/sql/sql_select.cc:4735
#9  0x0000000000a03a95 in JOIN::exec (this=0x7f597c017b20) at ../src/sql/sql_select.cc:4513
#10 0x00000000009dacc3 in mysql_select (thd=0x7f597c000d48, tables=0x7f597c016b20, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f597c017af8, unit=0x7f597c005128, select_lex=0x7f597c0163e8) at ../src/sql/sql_select.cc:4993

Useful classes:

class Item_maxmin_subselect :public Item_singlerow_subselect

midenok commented 2 years ago

1. "to" frm created

#0  build_frm_image (thd=0x7f535c000d48, table=..., create_info=0x7f536cfae278, create_fields=..., keys=0, key_info=0x7f535c019fe8, db_file=0x7f535c018878) at ../src/sql/unireg.cc:272
#1  0x0000000000a95fd6 in mysql_create_frm_image (thd=0x7f535c000d48, db=..., table_name=..., create_info=0x7f536cfae278, alter_info=0x7f536cfae190, create_table_mode=-2, key_info=0x7f536cfad880, key_count=0x7f536cfad87c, frm=0x7f536cfad840) at ../src/sql/sql_table.cc:4237
#2  0x0000000000a96f80 in create_table_impl (thd=0x7f535c000d48, ddl_log_state_create=0x0, ddl_log_state_rm=0x0, orig_db=..., orig_table_name=..., db=..., table_name=..., path=..., options=..., create_info=0x7f536cfae278, alter_info=0x7f536cfae190, create_table_mode=-2, is_trans=0x0, key_info=0x7f536cfad880, key_count=0x7f536cfad87c, frm=0x7f536cfad840) at ../src/sql/sql_table.cc:4543
#3  0x0000000000aa2ebd in mysql_alter_table (thd=0x7f535c000d48, new_db=0x7f535c0059c8, new_name=0x7f535c005de0, create_info=0x7f536cfae278, table_list=0x7f535c0164b0, alter_info=0x7f536cfae190, order_num=0, order=0x0, ignore=false, if_exists=false) at ../src/sql/sql_table.cc:10183
#4  0x0000000000b79b49 in Sql_cmd_alter_table::execute (this=0x7f535c016f80, thd=0x7f535c000d48) at ../src/sql/sql_alter.cc:540
#5  0x0000000000972f33 in mysql_execute_command (thd=0x7f535c000d48, is_called_from_prepared_stmt=false) at ../src/sql/sql_parse.cc:5990
#6  0x0000000000963ae3 in mysql_parse (thd=0x7f535c000d48, rawbuf=0x7f535c016360 "alter table t1 partition by system_time\ninterval 1 hour starts '2000-01-01 00:00:00'", length=84, parser_state=0x7f536cfb0338) at ../src/sql/sql_parse.cc:8029

2. "to" table opened

#0  open_table_from_share (thd=0x7f535c000d48, share=0x7f535c091c68, alias=0x7f536cfaab88, db_stat=1, prgflag=8, ha_open_flags=4112, outparam=0x7f535c096368, is_create_table=false, partitions_to_open=0x0) at ../src/sql/table.cc:4000
#1  0x0000000000c8cf0e in THD::open_temporary_table (this=0x7f535c000d48, share=0x7f535c091c68, alias_arg=0x7f535c016478 "t1") at ../src/sql/temporary_tables.cc:1117
#2  0x0000000000c8c6d1 in THD::create_and_open_tmp_table (this=0x7f535c000d48, frm=0x7f536cfad840, path=0x7f536cfacc0f "./test/#sql-alter-2a713-3", db=0x7f535c016b98 "test", table_name=0x7f535c016478 "t1", open_internal_tables=true) at ../src/sql/temporary_tables.cc:74
#3  0x0000000000aa3a8f in mysql_alter_table (thd=0x7f535c000d48, new_db=0x7f535c0059c8, new_name=0x7f535c005de0, create_info=0x7f536cfae278, table_list=0x7f535c0164b0, alter_info=0x7f536cfae190, order_num=0, order=0x0, ignore=false, if_exists=false) at ../src/sql/sql_table.cc:10420
#4  0x0000000000b79b49 in Sql_cmd_alter_table::execute (this=0x7f535c016f80, thd=0x7f535c000d48) at ../src/sql/sql_alter.cc:540
#5  0x0000000000972f33 in mysql_execute_command (thd=0x7f535c000d48, is_called_from_prepared_stmt=false) at ../src/sql/sql_parse.cc:5990
#6  0x0000000000963ae3 in mysql_parse (thd=0x7f535c000d48, rawbuf=0x7f535c016360 "alter table t1 partition by system_time\ninterval 1 hour starts '2000-01-01 00:00:00'", length=84, parser_state=0x7f536cfb0338) at ../src/sql/sql_parse.cc:8029

3. row written to "to"

#0  vers_get_partition_id (part_info=0x7f535c09bc68, part_id=0x7f536cfa9c5c, func_value=0x7f536cfa9c50) at /home/midenok/src/mariadb/10.6/src/sql/sql_partition.cc:3424
#1  0x00000000010ff39b in ha_partition::write_row (this=0x7f535c096c30, buf=0x7f535c096850 <incomplete sequence \375>) at ../src/sql/ha_partition.cc:4445
#2  0x0000000000da067b in handler::ha_write_row (this=0x7f535c096c30, buf=0x7f535c096850 <incomplete sequence \375>) at ../src/sql/handler.cc:7517
#3  0x0000000000aae154 in copy_data_between_tables (thd=0x7f535c000d48, from=0x7f535c029a68, to=0x7f535c096368, create=..., ignore=false, order_num=0, order=0x0, copied=0x7f536cfad858, deleted=0x7f536cfad850, keys_onoff=Alter_info::LEAVE_AS_IS, alter_ctx=0x7f536cfac1d0) at ../src/sql/sql_table.cc:11218
#4  0x0000000000aa3ed3 in mysql_alter_table (thd=0x7f535c000d48, new_db=0x7f535c0059c8, new_name=0x7f535c005de0, create_info=0x7f536cfae278, table_list=0x7f535c0164b0, alter_info=0x7f536cfae190, order_num=0, order=0x0, ignore=false, if_exists=false) at ../src/sql/sql_table.cc:10493
#5  0x0000000000b79b49 in Sql_cmd_alter_table::execute (this=0x7f535c016f80, thd=0x7f535c000d48) at ../src/sql/sql_alter.cc:540
#6  0x0000000000972f33 in mysql_execute_command (thd=0x7f535c000d48, is_called_from_prepared_stmt=false) at ../src/sql/sql_parse.cc:5990
#7  0x0000000000963ae3 in mysql_parse (thd=0x7f535c000d48, rawbuf=0x7f535c016360 "alter table t1 partition by system_time\ninterval 1 hour starts '2000-01-01 00:00:00'", length=84, parser_state=0x7f536cfb0338) at ../src/sql/sql_parse.cc:8029

For copy_data_between_tables() destination table is precreated and preopened. part_info is written into frm. At the copying stage partitions list is fixed and cannot be expanded.

min/max statistics for row_end easily accessed if there is an index for row_end. Otherwise a table scan is required. We cannot store statistics somewhere outside the storage engine (f.ex. in FRM) in multi-user concurrent environment: since some storage engines support MVCC that must be MVCC too.

Note: Partitioning: persistent min/max stats

vers_scan_min_max() removed: e36c5ec0a50332840c7dcb8e6b08a369ec2a829c

midenok commented 2 years ago

Info: min/max in test_quick_select() coverage

diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 06063cb9ae1..81ae2ada6c2 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -3038,6 +3038,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use,
           grp_summary.add("chosen", true);
           best_trp= group_trp;
           best_read_time= best_trp->read_cost;
+          DBUG_ASSERT(0);
         }
         else
           grp_summary.add("chosen", false).add("cause", "cost");

    if (!only_single_index_range_scan)
    {
      TRP_GROUP_MIN_MAX *group_trp;
      if (tree)
        restore_nonrange_trees(&param, tree, backup_keys);
      if ((group_trp= get_best_group_min_max(&param, tree, read_time)))
      {
        param.table->opt_range_condition_rows= MY_MIN(group_trp->records,
                                                  head->stat_records());
        Json_writer_object grp_summary(thd, "best_group_range_summary");

        if (unlikely(thd->trace_started()))
          group_trp->trace_basic_info(&param, &grp_summary);

        if (group_trp->read_cost < best_read_time || force_group_by)
        {
          grp_summary.add("chosen", true);
          best_trp= group_trp;
          best_read_time= best_trp->read_cost;
          DBUG_ASSERT(0);
        }
        else
          grp_summary.add("chosen", false).add("cause", "cost");
      }
      if (tree)
        remove_nonrange_trees(&param, tree);
    }

Tests affected

mtr_20220124_181943.log

CURRENT_TEST: main.group_min_max
mysqltest: At line 177: query 'explain select a1, min(a2) from t1 group by a1' failed: <Unknown> (2013): Lost connection to server during query

CURRENT_TEST: main.innodb_ext_key
mysqltest: At line 114: query 'explain 
select max(l_orderkey) from lineitem
where l_partkey between 1 and 10 group by l_partkey' failed: <Unknown> (2013): Lost connection to server during query

Notes

TRP_GROUP_MIN_MAX is only for MIN/MAX in GROUP BY.

All table read plan points

--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -2934,6 +2934,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use,
       {
         best_trp= range_trp;
         best_read_time= best_trp->read_cost;
+        DBUG_ASSERT(opt_bootstrap || !range_trp);
       }

       /*
@@ -2962,6 +2963,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use,
               (rori_trp= get_best_covering_ror_intersect(&param, tree,
                                                          best_read_time)))
             best_trp= rori_trp;
+          DBUG_ASSERT(opt_bootstrap || !rori_trp);
         }
       }
       /*
@@ -2981,6 +2983,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use,
           best_read_time= best_trp->read_cost;
           set_if_smaller(param.table->opt_range_condition_rows,
                          intersect_trp->records);
+          DBUG_ASSERT(opt_bootstrap || !intersect_trp);
         }
       }

@@ -3010,7 +3013,10 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use,
           }
         }
         if (best_conj_trp)
+        {
           best_trp= best_conj_trp;
+          DBUG_ASSERT(opt_bootstrap || !best_conj_trp);
+        }
       }
     }

@@ -3038,6 +3044,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use,
           grp_summary.add("chosen", true);
           best_trp= group_trp;
           best_read_time= best_trp->read_cost;
+          DBUG_ASSERT(opt_bootstrap || !group_trp);
         }
         else
           grp_summary.add("chosen", false).add("cause", "cost");

More tests affected (range_trp)

mtr.log.zip

CURRENT_TEST: main.type_time_hires

mysqltest: At line 9: query 'select min(a - interval 1 hour), max(a - interval 1 hour) from t1 where a < 0' failed: <Unknown> (2013): Lost connection to server during query

midenok commented 2 years ago

Info: temporary table created in ALTER

--source include/have_partition.inc

create table t1 (x int);
alter table t1 add y int, algorithm=copy;

create or replace table t1 (x int) with system versioning;
set timestamp= unix_timestamp('2000-01-01 00:00:00');
insert t1 values (0);
set timestamp= unix_timestamp('2000-01-01 00:10:00');
update t1 set x= x + 1;
set timestamp= unix_timestamp('2000-01-01 01:00:00');
update t1 set x= x + 1;
set timestamp= unix_timestamp('2000-01-01 01:30:00');
update t1 set x= x + 1;
set timestamp= unix_timestamp('2000-01-01 02:00:00');
update t1 set x= x + 1;
set timestamp= unix_timestamp('2000-01-01 03:00:00');
update t1 set x= x + 1;
set timestamp= unix_timestamp('2000-01-01 05:00:00');
update t1 set x= x + 1;

set timestamp= default;

set timestamp= unix_timestamp('2000-01-01 00:00:00');
alter table t1 partition by system_time
interval 1 hour auto;
show create table t1;

select *, row_start, row_end from t1 partition (p0);
select *, row_start, row_end from t1 partition (p1);
select *, row_start, row_end from t1 partition (p2);
select *, row_start, row_end from t1 partition (p3);
select *, row_start, row_end from t1 partition (p4);
select *, row_start, row_end from t1 partition (pn);

drop table t1;

1. TMP name created

#0  build_table_filename (buff=0x7f9478059867 "", bufflen=512, db=0x7f945c016cd8 "test", table_name=0x7f94780593a4 "#sql-alter-3802c-3", ext=0x56084cf2915b "", flags=3) at ../src/sql/sql_table.cc:558
#1  0x000056084c14ae3d in Alter_table_ctx::Alter_table_ctx (this=0x7f9478058e28, thd=0x7f945c000d58, table_list=0x7f945c0165f0, tables_opened_arg=1, new_db_arg=0x7f945c0059f0, new_name_arg=0x7f945c005e00) at ../src/sql/sql_alter.cc:344
#2  0x000056084c0779c3 in mysql_alter_table (thd=0x7f945c000d58, new_db=0x7f945c0059f0, new_name=0x7f945c005e00, create_info=0x7f947805aee0, table_list=0x7f945c0165f0, alter_info=0x7f947805adf0, order_num=0, order=0x0, ignore=false, if_exists=false) at ../src/sql/sql_table.cc:10438
#3  0x000056084c14ba96 in Sql_cmd_alter_table::execute (this=0x7f945c016fe0, thd=0x7f945c000d58) at ../src/sql/sql_alter.cc:548
#4  0x000056084bf5b8aa in mysql_execute_command (thd=0x7f945c000d58, is_called_from_prepared_stmt=false) at ../src/sql/sql_parse.cc:5996
#5  0x000056084bf4c94f in mysql_parse (thd=0x7f945c000d58, rawbuf=0x7f945c0164d0 "alter table t1 partition by system_time\ninterval 1 hour auto", length=60, parser_state=0x7f947805d348) at ../src/sql/sql_parse.cc:8038

frame 1

327       tmp_name.str= tmp_name_buff;
328       tmp_name.length= my_snprintf(tmp_name_buff, sizeof(tmp_name_buff),
329                                    "%s-alter-%lx-%llx",
330                                    tmp_file_prefix, current_pid, thd->thread_id);
...
335       if (table_list->table->s->tmp_table == NO_TMP_TABLE)
336       {
...
344         build_table_filename(tmp_path, sizeof(tmp_path) - 1, new_db.str, tmp_name.str, "",
345                              FN_IS_TMP);

(rr) p tmp_name.str
$13 = 0x7f94780593a4 "#sql-alter-3802c-3"
(rr) p/s tmp_path
$14 = "./test/#sql-alter-3802c-3\000f...

2. TMP frm is written

#0  TABLE_SHARE::write_frm_image (this=0x7ff02baf7db0, frm=0x7ff01c02ede8 "\376\001\n\t\022", len=455) at ../src/sql/table.cc:3576
#1  0x000055c528782b6a in TABLE_SHARE::init_from_binary_frm_image (this=0x7ff02baf7db0, thd=0x7ff01c000d58, write=true, frm_image=0x7ff01c02ede8 "\376\001\n\t\022", frm_length=455, par_image=0x0, par_length=0) at ../src/sql/table.cc:1802
#2  0x000055c528a14714 in ha_create_table (thd=0x7ff01c000d58, path=0x7ff02bafa867 "./test/#sql-alter-386cb-3", db=0x7ff01c016cb0 "test", table_name=0x7ff01c016590 "t1", create_info=0x7ff02bafbee0, frm=0x7ff02bafb4b0, skip_frm_file=false) at ../src/sql/handler.cc:5832
#3  0x000055c52872f6b9 in mysql_alter_table (thd=0x7ff01c000d58, new_db=0x7ff01c0059f0, new_name=0x7ff01c005e00, create_info=0x7ff02bafbee0, table_list=0x7ff01c0165c8, alter_info=0x7ff02bafbdf0, order_num=0, order=0x0, ignore=false, if_exists=false) at ../src/sql/sql_table.cc:11254

(rr) p tmp_table
$18 = INTERNAL_TMP_TABLE
(rr) p normalized_path.str
$19 = 0x7f9478059867 "./test/#sql-alter-3802c-3"

frame 2 (ha_create_table)

5816      TABLE_SHARE share;
....
5822      init_tmp_table_share(thd, &share, db, 0, table_name, path);
5823
5824      if (frm)
5825      {
5826        bool write_frm_now= (!create_info->db_type->discover_table &&
5827                             !create_info->tmp_table() && !skip_frm_file);
5828
5829        share.frm_image= frm;
5830
5831        // open an frm image
5832        if (share.init_from_binary_frm_image(thd, write_frm_now,
5833                                             frm->str, frm->length))
5834          goto err;
5835      }

3. TMP share created

#0  init_tmp_table_share (thd=0x7f945c000d58, share=0x7f945c072688, key=0x7f945c072c72 "test", key_length=16, table_name=0x7f945c072c77 "t1", path=0x7f945c072c58 "./test/#sql-alter-3802c-3") at ../src/sql/table.cc:424
#1  0x000056084c25d83c in THD::create_temporary_table (this=0x7f945c000d58, frm=0x7f947805a4b0, path=0x7f9478059867 "./test/#sql-alter-3802c-3", db=0x7f945c016cd8 "test", table_name=0x7f945c0165b8 "t1") at ../src/sql/temporary_tables.cc:980
#2  0x000056084c25d437 in THD::create_and_open_tmp_table (this=0x7f945c000d58, frm=0x7f947805a4b0, path=0x7f9478059867 "./test/#sql-alter-3802c-3", db=0x7f945c016cd8 "test", table_name=0x7f945c0165b8 "t1", open_internal_tables=true) at ../src/sql/temporary_tables.cc:71
#3  0x000056084c07a77f in mysql_alter_table (thd=0x7f945c000d58, new_db=0x7f945c0059f0, new_name=0x7f945c005e00, create_info=0x7f947805aee0, table_list=0x7f945c0165f0, alter_info=0x7f947805adf0, order_num=0, order=0x0, ignore=false, if_exists=false) at ../src/sql/sql_table.cc:11266

Note: init_tmp_table_share() is done in ha_create_table() as well. That was needed only for create table (and write frm). That share is destroyed in ha_create_table(), this one is used across all ALTER TABLE.

4. TMP table opened

#0  open_table_from_share (thd=0x7f945c000d58, share=0x7f945c072688, alias=0x7f9478057888, db_stat=1, prgflag=8, ha_open_flags=4112, outparam=0x7f945c074c28, is_create_table=false, partitions_to_open=0x0) at ../src/sql/table.cc:4112
#1  0x000056084c25dc6f in THD::open_temporary_table (this=0x7f945c000d58, share=0x7f945c072688, alias_arg=0x7f945c0165b8 "t1") at ../src/sql/temporary_tables.cc:1128
#2  0x000056084c25d47a in THD::create_and_open_tmp_table (this=0x7f945c000d58, frm=0x7f947805a4b0, path=0x7f9478059867 "./test/#sql-alter-3802c-3", db=0x7f945c016cd8 "test", table_name=0x7f945c0165b8 "t1", open_internal_tables=true) at ../src/sql/temporary_tables.cc:74
#3  0x000056084c07a77f in mysql_alter_table (thd=0x7f945c000d58, new_db=0x7f945c0059f0, new_name=0x7f945c005e00, create_info=0x7f947805aee0, table_list=0x7f945c0165f0, alter_info=0x7f947805adf0, order_num=0, order=0x0, ignore=false, if_exists=false) at ../src/sql/sql_table.cc:11266

(rr) p share->table_name.str
$16 = 0x7f945c072c77 "t1"
(rr) p share->path.str
$17 = 0x7f945c072c58 "./test/#sql-alter-3802c-3"

midenok commented 2 years ago

Share must be also reopened

#6  0x00007fe81be9ae96 in __GI___assert_fail (assertion=0x562a5740bc19 "part_share->partitions_share_refs.num_parts >= m_tot_parts", file=0x562a5740af3a "/home/midenok/src/mariadb/10.6/src/sql/ha_partition.cc", line=3092, function=0x562a5740bbcc "virtual bool ha_partition::set_ha_share_ref(Handler_share **)") at ./assert/assert.c:101
#7  0x0000562a56b4efdd in ha_partition::set_ha_share_ref (this=0x7fe8040d5080, ha_share_arg=0x7fe8040b0388) at /home/midenok/src/mariadb/10.6/src/sql/ha_partition.cc:3092
#8  0x0000562a56596832 in open_table_from_share (thd=0x7fe804000d58, share=0x7fe8040afde8, alias=0x7fe815ac93d8, db_stat=1, prgflag=8, ha_open_flags=16, outparam=0x7fe8040b2388, is_create_table=false, partitions_to_open=0x0) at /home/midenok/src/mariadb/10.6/src/sql/table.cc:4160
#9  0x0000562a56719d4f in THD::open_temporary_table (this=0x7fe804000d58, share=0x7fe8040afde8, alias_arg=0x7fe8040b03d6 "t1") at /home/midenok/src/mariadb/10.6/src/sql/temporary_tables.cc:1128
#10 0x0000562a565402c0 in copy_data_between_tables (thd=0x7fe804000d58, from=0x7fe8040a3718, to_ptr=0x7fe815acd550, create=..., ignore=false, order_num=0, order=0x0, copied=0x7fe815acd4d0, deleted=0x7fe815acd4c8, keys_onoff=Alter_info::LEAVE_AS_IS, alter_ctx=0x7fe815acbe28) at /home/midenok/src/mariadb/10.6/src/sql/sql_table.cc:11896
#11 0x0000562a56536b4f in mysql_alter_table (thd=0x7fe804000d58, new_db=0x7fe8040059f0, new_name=0x7fe804005e00, create_info=0x7fe815acdee0, table_list=0x7fe8040165f0, alter_info=0x7fe815acddf0, order_num=0, order=0x0, ignore=false, if_exists=false) at /home/midenok/src/mariadb/10.6/src/sql/sql_table.cc:11335

frame 7

3092      DBUG_ASSERT(part_share->partitions_share_refs.num_parts >= m_tot_parts);

(rr) p part_share->partitions_share_refs.num_parts
$3 = 2
(rr) p m_tot_parts
$4 = 32

Cause

part_share->partitions_share_refs.num_parts not updated.

part_share->partitions_share_refs.num_parts set

#0  0x0000562a56b69ce5 in Parts_share_refs::init (this=0x7fe8040b1b68, arg_num_parts=2) at /home/midenok/src/mariadb/10.6/src/sql/ha_partition.h:68
#1  0x0000562a56b43ec1 in Partition_share::init (this=0x7fe8040b1a10, num_parts=2) at /home/midenok/src/mariadb/10.6/src/sql/ha_partition.cc:238
#2  0x0000562a56b4f246 in ha_partition::get_share (this=0x7fe8040b0840) at /home/midenok/src/mariadb/10.6/src/sql/ha_partition.cc:3126
#3  0x0000562a56b4ef04 in ha_partition::set_ha_share_ref (this=0x7fe8040b0840, ha_share_arg=0x7fe8040b0388) at /home/midenok/src/mariadb/10.6/src/sql/ha_partition.cc:3090
#4  0x0000562a5658b9bc in TABLE_SHARE::init_from_binary_frm_image (this=0x7fe8040afde8, thd=0x7fe804000d58, write=false, frm_image=0x7fe8040a22d8 "\376\001\n\024%", frm_length=597, par_image=0x0, par_length=0) at /home/midenok/src/mariadb/10.6/src/sql/table.cc:2298
#5  0x0000562a56719959 in THD::create_temporary_table (this=0x7fe804000d58, frm=0x7fe815acd4b0, path=0x7fe815acc867 "./test/#sql-alter-3aff-3", db=0x7fe804016cd8 "test", table_name=0x7fe8040165b8 "t1") at /home/midenok/src/mariadb/10.6/src/sql/temporary_tables.cc:989
#6  0x0000562a56719517 in THD::create_and_open_tmp_table (this=0x7fe804000d58, frm=0x7fe815acd4b0, path=0x7fe815acc867 "./test/#sql-alter-3aff-3", db=0x7fe804016cd8 "test", table_name=0x7fe8040165b8 "t1", open_internal_tables=true) at /home/midenok/src/mariadb/10.6/src/sql/temporary_tables.cc:71
#7  0x0000562a5653672f in mysql_alter_table (thd=0x7fe804000d58, new_db=0x7fe8040059f0, new_name=0x7fe804005e00, create_info=0x7fe815acdee0, table_list=0x7fe8040165f0, alter_info=0x7fe815acddf0, order_num=0, order=0x0, ignore=false, if_exists=false) at /home/midenok/src/mariadb/10.6/src/sql/sql_table.cc:11262

midenok commented 2 years ago

Bug: pruning and placement discrepancy between CREATE and ALTER

Reference: MDEV-25390 Pruning boundary for history partitions is wrong by 1 second

On partition boundary row is placed differently between CREATE and ALTER. Strange enough, pruning works also differently and that makes SELECT correct in both cases.

Reproduce

--source include/have_partition.inc
set timestamp= unix_timestamp('2000-01-01 00:00:00');
create or replace table t1 (x int) with system versioning
partition by system_time interval 1 hour partitions 3;
set timestamp= unix_timestamp('2000-01-01 00:00:00');
insert t1 values (0);
set timestamp= unix_timestamp('2000-01-01 00:10:00');
update t1 set x= 1;
set timestamp= unix_timestamp('2000-01-01 01:00:00');
update t1 set x= 2;
set timestamp= unix_timestamp('2000-01-01 01:30:00');
update t1 set x= 3;
--echo # Common result
flush tables;
select * from t1 for system_time as of '2000-01-01 00:59:59';
--echo # CREATE case
explain partitions select * from t1 for system_time as of '2000-01-01 00:59:59';
select *, row_start, row_end from t1 partition (p0);
select *, row_start, row_end from t1 partition (p1);

set timestamp= unix_timestamp('2000-01-01 00:00:00');
alter table t1
partition by system_time interval 1 hour partitions 3;
--echo # Common result
flush tables;
select * from t1 for system_time as of '2000-01-01 00:59:59';
--echo # ALTER case
Explain partitions select * from t1 for system_time as of '2000-01-01 00:59:59';
Select *, row_start, row_end from t1 partition (p0);
Select *, row_start, row_end from t1 partition (p1);

drop table t1;

Result

# Common result
flush tables;
select * from t1 for system_time as of '2000-01-01 00:59:59';
x
1

# CREATE case
explain partitions select * from t1 for system_time as of '2000-01-01 00:59:59';
id      select_type     table   partitions      type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE  t1      p1,pn   ALL     NULL    NULL    NULL    NULL    3       Using where
select *, row_start, row_end from t1 partition (p0);
x       row_start       row_end
0       2000-01-01 00:00:00.000000      2000-01-01 00:10:00.000000
select *, row_start, row_end from t1 partition (p1);
x       row_start       row_end
1       2000-01-01 00:10:00.000000      2000-01-01 01:00:00.000000
2       2000-01-01 01:00:00.000000      2000-01-01 01:30:00.000000

# ALTER case
Explain partitions select * from t1 for system_time as of '2000-01-01 00:59:59';
id      select_type     table   partitions      type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE  t1      p0,p1,pn        ALL     NULL    NULL    NULL    NULL    4       Using where
Select *, row_start, row_end from t1 partition (p0);
x       row_start       row_end
0       2000-01-01 00:00:00.000000      2000-01-01 00:10:00.000000
1       2000-01-01 00:10:00.000000      2000-01-01 01:00:00.000000
Select *, row_start, row_end from t1 partition (p1);
x       row_start       row_end
2       2000-01-01 01:00:00.000000      2000-01-01 01:30:00.000000

midenok commented 2 years ago

Related to MDEV-22247:

CREATE case

Pruned to: p1,pn

#0  get_partition_id_range_for_endpoint (part_info=0x7fb6f0040550, left_endpoint=true, include_endpoint=false) at ../src/sql/sql_partition.cc:3659
#1  0x000055cd0526f088 in get_part_iter_for_interval_via_mapping (part_info=0x7fb6f0040550, is_subpart=false, store_length_array=0x7fb7022ddf30, min_value=0x7fb6f00bed28 "8m'_", max_value=0x7fb6f00bed30 "\245\245\245\245\245\245\245\245\003", min_len=7, max_len=0, flags=6, part_iter=0x7fb7022de530) at ../src/sql/sql_partition.cc:7932
#2  0x000055cd050a9c74 in find_used_partitions (ppar=0x7fb7022de168, key_tree=0x7fb6f00bedc0) at ../src/sql/opt_range.cc:4480
#3  0x000055cd050a8dfd in prune_partitions (thd=0x7fb6f0000d58, table=0x7fb6f0031728, pprune_cond=0x7fb6f0018938) at ../src/sql/opt_range.cc:3995
#4  0x000055cd052bac18 in JOIN::optimize_inner (this=0x7fb6f0017ce8) at ../src/sql/sql_select.cc:2379
#5  0x000055cd052b5b88 in JOIN::optimize (this=0x7fb6f0017ce8) at ../src/sql/sql_select.cc:1847
#6  0x000055cd052ad045 in mysql_select (thd=0x7fb6f0000d58, tables=0x7fb6f0016c48, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fb6f0017cc0, unit=0x7fb6f0005150, select_lex=0x7fb6f00165a8) at ../src/sql/sql_select.cc:5032
#7  0x000055cd052ac8fa in handle_select (thd=0x7fb6f0000d58, lex=0x7fb6f0005078, result=0x7fb6f0017cc0, setup_tables_done_option=0) at ../src/sql/sql_select.cc:580
#8  0x000055cd0524dee2 in execute_sqlcom_select (thd=0x7fb6f0000d58, all_tables=0x7fb6f0016c48) at ../src/sql/sql_parse.cc:6260
#9  0x000055cd05243df7 in mysql_execute_command (thd=0x7fb6f0000d58, is_called_from_prepared_stmt=false) at ../src/sql/sql_parse.cc:3944
#10 0x000055cd0523cecf in mysql_parse (thd=0x7fb6f0000d58, rawbuf=0x7fb6f00164d0 "select * from t1 for system_time as of '2000-01-01 00:59:59'", length=60, parser_state=0x7fb7022e1348) at ../src/sql/sql_parse.cc:8038

3659        if (part_info->part_type == VERSIONING_PARTITION &&
3660            part_func_value < INT_MAX32 &&
3661            loc_part_id > part_info->vers_info->hist_part->id)
3662        {
3663          /*
3664            Historical query with AS OF point after the last history partition must
3665            include last history partition because it can be overflown (contain
3666            history rows out of right endpoint).
3667          */
3668          loc_part_id= part_info->vers_info->hist_part->id;
3669        }

(rr) p loc_part_id
$1 = 1
(rr) p part_info->vers_info->hist_part->id
$2 = 1

ALTER case

Pruned to: p0,p1,pn

(rr) p loc_part_id
$1 = 1
(rr) p part_info->vers_info->hist_part->id
$2 = 0

loc_part_id is changed to 0.

midenok commented 2 years ago

CREATE case: hist_part->id set

#0  0x000055ebd514a3a0 in check_vers_constants (thd=0x7f4a8c000d58, part_info=0x7f4a8c040550) at ../src/sql/sql_partition.cc:1575
#1  0x000055ebd5138d51 in fix_partition_func (thd=0x7f4a8c000d58, table=0x7f4a8c031728, is_create_table_ind=false) at ../src/sql/sql_partition.cc:2019
#2  0x000055ebd52a855b in open_table_from_share (thd=0x7f4a8c000d58, share=0x7f4a8c0650c0, alias=0x7f4a8c016c68, db_stat=33, prgflag=8, ha_open_flags=16, outparam=0x7f4a8c031728, is_create_table=false, partitions_to_open=0x0) at ../src/sql/table.cc:4370
#3  0x000055ebd50523b5 in open_table (thd=0x7f4a8c000d58, table_list=0x7f4a8c016c48, ot_ctx=0x7f4a9e0feb88) at ../src/sql/sql_base.cc:2160
#4  0x000055ebd5058280 in open_and_process_table (thd=0x7f4a8c000d58, tables=0x7f4a8c016c48, counter=0x7f4a9e0fec7c, flags=0, prelocking_strategy=0x7f4a9e0fece0, has_prelocking_list=false, ot_ctx=0x7f4a9e0feb88) at ../src/sql/sql_base.cc:4090
#5  0x000055ebd5056c88 in open_tables (thd=0x7f4a8c000d58, options=..., start=0x7f4a9e0fec90, counter=0x7f4a9e0fec7c, flags=0, prelocking_strategy=0x7f4a9e0fece0) at ../src/sql/sql_base.cc:4577
#6  0x000055ebd505ab3f in open_and_lock_tables (thd=0x7f4a8c000d58, options=..., tables=0x7f4a8c016c48, derived=true, flags=0, prelocking_strategy=0x7f4a9e0fece0) at ../src/sql/sql_base.cc:5550
#7  0x000055ebd5006ade in open_and_lock_tables (thd=0x7f4a8c000d58, tables=0x7f4a8c016c48, derived=true, flags=0) at ../src/sql/sql_base.h:510
#8  0x000055ebd512a886 in execute_sqlcom_select (thd=0x7f4a8c000d58, all_tables=0x7f4a8c016c48) at ../src/sql/sql_parse.cc:6180
#9  0x000055ebd5120df7 in mysql_execute_command (thd=0x7f4a8c000d58, is_called_from_prepared_stmt=false) at ../src/sql/sql_parse.cc:3944
#10 0x000055ebd5119ecf in mysql_parse (thd=0x7f4a8c000d58, rawbuf=0x7f4a8c0164d0 "select * from t1 for system_time as of '2000-01-01 00:59:59'", length=60, parser_state=0x7f4a9e101348) at ../src/sql/sql_parse.cc:8038

1574        if (vers_info->hist_part->range_value <= thd->query_start())
1575          vers_info->hist_part= el;

(rr) p vers_info->hist_part->id
$2 = 0
(rr) pts vers_info->hist_part->range_value
Sat Jan  1 01:00:00 MSK 2000
(rr) pts thd->query_start()
Sat Jan  1 01:30:00 MSK 2000
(rr) p el->id
$3 = 1

ALTER case: hist_part->id set

#0  0x000055ebd514a236 in check_vers_constants (thd=0x7f4a8c000d58, part_info=0x7f4a8c037220) at ../src/sql/sql_partition.cc:1551
...
#10 0x000055ebd5119ecf in mysql_parse (thd=0x7f4a8c000d58, rawbuf=0x7f4a8c0164d0 "Select * from t1 for system_time as of '2000-01-01 00:59:59'", length=60, parser_state=0x7f4a9e101348) at ../src/sql/sql_parse.cc:8038

1551      vers_info->hist_part= part_info->partitions.head();

(rr) p vers_info->hist_part->id
$3 = 0
(rr) pts vers_info->hist_part->range_value
Sat Jan  1 01:00:00 MSK 2000
(rr) pts thd->query_start()
Sat Jan  1 00:00:00 MSK 2000
(rr) p el->id
$4 = 1

Cause

SELECT pruning depends on current history partition which is set from query_start().

midenok commented 2 years ago

1. `vers_get_partition_id()`

#0  vers_get_partition_id (part_info=0x7f4a8c0c8d10, part_id=0x7f4a9e0fa9fc, func_value=0x7f4a9e0fa9f0) at ../src/sql/sql_partition.cc:3442
#1  0x000055ebd58a275d in ha_partition::write_row (this=0x7f4a8c0c64b0, buf=0x7f4a8c0c60b8 "\375\003") at ../src/sql/ha_partition.cc:4077
#2  0x000055ebd5532341 in handler::ha_write_row (this=0x7f4a8c0c64b0, buf=0x7f4a8c0c60b8 "\375\003") at ../src/sql/handler.cc:7488
#3  0x000055ebd5252676 in copy_data_between_tables (thd=0x7f4a8c000d58, from=0x7f4a8c031728, to=0x55ebd7e7c878, create=..., ignore=false, order_num=0, order=0x0, copied=0x7f4a9e0fe4d0, deleted=0x7f4a9e0fe4c8, keys_onoff=Alter_info::LEAVE_AS_IS, alter_ctx=0x7f4a9e0fce28) at ../src/sql/sql_table.cc:12098


int vers_get_partition_id(partition_info *part_info, uint32 *part_id,
                          longlong *func_value)
{
  DBUG_ENTER("vers_get_partition_id");
  Field *row_end= part_info->part_field_array[STAT_TRX_END];
  Vers_part_info *vers_info= part_info->vers_info;

  if (row_end->is_max() || row_end->is_null())
    *part_id= vers_info->now_part->id;
  else // row is historical
  {
    longlong *range_value= part_info->range_int_array;
    uint max_hist_id= part_info->num_parts - 2;
    uint min_hist_id= 0, loc_hist_id= vers_info->hist_part->id;
    ulong unused;
    my_time_t ts;

    if (!range_value)
      goto done; // fastpath

    ts= row_end->get_timestamp(&unused);
    if ((loc_hist_id == 0 || range_value[loc_hist_id - 1] < ts) &&
        (loc_hist_id == max_hist_id || range_value[loc_hist_id] >= ts))
      goto done; // fastpath

    while (max_hist_id > min_hist_id)
    {
      loc_hist_id= (max_hist_id + min_hist_id) / 2;
      if (range_value[loc_hist_id] < ts)
        min_hist_id= loc_hist_id + 1;
      else
        max_hist_id= loc_hist_id;
    }
    loc_hist_id= max_hist_id;
done:
    *part_id= (uint32)loc_hist_id;
  }
  DBUG_PRINT("exit",("partition: %d", *part_id));
  DBUG_RETURN(0);
}

2a. `check_vers_constants()`

#0  0x000055ebd514a3a0 in check_vers_constants (thd=0x7f4a8c000d58, part_info=0x7f4a8c040550) at ../src/sql/sql_partition.cc:1575
#1  0x000055ebd5138d51 in fix_partition_func (thd=0x7f4a8c000d58, table=0x7f4a8c031728, is_create_table_ind=false) at ../src/sql/sql_partition.cc:2019
#2  0x000055ebd52a855b in open_table_from_share (thd=0x7f4a8c000d58, share=0x7f4a8c0650c0, alias=0x7f4a8c016c68, db_stat=33, prgflag=8, ha_open_flags=16, outparam=0x7f4a8c031728, is_create_table=false, partitions_to_open=0x0) at ../src/sql/table.cc:4370

/* Set partition boundaries when rotating by INTERVAL */
static bool check_vers_constants(THD *thd, partition_info *part_info)
{
  uint hist_parts= part_info->num_parts - 1;
  Vers_part_info *vers_info= part_info->vers_info;
  vers_info->hist_part= part_info->partitions.head();
  vers_info->now_part= part_info->partitions.elem(hist_parts);

  if (!vers_info->interval.is_set())
    return 0;

  part_info->range_int_array=
    (longlong*) thd->alloc(part_info->num_parts * sizeof(longlong));

  MYSQL_TIME ltime;
  List_iterator<partition_element> it(part_info->partitions);
  partition_element *el;
  my_tz_OFFSET0->gmt_sec_to_TIME(&ltime, vers_info->interval.start);
  while ((el= it++)->id < hist_parts)
  {
    if (date_add_interval(thd, &ltime, vers_info->interval.type,
                          vers_info->interval.step))
      goto err;
    uint error= 0;
    part_info->range_int_array[el->id]= el->range_value=
      my_tz_OFFSET0->TIME_to_gmt_sec(&ltime, &error);
    if (error)
      goto err;
    if (vers_info->hist_part->range_value <= thd->query_start())
      vers_info->hist_part= el;
  }
  DBUG_ASSERT(el == vers_info->now_part);
  el->max_value= true;
  part_info->range_int_array[el->id]= el->range_value= LONGLONG_MAX;
  return 0;
err:
  my_error(ER_DATA_OUT_OF_RANGE, MYF(0), "TIMESTAMP", "INTERVAL");
  return 1;
}

2b. `vers_set_hist_part()` (when table is cached)

#0  partition_info::vers_set_hist_part (this=0x7f4a8c028980, thd=0x7f4a8c000d58, create_count=0x7f4a9e0fe7cc) at ../src/sql/partition_info.cc:826
#1  0x000055ebd50512d9 in TABLE::vers_switch_partition (this=0x7f4a8c031728, thd=0x7f4a8c000d58, table_list=0x7f4a8c0165a0, ot_ctx=0x7f4a9e0fe798) at ../src/sql/sql_base.cc:1742
#2  0x000055ebd50524fe in open_table (thd=0x7f4a8c000d58, table_list=0x7f4a8c0165a0, ot_ctx=0x7f4a9e0fe798) at ../src/sql/sql_base.cc:2198
#3  0x000055ebd5058280 in open_and_process_table (thd=0x7f4a8c000d58, tables=0x7f4a8c0165a0, counter=0x7f4a9e0ff23c, flags=0, prelocking_strategy=0x7f4a9e0fe848, has_prelocking_list=false, ot_ctx=0x7f4a9e0fe798) at ../src/sql/sql_base.cc:4090
#4  0x000055ebd5056c88 in open_tables (thd=0x7f4a8c000d58, options=..., start=0x7f4a9e0ff288, counter=0x7f4a9e0ff23c, flags=0, prelocking_strategy=0x7f4a9e0fe848) at ../src/sql/sql_base.cc:4577
#5  0x000055ebd50493a8 in open_tables (thd=0x7f4a8c000d58, tables=0x7f4a9e0ff288, counter=0x7f4a9e0ff23c, flags=0) at ../src/sql/sql_base.h:480
#6  0x000055ebd5283b59 in mysql_update (thd=0x7f4a8c000d58, table_list=0x7f4a8c0165a0, fields=..., values=..., conds=0x0, order_num=0, order=0x0, limit=18446744073709551615, ignore=false, found_return=0x7f4a9e0fff18, updated_return=0x7f4a9e0fff10) at ../src/sql/sql_update.cc:410
#7  0x000055ebd51228e9 in mysql_execute_command (thd=0x7f4a8c000d58, is_called_from_prepared_stmt=false) at ../src/sql/sql_parse.cc:4405

    partition_element *next= NULL;
    bool error= true;
    List_iterator<partition_element> it(partitions);
    while (next != vers_info->hist_part)
      next= it++;

    while ((next= it++) != vers_info->now_part)
    {
      vers_info->hist_part= next;
      if (next->range_value > thd->query_start())
      {
        error= false;
        break;
      }
    }

3. `get_partition_id_range_for_endpoint()`

#0  get_partition_id_range_for_endpoint (part_info=0x7f4a8c037220, left_endpoint=true, include_endpoint=false) at ../src/sql/sql_partition.cc:3659
#1  0x000055ebd514c088 in get_part_iter_for_interval_via_mapping (part_info=0x7f4a8c037220, is_subpart=false, store_length_array=0x7f4a9e0fdf30, min_value=0x7f4a8c0c6ff8 "8m'_", max_value=0x7f4a8c0c7000 "\245\245\245\245\245\245\245\245\003", min_len=7, max_len=0, flags=6, part_iter=0x7f4a9e0fe530) at ../src/sql/sql_partition.cc:7932
#2  0x000055ebd4f86c74 in find_used_partitions (ppar=0x7f4a9e0fe168, key_tree=0x7f4a8c0c7090) at ../src/sql/opt_range.cc:4480
#3  0x000055ebd4f85dfd in prune_partitions (thd=0x7f4a8c000d58, table=0x55ebd7eb5b98, pprune_cond=0x7f4a8c018938) at ../src/sql/opt_range.cc:3995

    if (part_info->part_type == VERSIONING_PARTITION &&
        part_func_value < INT_MAX32 &&
        loc_part_id > part_info->vers_info->hist_part->id)
    {
      /*
        Historical query with AS OF point after the last history partition must
        include last history partition because it can be overflown (contain
        history rows out of right endpoint).
      */
      loc_part_id= part_info->vers_info->hist_part->id;
    }

midenok commented 2 years ago

Fix attempt

--- a/sql/sql_partition.cc
+++ b/sql/sql_partition.cc
@@ -3530,7 +3530,7 @@ int vers_get_partition_id(partition_info *part_info, uint32 *part_id,
   {
     longlong *range_value= part_info->range_int_array;
     uint max_hist_id= part_info->num_parts - 2;
-    uint min_hist_id= 0, loc_hist_id= vers_info->hist_part->id;
+    uint min_hist_id= 0, loc_hist_id= 0;
     ulong unused;
     my_time_t ts;

Fix variant 1

2000-01-01 01:00:00 gets into p0

--- a/sql/partition_info.cc
+++ b/sql/partition_info.cc
@@ -866,7 +866,7 @@ int partition_info::vers_set_hist_part(THD *thd)

   if (vers_info->interval.is_set())
   {
-    if (vers_info->hist_part->range_value > thd->query_start())
+    if (vers_info->hist_part->range_value >= thd->query_start())
       return 0;

     partition_element *next= NULL;
@@ -877,7 +877,7 @@ int partition_info::vers_set_hist_part(THD *thd)
     while ((next= it++) != vers_info->now_part)
     {
       vers_info->hist_part= next;
-      if (next->range_value > thd->query_start())
+      if (next->range_value >= thd->query_start())
         return 0;
     }
   }

midenok / mariadb

MDEV-25529 Auto-create: Pre-existing historical data is not partitioned as specified by ALTER #101

TODO

Reproduce

Links

Functions of interest

Getting partition id for written row

frame 1

frame 0

Pruning selects partition 0 even for long ago history

Getting MIN-row info

When index is used and the table is optimized out

frame 3

When there is no index

1. "to" frm created

2. "to" table opened

3. row written to "to"

Info: min/max in test_quick_select() coverage

Tests affected

Notes

All table read plan points

More tests affected (range_trp)

Info: temporary table created in ALTER

1. TMP name created

frame 1

2. TMP frm is written

frame 2 (ha_create_table)

3. TMP share created

4. TMP table opened

Share must be also reopened

frame 7

Cause

part_share->partitions_share_refs.num_parts set

Bug: pruning and placement discrepancy between CREATE and ALTER

Reproduce

Result

CREATE case

ALTER case

CREATE case: hist_part->id set

ALTER case: hist_part->id set

Cause

1. vers_get_partition_id()

2a. check_vers_constants()

2b. vers_set_hist_part() (when table is cached)

3. get_partition_id_range_for_endpoint()

Fix attempt

Fix variant 1

1. `vers_get_partition_id()`

2a. `check_vers_constants()`

2b. `vers_set_hist_part()` (when table is cached)

3. `get_partition_id_range_for_endpoint()`