- developers - lists.mariadb.org

Re: [Maria-developers] 24c653be25a: unpack_row: unpack a correct number of fields
by Sergei Golubchik 06 May '23

06 May '23

Hi, Nikita, On May 05, Nikita Malyavin wrote: > revision-id: 24c653be25a (mariadb-11.0.1-123-g24c653be25a) > parent(s): c1cbda5a2c8 > author: Nikita Malyavin > committer: Nikita Malyavin > timestamp: 2023-05-04 20:49:43 +0300 > message: > > unpack_row: unpack a correct number of fields > > diff --git a/sql/rpl_record.cc b/sql/rpl_record.cc > index 908d2c39d97..30bd80bdfc7 100644 > --- a/sql/rpl_record.cc > +++ b/sql/rpl_record.cc > @@ -228,24 +228,30 @@ int unpack_row(const rpl_group_info *rgi, TABLE *table, uint const colcnt, > const TABLE *conv_table= rpl_data.conv_table; > DBUG_PRINT("debug", ("Table data: tabldef: %p, conv_table: %p", > tabledef, conv_table)); > - > + bool is_online_alter= rpl_data.is_online_alter(); > DBUG_ASSERT(rgi); > > - for (field_ptr= begin_ptr; field_ptr < end_ptr && *field_ptr; ++field_ptr) > + for (field_ptr= begin_ptr; field_ptr < end_ptr > + /* In Online Alter conv_table can be wider than > + original table, but we need to unpack it all. */ > + && (*field_ptr || is_online_alter); I failed to understand this, could you please explain? The comment didn't help much, unfortunately. The test case didn't either. > + ++field_ptr) > { > + intptr fidx= field_ptr - begin_ptr; intptr is a variant of int with sizeof(intptr) == sizeof(void*). it's needed to cast a pointer to int, do some math, cast back. here you need my_ptrdiff_t or ptrdiff_t > /* > If there is a conversion table, we pick up the field pointer to > the conversion table. If the conversion table or the field > pointer is NULL, no conversions are necessary. > */ > - Field *conv_field= conv_table ? conv_table->field[field_ptr - begin_ptr] : NULL; > + Field *conv_field= conv_table ? conv_table->field[fidx] : NULL; > Field *const f= conv_field ? conv_field : *field_ptr; > +#ifdef DBUG_TRACE > + Field *dbg= is_online_alter ? f : *field_ptr; > +#endif > DBUG_PRINT("debug", ("Conversion %srequired for field '%s' (#%ld)", > conv_field ? "" : "not ", > - (*field_ptr)->field_name.str, > - (long) (field_ptr - begin_ptr))); > + dbg->field_name.str, > + (long) ())); eh? you surely meant `(long) fidx` ? how did it even compile? > DBUG_ASSERT(f != NULL); Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

2 1

Re: [Maria-developers] c5ce597f06a: MDEV-31043 ER_KEY_NOT_FOUND upon concurrent ALTER and transaction
by Sergei Golubchik 06 May '23

06 May '23

Hi, Nikita, On May 03, Nikita Malyavin wrote: > revision-id: c5ce597f06a (mariadb-11.0.1-115-gc5ce597f06a) > parent(s): da5a72f32d4 > author: Nikita Malyavin > committer: Nikita Malyavin > timestamp: 2023-05-01 01:15:41 +0300 > message: > > MDEV-31043 ER_KEY_NOT_FOUND upon concurrent ALTER and transaction > > Non-transactional engines changes are visible immediately the row operation > succeeds, so we should apply the changes to the online buffer immediately > after the statement ends. > > diff --git a/sql/log.cc b/sql/log.cc > index f30ce3bcbd1..1d98455c2b3 100644 > --- a/sql/log.cc > +++ b/sql/log.cc > @@ -7701,23 +7701,35 @@ static int binlog_online_alter_end_trans(THD *thd, bool all, bool commit) > auto *binlog= cache.sink_log; > DBUG_ASSERT(binlog); > > - bool do_commit= commit || !cache.hton->rollback || > - cache.hton->flags & HTON_NO_ROLLBACK; // Aria > + bool do_commit= (commit && is_ending_transaction) > + || cache.hton->flags & HTON_NO_ROLLBACK // Aria > + || !cache.hton->rollback; > + > + error= binlog_flush_pending_rows_event(thd, false, true, binlog, &cache); you've lost the useful comment about STMT_END. and you flush events for rollback too that seems like a waste. I personally would've kept the old structure of if()'s. Like - bool do_commit= commit || !cache.hton->rollback || + bool non_trans= !cache.hton->rollback || and - if (do_commit) + if (commit || non_trans) with - if (is_ending_transaction) + if (is_ending_transaction || non_trans) That would've called binlog flush only where it needs to be. > + > if (do_commit) > { > - // do not set STMT_END for last event to leave table open in altering thd > - error= binlog_flush_pending_rows_event(thd, false, true, binlog, &cache); > - if (is_ending_transaction) > + /* > + If the cache wasn't reinited to write, then it remains empty after > + the last write. > + */ > + if (cache.cache_log.type != READ_CACHE && !error) this is a confusing new condition. are you trying to avoid locking a mutex for an empty cache? If yes, you can check my_b_bytes_in_cache(), that'd be more clear. > { > mysql_mutex_lock(binlog->get_log_lock()); > error= binlog->write_cache(thd, &cache.cache_log); > mysql_mutex_unlock(binlog->get_log_lock()); > } > - else > - cache.store_prev_position(); > } > - else if (!is_ending_transaction) > + else if (!commit) // rollback > + { > cache.restore_prev_position(); > + } > + else add // trans engine, end of statement > + { > + DBUG_ASSERT(!is_ending_transaction); > + cache.store_prev_position(); > + } Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

2 3

Re: [Maria-developers] d25439f1cbb: MDEV-31140: FLUSH BINARY LOGS DELETE_DOMAIN_ID=(D) can errorneously delete active domains
by Kristian Nielsen 02 May '23

02 May '23

Andrei Elkin <andrei.elkin(a)mariadb.com> writes: >> Want to review a small patch for https://jira.mariadb.org/browse/MDEV-31140 ? > Thanks for a very good piece of analysis and the refinement! > I agree with both. Great, thanks for review! Do you have an opinion into which branch I should push it? The fix should be low-risk. I'm thinking maybe 11.0 or 10.11 is fine, since the bug does not affect normal operation. But I'm open to other suggestions, whatever is the current policy for this kind of bug? > The existed test was more than a bit unlucky, to confess, > to let the problem stay unnoticed. Yes, there is a good test coverage, so a bit unlucky, agree. - Kristian. > On the occasion of the International workers' day, let me wish you more > contribution, and inspiration for how to make not just this server > better alone :-)!!! Thanks, and all the best to you too :-) - Kristian. >> Kristian Nielsen <knielsen(a)knielsen-hq.org> writes: >> >>> revision-id: d25439f1cbb79e5467b4249792130bfe524e3f10 (mariadb-10.11.2-21-gd25439f1cbb) >>> parent(s): 656c2e18b1e9ea5d0314745f3988d126eedbc22a >>> author: Kristian Nielsen >>> committer: Kristian Nielsen >>> timestamp: 2023-04-27 12:16:07 +0200 >>> message: >>> >>> MDEV-31140: FLUSH BINARY LOGS DELETE_DOMAIN_ID=(D) can errorneously delete active domains >>> >>> Fix the code in rpl_binlog_state::drop_domain(), so that _all_ entries for >>> the domain in the binlog state must match an entry in the initial GTID_LIST, >>> not just one entry match. >>> >>> Signed-off-by: Kristian Nielsen <knielsen(a)knielsen-hq.org> >>> >>> --- >>> .../r/binlog_flush_binlogs_delete_domain.result | 14 ++++++++++--- >>> .../t/binlog_flush_binlogs_delete_domain.test | 13 +++++++++++- >>> sql/rpl_gtid.cc | 24 ++++++++++++---------- >>> 3 files changed, 36 insertions(+), 15 deletions(-) >>> >>> diff --git a/mysql-test/suite/binlog/r/binlog_flush_binlogs_delete_domain.result b/mysql-test/suite/binlog/r/binlog_flush_binlogs_delete_domain.result >>> index fdcfb4bfa01..1c11191802f 100644 >>> --- a/mysql-test/suite/binlog/r/binlog_flush_binlogs_delete_domain.result >>> +++ b/mysql-test/suite/binlog/r/binlog_flush_binlogs_delete_domain.result >>> @@ -46,15 +46,23 @@ Warning 1076 The current gtid binlog state is incompatible with a former one mis >>> Warning 1076 The gtid domain being deleted ('1') is not in the current binlog state >>> FLUSH BINARY LOGS DELETE_DOMAIN_ID = (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 0); >>> ERROR HY000: Could not delete gtid domain. Reason: binlog files may contain gtids from the domain ('1') being deleted. Make sure to first purge those files. >>> +MDEV-31140: Missing error from DELETE_DOMAIN_ID when gtid_binlog_state partially matches GTID_LIST. >>> FLUSH BINARY LOGS; >>> PURGE BINARY LOGS TO 'master-bin.000005'; >>> +SET @@SESSION.gtid_domain_id=8; >>> +SET @@SESSION.server_id=10*8 + 1; >>> +INSERT INTO t SELECT 1+MAX(a) FROM t; >>> +FLUSH BINARY LOGS DELETE_DOMAIN_ID = (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 0); >>> +ERROR HY000: Could not delete gtid domain. Reason: binlog files may contain gtids from the domain ('8') being deleted. Make sure to first purge those files. >>> +FLUSH BINARY LOGS; >>> +PURGE BINARY LOGS TO 'master-bin.000006'; >>> FLUSH BINARY LOGS DELETE_DOMAIN_ID = (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 0); >>> Warnings: >>> Warning 1076 The gtid domain being deleted ('0') is not in the current binlog state >>> Gtid_list of the current binlog does not contain 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 0: >>> -show binlog events in 'master-bin.000006' limit 1,1; >>> +show binlog events in 'master-bin.000007' limit 1,1; >>> Log_name Pos Event_type Server_id End_log_pos Info >>> -master-bin.000006 # Gtid_list 1 # [] >>> +master-bin.000007 # Gtid_list 1 # [] >>> SET @@SESSION.gtid_domain_id=1;; >>> SET @@SESSION.server_id=1; >>> SET @@SESSION.gtid_seq_no=1; >>> @@ -75,7 +83,7 @@ INSERT INTO t SET a=1; >>> SELECT @gtid_binlog_state_saved "as original state", @@GLOBAL.gtid_binlog_state as "out of order for 11 domain state"; >>> as original state out of order for 11 domain state >>> 1-1-1,1-2-2,11-11-11 1-1-1,1-2-2,11-11-1 >>> -PURGE BINARY LOGS TO 'master-bin.000007'; >>> +PURGE BINARY LOGS TO 'master-bin.000008'; >>> the following command succeeds with warnings >>> FLUSH BINARY LOGS DELETE_DOMAIN_ID = (1); >>> Warnings: >>> diff --git a/mysql-test/suite/binlog/t/binlog_flush_binlogs_delete_domain.test b/mysql-test/suite/binlog/t/binlog_flush_binlogs_delete_domain.test >>> index 8311f4bd800..1643ecff72d 100644 >>> --- a/mysql-test/suite/binlog/t/binlog_flush_binlogs_delete_domain.test >>> +++ b/mysql-test/suite/binlog/t/binlog_flush_binlogs_delete_domain.test >>> @@ -21,7 +21,6 @@ FLUSH BINARY LOGS DELETE_DOMAIN_ID = (); >>> --echo but with a warning >>> --let $binlog_pre_flush=query_get_value(SHOW MASTER STATUS, Position, 1) >>> FLUSH BINARY LOGS DELETE_DOMAIN_ID = (99); >>> ---let $binlog_start=$binlog_pre_flush >>> --source include/show_binary_logs.inc >>> >>> # Log one event in a specified domain and try to delete the domain >>> @@ -62,6 +61,8 @@ FLUSH BINARY LOGS DELETE_DOMAIN_ID = (1); >>> # expected overrun of the static buffers of underlying dynamic arrays is doing. >>> --let $domain_cnt=17 >>> --let $server_in_domain_cnt=3 >>> +--let $err_domain_id=`SELECT FLOOR($domain_cnt/2)` >>> +--let $err_server_id=`SELECT FLOOR($server_in_domain_cnt/2)` >>> --let $domain_list= >>> --disable_query_log >>> while ($domain_cnt) >>> @@ -86,6 +87,16 @@ while ($domain_cnt) >>> --error ER_BINLOG_CANT_DELETE_GTID_DOMAIN >>> --eval FLUSH BINARY LOGS DELETE_DOMAIN_ID = ($domain_list) >>> >>> +--echo MDEV-31140: Missing error from DELETE_DOMAIN_ID when gtid_binlog_state partially matches GTID_LIST. >>> +FLUSH BINARY LOGS; >>> +--let $purge_to_binlog= query_get_value(SHOW MASTER STATUS, File, 1) >>> +--eval PURGE BINARY LOGS TO '$purge_to_binlog' >>> +--eval SET @@SESSION.gtid_domain_id=$err_domain_id >>> +--eval SET @@SESSION.server_id=10*$err_domain_id + $err_server_id >>> +eval INSERT INTO t SELECT 1+MAX(a) FROM t; >>> +--error ER_BINLOG_CANT_DELETE_GTID_DOMAIN >>> +--eval FLUSH BINARY LOGS DELETE_DOMAIN_ID = ($domain_list) >>> + >>> # Now satisfy the safety condtion to purge log files containing $domain list >>> FLUSH BINARY LOGS; >>> --let $purge_to_binlog= query_get_value(SHOW MASTER STATUS, File, 1) >>> diff --git a/sql/rpl_gtid.cc b/sql/rpl_gtid.cc >>> index c4e5c75b10a..7b67a83b3dd 100644 >>> --- a/sql/rpl_gtid.cc >>> +++ b/sql/rpl_gtid.cc >>> @@ -2209,18 +2209,16 @@ rpl_binlog_state::drop_domain(DYNAMIC_ARRAY *ids, >>> >>> /* >>> For each domain_id from ids >>> - when no such domain in binlog state >>> - warn && continue >>> - For each domain.server's last gtid >>> - when not locate the last gtid in glev.list >>> - error out binlog state can't change >>> - otherwise continue >>> + If the domain is already absent from the binlog state >>> + Warn && continue >>> + If any GTID with that domain in binlog state is missing from glev.list >>> + Error out binlog state can't change >>> */ >>> for (ulong i= 0; i < ids->elements; i++) >>> { >>> rpl_binlog_state::element *elem= NULL; >>> uint32 *ptr_domain_id; >>> - bool not_match; >>> + bool all_found; >>> >>> ptr_domain_id= (uint32*) dynamic_array_ptr(ids, i); >>> elem= (rpl_binlog_state::element *) >>> @@ -2235,14 +2233,18 @@ rpl_binlog_state::drop_domain(DYNAMIC_ARRAY *ids, >>> continue; >>> } >>> >>> - for (not_match= true, k= 0; k < elem->hash.records; k++) >>> + all_found= true; >>> + for (k= 0; k < elem->hash.records && all_found; k++) >>> { >>> rpl_gtid *d_gtid= (rpl_gtid *)my_hash_element(&elem->hash, k); >>> - for (ulong l= 0; l < glev->count && not_match; l++) >>> - not_match= !(*d_gtid == glev->list[l]); >>> + bool match_found= false; >>> + for (ulong l= 0; l < glev->count && !match_found; l++) >>> + match_found= match_found || (*d_gtid == glev->list[l]); >>> + if (!match_found) >>> + all_found= false; >>> } >>> >>> - if (not_match) >>> + if (!all_found) >>> { >>> sprintf(errbuf, "binlog files may contain gtids from the domain ('%u') " >>> "being deleted. Make sure to first purge those files", > > modified sql/rpl_gtid.cc > @@ -2220,7 +2220,7 @@ rpl_binlog_state::drop_domain(DYNAMIC_ARRAY *ids, > { > rpl_binlog_state::element *elem= NULL; > uint32 *ptr_domain_id; > - bool not_match; > + ulong match; // count-down for matches found > > ptr_domain_id= (uint32*) dynamic_array_ptr(ids, i); > elem= (rpl_binlog_state::element *) > @@ -2235,14 +2235,15 @@ rpl_binlog_state::drop_domain(DYNAMIC_ARRAY *ids, > continue; > } > > - for (not_match= true, k= 0; k < elem->hash.records; k++) > + for (match= elem->hash.records, k= 0; > + k < elem->hash.records && match + k == elem->hash.records; k++) > { > rpl_gtid *d_gtid= (rpl_gtid *)my_hash_element(&elem->hash, k); > - for (ulong l= 0; l < glev->count && not_match; l++) > - not_match= !(*d_gtid == glev->list[l]); > + for (ulong l= 0, same= 0; l < glev->count && !same; l++) > + match-= (same= (*d_gtid == glev->list[l])); > } > > - if (not_match) > + if (match) > { > sprintf(errbuf, "binlog files may contain gtids from the domain ('%u') " > "being deleted. Make sure to first purge those files",

1 0

Re: [Maria-developers] MDEV-26301: Obvious wrong query result
by Sergey Petrunia 28 Apr '23

28 Apr '23

Hello Igor, I'm looking at your latest patch for MDEV-26301 in bb-10.4-igor and I see an obvious bug in the execution part: Split-materialized table re-fill is triggered by this code in sub_select(): if (join_tab->split_derived_to_update && !end_of_records) { table_map tab_map= join_tab->split_derived_to_update; for (uint i= 0; tab_map; i++, tab_map>>= 1) { if (tab_map & 1) join->map2table[i]->preread_init_done= false; } } (did you see Table_map_iterator class btw?) but what if the table in question uses join buffer and so uses sub_select_cache(), not sub_select()? Please find the testcase below: # 5 values create table t1(a int, b int); insert into t1 select seq,seq from seq_1_to_5; # 5 value groups of size 2 each create table t2(a int, b int, key(a)); insert into t2 select A.seq,B.seq from seq_1_to_25 A, seq_1_to_2 B; # 5 value groups of size 3 each create table t3(a int, b int, key(a)); insert into t3 select A.seq,B.seq from seq_1_to_5 A, seq_1_to_3 B; analyze table t1,t2,t3 persistent for all; create table t10 ( grp_id int, col1 int, key(grp_id) ); # 100 groups of 100 values each insert into t10 select A.seq, B.seq from seq_1_to_100 A, seq_1_to_100 B; # and X10 multiplier create table t11 ( col1 int, col2 int ); insert into t11 select A.seq, A.seq from seq_1_to_10 A; analyze table t10,t11 persistent for all; explain select * from ( (t1 left join t2 on t2.a=t1.b) left join t3 on t3.a=t1.b ) left join (select grp_id, count(*) from t10 left join t11 on t11.col1=t10.col1 group by grp_id) T on T.grp_id=t1.b; +------+-----------------+------------+------+---------------+--------+---------+---------+------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-----------------+------------+------+---------------+--------+---------+---------+------+-------------------------------------------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 5 | | | 1 | PRIMARY | t2 | ref | a | a | 5 | j1.t1.b | 2 | Using where | | 1 | PRIMARY | t3 | ref | a | a | 5 | j1.t1.b | 3 | Using where | | 1 | PRIMARY | <derived2> | ref | key0 | key0 | 5 | j1.t1.b | 10 | Using where | | 2 | LATERAL DERIVED | t10 | ref | grp_id | grp_id | 5 | j1.t1.b | 100 | | | 2 | LATERAL DERIVED | t11 | ALL | NULL | NULL | NULL | NULL | 10 | Using where; Using join buffer (flat, BNL join) | +------+-----------------+------------+------+---------------+--------+---------+---------+------+-------------------------------------------------+ Ok Note the query result: MariaDB [j1]> select * from -> ( -> (t1 left join t2 on t2.a=t1.b) -> left join t3 on t3.a=t1.b -> ) left join (select grp_id, count(*) -> from t10 left join t11 on t11.col1=t10.col1 -> group by grp_id) T on T.grp_id=t1.b; +------+------+------+------+------+------+--------+----------+ | a | b | a | b | a | b | grp_id | count(*) | +------+------+------+------+------+------+--------+----------+ | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 100 | | 1 | 1 | 1 | 1 | 1 | 2 | 1 | 100 | | 1 | 1 | 1 | 1 | 1 | 3 | 1 | 100 | | 1 | 1 | 1 | 2 | 1 | 1 | 1 | 100 | | 1 | 1 | 1 | 2 | 1 | 2 | 1 | 100 | | 1 | 1 | 1 | 2 | 1 | 3 | 1 | 100 | | 2 | 2 | 2 | 1 | 2 | 1 | 2 | 100 | | 2 | 2 | 2 | 1 | 2 | 2 | 2 | 100 | | 2 | 2 | 2 | 1 | 2 | 3 | 2 | 100 | | 2 | 2 | 2 | 2 | 2 | 1 | 2 | 100 | | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 100 | | 2 | 2 | 2 | 2 | 2 | 3 | 2 | 100 | | 3 | 3 | 3 | 1 | 3 | 1 | 3 | 100 | | 3 | 3 | 3 | 1 | 3 | 2 | 3 | 100 | | 3 | 3 | 3 | 1 | 3 | 3 | 3 | 100 | | 3 | 3 | 3 | 2 | 3 | 1 | 3 | 100 | | 3 | 3 | 3 | 2 | 3 | 2 | 3 | 100 | | 3 | 3 | 3 | 2 | 3 | 3 | 3 | 100 | | 4 | 4 | 4 | 1 | 4 | 1 | 4 | 100 | | 4 | 4 | 4 | 1 | 4 | 2 | 4 | 100 | | 4 | 4 | 4 | 1 | 4 | 3 | 4 | 100 | | 4 | 4 | 4 | 2 | 4 | 1 | 4 | 100 | | 4 | 4 | 4 | 2 | 4 | 2 | 4 | 100 | | 4 | 4 | 4 | 2 | 4 | 3 | 4 | 100 | | 5 | 5 | 5 | 1 | 5 | 1 | 5 | 100 | | 5 | 5 | 5 | 1 | 5 | 2 | 5 | 100 | | 5 | 5 | 5 | 1 | 5 | 3 | 5 | 100 | | 5 | 5 | 5 | 2 | 5 | 1 | 5 | 100 | | 5 | 5 | 5 | 2 | 5 | 2 | 5 | 100 | | 5 | 5 | 5 | 2 | 5 | 3 | 5 | 100 | +------+------+------+------+------+------+--------+----------+ 30 rows in set (0.015 sec) Now, let's make the tables not use indexes and use join buffer in the top-level select: explain select * from ( (t1 left join t2 use index() on t2.a=t1.b) left join t3 use index() on t3.a=t1.b ) left join (select grp_id, count(*) from t10 left join t11 on t11.col1=t10.col1 group by grp_id) T on T.grp_id=t1.b; +------+-----------------+------------+------+---------------+--------+---------+---------+------+--------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-----------------+------------+------+---------------+--------+---------+---------+------+--------------------------------------------------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 5 | | | 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 50 | Using where; Using join buffer (flat, BNL join) | | 1 | PRIMARY | t3 | ALL | NULL | NULL | NULL | NULL | 15 | Using where; Using join buffer (incremental, BNL join) | | 1 | PRIMARY | <derived2> | ref | key0 | key0 | 5 | j1.t1.b | 10 | Using where | | 2 | LATERAL DERIVED | t10 | ref | grp_id | grp_id | 5 | j1.t1.b | 100 | | | 2 | LATERAL DERIVED | t11 | ALL | NULL | NULL | NULL | NULL | 10 | Using where; Using join buffer (flat, BNL join) | +------+-----------------+------------+------+---------------+--------+---------+---------+------+--------------------------------------------------------+ select * from ( (t1 left join t2 use index() on t2.a=t1.b) left join t3 use index() on t3.a=t1.b ) left join (select grp_id, count(*) from t10 left join t11 on t11.col1=t10.col1 group by grp_id) T on T.grp_id=t1.b; +------+------+------+------+------+------+--------+----------+ | a | b | a | b | a | b | grp_id | count(*) | +------+------+------+------+------+------+--------+----------+ | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 100 | | 1 | 1 | 1 | 2 | 1 | 1 | 1 | 100 | | 1 | 1 | 1 | 1 | 1 | 2 | 1 | 100 | | 1 | 1 | 1 | 2 | 1 | 2 | 1 | 100 | | 1 | 1 | 1 | 1 | 1 | 3 | 1 | 100 | | 1 | 1 | 1 | 2 | 1 | 3 | 1 | 100 | | 2 | 2 | 2 | 1 | 2 | 1 | NULL | NULL | | 2 | 2 | 2 | 2 | 2 | 1 | NULL | NULL | | 2 | 2 | 2 | 1 | 2 | 2 | NULL | NULL | | 2 | 2 | 2 | 2 | 2 | 2 | NULL | NULL | | 2 | 2 | 2 | 1 | 2 | 3 | NULL | NULL | | 2 | 2 | 2 | 2 | 2 | 3 | NULL | NULL | | 3 | 3 | 3 | 1 | 3 | 1 | NULL | NULL | | 3 | 3 | 3 | 2 | 3 | 1 | NULL | NULL | | 3 | 3 | 3 | 1 | 3 | 2 | NULL | NULL | | 3 | 3 | 3 | 2 | 3 | 2 | NULL | NULL | | 3 | 3 | 3 | 1 | 3 | 3 | NULL | NULL | | 3 | 3 | 3 | 2 | 3 | 3 | NULL | NULL | | 4 | 4 | 4 | 1 | 4 | 1 | NULL | NULL | | 4 | 4 | 4 | 2 | 4 | 1 | NULL | NULL | | 4 | 4 | 4 | 1 | 4 | 2 | NULL | NULL | | 4 | 4 | 4 | 2 | 4 | 2 | NULL | NULL | | 4 | 4 | 4 | 1 | 4 | 3 | NULL | NULL | | 4 | 4 | 4 | 2 | 4 | 3 | NULL | NULL | | 5 | 5 | 5 | 1 | 5 | 1 | NULL | NULL | | 5 | 5 | 5 | 2 | 5 | 1 | NULL | NULL | | 5 | 5 | 5 | 1 | 5 | 2 | NULL | NULL | | 5 | 5 | 5 | 2 | 5 | 2 | NULL | NULL | | 5 | 5 | 5 | 1 | 5 | 3 | NULL | NULL | | 5 | 5 | 5 | 2 | 5 | 3 | NULL | NULL | +------+------+------+------+------+------+--------+----------+ 30 rows in set (0.008 sec) Bummer. BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net

1 0

Re: [Maria-developers] d25439f1cbb: MDEV-31140: FLUSH BINARY LOGS DELETE_DOMAIN_ID=(D) can errorneously delete active domains
by Kristian Nielsen 27 Apr '23

27 Apr '23

Hi Andrei, Want to review a small patch for https://jira.mariadb.org/browse/MDEV-31140 ? I found this problem while trying to find the root cause of https://jira.mariadb.org/browse/MDEV-30386 . However this bug seems to be unrelated to MDEV-30386. Basically the logic for comparing the gtid_binlog_state with the GTID_LIST event was flawed, making it possible to delete a domain_id from the binlog state when this should not be allowed. - Kristian. Kristian Nielsen <knielsen(a)knielsen-hq.org> writes: > revision-id: d25439f1cbb79e5467b4249792130bfe524e3f10 (mariadb-10.11.2-21-gd25439f1cbb) > parent(s): 656c2e18b1e9ea5d0314745f3988d126eedbc22a > author: Kristian Nielsen > committer: Kristian Nielsen > timestamp: 2023-04-27 12:16:07 +0200 > message: > > MDEV-31140: FLUSH BINARY LOGS DELETE_DOMAIN_ID=(D) can errorneously delete active domains > > Fix the code in rpl_binlog_state::drop_domain(), so that _all_ entries for > the domain in the binlog state must match an entry in the initial GTID_LIST, > not just one entry match. > > Signed-off-by: Kristian Nielsen <knielsen(a)knielsen-hq.org> > > --- > .../r/binlog_flush_binlogs_delete_domain.result | 14 ++++++++++--- > .../t/binlog_flush_binlogs_delete_domain.test | 13 +++++++++++- > sql/rpl_gtid.cc | 24 ++++++++++++---------- > 3 files changed, 36 insertions(+), 15 deletions(-) > > diff --git a/mysql-test/suite/binlog/r/binlog_flush_binlogs_delete_domain.result b/mysql-test/suite/binlog/r/binlog_flush_binlogs_delete_domain.result > index fdcfb4bfa01..1c11191802f 100644 > --- a/mysql-test/suite/binlog/r/binlog_flush_binlogs_delete_domain.result > +++ b/mysql-test/suite/binlog/r/binlog_flush_binlogs_delete_domain.result > @@ -46,15 +46,23 @@ Warning 1076 The current gtid binlog state is incompatible with a former one mis > Warning 1076 The gtid domain being deleted ('1') is not in the current binlog state > FLUSH BINARY LOGS DELETE_DOMAIN_ID = (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 0); > ERROR HY000: Could not delete gtid domain. Reason: binlog files may contain gtids from the domain ('1') being deleted. Make sure to first purge those files. > +MDEV-31140: Missing error from DELETE_DOMAIN_ID when gtid_binlog_state partially matches GTID_LIST. > FLUSH BINARY LOGS; > PURGE BINARY LOGS TO 'master-bin.000005'; > +SET @@SESSION.gtid_domain_id=8; > +SET @@SESSION.server_id=10*8 + 1; > +INSERT INTO t SELECT 1+MAX(a) FROM t; > +FLUSH BINARY LOGS DELETE_DOMAIN_ID = (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 0); > +ERROR HY000: Could not delete gtid domain. Reason: binlog files may contain gtids from the domain ('8') being deleted. Make sure to first purge those files. > +FLUSH BINARY LOGS; > +PURGE BINARY LOGS TO 'master-bin.000006'; > FLUSH BINARY LOGS DELETE_DOMAIN_ID = (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 0); > Warnings: > Warning 1076 The gtid domain being deleted ('0') is not in the current binlog state > Gtid_list of the current binlog does not contain 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 0: > -show binlog events in 'master-bin.000006' limit 1,1; > +show binlog events in 'master-bin.000007' limit 1,1; > Log_name Pos Event_type Server_id End_log_pos Info > -master-bin.000006 # Gtid_list 1 # [] > +master-bin.000007 # Gtid_list 1 # [] > SET @@SESSION.gtid_domain_id=1;; > SET @@SESSION.server_id=1; > SET @@SESSION.gtid_seq_no=1; > @@ -75,7 +83,7 @@ INSERT INTO t SET a=1; > SELECT @gtid_binlog_state_saved "as original state", @@GLOBAL.gtid_binlog_state as "out of order for 11 domain state"; > as original state out of order for 11 domain state > 1-1-1,1-2-2,11-11-11 1-1-1,1-2-2,11-11-1 > -PURGE BINARY LOGS TO 'master-bin.000007'; > +PURGE BINARY LOGS TO 'master-bin.000008'; > the following command succeeds with warnings > FLUSH BINARY LOGS DELETE_DOMAIN_ID = (1); > Warnings: > diff --git a/mysql-test/suite/binlog/t/binlog_flush_binlogs_delete_domain.test b/mysql-test/suite/binlog/t/binlog_flush_binlogs_delete_domain.test > index 8311f4bd800..1643ecff72d 100644 > --- a/mysql-test/suite/binlog/t/binlog_flush_binlogs_delete_domain.test > +++ b/mysql-test/suite/binlog/t/binlog_flush_binlogs_delete_domain.test > @@ -21,7 +21,6 @@ FLUSH BINARY LOGS DELETE_DOMAIN_ID = (); > --echo but with a warning > --let $binlog_pre_flush=query_get_value(SHOW MASTER STATUS, Position, 1) > FLUSH BINARY LOGS DELETE_DOMAIN_ID = (99); > ---let $binlog_start=$binlog_pre_flush > --source include/show_binary_logs.inc > > # Log one event in a specified domain and try to delete the domain > @@ -62,6 +61,8 @@ FLUSH BINARY LOGS DELETE_DOMAIN_ID = (1); > # expected overrun of the static buffers of underlying dynamic arrays is doing. > --let $domain_cnt=17 > --let $server_in_domain_cnt=3 > +--let $err_domain_id=`SELECT FLOOR($domain_cnt/2)` > +--let $err_server_id=`SELECT FLOOR($server_in_domain_cnt/2)` > --let $domain_list= > --disable_query_log > while ($domain_cnt) > @@ -86,6 +87,16 @@ while ($domain_cnt) > --error ER_BINLOG_CANT_DELETE_GTID_DOMAIN > --eval FLUSH BINARY LOGS DELETE_DOMAIN_ID = ($domain_list) > > +--echo MDEV-31140: Missing error from DELETE_DOMAIN_ID when gtid_binlog_state partially matches GTID_LIST. > +FLUSH BINARY LOGS; > +--let $purge_to_binlog= query_get_value(SHOW MASTER STATUS, File, 1) > +--eval PURGE BINARY LOGS TO '$purge_to_binlog' > +--eval SET @@SESSION.gtid_domain_id=$err_domain_id > +--eval SET @@SESSION.server_id=10*$err_domain_id + $err_server_id > +eval INSERT INTO t SELECT 1+MAX(a) FROM t; > +--error ER_BINLOG_CANT_DELETE_GTID_DOMAIN > +--eval FLUSH BINARY LOGS DELETE_DOMAIN_ID = ($domain_list) > + > # Now satisfy the safety condtion to purge log files containing $domain list > FLUSH BINARY LOGS; > --let $purge_to_binlog= query_get_value(SHOW MASTER STATUS, File, 1) > diff --git a/sql/rpl_gtid.cc b/sql/rpl_gtid.cc > index c4e5c75b10a..7b67a83b3dd 100644 > --- a/sql/rpl_gtid.cc > +++ b/sql/rpl_gtid.cc > @@ -2209,18 +2209,16 @@ rpl_binlog_state::drop_domain(DYNAMIC_ARRAY *ids, > > /* > For each domain_id from ids > - when no such domain in binlog state > - warn && continue > - For each domain.server's last gtid > - when not locate the last gtid in glev.list > - error out binlog state can't change > - otherwise continue > + If the domain is already absent from the binlog state > + Warn && continue > + If any GTID with that domain in binlog state is missing from glev.list > + Error out binlog state can't change > */ > for (ulong i= 0; i < ids->elements; i++) > { > rpl_binlog_state::element *elem= NULL; > uint32 *ptr_domain_id; > - bool not_match; > + bool all_found; > > ptr_domain_id= (uint32*) dynamic_array_ptr(ids, i); > elem= (rpl_binlog_state::element *) > @@ -2235,14 +2233,18 @@ rpl_binlog_state::drop_domain(DYNAMIC_ARRAY *ids, > continue; > } > > - for (not_match= true, k= 0; k < elem->hash.records; k++) > + all_found= true; > + for (k= 0; k < elem->hash.records && all_found; k++) > { > rpl_gtid *d_gtid= (rpl_gtid *)my_hash_element(&elem->hash, k); > - for (ulong l= 0; l < glev->count && not_match; l++) > - not_match= !(*d_gtid == glev->list[l]); > + bool match_found= false; > + for (ulong l= 0; l < glev->count && !match_found; l++) > + match_found= match_found || (*d_gtid == glev->list[l]); > + if (!match_found) > + all_found= false; > } > > - if (not_match) > + if (!all_found) > { > sprintf(errbuf, "binlog files may contain gtids from the domain ('%u') " > "being deleted. Make sure to first purge those files",

1 0

Re: [Maria-developers] MDEV-30946 Index usage for DATE(datetime_column) = const does not work for DELETE and UPDATE
by Sergey Petrunia 12 Apr '23

12 Apr '23

Hi Oleg, Why is the call to Item::date_conds_transformer done after prune_partitions()? This causes partition pruning not to be able to use the conditions: --source include/have_partition.inc --source include/have_sequence.inc create table t1 ( a datetime, key(a) ) partition by range(year(a)) ( partition p0 values less than (2022), partition p1 values less than (MAXVALUE) ); insert into t1 select date_add('2020-01-01', interval seq day) from seq_1_to_1000; explain partitions select * from t1 where year(a) = 2020; explain partitions delete from t1 where year(a) = 2020; drop table t1; Gives: explain partitions select * from t1 where year(a) = 2020; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0 range a a 6 NULL 348 Using where; Using index explain partitions delete from t1 where year(a) = 2020; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1 ALL a NULL NULL NULL 1000 Using where Note that SELECT uses one partition while DELETE uses two. Please fix this. BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net

1 0

[Maria-developers] Participation in MariaDB Community's Open Source Project
by 和映泉 09 Apr '23

09 Apr '23

Dear MariaDB Development Team, I am writing to express my interest in participating in your open source project. I am an experienced programmer with knowledge of multiple programming languages and technologies, and I have some understanding of MariaDB products and source code. I believe I can make positive contributions to your project and gain more learning and growth opportunities from it. Here is a summary of my programming experience and skills: - I have 6 years of experience in software development and have participated in commercial projects. - I am proficient in multiple programming languages such as Java, Python, and Scala, and have knowledge of SQL and database technologies. - I am familiar with Linux systems and command-line tools, and can use version control tools such as Git. - I have some understanding and practical experience in software testing, code quality, and performance optimization. In addition, I have also used MySQL products and have some understanding of their functions and features. I believe this will help me better understand and contribute to your open source project. Thank you for taking the time to read my email. I look forward to your reply and hope to have the opportunity to contribute to the MariaDB community. Best regards, Yingquan He

2 1

[Maria-developers] CI failing on almost all branches - time to adopt protected branches?
by Otto Kekäläinen 03 Apr '23

03 Apr '23

Hello! Has there recently been any discussions about enabling the Github 'protected branch' feature? Review of current mainlines shows that ALL but 10.4 are broken and most of them on different tests. I am afraid that quality and test fixes will never catch up if people are allowed to push code that makes testable things fail. Using protected branches would prevent people from being able to push commits on mainline that fail in CI. https://github.com/MariaDB/server/tree/10.3 / 4c4939b NOT OK: - amd64-debian-10-debug-embedded fails on sql_sequence.kill - amd64-fedora-36 fails on main.func_math https://github.com/MariaDB/server/tree/10.4 / 1767390 OK https://github.com/MariaDB/server/tree/10.5 / ac5a534 NOT OK: - amd64-ubuntu-2004-msan fails on main.selectivity_innodb - amd64-windows fails to compile https://github.com/MariaDB/server/tree/10.6 / dc1d621 NOT OK: - buildbot/amd64-ubuntu-2204-msan fails on innodb.gap_locks https://github.com/MariaDB/server/tree/10.7 / bc656c4 NOT OK: - buildbot/amd64-debian-10 fails to start MTR https://github.com/MariaDB/server/tree/10.8 / dd2fe81 NOT OK: - amd64-ubuntu-2204-msan fails on MTR timeout (crash?) https://github.com/MariaDB/server/tree/10.9 / 55e78eb NOT OK: - buildbot/amd64-ubuntu-2204-msan fails on mariabackup.incremental_page_compressed innodb.innodb_page_compressed https://github.com/MariaDB/server/tree/10.10 / 191821f NOT OK: - amd64-ubuntu-2004-debug fails on binlog.binlog_truncate_multi_engine - amd64-ubuntu-2204-debug-ps fails on main.bad_startup_options https://github.com/MariaDB/server/tree/10.11 / d84a282 NOT OK: - buildbot/amd64-ubuntu-2204-msan fails on mariabackup.incremental_page_compressed innodb.innodb_page_compressed https://github.com/MariaDB/server/tree/11.0 / 8e55d7e NOT OK: - buildbot/amd64-ubuntu-2004-msan fails on innodb.table_definition_cache_debug main.lock_sync - continuous-integration/appveyor/branch fails on main.innodb_ext_key

2 1

[Maria-developers] How to get started with GSOC contribution
by Komala Elusuri 25 Mar '23

25 Mar '23

Respected sir, I am Komala, a computer science undergraduate from India. I am new to open source contributions but I am well aware of python, C, C++, Java,JavaScript and Databases. I did certifications on Google cloud computing foundation with Kubernetes and Python projects from Infosys springboard. I have experience as an intern from Smart Knower. I would love to contribute to your organisation but could you please tell me how to get started? Hoping to hear from you soon. Regards Komala

1 0

Re: [Maria-developers] 940d028521f: MDEV-30164 System variable for default collations
by Sergei Golubchik 21 Mar '23

21 Mar '23

Hi, Alexander, I'm sorry it took a while. I'm still thinking about the whole thing, it's a rather big change for a really fringe functionality. But I failed to come up with something better so far. Code-wise the patch is mostly fine. See few small comments below, and one slightly larger comment re. replication. On Mar 07, Alexander Barkov wrote: > revision-id: 940d028521f (mariadb-10.11.1-4-g940d028521f) > parent(s): 71dedd0ebcd > author: Alexander Barkov > committer: Alexander Barkov > timestamp: 2022-12-14 20:00:22 +0400 > message: > > MDEV-30164 System variable for default collations > > This patch adds a way to override default collations > (or "character set collations") for desired character sets. > > diff --git a/sql/lex_charset.h b/sql/lex_charset.h > --- a/sql/lex_charset.h > +++ b/sql/lex_charset.h > @@ -544,6 +699,20 @@ struct Lex_exact_charset_extended_collation_attrs_st > { > return m_ci; > } > + CHARSET_INFO *charset_info(const Charset_collation_map_st &map) const > + { > + switch (m_type) > + { > + case Lex_exact_charset_extended_collation_attrs_st::TYPE_CHARACTER_SET: > + return map.get_collation_for_charset(m_ci); > + case TYPE_EMPTY: Lex_exact_charset_extended_collation_attrs_st::TYPE_EMPTY (or all case labels without a struct name) > + case Lex_exact_charset_extended_collation_attrs_st::TYPE_CHARACTER_SET_COLLATE_EXACT: > + case Lex_exact_charset_extended_collation_attrs_st::TYPE_COLLATE_CONTEXTUALLY_TYPED: COLLATE DEFAULT is TYPE_COLLATE_CONTEXTUALLY_TYPED. shouldn't it use the map too? > + case Lex_exact_charset_extended_collation_attrs_st::TYPE_COLLATE_EXACT: > + break; > + } > + return m_ci; > + } > Type type() const > { > return m_type; > diff --git a/sql/log_event.h b/sql/log_event.h > --- a/sql/log_event.h > +++ b/sql/log_event.h > @@ -2147,6 +2153,8 @@ class Query_log_event: public Log_event > bool sql_mode_inited; > bool charset_inited; > > + LEX_CSTRING character_set_collations_str; better LEX_CUSTRING, don't you think? It's not even a _str. > + > uint32 flags2; > sql_mode_t sql_mode; > ulong auto_increment_increment, auto_increment_offset; > diff --git a/sql/log_event_server.cc b/sql/log_event_server.cc > --- a/sql/log_event_server.cc > +++ b/sql/log_event_server.cc > @@ -1194,6 +1194,14 @@ bool Query_log_event::write() > int2store(start+2, auto_increment_offset); > start+= 4; > } > + > + if (thd && thd->variables.character_set_collations.count()) > + { > + *start++= Q_COLLATIONS_SESSION; > + size_t len= thd->variables.character_set_collations.to_binary((char*)start); > + start+= len; > + } Perhaps, detect if it's needed? A cheap way of doing it would be to extend your Elem_st with a query_id. And every time you find_elem_by_charset, you set this elem's query_id to thd->query_id. And here you write only elements with the current query id. If any. Another approach would be to have a bitmap, like uchar used_default_coll_mapping; and in find_elem_by_charset() you set the bit, like used_default_coll_mapping |= 1 << i; and then, again, print affected collations, if any. Most often used_default_coll_mapping will likely be zero one more question. In, say, 10.10->11.1 replication master and slave will have different default collations, but thd->variables.character_set_collations will not reflect that. How do you plan to solve it? > + > if (charset_inited) > { > *start++= Q_CHARSET_CODE; Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

2 3

Re: [Maria-developers] MDEV-28152: Features for sequence
by Oleksandr Byelkin 21 Mar '23

21 Mar '23

Hi, Yuchen! As for first work for the server itself I find it is very good, but please read my notes below (many of them just code style ones) JFYI if you fix something in the wrong or old code style, you should fix it, but please do not fix the style only things in the code you are not going to change. It is one of the not yet written ( :( ) rules of our fix code guide. As well as code style defined only by old memory and examples in the code. > diff --git a/mysql-test/main/information_schema.result b/mysql-test/main/information_schema.result > index e46014e44b9..ce111c58796 100644 > --- a/mysql-test/main/information_schema.result > +++ b/mysql-test/main/information_schema.result > @@ -81,6 +81,7 @@ REFERENTIAL_CONSTRAINTS > ROUTINES > SCHEMATA > SCHEMA_PRIVILEGES > +SEQUENCES > SESSION_STATUS > SESSION_VARIABLES > SPATIAL_REF_SYS > diff --git a/mysql-test/main/information_schema_all_engines.result b/mysql-test/main/information_schema_all_engines.result > index 23a853e363c..316871995c1 100644 > --- a/mysql-test/main/information_schema_all_engines.result > +++ b/mysql-test/main/information_schema_all_engines.result > @@ -52,6 +52,7 @@ REFERENTIAL_CONSTRAINTS > ROUTINES > SCHEMATA > SCHEMA_PRIVILEGES > +SEQUENCES > SESSION_STATUS > SESSION_VARIABLES > SPATIAL_REF_SYS > @@ -133,6 +134,7 @@ REFERENTIAL_CONSTRAINTS CONSTRAINT_SCHEMA > ROUTINES ROUTINE_SCHEMA > SCHEMATA SCHEMA_NAME > SCHEMA_PRIVILEGES TABLE_SCHEMA > +SEQUENCES SEQUENCE_SCHEMA > SESSION_STATUS VARIABLE_NAME > SESSION_VARIABLES VARIABLE_NAME > SPATIAL_REF_SYS SRID > @@ -214,6 +216,7 @@ REFERENTIAL_CONSTRAINTS CONSTRAINT_SCHEMA > ROUTINES ROUTINE_SCHEMA > SCHEMATA SCHEMA_NAME > SCHEMA_PRIVILEGES TABLE_SCHEMA > +SEQUENCES SEQUENCE_SCHEMA > SESSION_STATUS VARIABLE_NAME > SESSION_VARIABLES VARIABLE_NAME > SPATIAL_REF_SYS SRID > @@ -299,6 +302,7 @@ REFERENTIAL_CONSTRAINTS information_schema.REFERENTIAL_CONSTRAINTS 1 > ROUTINES information_schema.ROUTINES 1 > SCHEMATA information_schema.SCHEMATA 1 > SCHEMA_PRIVILEGES information_schema.SCHEMA_PRIVILEGES 1 > +SEQUENCES information_schema.SEQUENCES 1 > SESSION_STATUS information_schema.SESSION_STATUS 1 > SESSION_VARIABLES information_schema.SESSION_VARIABLES 1 > SPATIAL_REF_SYS information_schema.SPATIAL_REF_SYS 1 > @@ -369,6 +373,7 @@ Database: information_schema > | ROUTINES | > | SCHEMATA | > | SCHEMA_PRIVILEGES | > +| SEQUENCES | > | SESSION_STATUS | > | SESSION_VARIABLES | > | SPATIAL_REF_SYS | > @@ -440,6 +445,7 @@ Database: INFORMATION_SCHEMA > | ROUTINES | > | SCHEMATA | > | SCHEMA_PRIVILEGES | > +| SEQUENCES | > | SESSION_STATUS | > | SESSION_VARIABLES | > | SPATIAL_REF_SYS | > @@ -463,5 +469,5 @@ Wildcard: inf_rmation_schema > | information_schema | > SELECT table_schema, count(*) FROM information_schema.TABLES WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test', 'mysqltest') GROUP BY TABLE_SCHEMA; > table_schema count(*) > -information_schema 66 > +information_schema 67 > mysql 31 > diff --git a/mysql-test/main/mysqldump.result b/mysql-test/main/mysqldump.result > index 44ed94e43c9..41607ea3068 100644 > --- a/mysql-test/main/mysqldump.result > +++ b/mysql-test/main/mysqldump.result > @@ -6369,16 +6369,16 @@ NEXTVAL(d.s1) NEXTVAL(d.s2) NEXTVAL(d.s3) NEXTVAL(d.s4) > # Show create before dump > show create sequence d.s1; > Table Create Table > -s1 CREATE SEQUENCE `s1` start with 100 minvalue 100 maxvalue 1100 increment by 10 cache 1000 cycle ENGINE=MyISAM > +s1 CREATE SEQUENCE `s1` as bigint start with 100 minvalue 100 maxvalue 1100 increment by 10 cache 1000 cycle ENGINE=MyISAM > show create sequence d.s2; > Table Create Table > -s2 CREATE SEQUENCE `s2` start with 200 minvalue 200 maxvalue 1200 increment by 20 cache 1000 cycle ENGINE=MyISAM > +s2 CREATE SEQUENCE `s2` as bigint start with 200 minvalue 200 maxvalue 1200 increment by 20 cache 1000 cycle ENGINE=MyISAM > show create sequence d.s3; > Table Create Table > -s3 CREATE SEQUENCE `s3` start with 300 minvalue 300 maxvalue 1300 increment by 30 cache 1000 cycle ENGINE=MyISAM > +s3 CREATE SEQUENCE `s3` as bigint start with 300 minvalue 300 maxvalue 1300 increment by 30 cache 1000 cycle ENGINE=MyISAM > show create sequence d.s4; > Table Create Table > -s4 CREATE SEQUENCE `s4` start with 400 minvalue 400 maxvalue 1400 increment by 40 cache 1000 cycle ENGINE=MyISAM > +s4 CREATE SEQUENCE `s4` as bigint start with 400 minvalue 400 maxvalue 1400 increment by 40 cache 1000 cycle ENGINE=MyISAM > # Dump sequence without `--no-data` > # Restore from mysqldump > SETVAL(`s1`, 1101, 0) > @@ -6392,16 +6392,16 @@ SETVAL(`s4`, 1401, 0) > # Show create after restore > show create sequence d.s1; > Table Create Table > -s1 CREATE SEQUENCE `s1` start with 100 minvalue 100 maxvalue 1100 increment by 10 cache 1000 cycle ENGINE=MyISAM > +s1 CREATE SEQUENCE `s1` as bigint start with 100 minvalue 100 maxvalue 1100 increment by 10 cache 1000 cycle ENGINE=MyISAM > show create sequence d.s2; > Table Create Table > -s2 CREATE SEQUENCE `s2` start with 200 minvalue 200 maxvalue 1200 increment by 20 cache 1000 cycle ENGINE=MyISAM > +s2 CREATE SEQUENCE `s2` as bigint start with 200 minvalue 200 maxvalue 1200 increment by 20 cache 1000 cycle ENGINE=MyISAM > show create sequence d.s3; > Table Create Table > -s3 CREATE SEQUENCE `s3` start with 300 minvalue 300 maxvalue 1300 increment by 30 cache 1000 cycle ENGINE=MyISAM > +s3 CREATE SEQUENCE `s3` as bigint start with 300 minvalue 300 maxvalue 1300 increment by 30 cache 1000 cycle ENGINE=MyISAM > show create sequence d.s4; > Table Create Table > -s4 CREATE SEQUENCE `s4` start with 400 minvalue 400 maxvalue 1400 increment by 40 cache 1000 cycle ENGINE=MyISAM > +s4 CREATE SEQUENCE `s4` as bigint start with 400 minvalue 400 maxvalue 1400 increment by 40 cache 1000 cycle ENGINE=MyISAM > SELECT NEXTVAL(d.s1),NEXTVAL(d.s2),NEXTVAL(d.s3), NEXTVAL(d.s4); > NEXTVAL(d.s1) NEXTVAL(d.s2) NEXTVAL(d.s3) NEXTVAL(d.s4) > 100 200 300 400 > @@ -6418,16 +6418,16 @@ SETVAL(`s4`, 1401, 0) > # Show create after restore `--no-data` > show create sequence d.s1; > Table Create Table > -s1 CREATE SEQUENCE `s1` start with 100 minvalue 100 maxvalue 1100 increment by 10 cache 1000 cycle ENGINE=MyISAM > +s1 CREATE SEQUENCE `s1` as bigint start with 100 minvalue 100 maxvalue 1100 increment by 10 cache 1000 cycle ENGINE=MyISAM > show create sequence d.s2; > Table Create Table > -s2 CREATE SEQUENCE `s2` start with 200 minvalue 200 maxvalue 1200 increment by 20 cache 1000 cycle ENGINE=MyISAM > +s2 CREATE SEQUENCE `s2` as bigint start with 200 minvalue 200 maxvalue 1200 increment by 20 cache 1000 cycle ENGINE=MyISAM > show create sequence d.s3; > Table Create Table > -s3 CREATE SEQUENCE `s3` start with 300 minvalue 300 maxvalue 1300 increment by 30 cache 1000 cycle ENGINE=MyISAM > +s3 CREATE SEQUENCE `s3` as bigint start with 300 minvalue 300 maxvalue 1300 increment by 30 cache 1000 cycle ENGINE=MyISAM > show create sequence d.s4; > Table Create Table > -s4 CREATE SEQUENCE `s4` start with 400 minvalue 400 maxvalue 1400 increment by 40 cache 1000 cycle ENGINE=MyISAM > +s4 CREATE SEQUENCE `s4` as bigint start with 400 minvalue 400 maxvalue 1400 increment by 40 cache 1000 cycle ENGINE=MyISAM > SELECT NEXTVAL(d.s1),NEXTVAL(d.s2),NEXTVAL(d.s3), NEXTVAL(d.s4); > NEXTVAL(d.s1) NEXTVAL(d.s2) NEXTVAL(d.s3) NEXTVAL(d.s4) > 100 200 300 400 > @@ -6443,7 +6443,7 @@ SETVAL(`s4`, 1401, 0) > 1401 > show create sequence d2.s1; > Table Create Table > -s1 CREATE SEQUENCE `s1` start with 100 minvalue 100 maxvalue 1100 increment by 10 cache 1000 cycle ENGINE=MyISAM > +s1 CREATE SEQUENCE `s1` as bigint start with 100 minvalue 100 maxvalue 1100 increment by 10 cache 1000 cycle ENGINE=MyISAM > drop sequence d.s1, d.s2, d.s3, d.s4; > drop database d; > drop database d2; > diff --git a/mysql-test/suite/funcs_1/r/is_columns_is.result b/mysql-test/suite/funcs_1/r/is_columns_is.result > index c88a3a9ac8d..03eece0b4ae 100644 > --- a/mysql-test/suite/funcs_1/r/is_columns_is.result > +++ b/mysql-test/suite/funcs_1/r/is_columns_is.result > @@ -339,6 +339,21 @@ def information_schema SCHEMA_PRIVILEGES IS_GRANTABLE 5 NULL NO varchar 3 9 NULL > def information_schema SCHEMA_PRIVILEGES PRIVILEGE_TYPE 4 NULL NO varchar 64 192 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(64) select NEVER NULL > def information_schema SCHEMA_PRIVILEGES TABLE_CATALOG 2 NULL NO varchar 512 1536 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(512) select NEVER NULL > def information_schema SCHEMA_PRIVILEGES TABLE_SCHEMA 3 NULL NO varchar 64 192 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(64) select NEVER NULL > +def information_schema SEQUENCES CYCLE_OPTION 12 NULL NO bigint NULL NULL 19 0 NULL NULL NULL bigint(21) select NEVER NULL As I remember CYCLE_OPTION is boolean or YES/NO, why does it become bigint ? > +def information_schema SEQUENCES DATA_TYPE 4 NULL NO varchar 64 192 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(64) select NEVER NULL > +def information_schema SEQUENCES DECLARED_DATA_TYPE 13 NULL YES int NULL NULL 10 0 NULL NULL NULL int(21) select NEVER NULL > +def information_schema SEQUENCES DECLARED_NUMERIC_PRECISION 14 NULL YES int NULL NULL 10 0 NULL NULL NULL int(21) select NEVER NULL > +def information_schema SEQUENCES DECLARED_NUMERIC_SCALE 15 NULL YES int NULL NULL 10 0 NULL NULL NULL int(21) select NEVER NULL We do not need DECLARED_* it is only needed for feature T322 “Declared data type attributes” and we do not have it. > +def information_schema SEQUENCES INCREMENT 11 NULL NO bigint NULL NULL 19 0 NULL NULL NULL bigint(21) select NEVER NULL > +def information_schema SEQUENCES MAXIMUM_VALUE 10 NULL NO decimal NULL NULL 21 0 NULL NULL NULL decimal(21,0) select NEVER NULL > +def information_schema SEQUENCES MINIMUM_VALUE 9 NULL NO decimal NULL NULL 21 0 NULL NULL NULL decimal(21,0) select NEVER NULL good decision to make it decimal > +def information_schema SEQUENCES NUMERIC_PRECISION 5 NULL NO int NULL NULL 10 0 NULL NULL NULL int(21) select NEVER NULL > +def information_schema SEQUENCES NUMERIC_PRECISION_RADIX 6 NULL YES int NULL NULL 10 0 NULL NULL NULL int(21) select NEVER NULL I have not found tests of NUMERIC_* output for all supported by us types. > +def information_schema SEQUENCES NUMERIC_SCALE 7 NULL YES int NULL NULL 10 0 NULL NULL NULL int(21) select NEVER NULL > +def information_schema SEQUENCES SEQUENCE_CATALOG 1 NULL NO varchar 512 1536 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(512) select NEVER NULL > +def information_schema SEQUENCES SEQUENCE_NAME 3 NULL NO varchar 64 192 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(64) select NEVER NULL > +def information_schema SEQUENCES SEQUENCE_SCHEMA 2 NULL NO varchar 64 192 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(64) select NEVER NULL > +def information_schema SEQUENCES START_VALUE 8 NULL NO decimal NULL NULL 21 0 NULL NULL NULL decimal(21,0) select NEVER NULL I do not see CACHED reflected here, please add it. > def information_schema SESSION_STATUS VARIABLE_NAME 1 NULL NO varchar 64 192 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(64) select NEVER NULL > def information_schema SESSION_STATUS VARIABLE_VALUE 2 NULL NO varchar 2048 6144 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(2048) select NEVER NULL > def information_schema SESSION_VARIABLES VARIABLE_NAME 1 NULL NO varchar 64 192 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(64) select NEVER NULL > @@ -890,6 +905,21 @@ NULL information_schema ROUTINES LAST_ALTERED datetime NULL NULL NULL NULL datet > 3.0000 information_schema SCHEMA_PRIVILEGES TABLE_SCHEMA varchar 64 192 utf8mb3 utf8mb3_general_ci varchar(64) > 3.0000 information_schema SCHEMA_PRIVILEGES PRIVILEGE_TYPE varchar 64 192 utf8mb3 utf8mb3_general_ci varchar(64) > 3.0000 information_schema SCHEMA_PRIVILEGES IS_GRANTABLE varchar 3 9 utf8mb3 utf8mb3_general_ci varchar(3) > +3.0000 information_schema SEQUENCES SEQUENCE_CATALOG varchar 512 1536 utf8mb3 utf8mb3_general_ci varchar(512) > +3.0000 information_schema SEQUENCES SEQUENCE_SCHEMA varchar 64 192 utf8mb3 utf8mb3_general_ci varchar(64) > +3.0000 information_schema SEQUENCES SEQUENCE_NAME varchar 64 192 utf8mb3 utf8mb3_general_ci varchar(64) > +3.0000 information_schema SEQUENCES DATA_TYPE varchar 64 192 utf8mb3 utf8mb3_general_ci varchar(64) > +NULL information_schema SEQUENCES NUMERIC_PRECISION int NULL NULL NULL NULL int(21) > +NULL information_schema SEQUENCES NUMERIC_PRECISION_RADIX int NULL NULL NULL NULL int(21) > +NULL information_schema SEQUENCES NUMERIC_SCALE int NULL NULL NULL NULL int(21) > +NULL information_schema SEQUENCES START_VALUE decimal NULL NULL NULL NULL decimal(21,0) > +NULL information_schema SEQUENCES MINIMUM_VALUE decimal NULL NULL NULL NULL decimal(21,0) > +NULL information_schema SEQUENCES MAXIMUM_VALUE decimal NULL NULL NULL NULL decimal(21,0) > +NULL information_schema SEQUENCES INCREMENT bigint NULL NULL NULL NULL bigint(21) > +NULL information_schema SEQUENCES CYCLE_OPTION bigint NULL NULL NULL NULL bigint(21) > +NULL information_schema SEQUENCES DECLARED_DATA_TYPE int NULL NULL NULL NULL int(21) > +NULL information_schema SEQUENCES DECLARED_NUMERIC_PRECISION int NULL NULL NULL NULL int(21) > +NULL information_schema SEQUENCES DECLARED_NUMERIC_SCALE int NULL NULL NULL NULL int(21) > 3.0000 information_schema SESSION_STATUS VARIABLE_NAME varchar 64 192 utf8mb3 utf8mb3_general_ci varchar(64) > 3.0000 information_schema SESSION_STATUS VARIABLE_VALUE varchar 2048 6144 utf8mb3 utf8mb3_general_ci varchar(2048) > 3.0000 information_schema SESSION_VARIABLES VARIABLE_NAME varchar 64 192 utf8mb3 utf8mb3_general_ci varchar(64) > diff --git a/mysql-test/suite/funcs_1/r/is_columns_is_embedded.result b/mysql-test/suite/funcs_1/r/is_columns_is_embedded.result > index bb12a0c38df..87cc440cdbc 100644 > --- a/mysql-test/suite/funcs_1/r/is_columns_is_embedded.result > +++ b/mysql-test/suite/funcs_1/r/is_columns_is_embedded.result > @@ -339,6 +339,21 @@ def information_schema SCHEMA_PRIVILEGES IS_GRANTABLE 5 NULL NO varchar 3 9 NULL > def information_schema SCHEMA_PRIVILEGES PRIVILEGE_TYPE 4 NULL NO varchar 64 192 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(64) NEVER NULL > def information_schema SCHEMA_PRIVILEGES TABLE_CATALOG 2 NULL NO varchar 512 1536 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(512) NEVER NULL > def information_schema SCHEMA_PRIVILEGES TABLE_SCHEMA 3 NULL NO varchar 64 192 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(64) NEVER NULL > +def information_schema SEQUENCES CYCLE_OPTION 12 NULL NO bigint NULL NULL 19 0 NULL NULL NULL bigint(21) NEVER NULL > +def information_schema SEQUENCES DATA_TYPE 4 NULL NO varchar 64 192 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(64) NEVER NULL > +def information_schema SEQUENCES DECLARED_DATA_TYPE 13 NULL YES int NULL NULL 10 0 NULL NULL NULL int(21) NEVER NULL > +def information_schema SEQUENCES DECLARED_NUMERIC_PRECISION 14 NULL YES int NULL NULL 10 0 NULL NULL NULL int(21) NEVER NULL > +def information_schema SEQUENCES DECLARED_NUMERIC_SCALE 15 NULL YES int NULL NULL 10 0 NULL NULL NULL int(21) NEVER NULL > +def information_schema SEQUENCES INCREMENT 11 NULL NO bigint NULL NULL 19 0 NULL NULL NULL bigint(21) NEVER NULL > +def information_schema SEQUENCES MAXIMUM_VALUE 10 NULL NO decimal NULL NULL 21 0 NULL NULL NULL decimal(21,0) NEVER NULL > +def information_schema SEQUENCES MINIMUM_VALUE 9 NULL NO decimal NULL NULL 21 0 NULL NULL NULL decimal(21,0) NEVER NULL > +def information_schema SEQUENCES NUMERIC_PRECISION 5 NULL NO int NULL NULL 10 0 NULL NULL NULL int(21) NEVER NULL > +def information_schema SEQUENCES NUMERIC_PRECISION_RADIX 6 NULL YES int NULL NULL 10 0 NULL NULL NULL int(21) NEVER NULL > +def information_schema SEQUENCES NUMERIC_SCALE 7 NULL YES int NULL NULL 10 0 NULL NULL NULL int(21) NEVER NULL > +def information_schema SEQUENCES SEQUENCE_CATALOG 1 NULL NO varchar 512 1536 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(512) NEVER NULL > +def information_schema SEQUENCES SEQUENCE_NAME 3 NULL NO varchar 64 192 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(64) NEVER NULL > +def information_schema SEQUENCES SEQUENCE_SCHEMA 2 NULL NO varchar 64 192 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(64) NEVER NULL > +def information_schema SEQUENCES START_VALUE 8 NULL NO decimal NULL NULL 21 0 NULL NULL NULL decimal(21,0) NEVER NULL > def information_schema SESSION_STATUS VARIABLE_NAME 1 NULL NO varchar 64 192 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(64) NEVER NULL > def information_schema SESSION_STATUS VARIABLE_VALUE 2 NULL NO varchar 2048 6144 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(2048) NEVER NULL > def information_schema SESSION_VARIABLES VARIABLE_NAME 1 NULL NO varchar 64 192 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(64) NEVER NULL > @@ -890,6 +905,21 @@ NULL information_schema ROUTINES LAST_ALTERED datetime NULL NULL NULL NULL datet > 3.0000 information_schema SCHEMA_PRIVILEGES TABLE_SCHEMA varchar 64 192 utf8mb3 utf8mb3_general_ci varchar(64) > 3.0000 information_schema SCHEMA_PRIVILEGES PRIVILEGE_TYPE varchar 64 192 utf8mb3 utf8mb3_general_ci varchar(64) > 3.0000 information_schema SCHEMA_PRIVILEGES IS_GRANTABLE varchar 3 9 utf8mb3 utf8mb3_general_ci varchar(3) > +3.0000 information_schema SEQUENCES SEQUENCE_CATALOG varchar 512 1536 utf8mb3 utf8mb3_general_ci varchar(512) > +3.0000 information_schema SEQUENCES SEQUENCE_SCHEMA varchar 64 192 utf8mb3 utf8mb3_general_ci varchar(64) > +3.0000 information_schema SEQUENCES SEQUENCE_NAME varchar 64 192 utf8mb3 utf8mb3_general_ci varchar(64) > +3.0000 information_schema SEQUENCES DATA_TYPE varchar 64 192 utf8mb3 utf8mb3_general_ci varchar(64) > +NULL information_schema SEQUENCES NUMERIC_PRECISION int NULL NULL NULL NULL int(21) > +NULL information_schema SEQUENCES NUMERIC_PRECISION_RADIX int NULL NULL NULL NULL int(21) > +NULL information_schema SEQUENCES NUMERIC_SCALE int NULL NULL NULL NULL int(21) > +NULL information_schema SEQUENCES START_VALUE decimal NULL NULL NULL NULL decimal(21,0) > +NULL information_schema SEQUENCES MINIMUM_VALUE decimal NULL NULL NULL NULL decimal(21,0) > +NULL information_schema SEQUENCES MAXIMUM_VALUE decimal NULL NULL NULL NULL decimal(21,0) > +NULL information_schema SEQUENCES INCREMENT bigint NULL NULL NULL NULL bigint(21) > +NULL information_schema SEQUENCES CYCLE_OPTION bigint NULL NULL NULL NULL bigint(21) > +NULL information_schema SEQUENCES DECLARED_DATA_TYPE int NULL NULL NULL NULL int(21) > +NULL information_schema SEQUENCES DECLARED_NUMERIC_PRECISION int NULL NULL NULL NULL int(21) > +NULL information_schema SEQUENCES DECLARED_NUMERIC_SCALE int NULL NULL NULL NULL int(21) > 3.0000 information_schema SESSION_STATUS VARIABLE_NAME varchar 64 192 utf8mb3 utf8mb3_general_ci varchar(64) > 3.0000 information_schema SESSION_STATUS VARIABLE_VALUE varchar 2048 6144 utf8mb3 utf8mb3_general_ci varchar(2048) > 3.0000 information_schema SESSION_VARIABLES VARIABLE_NAME varchar 64 192 utf8mb3 utf8mb3_general_ci varchar(64) > diff --git a/mysql-test/suite/funcs_1/r/is_tables_is.result b/mysql-test/suite/funcs_1/r/is_tables_is.result > index c18f733c86f..8c81a79b605 100644 > --- a/mysql-test/suite/funcs_1/r/is_tables_is.result > +++ b/mysql-test/suite/funcs_1/r/is_tables_is.result > @@ -739,6 +739,31 @@ user_comment > Separator ----------------------------------------------------- > TABLE_CATALOG def > TABLE_SCHEMA information_schema > +TABLE_NAME SEQUENCES > +TABLE_TYPE SYSTEM VIEW > +ENGINE MEMORY > +VERSION 11 > +ROW_FORMAT Fixed > +TABLE_ROWS #TBLR# > +AVG_ROW_LENGTH #ARL# > +DATA_LENGTH #DL# > +MAX_DATA_LENGTH #MDL# > +INDEX_LENGTH #IL# > +DATA_FREE #DF# > +AUTO_INCREMENT NULL > +CREATE_TIME #CRT# > +UPDATE_TIME #UT# > +CHECK_TIME #CT# > +TABLE_COLLATION utf8mb3_general_ci > +CHECKSUM NULL > +CREATE_OPTIONS #CO# > +TABLE_COMMENT #TC# > +MAX_INDEX_LENGTH #MIL# > +TEMPORARY Y > +user_comment > +Separator ----------------------------------------------------- > +TABLE_CATALOG def > +TABLE_SCHEMA information_schema > TABLE_NAME SESSION_STATUS > TABLE_TYPE SYSTEM VIEW > ENGINE MEMORY > @@ -1855,6 +1880,31 @@ user_comment > Separator ----------------------------------------------------- > TABLE_CATALOG def > TABLE_SCHEMA information_schema > +TABLE_NAME SEQUENCES > +TABLE_TYPE SYSTEM VIEW > +ENGINE MEMORY > +VERSION 11 > +ROW_FORMAT Fixed > +TABLE_ROWS #TBLR# > +AVG_ROW_LENGTH #ARL# > +DATA_LENGTH #DL# > +MAX_DATA_LENGTH #MDL# > +INDEX_LENGTH #IL# > +DATA_FREE #DF# > +AUTO_INCREMENT NULL > +CREATE_TIME #CRT# > +UPDATE_TIME #UT# > +CHECK_TIME #CT# > +TABLE_COLLATION utf8mb3_general_ci > +CHECKSUM NULL > +CREATE_OPTIONS #CO# > +TABLE_COMMENT #TC# > +MAX_INDEX_LENGTH #MIL# > +TEMPORARY Y > +user_comment > +Separator ----------------------------------------------------- > +TABLE_CATALOG def > +TABLE_SCHEMA information_schema > TABLE_NAME SESSION_STATUS > TABLE_TYPE SYSTEM VIEW > ENGINE MEMORY > diff --git a/mysql-test/suite/funcs_1/r/is_tables_is_embedded.result b/mysql-test/suite/funcs_1/r/is_tables_is_embedded.result > index c18f733c86f..8c81a79b605 100644 > --- a/mysql-test/suite/funcs_1/r/is_tables_is_embedded.result > +++ b/mysql-test/suite/funcs_1/r/is_tables_is_embedded.result > @@ -739,6 +739,31 @@ user_comment > Separator ----------------------------------------------------- > TABLE_CATALOG def > TABLE_SCHEMA information_schema > +TABLE_NAME SEQUENCES > +TABLE_TYPE SYSTEM VIEW > +ENGINE MEMORY > +VERSION 11 > +ROW_FORMAT Fixed > +TABLE_ROWS #TBLR# > +AVG_ROW_LENGTH #ARL# > +DATA_LENGTH #DL# > +MAX_DATA_LENGTH #MDL# > +INDEX_LENGTH #IL# > +DATA_FREE #DF# > +AUTO_INCREMENT NULL > +CREATE_TIME #CRT# > +UPDATE_TIME #UT# > +CHECK_TIME #CT# > +TABLE_COLLATION utf8mb3_general_ci > +CHECKSUM NULL > +CREATE_OPTIONS #CO# > +TABLE_COMMENT #TC# > +MAX_INDEX_LENGTH #MIL# > +TEMPORARY Y > +user_comment > +Separator ----------------------------------------------------- > +TABLE_CATALOG def > +TABLE_SCHEMA information_schema > TABLE_NAME SESSION_STATUS > TABLE_TYPE SYSTEM VIEW > ENGINE MEMORY > @@ -1855,6 +1880,31 @@ user_comment > Separator ----------------------------------------------------- > TABLE_CATALOG def > TABLE_SCHEMA information_schema > +TABLE_NAME SEQUENCES > +TABLE_TYPE SYSTEM VIEW > +ENGINE MEMORY > +VERSION 11 > +ROW_FORMAT Fixed > +TABLE_ROWS #TBLR# > +AVG_ROW_LENGTH #ARL# > +DATA_LENGTH #DL# > +MAX_DATA_LENGTH #MDL# > +INDEX_LENGTH #IL# > +DATA_FREE #DF# > +AUTO_INCREMENT NULL > +CREATE_TIME #CRT# > +UPDATE_TIME #UT# > +CHECK_TIME #CT# > +TABLE_COLLATION utf8mb3_general_ci > +CHECKSUM NULL > +CREATE_OPTIONS #CO# > +TABLE_COMMENT #TC# > +MAX_INDEX_LENGTH #MIL# > +TEMPORARY Y > +user_comment > +Separator ----------------------------------------------------- > +TABLE_CATALOG def > +TABLE_SCHEMA information_schema > TABLE_NAME SESSION_STATUS > TABLE_TYPE SYSTEM VIEW > ENGINE MEMORY > diff --git a/mysql-test/suite/sql_sequence/alter.result b/mysql-test/suite/sql_sequence/alter.result > index 60b708b8289..e6cd599ec40 100644 > --- a/mysql-test/suite/sql_sequence/alter.result > +++ b/mysql-test/suite/sql_sequence/alter.result > @@ -14,7 +14,7 @@ next value for t1 > alter sequence t1 start=50; > show create sequence t1; > Table Create Table > -t1 CREATE SEQUENCE `t1` start with 50 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM > +t1 CREATE SEQUENCE `t1` as bigint start with 50 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM > select * from t1; > next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count > 2 1 9223372036854775806 50 1 0 0 0 > @@ -24,7 +24,7 @@ next value for t1 > alter sequence t1 minvalue=-100; > show create sequence t1; > Table Create Table > -t1 CREATE SEQUENCE `t1` start with 50 minvalue -100 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM > +t1 CREATE SEQUENCE `t1` as bigint start with 50 minvalue -100 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM > select * from t1; > next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count > 3 -100 9223372036854775806 50 1 0 0 0 > @@ -33,14 +33,14 @@ ERROR HY000: Sequence 'test.t1' has out of range value for options > alter sequence t1 minvalue=100 start=100 restart=100; > show create sequence t1; > Table Create Table > -t1 CREATE SEQUENCE `t1` start with 100 minvalue 100 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM > +t1 CREATE SEQUENCE `t1` as bigint start with 100 minvalue 100 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM > select * from t1; > next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count > 100 100 9223372036854775806 100 1 0 0 0 > alter sequence t1 maxvalue=500; > show create sequence t1; > Table Create Table > -t1 CREATE SEQUENCE `t1` start with 100 minvalue 100 maxvalue 500 increment by 1 nocache nocycle ENGINE=MyISAM > +t1 CREATE SEQUENCE `t1` as bigint start with 100 minvalue 100 maxvalue 500 increment by 1 nocache nocycle ENGINE=MyISAM > select * from t1; > next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count > 100 100 500 100 1 0 0 0 > @@ -49,20 +49,20 @@ CREATE SEQUENCE t1 engine=myisam; > alter sequence t1 nocache; > show create sequence t1; > Table Create Table > -t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM > +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM > alter sequence t1 cache=100; > flush tables; > show create sequence t1; > Table Create Table > -t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 100 nocycle ENGINE=MyISAM > +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 100 nocycle ENGINE=MyISAM > alter sequence t1 nocache; > show create sequence t1; > Table Create Table > -t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM > +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM > flush tables; > show create sequence t1; > Table Create Table > -t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM > +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM > select * from t1; > next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count > 1 1 9223372036854775806 1 1 0 0 0 > @@ -83,19 +83,19 @@ CREATE SEQUENCE t1 maxvalue=100 engine=myisam; > alter sequence t1 no maxvalue; > show create sequence t1; > Table Create Table > -t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > select * from t1; > next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count > 1 1 9223372036854775806 1 1 1000 0 0 > alter sequence t1 cycle; > show create sequence t1; > Table Create Table > -t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 cycle ENGINE=MyISAM > +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 cycle ENGINE=MyISAM > alter sequence t1 nocycle; > alter sequence t1 start=15 restart minvalue=10 maxvalue=20 cycle; > show create sequence t1; > Table Create Table > -t1 CREATE SEQUENCE `t1` start with 15 minvalue 10 maxvalue 20 increment by 1 cache 1000 cycle ENGINE=MyISAM > +t1 CREATE SEQUENCE `t1` as bigint start with 15 minvalue 10 maxvalue 20 increment by 1 cache 1000 cycle ENGINE=MyISAM > select * from t1; > next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count > 15 10 20 15 1 1000 1 0 > @@ -129,7 +129,7 @@ CREATE SEQUENCE t1 maxvalue=100; > alter sequence t1 increment=-2 start with 50 minvalue=-100; > show create sequence t1; > Table Create Table > -t1 CREATE SEQUENCE `t1` start with 50 minvalue -100 maxvalue 100 increment by -2 cache 1000 nocycle ENGINE=MyISAM > +t1 CREATE SEQUENCE `t1` as bigint start with 50 minvalue -100 maxvalue 100 increment by -2 cache 1000 nocycle ENGINE=MyISAM > select * from t1; > next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count > 1 -100 100 50 -2 1000 0 0 > @@ -159,7 +159,7 @@ next value for t1 > alter sequence t1 start=100; > show create sequence t1; > Table Create Table > -t1 CREATE SEQUENCE `t1` start with 100 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 10 nocycle ENGINE=InnoDB > +t1 CREATE SEQUENCE `t1` as bigint start with 100 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 10 nocycle ENGINE=InnoDB > select * from t1; > next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count > 11 1 9223372036854775806 100 1 10 0 0 > @@ -185,15 +185,15 @@ next value for t1 > alter table t1 comment="foo"; > show create sequence t1; > Table Create Table > -t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB COMMENT='foo' > +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB COMMENT='foo' > alter table t1 engine=myisam; > show create sequence t1; > Table Create Table > -t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM COMMENT='foo' > +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM COMMENT='foo' > alter table t1 engine=innodb; > show create sequence t1; > Table Create Table > -t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB COMMENT='foo' > +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB COMMENT='foo' > select * from t1; > next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count > 3001 1 9223372036854775806 1 1 1000 0 0 > @@ -248,14 +248,186 @@ SELECT NEXTVAL(s); > NEXTVAL(s) > 1 > DROP SEQUENCE s; > +# > +# MDEV-28152 Features for sequence > +# > create sequence s maxvalue 12345; > show create sequence s; > Table Create Table > -s CREATE SEQUENCE `s` start with 1 minvalue 1 maxvalue 12345 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +s CREATE SEQUENCE `s` as bigint start with 1 minvalue 1 maxvalue 12345 increment by 1 cache 1000 nocycle ENGINE=MyISAM > alter sequence s maxvalue 123456789012345678901234; > Warnings: > Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' > show create sequence s; > Table Create Table > -s CREATE SEQUENCE `s` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +s CREATE SEQUENCE `s` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +drop sequence s; > +create sequence s as tinyint; > +show create sequence s; > +Table Create Table > +s CREATE SEQUENCE `s` as tinyint start with 1 minvalue 1 maxvalue 126 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +show create table s; > +Table Create Table > +s CREATE TABLE `s` ( > + `next_not_cached_value` tinyint(5) NOT NULL, > + `minimum_value` tinyint(5) NOT NULL, > + `maximum_value` tinyint(5) NOT NULL, > + `start_value` tinyint(5) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', > + `increment` bigint(21) NOT NULL COMMENT 'increment value', > + `cache_size` bigint(21) unsigned NOT NULL, > + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', > + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' > +) ENGINE=MyISAM SEQUENCE=1 > +alter sequence s as int; > +show create sequence s; > +Table Create Table > +s CREATE SEQUENCE `s` as int start with 1 minvalue 1 maxvalue 126 increment by 1 cache 1000 nocycle ENGINE=MyISAM Here (and for each type) this should be tested: 1. check maxvalue cycling and reaching maximum in case of not cycling which was inherited (126 in the case) 2. alter maxvalue according the new type 2.1 make it higher then possible (get a error or fixing the value with a warning) 2.2 make it maximum allowed and test it works 2.3. make it lower (may be around middle range) and test if it works > +show create table s; > +Table Create Table > +s CREATE TABLE `s` ( > + `next_not_cached_value` int(12) NOT NULL, > + `minimum_value` int(12) NOT NULL, > + `maximum_value` int(12) NOT NULL, > + `start_value` int(12) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', > + `increment` bigint(21) NOT NULL COMMENT 'increment value', > + `cache_size` bigint(21) unsigned NOT NULL, > + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', > + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' > +) ENGINE=MyISAM SEQUENCE=1 > +alter sequence s maxvalue 12345; > +show create sequence s; > +Table Create Table > +s CREATE SEQUENCE `s` as int start with 1 minvalue 1 maxvalue 12345 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +show create table s; > +Table Create Table > +s CREATE TABLE `s` ( > + `next_not_cached_value` int(12) NOT NULL, > + `minimum_value` int(12) NOT NULL, > + `maximum_value` int(12) NOT NULL, > + `start_value` int(12) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', > + `increment` bigint(21) NOT NULL COMMENT 'increment value', > + `cache_size` bigint(21) unsigned NOT NULL, > + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', > + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' > +) ENGINE=MyISAM SEQUENCE=1 > +drop sequence s; > +create sequence s; > +show create sequence s; > +Table Create Table > +s CREATE SEQUENCE `s` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +show create table s; > +Table Create Table > +s CREATE TABLE `s` ( > + `next_not_cached_value` bigint(21) NOT NULL, > + `minimum_value` bigint(21) NOT NULL, > + `maximum_value` bigint(21) NOT NULL, > + `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', > + `increment` bigint(21) NOT NULL COMMENT 'increment value', > + `cache_size` bigint(21) unsigned NOT NULL, > + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', > + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' > +) ENGINE=MyISAM SEQUENCE=1 > +alter sequence s maxvalue 123; > +show create sequence s; > +Table Create Table > +s CREATE SEQUENCE `s` as bigint start with 1 minvalue 1 maxvalue 123 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +show create table s; > +Table Create Table > +s CREATE TABLE `s` ( > + `next_not_cached_value` bigint(21) NOT NULL, > + `minimum_value` bigint(21) NOT NULL, > + `maximum_value` bigint(21) NOT NULL, > + `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', > + `increment` bigint(21) NOT NULL COMMENT 'increment value', > + `cache_size` bigint(21) unsigned NOT NULL, > + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', > + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' > +) ENGINE=MyISAM SEQUENCE=1 > +alter sequence s as tinyint; > +show create sequence s; > +Table Create Table > +s CREATE SEQUENCE `s` as tinyint start with 1 minvalue 1 maxvalue 123 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +show create table s; > +Table Create Table > +s CREATE TABLE `s` ( > + `next_not_cached_value` tinyint(5) NOT NULL, > + `minimum_value` tinyint(5) NOT NULL, > + `maximum_value` tinyint(5) NOT NULL, > + `start_value` tinyint(5) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', > + `increment` bigint(21) NOT NULL COMMENT 'increment value', > + `cache_size` bigint(21) unsigned NOT NULL, > + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', > + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' > +) ENGINE=MyISAM SEQUENCE=1 > +drop sequence s; > +create sequence s as int; > +show create sequence s; > +Table Create Table > +s CREATE SEQUENCE `s` as int start with 1 minvalue 1 maxvalue 2147483646 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +show create table s; > +Table Create Table > +s CREATE TABLE `s` ( > + `next_not_cached_value` int(12) NOT NULL, > + `minimum_value` int(12) NOT NULL, > + `maximum_value` int(12) NOT NULL, > + `start_value` int(12) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', > + `increment` bigint(21) NOT NULL COMMENT 'increment value', > + `cache_size` bigint(21) unsigned NOT NULL, > + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', > + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' > +) ENGINE=MyISAM SEQUENCE=1 > +alter sequence s as tinyint; > +ERROR 22003: Out of range value for column 'maximum_value' at row 1 > +drop sequence s; Here it would be nice (before that drop) to change maximum_value and then repeat operation to be sure it is allowed. alter sequence s maxvalue 126; alter sequence s as tinyint; > +create sequence s as tinyint; > +show create sequence s; > +Table Create Table > +s CREATE SEQUENCE `s` as tinyint start with 1 minvalue 1 maxvalue 126 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +show create table s; > +Table Create Table > +s CREATE TABLE `s` ( > + `next_not_cached_value` tinyint(5) NOT NULL, > + `minimum_value` tinyint(5) NOT NULL, > + `maximum_value` tinyint(5) NOT NULL, > + `start_value` tinyint(5) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', > + `increment` bigint(21) NOT NULL COMMENT 'increment value', > + `cache_size` bigint(21) unsigned NOT NULL, > + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', > + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' > +) ENGINE=MyISAM SEQUENCE=1 > +alter sequence s as int maxvalue 123; > +ERROR 42000: This version of MariaDB doesn't yet support 'ALTER SEQUENCE with both AS <type> and something else.' > +drop sequence s; > +create sequence s as int; > +show create sequence s; > +Table Create Table > +s CREATE SEQUENCE `s` as int start with 1 minvalue 1 maxvalue 2147483646 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +show create table s; > +Table Create Table > +s CREATE TABLE `s` ( > + `next_not_cached_value` int(12) NOT NULL, > + `minimum_value` int(12) NOT NULL, > + `maximum_value` int(12) NOT NULL, > + `start_value` int(12) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', > + `increment` bigint(21) NOT NULL COMMENT 'increment value', > + `cache_size` bigint(21) unsigned NOT NULL, > + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', > + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' > +) ENGINE=MyISAM SEQUENCE=1 > +alter sequence s as int unsigned; > +show create sequence s; > +Table Create Table > +s CREATE SEQUENCE `s` as int unsigned start with 1 minvalue 1 maxvalue 2147483646 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +show create table s; > +Table Create Table > +s CREATE TABLE `s` ( > + `next_not_cached_value` int(12) unsigned NOT NULL, > + `minimum_value` int(12) unsigned NOT NULL, > + `maximum_value` int(12) unsigned NOT NULL, > + `start_value` int(12) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', > + `increment` bigint(21) NOT NULL COMMENT 'increment value', > + `cache_size` bigint(21) unsigned NOT NULL, > + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', > + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' > +) ENGINE=MyISAM SEQUENCE=1 > drop sequence s; > diff --git a/mysql-test/suite/sql_sequence/alter.test b/mysql-test/suite/sql_sequence/alter.test > index 3afad38a9e5..4949075f492 100644 > --- a/mysql-test/suite/sql_sequence/alter.test > +++ b/mysql-test/suite/sql_sequence/alter.test > @@ -162,12 +162,63 @@ ALTER TABLE s ORDER BY cache_size; > SELECT NEXTVAL(s); > DROP SEQUENCE s; > > -# > -# MDEV-28152 Features for sequence > -# > > +--echo # > +--echo # MDEV-28152 Features for sequence > +--echo # > + > +# truncation in alter sequence > create sequence s maxvalue 12345; > show create sequence s; > alter sequence s maxvalue 123456789012345678901234; > show create sequence s; > drop sequence s; > + > +# alter first from a narrower type to a wider type, then maxvalue > +create sequence s as tinyint; > +show create sequence s; > +show create table s; > +alter sequence s as int; > +show create sequence s; > +show create table s; > +alter sequence s maxvalue 12345; > +show create sequence s; > +show create table s; > +drop sequence s; > + > +# alter first maxvalue then from a wider type to a narrower type > +create sequence s; > +show create sequence s; > +show create table s; > +alter sequence s maxvalue 123; > +show create sequence s; > +show create table s; > +alter sequence s as tinyint; > +show create sequence s; > +show create table s; > +drop sequence s; > + > +# from a wider type to a narrower type with out of range values > +create sequence s as int; > +show create sequence s; > +show create table s; > +--error ER_WARN_DATA_OUT_OF_RANGE > +alter sequence s as tinyint; > +drop sequence s; > + > +# cannot alter both value type and something else yet. > +create sequence s as tinyint; > +show create sequence s; > +show create table s; > +--error ER_NOT_SUPPORTED_YET > +alter sequence s as int maxvalue 123; > +drop sequence s; > + > +# from signed to unsigned > +create sequence s as int; > +show create sequence s; > +show create table s; > +alter sequence s as int unsigned; > +show create sequence s; > +show create table s; > +drop sequence s; > diff --git a/mysql-test/suite/sql_sequence/alter_notembedded.result b/mysql-test/suite/sql_sequence/alter_notembedded.result > index f3e1f5f18cd..c2ea6633d1d 100644 > --- a/mysql-test/suite/sql_sequence/alter_notembedded.result > +++ b/mysql-test/suite/sql_sequence/alter_notembedded.result > @@ -12,7 +12,7 @@ select nextval(s1); > ERROR 42000: INSERT command denied to user 'normal_1'@'localhost' for table `s_db`.`s1` > show create sequence s1; > Table Create Table > -s1 CREATE SEQUENCE `s1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +s1 CREATE SEQUENCE `s1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > alter sequence s1 restart 50; > ERROR 42000: ALTER command denied to user 'normal_1'@'localhost' for table `s_db`.`s1` > connection default; > diff --git a/mysql-test/suite/sql_sequence/aria.result b/mysql-test/suite/sql_sequence/aria.result > index cfc7d946772..1e42be58240 100644 > --- a/mysql-test/suite/sql_sequence/aria.result > +++ b/mysql-test/suite/sql_sequence/aria.result > @@ -2,7 +2,7 @@ set @@default_storage_engine="aria"; > CREATE SEQUENCE t1 cache=10; > show create sequence t1; > Table Create Table > -t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 10 nocycle ENGINE=Aria > +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 10 nocycle ENGINE=Aria > select NEXT VALUE for t1,seq from seq_1_to_20; > NEXT VALUE for t1 seq > 1 1 > diff --git a/mysql-test/suite/sql_sequence/concurrent_create.result b/mysql-test/suite/sql_sequence/concurrent_create.result > index 2473abef37d..e28c98e46be 100644 > --- a/mysql-test/suite/sql_sequence/concurrent_create.result > +++ b/mysql-test/suite/sql_sequence/concurrent_create.result > @@ -22,6 +22,20 @@ select * from s2; > next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count > 1 1 9223372036854775806 1 1 1000 0 0 > DROP SEQUENCE s1, s2; > +# > +# MDEV-28152 Features for sequence > +# > +CREATE SEQUENCE s1 as mediumint unsigned ENGINE=InnoDB; > +PREPARE stmt FROM "CREATE TABLE s2 LIKE s1"; > +execute stmt; > +drop table s2; > +execute stmt; > +drop table s2; > +execute stmt; > +select * from s2; > +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count > +1 1 16777214 1 1 1000 0 0 > +DROP SEQUENCE s1, s2; > CREATE SEQUENCE s1 ENGINE=InnoDB; > connect con1,localhost,root,,test; > CREATE TABLE s2 LIKE s1;; > diff --git a/mysql-test/suite/sql_sequence/concurrent_create.test b/mysql-test/suite/sql_sequence/concurrent_create.test > index b27a6d3bdb9..b4def8d7ce5 100644 > --- a/mysql-test/suite/sql_sequence/concurrent_create.test > +++ b/mysql-test/suite/sql_sequence/concurrent_create.test > @@ -38,6 +38,20 @@ execute stmt; > select * from s2; > DROP SEQUENCE s1, s2; > > +--echo # > +--echo # MDEV-28152 Features for sequence > +--echo # > + > +CREATE SEQUENCE s1 as mediumint unsigned ENGINE=InnoDB; > +PREPARE stmt FROM "CREATE TABLE s2 LIKE s1"; > +execute stmt; > +drop table s2; > +execute stmt; > +drop table s2; > +execute stmt; > +select * from s2; > +DROP SEQUENCE s1, s2; You also better check second execution of CREATE SEQUENCE with the type in SP/PS (probably not in this test file) > + > # > # MDEV-15117 Server crashes in in open_and_process_table or ASAN > # heap-use-after-free in is_temporary_table upon creating/flushing sequences > diff --git a/mysql-test/suite/sql_sequence/create.result b/mysql-test/suite/sql_sequence/create.result > index e6a382ec3bf..f2d376774a7 100644 > --- a/mysql-test/suite/sql_sequence/create.result > +++ b/mysql-test/suite/sql_sequence/create.result > @@ -4,7 +4,7 @@ Note 1051 Unknown table 'test.t1' > create or replace sequence t1 engine=myisam; > show create sequence t1; > Table Create Table > -t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > show create table t1; > Table Create Table > t1 CREATE TABLE `t1` ( > @@ -23,7 +23,7 @@ next_not_cached_value minimum_value maximum_value start_value increment cache_si > create or replace sequence t1 engine=innodb; > show create sequence t1; > Table Create Table > -t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB > +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB > show create table t1; > Table Create Table > t1 CREATE TABLE `t1` ( > @@ -42,7 +42,7 @@ next_not_cached_value minimum_value maximum_value start_value increment cache_si > create or replace sequence t1 engine=maria; > show create sequence t1; > Table Create Table > -t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=Aria > +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=Aria > show create table t1; > Table Create Table > t1 CREATE TABLE `t1` ( > @@ -65,59 +65,59 @@ ERROR 42S02: Table 'test.t1' doesn't exist > create or replace sequence t1 start with 10; > show create sequence t1; > Table Create Table > -t1 CREATE SEQUENCE `t1` start with 10 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +t1 CREATE SEQUENCE `t1` as bigint start with 10 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > select * from t1; > next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count > 10 1 9223372036854775806 10 1 1000 0 0 > create or replace sequence t1 minvalue=11; > show create sequence t1; > Table Create Table > -t1 CREATE SEQUENCE `t1` start with 11 minvalue 11 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +t1 CREATE SEQUENCE `t1` as bigint start with 11 minvalue 11 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > select * from t1; > next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count > 11 11 9223372036854775806 11 1 1000 0 0 > create or replace sequence t1 maxvalue=13 increment by -1; > show create sequence t1; > Table Create Table > -t1 CREATE SEQUENCE `t1` start with 13 minvalue -9223372036854775807 maxvalue 13 increment by -1 cache 1000 nocycle ENGINE=MyISAM > +t1 CREATE SEQUENCE `t1` as bigint start with 13 minvalue -9223372036854775807 maxvalue 13 increment by -1 cache 1000 nocycle ENGINE=MyISAM > select * from t1; > next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count > 13 -9223372036854775807 13 13 -1 1000 0 0 > create or replace sequence t1 increment by -1 cache 100; > show create sequence t1; > Table Create Table > -t1 CREATE SEQUENCE `t1` start with -1 minvalue -9223372036854775807 maxvalue -1 increment by -1 cache 100 nocycle ENGINE=MyISAM > +t1 CREATE SEQUENCE `t1` as bigint start with -1 minvalue -9223372036854775807 maxvalue -1 increment by -1 cache 100 nocycle ENGINE=MyISAM > select * from t1; > next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count > -1 -9223372036854775807 -1 -1 -1 100 0 0 > create or replace sequence t1 cycle; > show create sequence t1; > Table Create Table > -t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 cycle ENGINE=MyISAM > +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 cycle ENGINE=MyISAM > select * from t1; > next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count > 1 1 9223372036854775806 1 1 1000 1 0 > create or replace sequence t1 nocycle; > show create sequence t1; > Table Create Table > -t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > select * from t1; > next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count > 1 1 9223372036854775806 1 1 1000 0 0 > show create sequence t1; > Table Create Table > -t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > create or replace sequence t1 cycle minvalue= 14; > show create sequence t1; > Table Create Table > -t1 CREATE SEQUENCE `t1` start with 14 minvalue 14 maxvalue 9223372036854775806 increment by 1 cache 1000 cycle ENGINE=MyISAM > +t1 CREATE SEQUENCE `t1` as bigint start with 14 minvalue 14 maxvalue 9223372036854775806 increment by 1 cache 1000 cycle ENGINE=MyISAM > select * from t1; > next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count > 14 14 9223372036854775806 14 1 1000 1 0 > create or replace sequence t1 cycle increment by -1; > show create sequence t1; > Table Create Table > -t1 CREATE SEQUENCE `t1` start with -1 minvalue -9223372036854775807 maxvalue -1 increment by -1 cache 1000 cycle ENGINE=MyISAM > +t1 CREATE SEQUENCE `t1` as bigint start with -1 minvalue -9223372036854775807 maxvalue -1 increment by -1 cache 1000 cycle ENGINE=MyISAM > drop sequence t1; > create sequence if not exists t1; > create sequence if not exists t1 start with 10; > @@ -128,81 +128,21 @@ next_not_cached_value minimum_value maximum_value start_value increment cache_si > 1 1 9223372036854775806 1 1 1000 0 0 > show create sequence t1; > Table Create Table > -t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM For backward compatibility (it is present in mariadbdump) and reducing sizeof the patch we can not print "as bigint" if it is bigint, because it is default. > create or replace sequence t1 start with 10 minvalue=10 maxvalue=11 nocache cycle; > show create sequence t1; > Table Create Table > -t1 CREATE SEQUENCE `t1` start with 10 minvalue 10 maxvalue 11 increment by 1 nocache cycle ENGINE=MyISAM > +t1 CREATE SEQUENCE `t1` as bigint start with 10 minvalue 10 maxvalue 11 increment by 1 nocache cycle ENGINE=MyISAM > select * from t1; > next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count > 10 10 11 10 1 0 1 0 > create or replace sequence t1 start with 10 minvalue=-10 maxvalue=11 cache=10 cycle increment by 10; > show create sequence t1; > Table Create Table > -t1 CREATE SEQUENCE `t1` start with 10 minvalue -10 maxvalue 11 increment by 10 cache 10 cycle ENGINE=MyISAM > +t1 CREATE SEQUENCE `t1` as bigint start with 10 minvalue -10 maxvalue 11 increment by 10 cache 10 cycle ENGINE=MyISAM > select * from t1; > next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count > 10 -10 11 10 10 10 1 0 > -create or replace sequence t1 minvalue -999999999999999999999; > -Warnings: > -Note 1292 Truncated incorrect INTEGER value: 'MINVALUE' > -show create sequence t1; > -Table Create Table > -t1 CREATE SEQUENCE `t1` start with -9223372036854775807 minvalue -9223372036854775807 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > -create or replace sequence t1 minvalue -9223372036854775808; > -Warnings: > -Note 1292 Truncated incorrect INTEGER value: 'MINVALUE' > -show create sequence t1; > -Table Create Table > -t1 CREATE SEQUENCE `t1` start with -9223372036854775807 minvalue -9223372036854775807 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > -create or replace sequence t1 minvalue -9223372036854775807; > -show create sequence t1; > -Table Create Table > -t1 CREATE SEQUENCE `t1` start with -9223372036854775807 minvalue -9223372036854775807 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > -create or replace sequence t1 minvalue 9223372036854775805; > -show create sequence t1; > -Table Create Table > -t1 CREATE SEQUENCE `t1` start with 9223372036854775805 minvalue 9223372036854775805 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > -create or replace sequence t1 minvalue 9223372036854775806; > -ERROR HY000: Sequence 'test.t1' has out of range value for options > -create or replace sequence t1 minvalue 9223372036854775807; > -ERROR HY000: Sequence 'test.t1' has out of range value for options > -create or replace sequence t1 minvalue 9223372036854775808; > -ERROR HY000: Sequence 'test.t1' has out of range value for options > -create or replace sequence t1 minvalue 9999999999999999999999; > -ERROR HY000: Sequence 'test.t1' has out of range value for options > -create or replace sequence t1 maxvalue -999999999999999999999 increment by -1; > -ERROR HY000: Sequence 'test.t1' has out of range value for options > -create or replace sequence t1 maxvalue -9223372036854775808 increment by -1; > -ERROR HY000: Sequence 'test.t1' has out of range value for options > -create or replace sequence t1 maxvalue -9223372036854775807 increment by -1; > -ERROR HY000: Sequence 'test.t1' has out of range value for options > -create or replace sequence t1 maxvalue -9223372036854775806 increment by -1; > -show create sequence t1; > -Table Create Table > -t1 CREATE SEQUENCE `t1` start with -9223372036854775806 minvalue -9223372036854775807 maxvalue -9223372036854775806 increment by -1 cache 1000 nocycle ENGINE=MyISAM > -create or replace sequence t1 maxvalue 9223372036854775806; > -show create sequence t1; > -Table Create Table > -t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > -create or replace sequence t1 maxvalue 9223372036854775807; > -Warnings: > -Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' > -show create sequence t1; > -Table Create Table > -t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > -create or replace sequence t1 maxvalue 9223372036854775808; > -Warnings: > -Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' > -show create sequence t1; > -Table Create Table > -t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > -create or replace sequence t1 maxvalue 9999999999999999999999; > -Warnings: > -Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' > -show create sequence t1; > -Table Create Table > -t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM Why was it deleted? > create or replace sequence t1 start with 10 NO MAXVALUE NO MINVALUE; > create or replace sequence t1 start with 10 maxvalue 10; > create or replace sequence t1 start with 10 minvalue 10; > @@ -213,7 +153,7 @@ drop sequence if exists t1; > create sequence t1 increment by 0; > show create sequence t1; > Table Create Table > -t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 0 cache 1000 nocycle ENGINE=MyISAM > +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 0 cache 1000 nocycle ENGINE=MyISAM > select * from t1; > next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count > 1 1 9223372036854775806 1 0 1000 0 0 > @@ -293,7 +233,7 @@ flush tables; > create or replace sequence t1 comment= "test 1"; > show create sequence t1; > Table Create Table > -t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM COMMENT='test 1' > +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM COMMENT='test 1' > show create table t1; > Table Create Table > t1 CREATE TABLE `t1` ( > @@ -309,7 +249,7 @@ t1 CREATE TABLE `t1` ( > create or replace sequence t1 comment= "test 2" min_rows=1 max_rows=2; > show create sequence t1; > Table Create Table > -t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM COMMENT='test 2' > +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM COMMENT='test 2' > show create table t1; > Table Create Table > t1 CREATE TABLE `t1` ( > @@ -338,7 +278,7 @@ CREATE TABLE t1 ( > ) sequence=1; > show create sequence t1; > Table Create Table > -t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > show create table t1; > Table Create Table > t1 CREATE TABLE `t1` ( > @@ -373,7 +313,7 @@ CREATE OR REPLACE TABLE t1 ( > `cycle_option` tinyint(1) unsigned NOT NULL, > `cycle_count` bigint(21) NOT NULL > ) sequence=1; > -ERROR HY000: Sequence 'test.t1' table structure is invalid (next_not_cached_value) > +ERROR HY000: Sequence 'test.t1' table structure is invalid (minimum_value) > CREATE OR REPLACE TABLE t1 ( > `next_not_cached_value` bigint(21) NOT NULL, > `minimum_value` bigint(21) NOT NULL, > @@ -607,7 +547,7 @@ t CREATE TABLE `t` ( > ) ENGINE=MyISAM SEQUENCE=1 > show create sequence t; > Table Create Table > -t CREATE SEQUENCE `t` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +t CREATE SEQUENCE `t` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > drop tables t, s; > # > # MDEV-13714 SEQUENCE option fix > @@ -772,3 +712,439 @@ CREATE TRIGGER s1 BEFORE UPDATE ON seq1 FOR EACH ROW SET @a= 5; > ERROR HY000: Trigger's 'seq1' is a view, temporary table or sequence > DROP SEQUENCE seq1; > # End of 10.4 test > +###### > +# MDEV-28152 Features for sequence > +###### > +# ----- > +# Truncating out-of-bound numbers for minvalue and maxvalue > +# ----- > +create or replace sequence t1 minvalue -999999999999999999999; > +Warnings: > +Note 1292 Truncated incorrect INTEGER value: 'MINVALUE' > +show create sequence t1; > +Table Create Table > +t1 CREATE SEQUENCE `t1` as bigint start with -9223372036854775807 minvalue -9223372036854775807 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +create or replace sequence t1 minvalue -9223372036854775808; > +Warnings: > +Note 1292 Truncated incorrect INTEGER value: 'MINVALUE' > +show create sequence t1; > +Table Create Table > +t1 CREATE SEQUENCE `t1` as bigint start with -9223372036854775807 minvalue -9223372036854775807 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +create or replace sequence t1 minvalue -9223372036854775807; > +show create sequence t1; > +Table Create Table > +t1 CREATE SEQUENCE `t1` as bigint start with -9223372036854775807 minvalue -9223372036854775807 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +create or replace sequence t1 minvalue 9223372036854775805; > +show create sequence t1; > +Table Create Table > +t1 CREATE SEQUENCE `t1` as bigint start with 9223372036854775805 minvalue 9223372036854775805 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +create or replace sequence t1 minvalue 9223372036854775806; > +ERROR HY000: Sequence 'test.t1' has out of range value for options > +create or replace sequence t1 minvalue 9223372036854775807; > +ERROR HY000: Sequence 'test.t1' has out of range value for options > +create or replace sequence t1 minvalue 9223372036854775808; > +ERROR HY000: Sequence 'test.t1' has out of range value for options > +create or replace sequence t1 minvalue 9999999999999999999999; > +ERROR HY000: Sequence 'test.t1' has out of range value for options > +create or replace sequence t1 maxvalue -999999999999999999999 increment by -1; > +ERROR HY000: Sequence 'test.t1' has out of range value for options > +create or replace sequence t1 maxvalue -9223372036854775808 increment by -1; > +ERROR HY000: Sequence 'test.t1' has out of range value for options > +create or replace sequence t1 maxvalue -9223372036854775807 increment by -1; > +ERROR HY000: Sequence 'test.t1' has out of range value for options > +create or replace sequence t1 maxvalue -9223372036854775806 increment by -1; > +show create sequence t1; > +Table Create Table > +t1 CREATE SEQUENCE `t1` as bigint start with -9223372036854775806 minvalue -9223372036854775807 maxvalue -9223372036854775806 increment by -1 cache 1000 nocycle ENGINE=MyISAM > +create or replace sequence t1 maxvalue 9223372036854775806; > +show create sequence t1; > +Table Create Table > +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +create or replace sequence t1 maxvalue 9223372036854775807; > +Warnings: > +Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' > +show create sequence t1; > +Table Create Table > +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +create or replace sequence t1 maxvalue 9223372036854775808; > +Warnings: > +Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' > +show create sequence t1; > +Table Create Table > +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +create or replace sequence t1 maxvalue 9999999999999999999999; > +Warnings: > +Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' > +show create sequence t1; > +Table Create Table > +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +# ----- > +# Create with value types > +# ----- > +create or replace sequence t1 as tinyint; > +show create sequence t1; > +Table Create Table > +t1 CREATE SEQUENCE `t1` as tinyint start with 1 minvalue 1 maxvalue 126 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `next_not_cached_value` tinyint(5) NOT NULL, > + `minimum_value` tinyint(5) NOT NULL, > + `maximum_value` tinyint(5) NOT NULL, > + `start_value` tinyint(5) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', > + `increment` bigint(21) NOT NULL COMMENT 'increment value', > + `cache_size` bigint(21) unsigned NOT NULL, > + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', > + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' > +) ENGINE=MyISAM SEQUENCE=1 > +create or replace sequence t1 as smallint; > +show create sequence t1; > +Table Create Table > +t1 CREATE SEQUENCE `t1` as smallint start with 1 minvalue 1 maxvalue 32766 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `next_not_cached_value` smallint(7) NOT NULL, > + `minimum_value` smallint(7) NOT NULL, > + `maximum_value` smallint(7) NOT NULL, > + `start_value` smallint(7) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', > + `increment` bigint(21) NOT NULL COMMENT 'increment value', > + `cache_size` bigint(21) unsigned NOT NULL, > + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', > + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' > +) ENGINE=MyISAM SEQUENCE=1 > +create or replace sequence t1 as mediumint; > +show create sequence t1; > +Table Create Table > +t1 CREATE SEQUENCE `t1` as mediumint start with 1 minvalue 1 maxvalue 8388606 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `next_not_cached_value` mediumint(10) NOT NULL, > + `minimum_value` mediumint(10) NOT NULL, > + `maximum_value` mediumint(10) NOT NULL, > + `start_value` mediumint(10) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', > + `increment` bigint(21) NOT NULL COMMENT 'increment value', > + `cache_size` bigint(21) unsigned NOT NULL, > + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', > + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' > +) ENGINE=MyISAM SEQUENCE=1 > +create or replace sequence t1 as int; > +show create sequence t1; > +Table Create Table > +t1 CREATE SEQUENCE `t1` as int start with 1 minvalue 1 maxvalue 2147483646 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `next_not_cached_value` int(12) NOT NULL, > + `minimum_value` int(12) NOT NULL, > + `maximum_value` int(12) NOT NULL, > + `start_value` int(12) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', > + `increment` bigint(21) NOT NULL COMMENT 'increment value', > + `cache_size` bigint(21) unsigned NOT NULL, > + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', > + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' > +) ENGINE=MyISAM SEQUENCE=1 > +create or replace sequence t1 as bigint; > +show create sequence t1; > +Table Create Table > +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `next_not_cached_value` bigint(21) NOT NULL, > + `minimum_value` bigint(21) NOT NULL, > + `maximum_value` bigint(21) NOT NULL, > + `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', > + `increment` bigint(21) NOT NULL COMMENT 'increment value', > + `cache_size` bigint(21) unsigned NOT NULL, > + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', > + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' > +) ENGINE=MyISAM SEQUENCE=1 > +create or replace sequence t1 as tinyint unsigned; > +show create sequence t1; > +Table Create Table > +t1 CREATE SEQUENCE `t1` as tiny unsigned start with 1 minvalue 1 maxvalue 254 increment by 1 cache 1000 nocycle ENGINE=MyISAM You better also check the minimum possible value for each signed and unsigned type (I see default 1 in most cases here, it would be nice to see legal minimums checked (illegal as I can see tested later)). > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `next_not_cached_value` tinyint(5) unsigned NOT NULL, > + `minimum_value` tinyint(5) unsigned NOT NULL, > + `maximum_value` tinyint(5) unsigned NOT NULL, > + `start_value` tinyint(5) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', > + `increment` bigint(21) NOT NULL COMMENT 'increment value', > + `cache_size` bigint(21) unsigned NOT NULL, > + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', > + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' > +) ENGINE=MyISAM SEQUENCE=1 > +create or replace sequence t1 as smallint unsigned; > +show create sequence t1; > +Table Create Table > +t1 CREATE SEQUENCE `t1` as smallint unsigned start with 1 minvalue 1 maxvalue 65534 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `next_not_cached_value` smallint(7) unsigned NOT NULL, > + `minimum_value` smallint(7) unsigned NOT NULL, > + `maximum_value` smallint(7) unsigned NOT NULL, > + `start_value` smallint(7) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', > + `increment` bigint(21) NOT NULL COMMENT 'increment value', > + `cache_size` bigint(21) unsigned NOT NULL, > + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', > + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' > +) ENGINE=MyISAM SEQUENCE=1 > +create or replace sequence t1 as mediumint unsigned; > +show create sequence t1; > +Table Create Table > +t1 CREATE SEQUENCE `t1` as mediumint unsigned start with 1 minvalue 1 maxvalue 16777214 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `next_not_cached_value` mediumint(10) unsigned NOT NULL, > + `minimum_value` mediumint(10) unsigned NOT NULL, > + `maximum_value` mediumint(10) unsigned NOT NULL, > + `start_value` mediumint(10) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', > + `increment` bigint(21) NOT NULL COMMENT 'increment value', > + `cache_size` bigint(21) unsigned NOT NULL, > + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', > + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' > +) ENGINE=MyISAM SEQUENCE=1 > +create or replace sequence t1 as int unsigned; > +show create sequence t1; > +Table Create Table > +t1 CREATE SEQUENCE `t1` as int unsigned start with 1 minvalue 1 maxvalue 4294967294 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `next_not_cached_value` int(12) unsigned NOT NULL, > + `minimum_value` int(12) unsigned NOT NULL, > + `maximum_value` int(12) unsigned NOT NULL, > + `start_value` int(12) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', > + `increment` bigint(21) NOT NULL COMMENT 'increment value', > + `cache_size` bigint(21) unsigned NOT NULL, > + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', > + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' > +) ENGINE=MyISAM SEQUENCE=1 > +create or replace sequence t1 as bigint unsigned; > +show create sequence t1; > +Table Create Table > +t1 CREATE SEQUENCE `t1` as bigint unsigned start with 1 minvalue 1 maxvalue 18446744073709551614 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `next_not_cached_value` bigint(21) unsigned NOT NULL, > + `minimum_value` bigint(21) unsigned NOT NULL, > + `maximum_value` bigint(21) unsigned NOT NULL, > + `start_value` bigint(21) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', > + `increment` bigint(21) NOT NULL COMMENT 'increment value', > + `cache_size` bigint(21) unsigned NOT NULL, > + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', > + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' > +) ENGINE=MyISAM SEQUENCE=1 > +create or replace sequence t1 as tinyint zerofill; > +ERROR HY000: Incorrect value 'ZEROFILL' for option 'AS' > +create or replace sequence t1 as mediumint unsigned increment by -1; > +ERROR HY000: Sequence 'test.t1' has out of range value for options > +create or replace sequence t1 as bigint unsigned start with 12345678901234567890; > +show create sequence t1; > +Table Create Table > +t1 CREATE SEQUENCE `t1` as bigint unsigned start with 12345678901234567890 minvalue 1 maxvalue 18446744073709551614 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `next_not_cached_value` bigint(21) unsigned NOT NULL, > + `minimum_value` bigint(21) unsigned NOT NULL, > + `maximum_value` bigint(21) unsigned NOT NULL, > + `start_value` bigint(21) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', > + `increment` bigint(21) NOT NULL COMMENT 'increment value', > + `cache_size` bigint(21) unsigned NOT NULL, > + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', > + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' > +) ENGINE=MyISAM SEQUENCE=1 > +# ----- > +# value types + truncating > +# ----- > +create or replace sequence t1 minvalue -23 maxvalue 9999 as tinyint unsigned; > +Warnings: > +Note 1292 Truncated incorrect INTEGER value: 'MINVALUE' > +Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' > +show create sequence t1; > +Table Create Table > +t1 CREATE SEQUENCE `t1` as tiny unsigned start with 1 minvalue 1 maxvalue 254 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `next_not_cached_value` tinyint(5) unsigned NOT NULL, > + `minimum_value` tinyint(5) unsigned NOT NULL, > + `maximum_value` tinyint(5) unsigned NOT NULL, > + `start_value` tinyint(5) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', > + `increment` bigint(21) NOT NULL COMMENT 'increment value', > + `cache_size` bigint(21) unsigned NOT NULL, > + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', > + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' > +) ENGINE=MyISAM SEQUENCE=1 > +create or replace sequence t1 minvalue -32768 maxvalue 32767 as smallint; > +Warnings: > +Note 1292 Truncated incorrect INTEGER value: 'MINVALUE' > +Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' > +show create sequence t1; > +Table Create Table > +t1 CREATE SEQUENCE `t1` as smallint start with -32767 minvalue -32767 maxvalue 32766 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `next_not_cached_value` smallint(7) NOT NULL, > + `minimum_value` smallint(7) NOT NULL, > + `maximum_value` smallint(7) NOT NULL, > + `start_value` smallint(7) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', > + `increment` bigint(21) NOT NULL COMMENT 'increment value', > + `cache_size` bigint(21) unsigned NOT NULL, > + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', > + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' > +) ENGINE=MyISAM SEQUENCE=1 > +create or replace sequence t1 minvalue 0 maxvalue 65535 as smallint unsigned; > +Warnings: > +Note 1292 Truncated incorrect INTEGER value: 'MINVALUE' > +Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' > +show create sequence t1; > +Table Create Table > +t1 CREATE SEQUENCE `t1` as smallint unsigned start with 1 minvalue 1 maxvalue 65534 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `next_not_cached_value` smallint(7) unsigned NOT NULL, > + `minimum_value` smallint(7) unsigned NOT NULL, > + `maximum_value` smallint(7) unsigned NOT NULL, > + `start_value` smallint(7) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', > + `increment` bigint(21) NOT NULL COMMENT 'increment value', > + `cache_size` bigint(21) unsigned NOT NULL, > + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', > + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' > +) ENGINE=MyISAM SEQUENCE=1 > +create or replace sequence t1 minvalue -12345678901234 as mediumint unsigned maxvalue 12345678901234; > +Warnings: > +Note 1292 Truncated incorrect INTEGER value: 'MINVALUE' > +Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' > +show create sequence t1; > +Table Create Table > +t1 CREATE SEQUENCE `t1` as mediumint unsigned start with 1 minvalue 1 maxvalue 16777214 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `next_not_cached_value` mediumint(10) unsigned NOT NULL, > + `minimum_value` mediumint(10) unsigned NOT NULL, > + `maximum_value` mediumint(10) unsigned NOT NULL, > + `start_value` mediumint(10) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', > + `increment` bigint(21) NOT NULL COMMENT 'increment value', > + `cache_size` bigint(21) unsigned NOT NULL, > + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', > + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' > +) ENGINE=MyISAM SEQUENCE=1 > +create or replace sequence t1 as bigint unsigned minvalue -12345678901234 maxvalue 12345678901234; > +Warnings: > +Note 1292 Truncated incorrect INTEGER value: 'MINVALUE' > +show create sequence t1; > +Table Create Table > +t1 CREATE SEQUENCE `t1` as bigint unsigned start with 1 minvalue 1 maxvalue 12345678901234 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `next_not_cached_value` bigint(21) unsigned NOT NULL, > + `minimum_value` bigint(21) unsigned NOT NULL, > + `maximum_value` bigint(21) unsigned NOT NULL, > + `start_value` bigint(21) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', > + `increment` bigint(21) NOT NULL COMMENT 'increment value', > + `cache_size` bigint(21) unsigned NOT NULL, > + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', > + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' > +) ENGINE=MyISAM SEQUENCE=1 > +# ----- > +# indistinguishable values during parsing if we did not pass back Longlong_hybrid from the parser. > +# ----- > +create or replace sequence t1 as bigint maxvalue -1 increment by -1; > +show create sequence t1; > +Table Create Table > +t1 CREATE SEQUENCE `t1` as bigint start with -1 minvalue -9223372036854775807 maxvalue -1 increment by -1 cache 1000 nocycle ENGINE=MyISAM > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `next_not_cached_value` bigint(21) NOT NULL, > + `minimum_value` bigint(21) NOT NULL, > + `maximum_value` bigint(21) NOT NULL, > + `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', > + `increment` bigint(21) NOT NULL COMMENT 'increment value', > + `cache_size` bigint(21) unsigned NOT NULL, > + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', > + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' > +) ENGINE=MyISAM SEQUENCE=1 > +create or replace sequence t1 as bigint maxvalue 18446744073709551615; > +Warnings: > +Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' > +show create sequence t1; > +Table Create Table > +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `next_not_cached_value` bigint(21) NOT NULL, > + `minimum_value` bigint(21) NOT NULL, > + `maximum_value` bigint(21) NOT NULL, > + `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', > + `increment` bigint(21) NOT NULL COMMENT 'increment value', > + `cache_size` bigint(21) unsigned NOT NULL, > + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', > + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' > +) ENGINE=MyISAM SEQUENCE=1 > +create or replace sequence t1 as bigint unsigned maxvalue -1; > +ERROR HY000: Sequence 'test.t1' has out of range value for options > +create or replace sequence t1 as bigint unsigned maxvalue 18446744073709551615; > +Warnings: > +Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' > +show create sequence t1; > +Table Create Table > +t1 CREATE SEQUENCE `t1` as bigint unsigned start with 1 minvalue 1 maxvalue 18446744073709551614 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `next_not_cached_value` bigint(21) unsigned NOT NULL, > + `minimum_value` bigint(21) unsigned NOT NULL, > + `maximum_value` bigint(21) unsigned NOT NULL, > + `start_value` bigint(21) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', > + `increment` bigint(21) NOT NULL COMMENT 'increment value', > + `cache_size` bigint(21) unsigned NOT NULL, > + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', > + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' > +) ENGINE=MyISAM SEQUENCE=1 > +# ----- > +# value types + out of range start > +# ----- > +create or replace sequence t1 start with -123456789012345678901 as tinyint unsigned; > +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '123456789012345678901 as tinyint unsigned' at line 1 > +create or replace sequence t1 start with -1 as tinyint unsigned; > +ERROR HY000: Sequence 'test.t1' has out of range value for options > +create or replace sequence t1 start with 0 as tinyint unsigned; > +ERROR HY000: Sequence 'test.t1' has out of range value for options > +show create sequence t1; > +Table Create Table > +t1 CREATE SEQUENCE `t1` as bigint unsigned start with 1 minvalue 1 maxvalue 18446744073709551614 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +create or replace sequence t1 start with 1 as tinyint unsigned; > +show create sequence t1; > +Table Create Table > +t1 CREATE SEQUENCE `t1` as tiny unsigned start with 1 minvalue 1 maxvalue 254 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +create or replace sequence t1 start with 254 as tinyint unsigned; > +create or replace sequence t1 start with 255 as tinyint unsigned; > +ERROR HY000: Sequence 'test.t1' has out of range value for options > +create or replace sequence t1 start with 256 as tinyint unsigned; > +ERROR HY000: Sequence 'test.t1' has out of range value for options > +create or replace sequence t1 start with 123456789012345678901 as tinyint unsigned; > +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '123456789012345678901 as tinyint unsigned' at line 1 > +drop sequence t1; > +create sequence s1 as tinyint unsigned increment by 23; > +create sequence s2 start with 42 minvalue -9223372036854775807; > +create sequence s3 as bigint unsigned start with 12345678901234567890 cycle; > +select * from information_schema.sequences; > +SEQUENCE_CATALOG SEQUENCE_SCHEMA SEQUENCE_NAME DATA_TYPE NUMERIC_PRECISION NUMERIC_PRECISION_RADIX NUMERIC_SCALE START_VALUE MINIMUM_VALUE MAXIMUM_VALUE INCREMENT CYCLE_OPTION DECLARED_DATA_TYPE DECLARED_NUMERIC_PRECISION DECLARED_NUMERIC_SCALE > +def test s3 bigint unsigned 64 2 0 12345678901234567890 1 18446744073709551614 1 1 NULL NULL NULL > +def test s2 bigint 64 2 0 42 -9223372036854775807 9223372036854775806 1 0 NULL NULL NULL > +def test s1 tiny unsigned 8 2 0 1 1 254 23 0 NULL NULL NULL > +drop sequence s1, s2, s3; > diff --git a/mysql-test/suite/sql_sequence/create.test b/mysql-test/suite/sql_sequence/create.test > index 54e181483d6..388b2fd3fe9 100644 > --- a/mysql-test/suite/sql_sequence/create.test > +++ b/mysql-test/suite/sql_sequence/create.test > @@ -71,41 +71,6 @@ create or replace sequence t1 start with 10 minvalue=-10 maxvalue=11 cache=10 cy > show create sequence t1; > select * from t1; > > -# Truncating out-of-bound numbers for minvalue and maxvalue > -create or replace sequence t1 minvalue -999999999999999999999; > -show create sequence t1; > -create or replace sequence t1 minvalue -9223372036854775808; > -show create sequence t1; > -create or replace sequence t1 minvalue -9223372036854775807; > -show create sequence t1; > -create or replace sequence t1 minvalue 9223372036854775805; > -show create sequence t1; > ---error ER_SEQUENCE_INVALID_DATA > -create or replace sequence t1 minvalue 9223372036854775806; > ---error ER_SEQUENCE_INVALID_DATA > -create or replace sequence t1 minvalue 9223372036854775807; > ---error ER_SEQUENCE_INVALID_DATA > -create or replace sequence t1 minvalue 9223372036854775808; > ---error ER_SEQUENCE_INVALID_DATA > -create or replace sequence t1 minvalue 9999999999999999999999; > - > ---error ER_SEQUENCE_INVALID_DATA > -create or replace sequence t1 maxvalue -999999999999999999999 increment by -1; > ---error ER_SEQUENCE_INVALID_DATA > -create or replace sequence t1 maxvalue -9223372036854775808 increment by -1; > ---error ER_SEQUENCE_INVALID_DATA > -create or replace sequence t1 maxvalue -9223372036854775807 increment by -1; > -create or replace sequence t1 maxvalue -9223372036854775806 increment by -1; > -show create sequence t1; > -create or replace sequence t1 maxvalue 9223372036854775806; > -show create sequence t1; > -create or replace sequence t1 maxvalue 9223372036854775807; > -show create sequence t1; > -create or replace sequence t1 maxvalue 9223372036854775808; > -show create sequence t1; > -create or replace sequence t1 maxvalue 9999999999999999999999; > -show create sequence t1; > - > # NO MINVALUE, NO MAXVALUE > create or replace sequence t1 start with 10 NO MAXVALUE NO MINVALUE; > > @@ -582,3 +547,160 @@ CREATE TRIGGER s1 BEFORE UPDATE ON seq1 FOR EACH ROW SET @a= 5; > DROP SEQUENCE seq1; > > --echo # End of 10.4 test > + > +--echo ###### > +--echo # MDEV-28152 Features for sequence > +--echo ###### > + > +--echo # ----- > +--echo # Truncating out-of-bound numbers for minvalue and maxvalue > +--echo # ----- > +--disable_ps_protocol > +create or replace sequence t1 minvalue -999999999999999999999; > +show create sequence t1; > +create or replace sequence t1 minvalue -9223372036854775808; > +show create sequence t1; > +create or replace sequence t1 minvalue -9223372036854775807; > +show create sequence t1; > +create or replace sequence t1 minvalue 9223372036854775805; > +show create sequence t1; > +--error ER_SEQUENCE_INVALID_DATA > +create or replace sequence t1 minvalue 9223372036854775806; > +--error ER_SEQUENCE_INVALID_DATA > +create or replace sequence t1 minvalue 9223372036854775807; > +--error ER_SEQUENCE_INVALID_DATA > +create or replace sequence t1 minvalue 9223372036854775808; > +--error ER_SEQUENCE_INVALID_DATA > +create or replace sequence t1 minvalue 9999999999999999999999; > +--error ER_SEQUENCE_INVALID_DATA > +create or replace sequence t1 maxvalue -999999999999999999999 increment by -1; > +--error ER_SEQUENCE_INVALID_DATA > +create or replace sequence t1 maxvalue -9223372036854775808 increment by -1; > +--error ER_SEQUENCE_INVALID_DATA > +create or replace sequence t1 maxvalue -9223372036854775807 increment by -1; > +create or replace sequence t1 maxvalue -9223372036854775806 increment by -1; > +show create sequence t1; > +create or replace sequence t1 maxvalue 9223372036854775806; > +show create sequence t1; > +create or replace sequence t1 maxvalue 9223372036854775807; > +show create sequence t1; > +create or replace sequence t1 maxvalue 9223372036854775808; > +show create sequence t1; > +create or replace sequence t1 maxvalue 9999999999999999999999; > +show create sequence t1; > +--enable_ps_protocol > + > +--echo # ----- > +--echo # Create with value types > +--echo # ----- > +create or replace sequence t1 as tinyint; > +show create sequence t1; > +show create table t1; > +create or replace sequence t1 as smallint; > +show create sequence t1; > +show create table t1; > +create or replace sequence t1 as mediumint; > +show create sequence t1; > +show create table t1; > +create or replace sequence t1 as int; > +show create sequence t1; > +show create table t1; > +create or replace sequence t1 as bigint; > +show create sequence t1; > +show create table t1; > +create or replace sequence t1 as tinyint unsigned; > +show create sequence t1; > +show create table t1; > +create or replace sequence t1 as smallint unsigned; > +show create sequence t1; > +show create table t1; > +create or replace sequence t1 as mediumint unsigned; > +show create sequence t1; > +show create table t1; > +create or replace sequence t1 as int unsigned; > +show create sequence t1; > +show create table t1; > +create or replace sequence t1 as bigint unsigned; > +show create sequence t1; > +show create table t1; > +#zerofill is not supported > +--error ER_BAD_OPTION_VALUE > +create or replace sequence t1 as tinyint zerofill; > +#an unsigned sequence has to have positive increment > +--error ER_SEQUENCE_INVALID_DATA > +create or replace sequence t1 as mediumint unsigned increment by -1; > +#start with a number between longlong_max and ulonglong_max > +create or replace sequence t1 as bigint unsigned start with 12345678901234567890; > +show create sequence t1; > +show create table t1; > + > +--echo # ----- > +--echo # value types + truncating > +--echo # ----- > +--disable_ps_protocol > +create or replace sequence t1 minvalue -23 maxvalue 9999 as tinyint unsigned; > +show create sequence t1; > +show create table t1; > +create or replace sequence t1 minvalue -32768 maxvalue 32767 as smallint; > +show create sequence t1; > +show create table t1; > +create or replace sequence t1 minvalue 0 maxvalue 65535 as smallint unsigned; > +show create sequence t1; > +show create table t1; > +create or replace sequence t1 minvalue -12345678901234 as mediumint unsigned maxvalue 12345678901234; > +show create sequence t1; > +show create table t1; > +create or replace sequence t1 as bigint unsigned minvalue -12345678901234 maxvalue 12345678901234; > +show create sequence t1; > +show create table t1; > +--enable_ps_protocol > + > +--echo # ----- > +--echo # indistinguishable values during parsing if we did not pass back Longlong_hybrid from the parser. > +--echo # ----- > +#signed, -1: no truncation. Note that we need a negative increment because this is a nagative sequence > +create or replace sequence t1 as bigint maxvalue -1 increment by -1; > +show create sequence t1; > +show create table t1; > +--disable_ps_protocol > +#signed, ulonglong_max: turncating to longlong_max-1 > +create or replace sequence t1 as bigint maxvalue 18446744073709551615; > +show create sequence t1; > +show create table t1; > +#unsigned, -1: truncation and invalid data (max_value truncated to 1 which is equal to min_value) > +--error ER_SEQUENCE_INVALID_DATA > +create or replace sequence t1 as bigint unsigned maxvalue -1; > +#unsigned, ulonglong_max: truncating to ulonglong_max-1 > +create or replace sequence t1 as bigint unsigned maxvalue 18446744073709551615; > +show create sequence t1; > +show create table t1; > +--enable_ps_protocol > + > +--echo # ----- > +--echo # value types + out of range start > +--echo # ----- > +--error ER_PARSE_ERROR > +create or replace sequence t1 start with -123456789012345678901 as tinyint unsigned; > +--error ER_SEQUENCE_INVALID_DATA > +create or replace sequence t1 start with -1 as tinyint unsigned; > +--error ER_SEQUENCE_INVALID_DATA > +create or replace sequence t1 start with 0 as tinyint unsigned; > +show create sequence t1; > +create or replace sequence t1 start with 1 as tinyint unsigned; > +show create sequence t1; > +create or replace sequence t1 start with 254 as tinyint unsigned; > +--error ER_SEQUENCE_INVALID_DATA > +create or replace sequence t1 start with 255 as tinyint unsigned; > +--error ER_SEQUENCE_INVALID_DATA > +create or replace sequence t1 start with 256 as tinyint unsigned; > +--error ER_PARSE_ERROR > +create or replace sequence t1 start with 123456789012345678901 as tinyint unsigned; > + > +drop sequence t1; > + > +# information_schema.sequences > +create sequence s1 as tinyint unsigned increment by 23; > +create sequence s2 start with 42 minvalue -9223372036854775807; > +create sequence s3 as bigint unsigned start with 12345678901234567890 cycle; > +select * from information_schema.sequences; > +drop sequence s1, s2, s3; > diff --git a/mysql-test/suite/sql_sequence/mysqldump.result b/mysql-test/suite/sql_sequence/mysqldump.result > index 0199bb7162f..3316fdac5ae 100644 > --- a/mysql-test/suite/sql_sequence/mysqldump.result > +++ b/mysql-test/suite/sql_sequence/mysqldump.result > @@ -3,9 +3,9 @@ CREATE TABLE t1(a INT, KEY (a)) KEY_BLOCK_SIZE=1024; > insert into t1 values (1),(2); > CREATE SEQUENCE x1 engine=innodb; > # dump whole database > -CREATE SEQUENCE `a1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=Aria; > +CREATE SEQUENCE `a1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=Aria; > SELECT SETVAL(`a1`, 1, 0); > -CREATE SEQUENCE `x1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB; > +CREATE SEQUENCE `x1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB; > SELECT SETVAL(`x1`, 1, 0); > /*!40101 SET @saved_cs_client = @@character_set_client */; > /*!40101 SET character_set_client = utf8 */; > @@ -18,9 +18,9 @@ INSERT INTO `t1` VALUES > (1), > (2); > # dump by tables order 1 > -CREATE SEQUENCE `a1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=Aria; > +CREATE SEQUENCE `a1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=Aria; > SELECT SETVAL(`a1`, 1, 0); > -CREATE SEQUENCE `x1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB; > +CREATE SEQUENCE `x1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB; > SELECT SETVAL(`x1`, 1, 0); > /*!40101 SET @saved_cs_client = @@character_set_client */; > /*!40101 SET character_set_client = utf8 */; > @@ -33,9 +33,9 @@ INSERT INTO `t1` VALUES > (1), > (2); > # dump by tables order 2 > -CREATE SEQUENCE `a1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=Aria; > +CREATE SEQUENCE `a1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=Aria; > SELECT SETVAL(`a1`, 1, 0); > -CREATE SEQUENCE `x1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB; > +CREATE SEQUENCE `x1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB; > SELECT SETVAL(`x1`, 1, 0); > /*!40101 SET @saved_cs_client = @@character_set_client */; > /*!40101 SET character_set_client = utf8 */; > @@ -59,9 +59,9 @@ INSERT INTO `t1` VALUES > (1), > (2); > # dump by tables only sequences > -CREATE SEQUENCE `a1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=Aria; > +CREATE SEQUENCE `a1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=Aria; > SELECT SETVAL(`a1`, 1, 0); > -CREATE SEQUENCE `x1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB; > +CREATE SEQUENCE `x1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB; > SELECT SETVAL(`x1`, 1, 0); > # end of dumps > DROP TABLE a1,t1,x1; > diff --git a/mysql-test/suite/sql_sequence/next.result b/mysql-test/suite/sql_sequence/next.result > index 9d55921006b..76f42143305 100644 > --- a/mysql-test/suite/sql_sequence/next.result > +++ b/mysql-test/suite/sql_sequence/next.result > @@ -548,3 +548,92 @@ SELECT SETVAL (v,0); > ERROR 42S02: 'test.v' is not a SEQUENCE > UNLOCK TABLES; > DROP VIEW v; > +# > +# MDEV-28152 Features for sequence > +# > +create or replace sequence t1 as tinyint unsigned minvalue 1 maxvalue 2; > +show create sequence t1; > +Table Create Table > +t1 CREATE SEQUENCE `t1` as tiny unsigned start with 1 minvalue 1 maxvalue 2 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `next_not_cached_value` tinyint(5) unsigned NOT NULL, > + `minimum_value` tinyint(5) unsigned NOT NULL, > + `maximum_value` tinyint(5) unsigned NOT NULL, > + `start_value` tinyint(5) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', > + `increment` bigint(21) NOT NULL COMMENT 'increment value', > + `cache_size` bigint(21) unsigned NOT NULL, > + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', > + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' > +) ENGINE=MyISAM SEQUENCE=1 > +select * from t1; > +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count > +1 1 2 1 1 1000 0 0 > +select next value for t1; > +next value for t1 > +1 > +select next value for t1; > +next value for t1 > +2 > +select next value for t1; > +ERROR HY000: Sequence 'test.t1' has run out > +create or replace sequence t1 as tinyint unsigned minvalue 1 maxvalue 2 cycle; > +show create sequence t1; > +Table Create Table > +t1 CREATE SEQUENCE `t1` as tiny unsigned start with 1 minvalue 1 maxvalue 2 increment by 1 cache 1000 cycle ENGINE=MyISAM > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `next_not_cached_value` tinyint(5) unsigned NOT NULL, > + `minimum_value` tinyint(5) unsigned NOT NULL, > + `maximum_value` tinyint(5) unsigned NOT NULL, > + `start_value` tinyint(5) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', > + `increment` bigint(21) NOT NULL COMMENT 'increment value', > + `cache_size` bigint(21) unsigned NOT NULL, > + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', > + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' > +) ENGINE=MyISAM SEQUENCE=1 > +select * from t1; > +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count > +1 1 2 1 1 1000 1 0 > +select next value for t1; > +next value for t1 > +1 > +select next value for t1; > +next value for t1 > +2 > +select next value for t1; > +next value for t1 > +1 > +create or replace sequence t1 minvalue -23 maxvalue 99999 as tinyint; > +Warnings: > +Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' > +show create sequence t1; > +Table Create Table > +t1 CREATE SEQUENCE `t1` as tinyint start with -23 minvalue -23 maxvalue 126 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `next_not_cached_value` tinyint(5) NOT NULL, > + `minimum_value` tinyint(5) NOT NULL, > + `maximum_value` tinyint(5) NOT NULL, > + `start_value` tinyint(5) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', > + `increment` bigint(21) NOT NULL COMMENT 'increment value', > + `cache_size` bigint(21) unsigned NOT NULL, > + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', > + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' > +) ENGINE=MyISAM SEQUENCE=1 > +select * from t1; > +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count > +-23 -23 126 -23 1 1000 0 0 > +select next value for t1; > +next value for t1 > +-23 > +select next value for t1; > +next value for t1 > +-22 > +select next value for t1; > +next value for t1 > +-21 > +drop sequence t1; > diff --git a/mysql-test/suite/sql_sequence/next.test b/mysql-test/suite/sql_sequence/next.test > index a80f9fad561..f2054e5f116 100644 > --- a/mysql-test/suite/sql_sequence/next.test > +++ b/mysql-test/suite/sql_sequence/next.test > @@ -297,3 +297,36 @@ SELECT SETVAL (v,0); > > UNLOCK TABLES; > DROP VIEW v; > + > +--echo # > +--echo # MDEV-28152 Features for sequence > +--echo # > + > +create or replace sequence t1 as tinyint unsigned minvalue 1 maxvalue 2; > +show create sequence t1; > +show create table t1; > +select * from t1; > +select next value for t1; > +select next value for t1; > +--error ER_SEQUENCE_RUN_OUT > +select next value for t1; > + > +create or replace sequence t1 as tinyint unsigned minvalue 1 maxvalue 2 cycle; > +show create sequence t1; > +show create table t1; > +select * from t1; > +select next value for t1; > +select next value for t1; > +select next value for t1; > + > +--disable_ps_protocol > +create or replace sequence t1 minvalue -23 maxvalue 99999 as tinyint; > +--enable_ps_protocol > +show create sequence t1; > +show create table t1; > +select * from t1; > +select next value for t1; > +select next value for t1; > +select next value for t1; > + > +drop sequence t1; It is also interesting to see rolling over maximum value. > diff --git a/mysql-test/suite/sql_sequence/replication.result b/mysql-test/suite/sql_sequence/replication.result > index 94b1c72b9e0..2cfc246f2bf 100644 > --- a/mysql-test/suite/sql_sequence/replication.result > +++ b/mysql-test/suite/sql_sequence/replication.result > @@ -149,7 +149,7 @@ CREATE TABLE `s2` ( > ) ENGINE=myisam DEFAULT CHARSET=latin1 sequence=1; > show create sequence s2; > Table Create Table > -s2 CREATE SEQUENCE `s2` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +s2 CREATE SEQUENCE `s2` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > drop sequence s2; > ########################################### > select sequence syntax test > @@ -188,7 +188,7 @@ alter table s2 rename to s2_1; > rename table s2_1 to s2_2; > show create sequence s2_2; > Table Create Table > -s2_2 CREATE SEQUENCE `s2_2` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > +s2_2 CREATE SEQUENCE `s2_2` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM > select * from s2_2; > next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count > 1 1 9223372036854775806 1 1 1000 0 0 > diff --git a/mysql-test/suite/sql_sequence/slave_nextval.result b/mysql-test/suite/sql_sequence/slave_nextval.result > index bfbc472e117..36efa85d920 100644 > --- a/mysql-test/suite/sql_sequence/slave_nextval.result > +++ b/mysql-test/suite/sql_sequence/slave_nextval.result > @@ -4,7 +4,7 @@ CREATE SEQUENCE s; > INSERT INTO s VALUES (1,1,4,1,1,1,0,0); > show create sequence s; > Table Create Table > -s CREATE SEQUENCE `s` start with 1 minvalue 1 maxvalue 4 increment by 1 cache 1 nocycle ENGINE=MyISAM > +s CREATE SEQUENCE `s` as bigint start with 1 minvalue 1 maxvalue 4 increment by 1 cache 1 nocycle ENGINE=MyISAM > SELECT NEXTVAL(s); > NEXTVAL(s) > 1 > @@ -38,7 +38,7 @@ CREATE SEQUENCE s; > INSERT INTO s VALUES (1,1,3,1,1,1,1,0); > show create sequence s; > Table Create Table > -s CREATE SEQUENCE `s` start with 1 minvalue 1 maxvalue 3 increment by 1 cache 1 cycle ENGINE=MyISAM > +s CREATE SEQUENCE `s` as bigint start with 1 minvalue 1 maxvalue 3 increment by 1 cache 1 cycle ENGINE=MyISAM > SELECT NEXTVAL(s); > NEXTVAL(s) > 1 > diff --git a/sql/handler.h b/sql/handler.h > index 6b05da2ca98..f573dc835e9 100644 > --- a/sql/handler.h > +++ b/sql/handler.h > @@ -1055,6 +1055,7 @@ enum enum_schema_tables > SCH_PROCEDURES, > SCH_SCHEMATA, > SCH_SCHEMA_PRIVILEGES, > + SCH_SEQUENCES, > SCH_SESSION_STATUS, > SCH_SESSION_VARIABLES, > SCH_STATISTICS, > diff --git a/sql/sql_sequence.cc b/sql/sql_sequence.cc > index 11be2e1a691..c5754dc9670 100644 > --- a/sql/sql_sequence.cc > +++ b/sql/sql_sequence.cc > @@ -30,15 +30,6 @@ > #include "wsrep_mysqld.h" > #endif > > -struct Field_definition > -{ > - const char *field_name; > - uint length; > - const Type_handler *type_handler; > - LEX_CSTRING comment; > - ulong flags; > -}; > - > /* > Structure for all SEQUENCE tables > > @@ -48,30 +39,87 @@ struct Field_definition > a column named NEXTVAL. > */ > > +#define MAX_AUTO_INCREMENT_VALUE 65535 > + > +Sequence_row_definition sequence_structure(const Type_handler* handler) > +{ > + // We don't really care about src because it is unused in max_display_length_for_field(). > + const Conv_source src(handler, 0, system_charset_info); > + const uint32 len= handler->max_display_length_for_field(src) + 1; > + const LEX_CSTRING empty= {STRING_WITH_LEN("")}; > + const uint flag_unsigned= handler->is_unsigned() ? UNSIGNED_FLAG : 0; > #define FL (NOT_NULL_FLAG | NO_DEFAULT_VALUE_FLAG) > +#define FLV (NOT_NULL_FLAG | NO_DEFAULT_VALUE_FLAG | flag_unsigned) FL was a bad name (probably meant flag) and so you added even worse FLV (what it mean?) Please make both probably not long, but at least a bit meaningful. > + return {{{"next_not_cached_value", len, handler, empty, FLV}, > + {"minimum_value", len, handler, empty, FLV}, > + {"maximum_value", len, handler, empty, FLV}, > + {"start_value", len, handler, > + {STRING_WITH_LEN("start value when sequences is created or value " > + "if RESTART is used")}, FLV}, > + {"increment", 21, &type_handler_slonglong, > + {STRING_WITH_LEN("increment value")}, FL}, > + {"cache_size", 21, &type_handler_ulonglong, empty, > + FL | UNSIGNED_FLAG}, > + {"cycle_option", 1, &type_handler_utiny, > + {STRING_WITH_LEN("0 if no cycles are allowed, 1 if the sequence " "should begin a new cycle when maximum_value is " "passed")}, FL | UNSIGNED_FLAG}, > + {"cycle_count", 21, &type_handler_slonglong, > + {STRING_WITH_LEN("How many cycles have been done")}, FL}, > + {NULL, 0, &type_handler_slonglong, {STRING_WITH_LEN("")}, 0}}}; > +#undef FLV > +#undef FL > +} > + > +bool sequence_definition::is_allowed_value_type(enum_field_types type) > +{ > + switch (type) > + { > + case MYSQL_TYPE_TINY: > + case MYSQL_TYPE_SHORT: > + case MYSQL_TYPE_LONG: > + case MYSQL_TYPE_INT24: > + case MYSQL_TYPE_LONGLONG: > + return true; > + default: > + return false; > + } > +} > > -static Field_definition sequence_structure[]= > +Type_handler const *sequence_definition::value_type_handler() > { > - {"next_not_cached_value", 21, &type_handler_slonglong, > - {STRING_WITH_LEN("")}, FL}, > - {"minimum_value", 21, &type_handler_slonglong, {STRING_WITH_LEN("")}, FL}, > - {"maximum_value", 21, &type_handler_slonglong, {STRING_WITH_LEN("")}, FL}, > - {"start_value", 21, &type_handler_slonglong, {STRING_WITH_LEN("start value when sequences is created or value if RESTART is used")}, FL}, > - {"increment", 21, &type_handler_slonglong, > - {STRING_WITH_LEN("increment value")}, FL}, > - {"cache_size", 21, &type_handler_ulonglong, {STRING_WITH_LEN("")}, > - FL | UNSIGNED_FLAG}, > - {"cycle_option", 1, &type_handler_utiny, {STRING_WITH_LEN("0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed")}, > - FL | UNSIGNED_FLAG }, > - {"cycle_count", 21, &type_handler_slonglong, > - {STRING_WITH_LEN("How many cycles have been done")}, FL}, > - {NULL, 0, &type_handler_slonglong, {STRING_WITH_LEN("")}, 0} > -}; > + const Type_handler *handler= Type_handler::get_handler_by_field_type(value_type); > + return is_unsigned ? handler->type_handler_unsigned() : handler; > +} > > -#undef FL > +longlong sequence_definition::value_type_max() > +{ > + // value_type != MYSQL_TYPE_LONGLONG to avoid undefined behaviour > + // https://stackoverflow.com/questions/9429156/by-left-shifting-can-a-number-b… We use // only for one line coments please use /* */ above (please fx it in all your cases) > + return is_unsigned && value_type != MYSQL_TYPE_LONGLONG ? > + ~(~0ULL << 8 * value_type_handler()->calc_pack_length(0)) : > + ~value_type_min(); > +} > > +longlong sequence_definition::value_type_min() { > + return is_unsigned ? 0 : > + ~0ULL << (8 * value_type_handler()->calc_pack_length(0) - 1); > +} > > -#define MAX_AUTO_INCREMENT_VALUE 65535 > +/* > + Truncate `original` to `result`. > + If `original` is greater than value_type_max(), truncate down to value_type_max() > + If `original` is less than value_type_min(), truncate up to value_type_min() > +*/ Please use your standard description of all functions/methods /** One-line description Here longer multiline description of the function and maybe how it works @param paramater1 parameter1 description @param param2 param2 description @note Some notes about function and or implementation can be multiline. @return description what it return */ it also can be instead of @return returning value description @retval TRUE error @retval FALSE everything OK > +longlong sequence_definition::truncate_value(const Longlong_hybrid& original) > +{ > + if (is_unsigned) > + return original.to_ulonglong(value_type_max()); > + else if (original.is_unsigned_outside_of_signed_range()) > + return value_type_max(); > + else > + return original.value() > value_type_max() ? value_type_max() > + : original.value() < value_type_min() ? value_type_min() > + : original.value(); > +} > > /* > Check whether sequence values are valid. > @@ -82,49 +130,66 @@ static Field_definition sequence_structure[]= > true invalid > */ > > +// from_parser: whether to check foo_from_parser or foo, where foo in > +// {min_value, max_value, ...} Above method comment is in old style. You want to fix it (add a note), do just rewrite it please in the new style /** (see my example in this review) > bool sequence_definition::check_and_adjust(THD *thd, bool set_reserved_until) > { > longlong max_increment; > - DBUG_ENTER("sequence_definition::check"); > + DBUG_ENTER("sequence_definition::check_and_adjust"); > > if (!(real_increment= increment)) > real_increment= global_system_variables.auto_increment_increment; > > /* > - If min_value is not set, set it to LONGLONG_MIN or 1, depending on > + If min_value is not set, set it to value_type_min()+1 or 1, depending on > real_increment > */ > - if (!(used_fields & seq_field_used_min_value)) > - min_value= real_increment < 0 ? LONGLONG_MIN+1 : 1; > + if (!(used_fields & seq_field_specified_min_value)) > + min_value= real_increment < 0 ? value_type_min()+1 : 1; > + else > + { > + min_value= truncate_value(min_value_from_parser); > + if ((is_unsigned && (ulonglong) min_value <= (ulonglong) value_type_min()) || above line is too long (at least should fit in 80 better in 72 character) > + (!is_unsigned && min_value <= value_type_min())) > + { > + push_warning_printf( > + thd, Sql_condition::WARN_LEVEL_NOTE, ER_TRUNCATED_WRONG_VALUE, > + ER_THD(thd, ER_TRUNCATED_WRONG_VALUE), "INTEGER", "MINVALUE"); > + min_value= value_type_min() + 1; > + } > + } > > /* > - If max_value is not set, set it to LONGLONG_MAX or -1, depending on > + If max_value is not set, set it to value_type_max()-1 or -1, depending on > real_increment > */ > - if (!(used_fields & seq_field_used_max_value)) > - max_value= real_increment < 0 ? -1 : LONGLONG_MAX-1; > - > - if (max_value == LONGLONG_MAX) > - { > - push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE, > - ER_TRUNCATED_WRONG_VALUE, > - ER_THD(thd, ER_TRUNCATED_WRONG_VALUE), > - "INTEGER", "MAXVALUE"); > - max_value= LONGLONG_MAX - 1; > - } > - if (min_value == LONGLONG_MIN) > + if (!(used_fields & seq_field_specified_max_value)) > + max_value= real_increment < 0 ? -1 : value_type_max()-1; > + else > { > - push_warning_printf( > - thd, Sql_condition::WARN_LEVEL_NOTE, ER_TRUNCATED_WRONG_VALUE, > - ER_THD(thd, ER_TRUNCATED_WRONG_VALUE), "INTEGER", "MINVALUE"); > - min_value= LONGLONG_MIN + 1; > + max_value= truncate_value(max_value_from_parser); > + if ((is_unsigned && (ulonglong) max_value >= (ulonglong) value_type_max()) || > + (!is_unsigned && max_value >= value_type_max())) > + { > + push_warning_printf( > + thd, Sql_condition::WARN_LEVEL_NOTE, ER_TRUNCATED_WRONG_VALUE, > + ER_THD(thd, ER_TRUNCATED_WRONG_VALUE), "INTEGER", "MAXVALUE"); > + max_value= value_type_max() - 1; > + } > } > > if (!(used_fields & seq_field_used_start)) > { > /* Use min_value or max_value for start depending on real_increment */ > start= real_increment < 0 ? max_value : min_value; > - } > + } else > + // If the supplied start value is out of range for the value type, > + // instead of immediately reporting error, we truncate it to > + // value_type_min or value_type_max depending on which side it is > + // one. Whenever such truncation happens, the condition that > + // max_value >= start >= min_value will be violated, and the error > + // will be reported then. We use // only for one line coments please use /* */ above (please fx it in all your cases) > + start= truncate_value(start_from_parser); Do we check that start value fit in [minvalue, maxvalue] range? > > if (set_reserved_until) > reserved_until= start; > @@ -136,12 +201,23 @@ bool sequence_definition::check_and_adjust(THD *thd, bool set_reserved_until) > llabs(real_increment) : > MAX_AUTO_INCREMENT_VALUE); > > - if (max_value >= start && > - max_value > min_value && > + // Common case for error, signed or unsigned. > + if (!is_allowed_value_type(value_type) || cache < 0) > + DBUG_RETURN(TRUE); > + > + // TODO: check for cache < (ULONGLONG_MAX - max_increment) / max_increment Why above is in TODO, IMHO it is not so difficult to make (maybe I do not see something?) > + if (is_unsigned && (ulonglong) max_value >= (ulonglong) start && > + (ulonglong) max_value > (ulonglong) min_value && > + (ulonglong) start >= (ulonglong) min_value && > + // Just like the case in signed, where a positive sequence > + // cannot have a negatvie increment, an unsigned sequence is > + // positive, so the increment has to be positive Why we can not count backward for unsigned sequence? Taking into account that increment probably should not exceed half of allowed range we always can store it as bigint (or decimal if we should support sequences with 1 value in them). We use // only for one line coments please use /* */ above (please fx it in all your cases) > + (real_increment > 0 && (ulonglong) reserved_until >= (ulonglong) min_value)) > + DBUG_RETURN(FALSE); > + > + if (!is_unsigned && max_value >= start && max_value > min_value && > start >= min_value && > - max_value != LONGLONG_MAX && > - min_value != LONGLONG_MIN && > - cache >= 0 && cache < (LONGLONG_MAX - max_increment) / max_increment && > + cache < (LONGLONG_MAX - max_increment) / max_increment && > ((real_increment > 0 && reserved_until >= min_value) || > (real_increment < 0 && reserved_until <= max_value))) > DBUG_RETURN(FALSE); > @@ -165,6 +241,11 @@ void sequence_definition::read_fields(TABLE *table) > cache= table->field[5]->val_int(); > cycle= table->field[6]->val_int(); > round= table->field[7]->val_int(); > + value_type= table->field[0]->type(); > + is_unsigned= table->field[0]->is_unsigned(); > + min_value_from_parser= Longlong_hybrid(min_value, is_unsigned); > + max_value_from_parser= Longlong_hybrid(max_value, is_unsigned); > + start_from_parser= Longlong_hybrid(start, is_unsigned); > dbug_tmp_restore_column_map(&table->read_set, old_map); > used_fields= ~(uint) 0; > print_dbug(); > @@ -181,10 +262,10 @@ void sequence_definition::store_fields(TABLE *table) > > /* zero possible delete markers & null bits */ > memcpy(table->record[0], table->s->default_values, table->s->null_bytes); > - table->field[0]->store(reserved_until, 0); > - table->field[1]->store(min_value, 0); > - table->field[2]->store(max_value, 0); > - table->field[3]->store(start, 0); > + table->field[0]->store(reserved_until, is_unsigned); > + table->field[1]->store(min_value, is_unsigned); > + table->field[2]->store(max_value, is_unsigned); > + table->field[3]->store(start, is_unsigned); > table->field[4]->store(increment, 0); > table->field[5]->store(cache, 0); > table->field[6]->store((longlong) cycle != 0, 0); > @@ -210,10 +291,17 @@ bool check_sequence_fields(LEX *lex, List<Create_field> *fields) > uint field_count; > uint field_no; > const char *reason; > + Sequence_row_definition row_structure; > DBUG_ENTER("check_sequence_fields"); > > field_count= fields->elements; > - if (field_count != array_elements(sequence_structure)-1) > + if (!field_count) > + { > + reason= "Wrong number of columns"; > + goto err; > + } > + row_structure= sequence_structure(fields->head()->type_handler()); > + if (field_count != array_elements(row_structure.fields)-1) > { > reason= "Wrong number of columns"; We do not have only english speaking users, it was done bad before and now we have chance to fix it and make it correct via localised errors. > goto err; > @@ -236,7 +324,7 @@ bool check_sequence_fields(LEX *lex, List<Create_field> *fields) > > for (field_no= 0; (field= it++); field_no++) > { > - Field_definition *field_def= &sequence_structure[field_no]; > + const Sequence_field_definition *field_def= &row_structure.fields[field_no]; > if (my_strcasecmp(system_charset_info, field_def->field_name, > field->field_name.str) || > field->flags != field_def->flags || > @@ -265,12 +353,13 @@ bool check_sequence_fields(LEX *lex, List<Create_field> *fields) > true Failure (out of memory) > */ > > -bool prepare_sequence_fields(THD *thd, List<Create_field> *fields) > +bool sequence_definition::prepare_sequence_fields(List<Create_field> *fields, bool alter) too long line > { > - Field_definition *field_info; > DBUG_ENTER("prepare_sequence_fields"); > + const Sequence_row_definition row_def= sequence_structure(value_type_handler()); We put DBUG_ENTER after variables definition (C code heritage) > > - for (field_info= sequence_structure; field_info->field_name ; field_info++) > + for (const Sequence_field_definition *field_info= row_def.fields; > + field_info->field_name; field_info++) > { > Create_field *new_field; > LEX_CSTRING field_name= {field_info->field_name, > @@ -285,6 +374,8 @@ bool prepare_sequence_fields(THD *thd, List<Create_field> *fields) > new_field->char_length= field_info->length; > new_field->comment= field_info->comment; > new_field->flags= field_info->flags; > + if (alter) > + new_field->change = field_name; > if (unlikely(fields->push_back(new_field))) > DBUG_RETURN(TRUE); /* purify inspected */ > } > @@ -313,20 +404,18 @@ bool sequence_insert(THD *thd, LEX *lex, TABLE_LIST *org_table_list) > Reprepare_observer *save_reprepare_observer; > sequence_definition *seq= lex->create_info.seq_create_info; > bool temporary_table= org_table_list->table != 0; > + /* > + seq is 0 if sequence was created with CREATE TABLE instead of > + CREATE SEQUENCE > + */ > + bool create_new= !seq; > Open_tables_backup open_tables_backup; > Query_tables_list query_tables_list_backup; > TABLE_LIST table_list; // For sequence table > DBUG_ENTER("sequence_insert"); > > - /* > - seq is 0 if sequence was created with CREATE TABLE instead of > - CREATE SEQUENCE > - */ > - if (!seq) > - { > - if (!(seq= new (thd->mem_root) sequence_definition)) > - DBUG_RETURN(TRUE); > - } > + if (create_new && !(seq= new (thd->mem_root) sequence_definition)) > + DBUG_RETURN(TRUE); > > #ifdef WITH_WSREP > if (WSREP_ON && seq->cache != 0) > @@ -387,7 +476,15 @@ bool sequence_insert(THD *thd, LEX *lex, TABLE_LIST *org_table_list) > else > table= org_table_list->table; > > - seq->reserved_until= seq->start; > + if (create_new) > + { > + seq->value_type= (*table->s->field)->type(); > + seq->is_unsigned= (*table->s->field)->is_unsigned(); > + // fixme: why do we need true here? because it is other copy so we reset it to the start, right? > + if (seq->check_and_adjust(thd, true)) > + DBUG_RETURN(TRUE); > + } > + > error= seq->write_initial_sequence(table); > { > uint save_unsafe_rollback_flags= > @@ -432,9 +529,9 @@ SEQUENCE::~SEQUENCE() > A sequence table can have many readers (trough normal SELECT's). > > We mark that we have a write lock in the table object so that > - ha_sequence::ha_write() can check if we have a lock. If already locked, then > + ha_sequence::write_row() can check if we have a lock. If already locked, then > ha_write() knows that we are running a sequence operation. If not, then > - ha_write() knows that it's an INSERT. > + ha_write() knows that it's an INSERT statement. > */ > > void SEQUENCE::write_lock(TABLE *table) > @@ -734,10 +831,9 @@ longlong SEQUENCE::next_value(TABLE *table, bool second_round, int *error) > write_lock(table); > > res_value= next_free_value; > - next_free_value= increment_value(next_free_value); > + next_free_value= increment_value(next_free_value, real_increment); > > - if ((real_increment > 0 && res_value < reserved_until) || > - (real_increment < 0 && res_value > reserved_until)) > + if (within_bounds(res_value, reserved_until, reserved_until, real_increment > 0)) > { > write_unlock(table); > DBUG_RETURN(res_value); > @@ -754,30 +850,10 @@ longlong SEQUENCE::next_value(TABLE *table, bool second_round, int *error) > overflow > */ > add_to= cache ? real_increment * cache : real_increment; > - out_of_values= 0; > > - if (real_increment > 0) > - { > - if (reserved_until > max_value - add_to || > - reserved_until + add_to > max_value) > - { > - reserved_until= max_value + 1; > - out_of_values= res_value >= reserved_until; > - } > - else > - reserved_until+= add_to; > - } > - else > - { > - if (reserved_until + add_to < min_value || > - reserved_until < min_value - add_to) > - { > - reserved_until= min_value - 1; > - out_of_values= res_value <= reserved_until; > - } > - else > - reserved_until+= add_to; > - } > + // TODO: consider extracting this refactoring to a separate earlier commit. You have send it on review, so I suppose you take decision about above? > + reserved_until= increment_value(reserved_until, add_to); > + out_of_values= !within_bounds(res_value, max_value + 1, min_value - 1, add_to > 0); > if (out_of_values) > { > if (!cycle || second_round) > @@ -866,7 +942,7 @@ int SEQUENCE::set_value(TABLE *table, longlong next_val, ulonglong next_round, > > write_lock(table); > if (is_used) > - next_val= increment_value(next_val); > + next_val= increment_value(next_val, real_increment); > > if (round > next_round) > goto end; // error = -1 > @@ -953,6 +1029,35 @@ bool Sql_cmd_alter_sequence::execute(THD *thd) > first_table)) > DBUG_RETURN(TRUE); > #endif /* WITH_WSREP */ > + > + if (new_seq->used_fields & seq_field_used_as) > + { > + // This shouldn't happen as it should have been prevented during > + // parsing. If something should not happened you better put DEBUG_ASSERT to notice in debug version if something goes wrong. Chenge comment stile if you want to leave it > + if (new_seq->used_fields - seq_field_used_as) > + DBUG_RETURN(TRUE); > + > + first_table->lock_type= TL_READ_NO_INSERT; > + first_table->mdl_request.set_type(MDL_SHARED_NO_WRITE); > + Alter_info alter_info; > + alter_info.flags= ALTER_CHANGE_COLUMN; > + if (new_seq->prepare_sequence_fields(&alter_info.create_list, true)) > + DBUG_RETURN(TRUE); > + Table_specification_st create_info; > + create_info.init(); > + create_info.alter_info= &alter_info; > + if (if_exists()) > + thd->push_internal_handler(&no_such_table_handler); > + error= mysql_alter_table(thd, &null_clex_str, &null_clex_str, &create_info, first_table, &alter_info, 0, (ORDER *) 0, 0, 0); > + if (if_exists()) > + { > + trapped_errors= no_such_table_handler.safely_trapped_errors(); > + thd->pop_internal_handler(); > + } > + // Do we need to store the sequence value in table share, like below? I do not understand comment above. (what it is about?) > + DBUG_RETURN(error); > + } > + > if (if_exists()) > thd->push_internal_handler(&no_such_table_handler); > error= open_and_lock_tables(thd, first_table, FALSE, 0); > @@ -989,22 +1094,30 @@ bool Sql_cmd_alter_sequence::execute(THD *thd) > if (!(new_seq->used_fields & seq_field_used_increment)) > new_seq->increment= seq->increment; > if (!(new_seq->used_fields & seq_field_used_min_value)) > - new_seq->min_value= seq->min_value; > + new_seq->min_value_from_parser= seq->min_value_from_parser; > if (!(new_seq->used_fields & seq_field_used_max_value)) > - new_seq->max_value= seq->max_value; > + new_seq->max_value_from_parser= seq->max_value_from_parser; > if (!(new_seq->used_fields & seq_field_used_start)) > - new_seq->start= seq->start; > + new_seq->start_from_parser= seq->start_from_parser; > if (!(new_seq->used_fields & seq_field_used_cache)) > new_seq->cache= seq->cache; > if (!(new_seq->used_fields & seq_field_used_cycle)) > new_seq->cycle= seq->cycle; > + if (!(new_seq->used_fields & seq_field_used_as)) > + { > + new_seq->value_type= seq->value_type; > + new_seq->is_unsigned= seq->is_unsigned; > + } > > /* If we should restart from a new value */ > if (new_seq->used_fields & seq_field_used_restart) > { > if (!(new_seq->used_fields & seq_field_used_restart_value)) > - new_seq->restart= new_seq->start; > - new_seq->reserved_until= new_seq->restart; > + new_seq->restart_from_parser= new_seq->start_from_parser; > + // Similar to start, we just need to truncate reserved_until and > + // the errors will be reported in check_and_adjust if truncation > + // happens on the wrong end. We use // only for one line coments please use /* */ above (please fx it in all your cases) > + new_seq->reserved_until= new_seq->truncate_value(new_seq->restart_from_parser); > } > > /* Let check_and_adjust think all fields are used */ > diff --git a/sql/sql_sequence.h b/sql/sql_sequence.h > index e2968cc20ae..0de92a90639 100644 > --- a/sql/sql_sequence.h > +++ b/sql/sql_sequence.h > @@ -25,12 +25,35 @@ > #define seq_field_used_cycle 32 > #define seq_field_used_restart 64 > #define seq_field_used_restart_value 128 > +#define seq_field_used_as 256 > +#define seq_field_specified_min_value 512 > +#define seq_field_specified_max_value 1024 > > /* Field position in sequence table for some fields we refer to directly */ > #define NEXT_FIELD_NO 0 > #define MIN_VALUE_FIELD_NO 1 > #define ROUND_FIELD_NO 7 > > +#include "mysql_com.h" > +#include "sql_type_int.h" > + > +class Create_field; > +class Type_handler; > + > +struct Sequence_field_definition > +{ > + const char *field_name; > + uint length; > + const Type_handler *type_handler; > + LEX_CSTRING comment; > + ulong flags; > +}; > + > +struct Sequence_row_definition > +{ > + Sequence_field_definition fields[9]; > +}; > + > /** > sequence_definition is used when defining a sequence as part of create > */ > @@ -39,20 +62,37 @@ class sequence_definition :public Sql_alloc > { > public: > sequence_definition(): > - min_value(1), max_value(LONGLONG_MAX-1), start(1), increment(1), > - cache(1000), round(0), restart(0), cycle(0), used_fields(0) > + min_value_from_parser(1, false), > + max_value_from_parser(LONGLONG_MAX-1, false), start_from_parser(1, false), > + increment(1), cache(1000), round(0), restart_from_parser(0, false), cycle(0), used_fields(0), > + // We use value type and is_unsigned instead of a handler because > + // Type_handler is incomplete, which we cannot initialise here > + // with &type_handler_slonglong. We use // only for one line coments please use /* */ above (please fx it in all your cases) > + value_type(MYSQL_TYPE_LONGLONG), is_unsigned(false) > {} > longlong reserved_until; > longlong min_value; > longlong max_value; > longlong start; > + Longlong_hybrid min_value_from_parser; > + Longlong_hybrid max_value_from_parser; > + Longlong_hybrid start_from_parser; > longlong increment; > longlong cache; > ulonglong round; > + // TODO: allow unsigned in restart > longlong restart; // alter sequence restart value > + Longlong_hybrid restart_from_parser; > bool cycle; > uint used_fields; // Which fields where used in CREATE > - > + enum_field_types value_type; // value type of the sequence > + bool is_unsigned; > + > + Type_handler const *value_type_handler(); > + // max value for the value type, e.g. 32767 for smallint. > + longlong value_type_max(); > + // min value for the value type, e.g. -32768 for smallint. > + longlong value_type_min(); > bool check_and_adjust(THD *thd, bool set_reserved_until); > void store_fields(TABLE *table); > void read_fields(TABLE *table); > @@ -60,12 +100,16 @@ class sequence_definition :public Sql_alloc > int write(TABLE *table, bool all_fields); > /* This must be called after sequence data has been updated */ > void adjust_values(longlong next_value); > + longlong truncate_value(const Longlong_hybrid& original); > inline void print_dbug() > { > DBUG_PRINT("sequence", ("reserved: %lld start: %lld increment: %lld min_value: %lld max_value: %lld cache: %lld round: %lld", > reserved_until, start, increment, min_value, > max_value, cache, round)); > } > + static bool is_allowed_value_type(enum_field_types type); > + bool prepare_sequence_fields(List<Create_field> *fields, bool alter); > + > protected: > /* > The following values are the values from sequence_definition > @@ -107,24 +151,54 @@ class SEQUENCE :public sequence_definition > longlong next_value(TABLE *table, bool second_round, int *error); > int set_value(TABLE *table, longlong next_value, ulonglong round_arg, > bool is_used); > - longlong increment_value(longlong value) > + bool within_bounds(const longlong value, const longlong upper, const longlong lower, bool increasing) > + { > + return > + (is_unsigned && increasing && (ulonglong) value < (ulonglong) upper) || > + (is_unsigned && !increasing && (ulonglong) value > (ulonglong) lower) || > + (!is_unsigned && increasing && value < upper) || > + (!is_unsigned && !increasing && value > lower); > + } > + > + longlong increment_value(longlong value, const longlong increment) > { > - if (real_increment > 0) > + if (is_unsigned) > { > - if (value > max_value - real_increment || > - value + real_increment > max_value) > - value= max_value + 1; > + if (increment > 0) > + { > + // in case value + increment overflows > + if ((ulonglong) value > (ulonglong) max_value - (ulonglong) increment || > + // in case max_value - increment underflows > + (ulonglong) value + (ulonglong) increment > (ulonglong) max_value) > + value= max_value + 1; > + else > + value+= increment; > + } > else > - value+= real_increment; > - } > - else > - { > - if (value + real_increment < min_value || > - value < min_value - real_increment) > - value= min_value - 1; > + { > + if ((ulonglong) value - (ulonglong) (-increment) < (ulonglong) min_value || > + (ulonglong) value < (ulonglong) min_value + (ulonglong) (- increment)) > + value= min_value - 1; > + else > + value+= increment; > + } > + } else > + if (increment > 0) > + { > + if (value > max_value - increment || > + value + increment > max_value) > + value= max_value + 1; > + else > + value+= increment; > + } > else > - value+= real_increment; > - } > + { > + if (value + increment < min_value || > + value < min_value - increment) > + value= min_value - 1; > + else > + value+= increment; > + } > return value; > } > > @@ -159,9 +233,6 @@ class SEQUENCE_LAST_VALUE > uchar table_version[MY_UUID_SIZE]; > }; > > - > -class Create_field; > -extern bool prepare_sequence_fields(THD *thd, List<Create_field> *fields); > extern bool check_sequence_fields(LEX *lex, List<Create_field> *fields); > extern bool sequence_insert(THD *thd, LEX *lex, TABLE_LIST *table_list); > #endif /* SQL_SEQUENCE_INCLUDED */ > diff --git a/sql/sql_show.cc b/sql/sql_show.cc > index 1fd31bd4947..12e4f655515 100644 > --- a/sql/sql_show.cc > +++ b/sql/sql_show.cc > @@ -2694,12 +2694,29 @@ static int show_create_sequence(THD *thd, TABLE_LIST *table_list, > > packet->append(STRING_WITH_LEN("CREATE SEQUENCE ")); > append_identifier(thd, packet, &alias); > + /* Do not show " as <type>" in oracle mode as it is not supported: In case of multiline comment /* should be on separate line > + https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-… you also probably should not show it if it is default (BIGINT), see other comment about it in the beginning. BTW add test of making sequence with/without oracle mode and dumping and restoring (mysqldump) them for all types. You can find examples of the tests by "ls mysql-test/main/*dump*.test". > + */ > + if (!(sql_mode & MODE_ORACLE)) > + { > + packet->append(STRING_WITH_LEN(" as ")); > + packet->append(seq->value_type_handler()->name().lex_cstring()); > + } > packet->append(STRING_WITH_LEN(" start with ")); > - packet->append_longlong(seq->start); > + if (seq->is_unsigned) > + packet->append_ulonglong(seq->start); > + else > + packet->append_longlong(seq->start); > packet->append(STRING_WITH_LEN(" minvalue ")); > - packet->append_longlong(seq->min_value); > + if (seq->is_unsigned) > + packet->append_ulonglong(seq->min_value); > + else > + packet->append_longlong(seq->min_value); > packet->append(STRING_WITH_LEN(" maxvalue ")); > - packet->append_longlong(seq->max_value); > + if (seq->is_unsigned) > + packet->append_ulonglong(seq->max_value); > + else > + packet->append_longlong(seq->max_value); > packet->append(STRING_WITH_LEN(" increment by ")); > packet->append_longlong(seq->increment); > if (seq->cache) > @@ -5512,6 +5529,36 @@ int fill_schema_schemata(THD *thd, TABLE_LIST *tables, COND *cond) > DBUG_RETURN(0); > } > > +static int get_schema_sequence_record(THD *thd, TABLE_LIST *tables, > + TABLE *table, bool res, > + const LEX_CSTRING *db_name, > + const LEX_CSTRING *table_name) > +{ > + DBUG_ENTER("get_sequence_record"); > + CHARSET_INFO *cs= system_charset_info; > + restore_record(table, s->default_values); > + sequence_definition *seq= tables->table->s->sequence; > + if (tables->table->s->table_type == TABLE_TYPE_SEQUENCE) > + { > + const Type_handler *handler= seq->value_type_handler(); > + table->field[0]->store(STRING_WITH_LEN("def"), cs); > + table->field[1]->store(db_name->str, db_name->length, cs); > + table->field[2]->store(table_name->str, table_name->length, cs); > + table->field[3]->store(handler->name().lex_cstring(), cs); > + table->field[4]->store(8 * handler->calc_pack_length(0)); > + table->field[5]->store(2); > + table->field[5]->set_notnull(); > + table->field[6]->store(0); > + table->field[6]->set_notnull(); > + table->field[7]->store(seq->start, seq->is_unsigned); > + table->field[8]->store(seq->min_value, seq->is_unsigned); > + table->field[9]->store(seq->max_value, seq->is_unsigned); > + table->field[10]->store(seq->increment, 0); > + table->field[11]->store(seq->cycle); > + DBUG_RETURN(schema_table_store_record(thd, table)); > + } > + DBUG_RETURN(0); > +} > > static int get_schema_tables_record(THD *thd, TABLE_LIST *tables, > TABLE *table, bool res, > @@ -9419,6 +9466,29 @@ ST_FIELD_INFO proc_fields_info[]= > }; > > > +ST_FIELD_INFO sequence_fields_info[]= > +{ > + Column("SEQUENCE_CATALOG", Catalog(), NOT_NULL, OPEN_FRM_ONLY), > + Column("SEQUENCE_SCHEMA", Name(), NOT_NULL, OPEN_FRM_ONLY), > + Column("SEQUENCE_NAME", Name(), NOT_NULL, "Table", OPEN_FRM_ONLY), > + Column("DATA_TYPE", Name(), NOT_NULL), > + Column("NUMERIC_PRECISION", SLong(21), NOT_NULL), > + Column("NUMERIC_PRECISION_RADIX", SLong(21), NULLABLE), > + Column("NUMERIC_SCALE", SLong(21), NULLABLE), > + // Decimal types for these values to incorporate possibly unsigned > + // longlongs. We use // only for one line coments please use /* */ above (please fx it in all your cases) > + Column("START_VALUE", Decimal(2100), NOT_NULL), > + Column("MINIMUM_VALUE", Decimal(2100), NOT_NULL), > + Column("MAXIMUM_VALUE", Decimal(2100), NOT_NULL), > + Column("INCREMENT", SLonglong(21), NOT_NULL), > + Column("CYCLE_OPTION", SLonglong(21), NOT_NULL), > + Column("DECLARED_DATA_TYPE", SLong(21), NULLABLE), > + Column("DECLARED_NUMERIC_PRECISION", SLong(21), NULLABLE), > + Column("DECLARED_NUMERIC_SCALE", SLong(21), NULLABLE), > + CEnd() > +}; > + > + > ST_FIELD_INFO stat_fields_info[]= > { > Column("TABLE_CATALOG", Catalog(), NOT_NULL, OPEN_FRM_ONLY), > @@ -9985,6 +10055,8 @@ ST_SCHEMA_TABLE schema_tables[]= > fill_schema_schemata, make_schemata_old_format, 0, 1, -1, 0, 0}, > {"SCHEMA_PRIVILEGES", Show::schema_privileges_fields_info, 0, > fill_schema_schema_privileges, 0, 0, -1, -1, 0, 0}, > + {"SEQUENCES", Show::sequence_fields_info, 0, > + get_all_tables, make_old_format, get_schema_sequence_record, 1, 2, 0, 0}, > {"SESSION_STATUS", Show::variables_fields_info, 0, > fill_status, make_old_format, 0, 0, -1, 0, 0}, > {"SESSION_VARIABLES", Show::variables_fields_info, 0, > diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy > index e3298a4a6c1..27ce8bcdbfc 100644 > --- a/sql/sql_yacc.yy > +++ b/sql/sql_yacc.yy > @@ -200,6 +200,9 @@ void _CONCAT_UNDERSCORED(turn_parser_debug_on,yyparse)() > ulonglong ulonglong_number; > longlong longlong_number; > uint sp_instr_addr; > + // Longlong_hybrid does not have a default constructor, hence the > + // default value below. We use // only for one line coments please use /* */ above (please fx it in all your cases) > + Longlong_hybrid longlong_hybrid_number= Longlong_hybrid(0, false); > > /* structs */ > LEX_CSTRING lex_str; > @@ -1466,7 +1469,10 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); > ulonglong_num real_ulonglong_num > > %type <longlong_number> > - sequence_value_num sequence_truncated_value_num > + sequence_value_num > + > +%type <longlong_hybrid_number> > + sequence_value_hybrid_num sequence_truncated_value_hybrid_num > > %type <choice> choice > > @@ -2429,8 +2435,8 @@ create: > } > > /* No fields specified, generate them */ > - if (unlikely(prepare_sequence_fields(thd, > - &lex->alter_info.create_list))) > + if (unlikely(lex->create_info.seq_create_info->prepare_sequence_fields( > + &lex->alter_info.create_list, false))) > MYSQL_YYABORT; > > /* CREATE SEQUENCE always creates a sequence */ > @@ -2605,13 +2611,25 @@ sequence_defs: > ; > > sequence_def: > - MINVALUE_SYM opt_equal sequence_truncated_value_num > + AS int_type field_options > + { > + if (unlikely(Lex->create_info.seq_create_info->used_fields & > + seq_field_used_as)) > + my_yyabort_error((ER_DUP_ARGUMENT, MYF(0), "AS")); > + if ($3 & ZEROFILL_FLAG) > + my_yyabort_error((ER_BAD_OPTION_VALUE, MYF(0), "ZEROFILL", "AS")); > + Lex->create_info.seq_create_info->value_type = $2->field_type(); > + Lex->create_info.seq_create_info->is_unsigned = $3 & UNSIGNED_FLAG ? true : false; > + Lex->create_info.seq_create_info->used_fields|= seq_field_used_as; > + } > + | MINVALUE_SYM opt_equal sequence_truncated_value_hybrid_num > { > if (unlikely(Lex->create_info.seq_create_info->used_fields & > seq_field_used_min_value)) > my_yyabort_error((ER_DUP_ARGUMENT, MYF(0), "MINVALUE")); > - Lex->create_info.seq_create_info->min_value= $3; > + Lex->create_info.seq_create_info->min_value_from_parser= $3; > Lex->create_info.seq_create_info->used_fields|= seq_field_used_min_value; > + Lex->create_info.seq_create_info->used_fields|= seq_field_specified_min_value; > } > | NO_SYM MINVALUE_SYM > { > @@ -2625,13 +2643,14 @@ sequence_def: > my_yyabort_error((ER_DUP_ARGUMENT, MYF(0), "MINVALUE")); > Lex->create_info.seq_create_info->used_fields|= seq_field_used_min_value; > } > - | MAXVALUE_SYM opt_equal sequence_truncated_value_num > + | MAXVALUE_SYM opt_equal sequence_truncated_value_hybrid_num > { > if (unlikely(Lex->create_info.seq_create_info->used_fields & > seq_field_used_max_value)) > my_yyabort_error((ER_DUP_ARGUMENT, MYF(0), "MAXVALUE")); > - Lex->create_info.seq_create_info->max_value= $3; > + Lex->create_info.seq_create_info->max_value_from_parser= $3; > Lex->create_info.seq_create_info->used_fields|= seq_field_used_max_value; > + Lex->create_info.seq_create_info->used_fields|= seq_field_specified_max_value; > } > | NO_SYM MAXVALUE_SYM > { > @@ -2645,12 +2664,12 @@ sequence_def: > my_yyabort_error((ER_DUP_ARGUMENT, MYF(0), "MAXVALUE")); > Lex->create_info.seq_create_info->used_fields|= seq_field_used_max_value; > } > - | START_SYM opt_with sequence_value_num > + | START_SYM opt_with sequence_value_hybrid_num > { > if (unlikely(Lex->create_info.seq_create_info->used_fields & > seq_field_used_start)) > my_yyabort_error((ER_DUP_ARGUMENT, MYF(0), "START")); > - Lex->create_info.seq_create_info->start= $3; > + Lex->create_info.seq_create_info->start_from_parser= $3; > Lex->create_info.seq_create_info->used_fields|= seq_field_used_start; > } > | INCREMENT_SYM opt_by sequence_value_num > @@ -2705,7 +2724,7 @@ sequence_def: > my_yyabort_error((ER_DUP_ARGUMENT, MYF(0), "RESTART")); > Lex->create_info.seq_create_info->used_fields|= seq_field_used_restart; > } > - | RESTART_SYM opt_with sequence_value_num > + | RESTART_SYM opt_with sequence_value_hybrid_num > { > if (unlikely(Lex->sql_command != SQLCOM_ALTER_SEQUENCE)) > { > @@ -2715,7 +2734,7 @@ sequence_def: > if (unlikely(Lex->create_info.seq_create_info->used_fields & > seq_field_used_restart)) > my_yyabort_error((ER_DUP_ARGUMENT, MYF(0), "RESTART")); > - Lex->create_info.seq_create_info->restart= $3; > + Lex->create_info.seq_create_info->restart_from_parser= $3; > Lex->create_info.seq_create_info->used_fields|= seq_field_used_restart | seq_field_used_restart_value; > } > ; > @@ -7122,6 +7141,8 @@ alter: > { > /* Create a generic ALTER SEQUENCE statment. */ > Lex->m_sql_cmd= new (thd->mem_root) Sql_cmd_alter_sequence($3); > + if ((Lex->create_info.seq_create_info->used_fields & seq_field_used_as) && (Lex->create_info.seq_create_info->used_fields - seq_field_used_as)) Above lie is to long > + my_yyabort_error((ER_NOT_SUPPORTED_YET, MYF(0), "ALTER SEQUENCE with both AS <type> and something else.")); Above lie is to long > if (unlikely(Lex->m_sql_cmd == NULL)) > MYSQL_YYABORT; > } stmt_end {} > @@ -12563,6 +12584,7 @@ real_ulong_num: > | dec_num_error { MYSQL_YYABORT; } > ; > > +// For simple sequence metadata values that are signed and do not need truncation Above lie is to long > sequence_value_num: > opt_plus NUM { int error; $$= (longlong) my_strtoll10($2.str, (char**) 0, &error); } > | opt_plus LONG_NUM { int error; $$= (longlong) my_strtoll10($2.str, (char**) 0, &error); } > @@ -12579,15 +12601,74 @@ sequence_value_num: > } > ; > > -sequence_truncated_value_num: > - opt_plus NUM { int error; $$= (longlong) my_strtoll10($2.str, (char**) 0, &error); } > - | opt_plus LONG_NUM { int error; $$= (longlong) my_strtoll10($2.str, (char**) 0, &error); } > - | opt_plus ULONGLONG_NUM { $$= LONGLONG_MAX; } > - | opt_plus DECIMAL_NUM { $$= LONGLONG_MAX; } > - | '-' NUM { int error; $$= -(longlong) my_strtoll10($2.str, (char**) 0, &error); } > - | '-' LONG_NUM { int error; $$= -(longlong) my_strtoll10($2.str, (char**) 0, &error); } > - | '-' ULONGLONG_NUM { $$= LONGLONG_MIN; } > - | '-' DECIMAL_NUM { $$= LONGLONG_MIN; } > +// For sequence metadata values that may be unsigned but do not need truncation (start, restart) Above lie is to long (it looks like you have them a lot, so please fix all lines to fit in 80 (better 72 character). > +sequence_value_hybrid_num: > + opt_plus NUM > + { > + int error; > + $$= Longlong_hybrid(my_strtoll10($2.str, (char**) 0, &error), false); > + } > + | opt_plus LONG_NUM > + { > + int error; > + $$= Longlong_hybrid(my_strtoll10($2.str, (char**) 0, &error), false); > + } > + | opt_plus ULONGLONG_NUM > + { > + int error; > + $$= Longlong_hybrid(my_strtoll10($2.str, (char**) 0, &error), true); > + } > + | '-' NUM > + { > + int error; > + $$= Longlong_hybrid(- my_strtoll10($2.str, (char**) 0, &error), false); > + } > + | '-' LONG_NUM > + { > + int error; > + $$= Longlong_hybrid(- my_strtoll10($2.str, (char**) 0, &error), false); > + } > + | '-' ULONGLONG_NUM > + { > + int error; > + const ulonglong abs= my_strtoll10($2.str, (char**) 0, &error); > + if (abs == 1 + (ulonglong) LONGLONG_MAX) > + $$= Longlong_hybrid(LONGLONG_MIN, false); > + else > + thd->parse_error(ER_DATA_OUT_OF_RANGE); > + } > + ; > + > +// For sequence metadata values that may be unsigned and need truncation (maxvalue, minvalue) > +sequence_truncated_value_hybrid_num: > + opt_plus NUM > + { > + int error; > + $$= Longlong_hybrid(my_strtoll10($2.str, (char**) 0, &error), false); > + } > + | opt_plus LONG_NUM > + { > + int error; > + $$= Longlong_hybrid(my_strtoll10($2.str, (char**) 0, &error), false); > + } > + | opt_plus ULONGLONG_NUM > + { > + int error; > + $$= Longlong_hybrid(my_strtoll10($2.str, (char**) 0, &error), true); > + } > + | opt_plus DECIMAL_NUM { $$= Longlong_hybrid(ULONGLONG_MAX, true); } > + | '-' NUM > + { > + int error; > + $$= Longlong_hybrid(- my_strtoll10($2.str, (char**) 0, &error), false); > + } > + | '-' LONG_NUM > + { > + int error; > + $$= Longlong_hybrid(- my_strtoll10($2.str, (char**) 0, &error), false); > + } > + | '-' ULONGLONG_NUM { $$= Longlong_hybrid(LONGLONG_MIN, false); } > + | '-' DECIMAL_NUM { $$= Longlong_hybrid(LONGLONG_MIN, false); } > ; > > ulonglong_num:

3 6

Re: [Maria-developers] 8c1ad2a9fe9: MDEV-30633 DATETIME to TIMESTAMP conversion to return maximum timestamp on overflow
by Sergei Golubchik 08 Mar '23

08 Mar '23

Hi, Alexander, please add tests with fractional seconds. remember, that in UTC the max timestamp(2) is 2038-01-19 03:14:07.99 and max timestamp(6) is 2038-01-19 03:14:07.999999 On Mar 08, Alexander Barkov wrote: > revision-id: 8c1ad2a9fe9 (mariadb-10.11.1-48-g8c1ad2a9fe9) > parent(s): ce4a289f1c3 > author: Alexander Barkov > committer: Alexander Barkov > timestamp: 2023-02-13 17:25:18 +0400 > message: > > MDEV-30633 DATETIME to TIMESTAMP conversion to return maximum timestamp on overflow a bit more verbose description would be nice > diff --git a/mysql-test/main/events_bugs.result b/mysql-test/main/events_bugs.result > --- a/mysql-test/main/events_bugs.result > +++ b/mysql-test/main/events_bugs.result > @@ -35,12 +35,34 @@ SET NAMES latin1; > set @a=3; > CREATE PROCEDURE p_16 () CREATE EVENT e_16 ON SCHEDULE EVERY @a SECOND DO SET @a=5; > ERROR HY000: Recursion of EVENT DDL statements is forbidden when body is present > +SET time_zone='+00:00'; > +SET timestamp=UNIX_TIMESTAMP('2023-02-13 00:00:00'); > create event e_55 on schedule at 99990101000000 do drop table t; > -ERROR HY000: Incorrect AT value: '99990101000000' Hmm, why did you not keep it an error? > +Warnings: > +Warning 1292 Truncated incorrect timestamp value: '9999-01-01 00:00:00' > +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. > diff --git a/mysql-test/main/func_time.result b/mysql-test/main/func_time.result > --- a/mysql-test/main/func_time.result > +++ b/mysql-test/main/func_time.result > @@ -599,19 +599,25 @@ Warnings: > Warning 1292 Truncated incorrect unixtime value: '2147483648' > select unix_timestamp('2039-01-20 01:00:00'); > unix_timestamp('2039-01-20 01:00:00') > -NULL > +2147483647 > +Warnings: > +Warning 1292 Truncated incorrect timestamp value: '2039-01-20 01:00:00' it's consistent with MariaDB [test]> select cast(1e30 as int); +---------------------+ | cast(1e30 as int) | +---------------------+ | 9223372036854775807 | +---------------------+ 1 row in set, 1 warning (0.000 sec) Note (Code 1916): Got overflow when converting '1e30' to SIGNED BIGINT. Value truncated good. Except that it's Note vs Warning. Want to unify this somehow? > select unix_timestamp('1968-01-20 01:00:00'); > unix_timestamp('1968-01-20 01:00:00') > NULL isn't it strange? That overflow is capped to max value, while underflow is NULL? > diff --git a/mysql-test/main/select.result b/mysql-test/main/select.result > --- a/mysql-test/main/select.result > +++ b/mysql-test/main/select.result > @@ -3766,11 +3766,15 @@ AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31"; > id select_type table type possible_keys key key_len ref rows Extra > 1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 > 1 SIMPLE t1 range ts ts 4 NULL 2 Using index condition; Using where > +Warnings: > +Warning 1292 Truncated incorrect timestamp value: '2999-12-31 00:00:00' Looks questionable. The query compares timestamp with a datetime. I think in this case timestamp should be casted to a datetime (so, no warning), not vice versa. > SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30 > AND t1.ts BETWEEN t2.dt1 AND t2.dt2 > AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31"; > a ts a dt1 dt2 > 30 2006-01-03 23:00:00 30 2006-01-01 00:00:00 2999-12-31 00:00:00 > +Warnings: > +Warning 1292 Truncated incorrect timestamp value: '2999-12-31 00:00:00' > DROP TABLE t1,t2; > create table t1 (a bigint unsigned); > insert into t1 values > diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc > --- a/sql/item_timefunc.cc > +++ b/sql/item_timefunc.cc > @@ -2793,11 +2793,17 @@ bool Item_func_convert_tz::get_date(THD *thd, MYSQL_TIME *ltime, > return true; > > { > - uint not_used; > - my_time_tmp= from_tz->TIME_to_gmt_sec(ltime, &not_used); > + uint error_code; > + my_time_tmp= from_tz->TIME_to_gmt_sec(ltime, &error_code); > ulong sec_part= ltime->second_part; > - /* my_time_tmp is guaranteed to be in the allowed range */ > - if (my_time_tmp) > + /* > + my_time_tmp is guaranteed to be in the allowed range. > + Don't perform the conversion in case the source DATETIME was above > + TIMESTAMP_MAX_VALUE (and was truncated to TIMESTAMP_MAX_VALUE). why not? > + */ > + if (my_time_tmp && > + (my_time_tmp != TIMESTAMP_MAX_VALUE || > + error_code != ER_WARN_DATA_OUT_OF_RANGE)) why `my_time_tmp != TIMESTAMP_MAX_VALUE` ? shouldn't error_code check be sufficient? > to_tz->gmt_sec_to_TIME(ltime, my_time_tmp); > /* we rely on the fact that no timezone conversion can change sec_part */ > ltime->second_part= sec_part; Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

1 0

Re: [Maria-developers] 6e28a576913: move alloca() definition from all *.h files to one place
by Sergei Golubchik 10 Feb '23

10 Feb '23

Hi, Julius, this is the combined diff of both commits: git diff e1ce867e5ab0c^^ e1ce867e5ab0c On Feb 10, Julius Goryavsky wrote: > diff --git a/include/CMakeLists.txt b/include/CMakeLists.txt > index a7b98a11050..7d87c4a42b3 100644 > --- a/include/CMakeLists.txt > +++ b/include/CMakeLists.txt > @@ -104,6 +105,7 @@ ENDMACRO() > > INSTALL_COMPAT_HEADER(my_global.h "") > INSTALL_COMPAT_HEADER(my_config.h "") > +INSTALL_COMPAT_HEADER(my_alloca.h "") I wouldn't use INSTALL_COMPAT_HEADER for my_alloca.h > INSTALL_COMPAT_HEADER(my_sys.h "") > INSTALL_COMPAT_HEADER(mysql_version.h " > #include <mariadb_version.h> > diff --git a/include/my_alloca.h b/include/my_alloca.h > new file mode 100644 > index 00000000000..fec0320195e > --- /dev/null > +++ b/include/my_alloca.h > @@ -0,0 +1,37 @@ > +/* Copyright (c) 2023, MariaDB Corporation. > + > + This program is free software; you can redistribute it and/or modify > + it under the terms of the GNU General Public License as published by > + the Free Software Foundation; version 2 of the License. > + > + This program is distributed in the hope that it will be useful, > + but WITHOUT ANY WARRANTY; without even the implied warranty of > + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the > + GNU General Public License for more details. > + > + You should have received a copy of the GNU General Public License > + along with this program; if not, write to the Free Software > + Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1335 USA */ > + > +#ifndef MY_ALLOCA_INCLUDED > +#define MY_ALLOCA_INCLUDED > + > +#ifdef _WIN32 > +#include <malloc.h> /*for alloca*/ > +#ifndef alloca When can alloca be defined? > +#define alloca _alloca > +#endif > +#else > +#include <stdlib.h> why stdlib.h? > +#ifdef HAVE_ALLOCA_H > +#include <alloca.h> > +#endif > +#endif > + > +#if defined(HAVE_ALLOCA) > +#if defined(__GNUC__) && !defined(HAVE_ALLOCA_H) && !defined(alloca) add a comment describing your MinGW use case when this #if is needed > +#define alloca __builtin_alloca > +#endif /* GNUC */ > +#endif > + > +#endif /* MY_ALLOCA_INCLUDED */ > diff --git a/include/mysql/service_encryption.h b/include/mysql/service_encryption.h > index 69d205a27e8..cab1418e1d6 100644 > --- a/include/mysql/service_encryption.h > +++ b/include/mysql/service_encryption.h > @@ -24,21 +24,19 @@ > *provider* (encryption plugin). > */ > > +#ifndef MYSQL_ABI_CHECK > +#include <my_alloca.h> > +#endif > + > #ifdef __cplusplus > extern "C" { > #endif > > #ifndef MYSQL_ABI_CHECK why not to put #include <my_alloca.h> here? > #ifdef _WIN32 > -#include <malloc.h> > #ifndef __cplusplus > #define inline __inline > #endif > -#else > -#include <stdlib.h> I suspect stdlib.h belongs here, not in my_alloca.h > -#ifdef HAVE_ALLOCA_H > -#include <alloca.h> > -#endif > #endif > #endif Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

1 0

Re: [Maria-developers] ceb75e9bd9e: MDEV-30203: Move mysql symlinks to different package (fixes)
by Sergei Golubchik 10 Feb '23

10 Feb '23

Hi, Daniel, On Feb 09, Daniel Black wrote: > revision-id: ceb75e9bd9e (mariadb-10.11.1-152-gceb75e9bd9e) > parent(s): 526fce28b90 > author: Daniel Black > committer: Daniel Black > timestamp: 2023-02-09 12:20:57 +1100 > message: > > MDEV-30203: Move mysql symlinks to different package (fixes) > > Fixes MDEV-30571 MariaDB-client does not pull MariaDB-compat anymore, conflicts with RHEL packages > > Fixes MDEV-30574 MariaDB-server conflicts with MariaDB-common from previous versions > > MariaDB-Server now depends on MariaDB-common 11.0+. > > MariaDB-{Client,Server} append to the Requirements rather than overriding them. > > MariaDB-Server package now depends on the MariaDB-client 11.0+. > > Because of moves between packages MariaDB-Client conflicts with > MariaDB-server < 11.0.0. > > diff --git a/cmake/cpack_rpm.cmake b/cmake/cpack_rpm.cmake > index 638f6b2dabb..e88a5493eac 100644 > --- a/cmake/cpack_rpm.cmake > +++ b/cmake/cpack_rpm.cmake > @@ -189,7 +189,7 @@ FOREACH(SYM_COMPONENT Server Client) > SET(CPACK_RPM_${SYM}_PACKAGE_ARCHITECTURE "noarch") > SET(CPACK_RPM_${SYM}_USER_FILELIST ${ignored}) > STRING(TOLOWER ${SYM_COMPONENT} SYM_COMPONENT_LOWER) > - SET(CPACK_RPM_${SYM}_PACKAGE_REQUIRES "MariaDB-${SYM_COMPONENT_LOWER} >= 11.0.0") > + SETA(CPACK_RPM_${SYM}_PACKAGE_REQUIRES "MariaDB-${SYM_COMPONENT_LOWER} >= 11.0.0") why? > SETA(CPACK_RPM_${SYM_COMPONENT_LOWER}_PACKAGE_RECOMMENDS "MariaDB-${SYM}") > ENDFOREACH() > > @@ -205,10 +205,10 @@ SETA(CPACK_RPM_client_PACKAGE_PROVIDES > "mysql-client" > "mytop") > SETA(CPACK_RPM_client_PACKAGE_CONFLICTS > - "MariaDB-server < 10.6.0") > + "MariaDB-server < 11.0.0") because of resolve-stack-dump, ok > SETA(CPACK_RPM_common_PACKAGE_CONFLICTS > - "MariaDB-server < 10.6.1") > + "MariaDB-server < 11.0.0") why? > SETA(CPACK_RPM_devel_PACKAGE_OBSOLETES > "MySQL-devel") Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

2 1

[Maria-developers] MDEV-30218: On what should be passed as 'rows' to filtering code.
by Sergey Petrunia 01 Feb '23

01 Feb '23

Hello Igor, So I'm looking at the bb-10.4-release-igor tree with your latest patch: commit c4fa6c3c4eaacefd9bf50b5c88c2c72474a15bc5 (HEAD -> bb-10.4-release-igor, origin/bb-10.4-release-igor) Author: Igor Babaev <igor(a)askmonty.org> Date: Tue Jan 31 13:14:53 2023 -0800 MDEV-30218 Incorrect optimization for rowid_filtering Correction over the last patch for this MDEV. I modify the code to add a printout: diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 4bdfb659513..2cd4495bc03 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -7965,6 +7965,8 @@ best_access_path(JOIN *join, table->best_range_rowid_filter_for_partial_join(start_key->key, rows, access_cost_factor); + trace_access_idx.add("ROWS_FOR_FILTER", rows); + trace_access_idx.add("REAL_ROWS_FOR_FILTER", tmp); if (filter) { tmp-= filter->get_adjusted_gain(rows) - filter->get_cmp_gain(rows); then, I'm trying this example: create table t1 (a int, b int); insert into t1 select seq, seq from seq_1_to_10000; create table t2 (a int, key(a)); insert into t2 select seq from seq_1_to_10000; insert into t2 select * from t2; insert into t2 select * from t2; analyze table t2 persistent for all; create table t3 ( pk int primary key, a int, b int, unique key(a), key(b) ); insert into t3 select seq, seq, seq from seq_1_to_100000; set optimizer_trace=1; explain select straight_join * from t1, t2, t3 where t2.a=t1.a and t3.a=t1.b and t3.b < 5000; +------+-------------+-------+---------------+---------------+------+---------+---------+--------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+---------------+---------------+------+---------+---------+--------+---------------------------------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 10157 | Using where | | 1 | SIMPLE | t2 | ref | a | a | 5 | j1.t1.a | 4 | Using index | | 1 | SIMPLE | t3 | eq_ref|filter | a,b | a|b | 5|5 | j1.t1.b | 1 (5%) | Using where; Using rowid filter | +------+-------------+-------+---------------+---------------+------+---------+---------+--------+---------------------------------+ good so far. select * from information_schema.optimizer_trace; shows this (note the added printouts in caps): { "plan_prefix": ["t1", "t2"], "table": "t3", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "a", "ROWS_FOR_FILTER": 40628, "REAL_ROWS_FOR_FILTER": 10157, "rowid_filter_key": "b", "rows": 1, "cost": 9498, "chosen": true }, Let's run the query and check what really happens: ANALYZE FORMAT=JSON select straight_join * from t1, t2, t3 where t2.a=t1.a and t3.a=t1.b and t3.b < 5000; shows: ... "table": { "table_name": "t3", "access_type": "eq_ref", "key": "a", ... "rowid_filter": { ... "r_lookups": 10000, ... }, "r_loops": 40000, ... So, ROWS_FOR_FILTER=40K REAL_ROWS_FOR_FILTER=10K which of the numbers is a closer match here for the observed value of r_lookups= 10K ? BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net

1 0

[Maria-developers] Unable to reset my mariadb root password.
by sanaboyina sarvani 01 Feb '23

01 Feb '23

Hello, I have just installed mariadb in my macos system. After installation it asks for the root password which I have not set yet. Tried to reset the root password but that did not work. So, how to know my mariadb root password? Is it possible to reset the password? Thank you. Regards

1 0

[Maria-developers] Optimizer call followup: negative gains from rowid filters
by Sergey Petrunia 31 Jan '23

31 Jan '23

Hello, Just taking notes from the optimizer call: Check out this branch: 10.4-mdev30218-print-negative-gain (git log -3 output below) It has: - 10.4-release contents - then mdev30218_bb-10.4-release.diff patch provided by Igor - then a debug patch by me. I run: ./mtr --mem --parallel=5 --force main/rowid_filter*test grep -rn filter_gain var/ I get this: negative gains from filters: var/4/log/mysqld.1.err:52:filter_gain:-0.0569276, query=select 1 var/4/log/mysqld.1.err:58:filter_gain:-0.0569276, query=explain extended select 1 var/4/log/mysqld.1.err:64:filter_gain:-0.250816, query=SELECT * FROM t1 HAVING (7, 9) IN (SELECT t2.i1, t2.i2 FROM t2 WHERE t2.i1 = 3) var/4/log/mysqld.1.err:65:filter_gain:-0.250816, query=EXPLAIN EXTENDED var/4/log/mysqld.1.err:67:filter_gain:-10.207, query=explain var/4/log/mysqld.1.err:69:filter_gain:-10.207, query=SELECT * FROM t1 WHERE a > 0 AND b=0 var/4/log/mysqld.1.err:70:filter_gain:-1.17502, query=select count(*) from t1 where a between 21 and 30 and b=2 var/4/log/mysqld.1.err:71:filter_gain:-1.17502, query=explain extended select count(*) from t1 where a between 21 and 30 and b=2 var/4/log/mysqld.1.err:72:filter_gain:-1.17502, query=select * from t1 where a between 21 and 30 and b=2 var/4/log/mysqld.1.err:73:filter_gain:-2.18259, query=SELECT * FROM t1 var/4/log/mysqld.1.err:77:filter_gain:-2.18259, query=EXPLAIN EXTENDED SELECT * FROM t1 var/4/log/mysqld.1.err:81:filter_gain:-0.151308, query=set statement optimizer_switch='rowid_filter=on' for explain extended select t.id, fi.* var/4/log/mysqld.1.err:90:filter_gain:-0.151308, query=set statement optimizer_switch='rowid_filter=on' for select t.id, fi.* var/4/log/mysqld.1.err:99:filter_gain:-0.151308, query=set statement optimizer_switch='rowid_filter=on' for explain extended select t.id, fi.* var/4/log/mysqld.1.err:108:filter_gain:-0.151308, query=set statement optimizer_switch='rowid_filter=on' for select t.id, fi.* var/4/log/mysqld.1.err:117:filter_gain:-0.151308, query=set statement optimizer_switch='rowid_filter=on' for analyze format=json select t.id, fi.* var/3/log/mysqld.1.err:51:filter_gain:-0.0569276, query=select 1 var/3/log/mysqld.1.err:57:filter_gain:-0.0569276, query=explain extended select 1 var/3/log/mysqld.1.err:63:filter_gain:-0.250816, query=SELECT * FROM t1 HAVING (7, 9) IN (SELECT t2.i1, t2.i2 FROM t2 WHERE t2.i1 = 3) var/3/log/mysqld.1.err:64:filter_gain:-0.250816, query=EXPLAIN EXTENDED var/3/log/mysqld.1.err:66:filter_gain:-12.7213, query=explain var/3/log/mysqld.1.err:68:filter_gain:-12.7213, query=SELECT * FROM t1 WHERE a > 0 AND b=0 === git log -3 output === commit 82d5977b9fd6be5f5152d6831cd744fc739a2f24 (HEAD -> 10.4-mdev30218-print-negative-gain, origin/10.4-mdev30218-print-negative-gain, bb-10.4-release) Author: Sergei Petrunia <sergey(a)mariadb.com> Date: Tue Jan 31 20:11:17 2023 +0300 Debug patch: print filter_gain if it is less than 0. commit 47a40d90627dcf3c870cecd33c345c2e224986af Author: Sergei Petrunia <sergey(a)mariadb.com> Date: Tue Jan 31 20:07:02 2023 +0300 Apply mdev30218_bb-10.4-release.diff commit c8f2e9a5c0ac5905f28b050b7df5a9ffd914b7e7 (origin/bb-10.4-release) Author: Oleksandr Byelkin <sanja(a)mariadb.com> Date: Mon Jan 30 11:49:42 2023 +0100 Fix number of rows passing in case of EQ_REF BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net

1 0

Re: [Maria-developers] dcd69cbd19a: MDEV-29447 MDEV-26285 Refactor spider_db_mbase_util::open_item_func
by Sergei Golubchik 30 Jan '23

30 Jan '23

Hi, Yuchen, Thanks! There are two kinds of comments below, you'll see which is which. I realize that this patch mainly just moves parts of spider code around. Some of my comments apply to the new code that didn't exist before and appeared only in this patch. It's what needs to be fixed (or discussed, if you disagree) before pushing. Other comments apply to the old spider code that this patch moved. If you'd like you can say "I'm just moving spider code around" and ignore all comments of that kind. /Sergei On Jan 22, Yuchen Pei wrote: > commit dcd69cbd19a > Author: Yuchen Pei <yuchen.pei(a)mariadb.com> > Date: Tue Jan 3 16:24:04 2023 +1100 > > MDEV-29447 MDEV-26285 Refactor spider_db_mbase_util::open_item_func > > Porting commit 3836098c29ef1b7ff9d5fbde99b690eab73a0df1 (MDEV-26285) Because 3836098c29ef will not be, apparently, pushed anywhere, you'd better put a self-containing comment here. May be use the comment of 3836098c29ef? Note that you shouldn't push your 10.4-11.0 versions of the patch directly, only push the 10.3 one (when ok) and let it propagate to 11.0 through merges. Your 10.4-11.0 patches are very useful, though, they will serve as guidelines for whoever will merge this change up. > to current versions 10.3+ to fix a problem (MDEV-29447) where field > items that are arguments of a func item may be used before created / > initialised. > > Signed-off-by: Yuchen Pei <yuchen.pei(a)mariadb.com> > > diff --git a/storage/spider/spd_db_mysql.cc b/storage/spider/spd_db_mysql.cc > index e942d1d9063..a886a4e6e99 100644 > --- a/storage/spider/spd_db_mysql.cc > +++ b/storage/spider/spd_db_mysql.cc > @@ -4035,6 +4042,170 @@ int spider_db_mbase_util::open_item_func( > uint alias_length, > bool use_fields, > spider_fields *fields > +) { > + DBUG_ENTER("spider_db_mbase_util::check_item_func"); incorrect function name in DBUG_ENTER > + > + int error = check_item_func(item_func, spider, alias, > + alias_length, use_fields, fields); > + if (error) > + DBUG_RETURN(error); > + if (!str) > + DBUG_RETURN(0); > + > + DBUG_RETURN(print_item_func(item_func, spider, str, alias, > + alias_length, use_fields, fields)); > +} > + > +namespace { > + bool item_func_is_timestampdiff( We generally use static in these cases. Why would an anonymous namespace be better? > + const char *func_name, > + int func_name_length > + ) { > + return func_name_length == 13 && > + !strncasecmp("timestampdiff", func_name, func_name_length); We build with rtti nowadays, so you can use dynamic_cast instead. and you won't need func_name and func_name_length. Ideally you won't need a check for timestampdiff at all > + } > + > + bool not_func_should_be_skipped( > + Item_func *item_func > + ){ > + DBUG_ENTER("not_func_should_be_skipped"); add DBUG_ASSERT(item_func->functype() == Item_func::NOT_FUNC); > + Item **item_list = item_func->arguments(); > + > + if (item_list[0]->type() == Item::COND_ITEM) > + { > + DBUG_PRINT("info",("spider item_list[0] is COND_ITEM")); > + Item_cond *item_cond = (Item_cond *) item_list[0]; > + if (item_cond->functype() == Item_func::COND_AND_FUNC) > + { > + DBUG_PRINT("info",("spider item_cond is COND_AND_FUNC")); > + List_iterator_fast<Item> lif(*(item_cond->argument_list())); > + bool has_expr_cache_item = FALSE; > + bool has_isnotnull_func = FALSE; > + bool has_other_item = FALSE; > + while(Item *item = lif++) > + { > + if ( > + item->type() == Item::EXPR_CACHE_ITEM > + ) { > + DBUG_PRINT("info",("spider EXPR_CACHE_ITEM")); > + has_expr_cache_item = TRUE; > + } else > + if ( > + item->type() == Item::FUNC_ITEM && > + ((Item_func *) item)->functype() == Item_func::ISNOTNULL_FUNC > + ) { > + DBUG_PRINT("info",("spider ISNOTNULL_FUNC")); > + has_isnotnull_func = TRUE; > + } else { > + DBUG_PRINT("info",("spider has other item")); > + DBUG_PRINT("info",("spider COND type=%d", item->type())); > + has_other_item = TRUE; > + } > + } > + if (has_expr_cache_item && has_isnotnull_func && !has_other_item) So, "not func should be skipped" if it's NOT (expr1 AND expr2 AND ...) and all expr's are either IS NOT NULL or a cached subquery. Why? IS NULL, IS NOT FALSE, non-cached subqueries, everything else is fine, IS NOT NULL if it is followed by `AND 2>1` is fine, OR is fine. Doesn't make much sense to me. > + { > + DBUG_PRINT("info",("spider NOT EXISTS skip")); > + DBUG_RETURN(TRUE); > + } > + } > + } > + DBUG_RETURN(FALSE); > + } > +} > + > +/** > + Check if the given item_func and its arguments can be pushed down to > + a data node. This function is recursive because we need to also check > + the arguments of the item_func. > + > + @return 0: success, otherwise: error > + */ > +int spider_db_mbase_util::check_item_func( > + Item_func *item_func, > + ha_spider *spider, > + const char *alias, > + uint alias_length, > + bool use_fields, > + spider_fields *fields > +) { > + DBUG_ENTER("spider_db_mbase_util::check_item_func"); > + > + Item_func::Functype func_type = item_func->functype(); > + DBUG_PRINT("info",("spider functype = %d", func_type)); > + > + const char *func_name = (char*) item_func->func_name(); > + int func_name_length = strlen(func_name); > + DBUG_PRINT("info",("spider func_name = %s", func_name)); > + > + /* The blacklist of the functions that cannot be pushed down */ > + if ( > + func_type == Item_func::TRIG_COND_FUNC || > + func_type == Item_func::CASE_SEARCHED_FUNC || > + func_type == Item_func::CASE_SIMPLE_FUNC Does CASE need to be skipped at all? > + ) { > + DBUG_RETURN(ER_SPIDER_COND_SKIP_NUM); > + } > + > + if (func_type == Item_func::NOT_FUNC) > + { > + /* Why the following check is necessary? */ indeed > + if(not_func_should_be_skipped(item_func)) > + DBUG_RETURN(ER_SPIDER_COND_SKIP_NUM); > + } > + > + if (func_type == Item_func::UDF_FUNC) old code used switch here. I think it looks more natural than a sequence of if's. A compiler might optimize them internally into a switch, but I'd better do it explicitly. > + { > + int use_pushdown_udf = spider_param_use_pushdown_udf( > + spider->trx->thd, spider->share->use_pushdown_udf); > + if (!use_pushdown_udf) { > + DBUG_RETURN(ER_SPIDER_COND_SKIP_NUM); > + } > + } > + > + if (func_type == Item_func::FT_FUNC) { > + if (spider_db_check_ft_idx(item_func, spider) == MAX_KEY) > + DBUG_RETURN(ER_SPIDER_COND_SKIP_NUM); > + } > + > + if (func_type == Item_func::UNKNOWN_FUNC) > + { > + if (item_func_is_timestampdiff(func_name, func_name_length)) { it doesn't seem that it needs to be skipped at all > + DBUG_RETURN(ER_SPIDER_COND_SKIP_NUM); > + } > + } > + /* End of the blacklist */ > + > + /* Check the arguments recursively */ > + if (uint item_count = item_func->argument_count()) > + { > + Item **item_list = item_func->arguments(); > + for (uint roop_count = 0; roop_count < item_count; roop_count++) > + { > + Item *item = item_list[roop_count]; > + if (int error_num = spider_db_print_item_type(item, NULL, spider, NULL, > + alias, alias_length, dbton_id, use_fields, fields)) it'd be more logical to invoke check_item_func() here instead of spider_db_print_item_type with str==NULL > + DBUG_RETURN(error_num); > + } > + } > + > + DBUG_RETURN(0); > +} > + > +/** > + The function print the string corresponding to the given item_func to str. > + The function is assumed to be called only when the check by the function > + check_item_func() has passed. > + > + @return 0: success, otherwise: error > + */ > +int spider_db_mbase_util::print_item_func( > + Item_func *item_func, > + ha_spider *spider, > + spider_string *str, > + const char *alias, > + uint alias_length, > + bool use_fields, > + spider_fields *fields > ) { > int error_num; > Item *item, **item_list = item_func->arguments(); > @@ -4049,13 +4220,14 @@ int spider_db_mbase_util::open_item_func( > last_str_length = SPIDER_SQL_NULL_CHAR_LEN; > int use_pushdown_udf; > bool merge_func = FALSE; > - DBUG_ENTER("spider_db_mbase_util::open_item_func"); > - if (str) > - { > + DBUG_ENTER("spider_db_mbase_util::print_item_func"); > + DBUG_ASSERT(!check_item_func(item_func, spider, alias, alias_length, > + use_fields, fields) && str); Make it two asserts please. One for !check_item_func(...) and the other DBUG_ASSERT(str); Generally, always try to split assert into as many separate asserts as possible, don't do assert(A && B); So that when it fails it'd provide as much information as possible about what exactly went wrong. > + > if (str->reserve(SPIDER_SQL_OPEN_PAREN_LEN)) > DBUG_RETURN(HA_ERR_OUT_OF_MEM); > str->q_append(SPIDER_SQL_OPEN_PAREN_STR, SPIDER_SQL_OPEN_PAREN_LEN); > - } > + > DBUG_PRINT("info",("spider functype = %d", item_func->functype())); > switch (item_func->functype()) > { > @@ -4374,94 +4523,12 @@ int spider_db_mbase_util::open_item_func( > { > if (!strncasecmp("utc_timestamp", func_name, func_name_length)) pretty much all that code can use dynamic_cast nowadays > { > - if (str) > str->length(str->length() - SPIDER_SQL_OPEN_PAREN_LEN); > DBUG_RETURN(spider_db_open_item_string(item_func, NULL, spider, str, > alias, alias_length, dbton_id, use_fields, fields)); > } else if (!strncasecmp("timestampdiff", func_name, func_name_length)) > { > -#ifdef ITEM_FUNC_TIMESTAMPDIFF_ARE_PUBLIC but it _is_ public, so you can push down Item_func_timestamp_diff all right > - Item_func_timestamp_diff *item_func_timestamp_diff = > - (Item_func_timestamp_diff *) item_func; > - if (str) > - { > - const char *interval_str; > - uint interval_len; > - switch (item_func_timestamp_diff->int_type) > - { > - case INTERVAL_YEAR: > - interval_str = SPIDER_SQL_YEAR_STR; > - interval_len = SPIDER_SQL_YEAR_LEN; > - break; > - case INTERVAL_QUARTER: > - interval_str = SPIDER_SQL_QUARTER_STR; > - interval_len = SPIDER_SQL_QUARTER_LEN; > - break; > - case INTERVAL_MONTH: > - interval_str = SPIDER_SQL_MONTH_STR; > - interval_len = SPIDER_SQL_MONTH_LEN; > - break; > - case INTERVAL_WEEK: > - interval_str = SPIDER_SQL_WEEK_STR; > - interval_len = SPIDER_SQL_WEEK_LEN; > - break; > - case INTERVAL_DAY: > - interval_str = SPIDER_SQL_DAY_STR; > - interval_len = SPIDER_SQL_DAY_LEN; > - break; > - case INTERVAL_HOUR: > - interval_str = SPIDER_SQL_HOUR_STR; > - interval_len = SPIDER_SQL_HOUR_LEN; > - break; > - case INTERVAL_MINUTE: > - interval_str = SPIDER_SQL_MINUTE_STR; > - interval_len = SPIDER_SQL_MINUTE_LEN; > - break; > - case INTERVAL_SECOND: > - interval_str = SPIDER_SQL_SECOND_STR; > - interval_len = SPIDER_SQL_SECOND_LEN; > - break; > - case INTERVAL_MICROSECOND: > - interval_str = SPIDER_SQL_MICROSECOND_STR; > - interval_len = SPIDER_SQL_MICROSECOND_LEN; > - break; > - default: > - interval_str = ""; > - interval_len = 0; > - break; > - } > - str->length(str->length() - SPIDER_SQL_OPEN_PAREN_LEN); > - if (str->reserve(func_name_length + SPIDER_SQL_OPEN_PAREN_LEN + > - interval_len + SPIDER_SQL_COMMA_LEN)) > - DBUG_RETURN(HA_ERR_OUT_OF_MEM); > - str->q_append(func_name, func_name_length); > - str->q_append(SPIDER_SQL_OPEN_PAREN_STR, SPIDER_SQL_OPEN_PAREN_LEN); > - str->q_append(interval_str, interval_len); > - str->q_append(SPIDER_SQL_COMMA_STR, SPIDER_SQL_COMMA_LEN); > - } > - if ((error_num = spider_db_print_item_type(item_list[0], NULL, spider, > - str, alias, alias_length, dbton_id, use_fields, fields))) > - DBUG_RETURN(error_num); > - if (str) > - { > - if (str->reserve(SPIDER_SQL_COMMA_LEN)) > - DBUG_RETURN(HA_ERR_OUT_OF_MEM); > - str->q_append(SPIDER_SQL_COMMA_STR, SPIDER_SQL_COMMA_LEN); > - } > - if ((error_num = spider_db_print_item_type(item_list[1], NULL, spider, > - str, alias, alias_length, dbton_id, use_fields, fields))) > - DBUG_RETURN(error_num); > - if (str) > - { > - if (str->reserve(SPIDER_SQL_CLOSE_PAREN_LEN)) > - DBUG_RETURN(HA_ERR_OUT_OF_MEM); > - str->q_append(SPIDER_SQL_CLOSE_PAREN_STR, > - SPIDER_SQL_CLOSE_PAREN_LEN); > - } > - DBUG_RETURN(0); > -#else > DBUG_RETURN(ER_SPIDER_COND_SKIP_NUM); > -#endif > } > } else if (func_name_length == 14) > { > @@ -5038,84 +4996,17 @@ int spider_db_mbase_util::open_item_func( > func_name = (char*)item_func->func_name(); > func_name_length = strlen(func_name); > } > - } > break; > case Item_func::CASE_SEARCHED_FUNC: > case Item_func::CASE_SIMPLE_FUNC: > -#ifdef ITEM_FUNC_CASE_PARAMS_ARE_PUBLIC Item_func_case looks somewhat different today, but I suspect it can be pushed too > - Item_func_case *item_func_case = (Item_func_case *) item_func; > - if (str) > - { > - if (str->reserve(SPIDER_SQL_CASE_LEN)) > - DBUG_RETURN(HA_ERR_OUT_OF_MEM); > - str->q_append(SPIDER_SQL_CASE_STR, SPIDER_SQL_CASE_LEN); > - } > - if (item_func_case->first_expr_num != -1) > - { > - if ((error_num = spider_db_print_item_type( > - item_list[item_func_case->first_expr_num], NULL, spider, str, > - alias, alias_length, dbton_id, use_fields, fields))) > - DBUG_RETURN(error_num); > - } > - for (roop_count = 0; roop_count < item_func_case->ncases; > - roop_count += 2) > - { > - if (str) > - { > - if (str->reserve(SPIDER_SQL_WHEN_LEN)) > - DBUG_RETURN(HA_ERR_OUT_OF_MEM); > - str->q_append(SPIDER_SQL_WHEN_STR, SPIDER_SQL_WHEN_LEN); > - } > - if ((error_num = spider_db_print_item_type( > - item_list[roop_count], NULL, spider, str, > - alias, alias_length, dbton_id, use_fields, fields))) > - DBUG_RETURN(error_num); > - if (str) > - { > - if (str->reserve(SPIDER_SQL_THEN_LEN)) > - DBUG_RETURN(HA_ERR_OUT_OF_MEM); > - str->q_append(SPIDER_SQL_THEN_STR, SPIDER_SQL_THEN_LEN); > - } > - if ((error_num = spider_db_print_item_type( > - item_list[roop_count + 1], NULL, spider, str, > - alias, alias_length, dbton_id, use_fields, fields))) > - DBUG_RETURN(error_num); > - } > - if (item_func_case->else_expr_num != -1) > - { > - if (str) > - { > - if (str->reserve(SPIDER_SQL_ELSE_LEN)) > - DBUG_RETURN(HA_ERR_OUT_OF_MEM); > - str->q_append(SPIDER_SQL_ELSE_STR, SPIDER_SQL_ELSE_LEN); > - } > - if ((error_num = spider_db_print_item_type( > - item_list[item_func_case->else_expr_num], NULL, spider, str, > - alias, alias_length, dbton_id, use_fields, fields))) > - DBUG_RETURN(error_num); > - } > - if (str) > - { > - if (str->reserve(SPIDER_SQL_END_LEN + SPIDER_SQL_CLOSE_PAREN_LEN)) > - DBUG_RETURN(HA_ERR_OUT_OF_MEM); > - str->q_append(SPIDER_SQL_END_STR, SPIDER_SQL_END_LEN); > - str->q_append(SPIDER_SQL_CLOSE_PAREN_STR, > - SPIDER_SQL_CLOSE_PAREN_LEN); > - } > - DBUG_RETURN(0); > -#else > DBUG_RETURN(ER_SPIDER_COND_SKIP_NUM); > -#endif > case Item_func::JSON_EXTRACT_FUNC: > func_name = (char*) item_func->func_name(); > func_name_length = strlen(func_name); > - if (str) > - { > if (str->reserve(func_name_length + SPIDER_SQL_OPEN_PAREN_LEN)) > DBUG_RETURN(HA_ERR_OUT_OF_MEM); > str->q_append(func_name, func_name_length); > str->q_append(SPIDER_SQL_OPEN_PAREN_STR, SPIDER_SQL_OPEN_PAREN_LEN); > - } > func_name = SPIDER_SQL_COMMA_STR; > func_name_length = SPIDER_SQL_COMMA_LEN; > separator_str = SPIDER_SQL_COMMA_STR; Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

2 1

[Maria-developers] To getting started for contribution at your GitHub repo
by Omkar shivaji Devgirikar 29 Jan '23

29 Jan '23

Respect sir, I Omkar Devgirikar , I am a student and as a beginner i want to start contributing in your server repo with C++ language . Can you please guide me with your project like resources for this project, how get started and some good first issues. Please look at my mail seriously and guide me please. I hope you will look at it. Thank You

2 2

Re: [Maria-developers] 06ce67c644b: MDEV-27653 long uniques don't work with unicode collations
by Sergei Golubchik 16 Jan '23

16 Jan '23

Hi, Alexander, On Jan 15, Alexander Barkov wrote: > revision-id: 06ce67c644b (mariadb-10.4.27-41-g06ce67c644b) > parent(s): 6cb84346e1b > author: Alexander Barkov > committer: Alexander Barkov > timestamp: 2023-01-10 18:27:16 +0400 > message: > > MDEV-27653 long uniques don't work with unicode collations > > diff --git a/sql/sql_admin.cc b/sql/sql_admin.cc > index 213d77f8237..5dd19c877af 100644 > --- a/sql/sql_admin.cc > +++ b/sql/sql_admin.cc > @@ -772,11 +772,14 @@ static bool mysql_admin_table(THD* thd, TABLE_LIST* tables, > int check_for_upgrade= file->ha_check_for_upgrade(check_opt); > > if (check_old_types == HA_ADMIN_NEEDS_ALTER || > - check_for_upgrade == HA_ADMIN_NEEDS_ALTER) > + check_old_types == HA_ADMIN_NEEDS_UPGRADE || > + check_for_upgrade == HA_ADMIN_NEEDS_ALTER || > + check_for_upgrade == HA_ADMIN_NEEDS_UPGRADE) eh. So old code was returning HA_ADMIN_NEEDS_ALTER actually quite intentionally. REPAIR TABLE was automatically switching to ALTER if these checks were returning HA_ADMIN_NEEDS_ALTER. So it didn't matter if the message confusingly said "Please use REPAIR", because it would've been ALTER internally anyway. Meaning, I suspect, that the old code was fine and you didn't need to change `case HA_ADMIN_NEEDS_ALTER` and didn't need to replace `return HA_ADMIN_NEEDS_UPGRADE` with HA_ADMIN_NEEDS_ALTER. All you needed to do was to return HA_ADMIN_NEEDS_ALTER from the check_long_hash_compatibility() method and the rest would've likely worked automatically. Or at least with much smaller changes. > { > /* We use extra_open_options to be able to open crashed tables */ > thd->open_options|= extra_open_options; > - result_code= admin_recreate_table(thd, table); > + result_code= admin_recreate_table(thd, table) ? HA_ADMIN_FAILED : > + HA_ADMIN_OK; good catch > thd->open_options&= ~extra_open_options; > goto send_result; > } Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

2 1

Re: [Maria-developers] 0bef50e50b5: MDEV-20501: Assertion `maybe_null || !null_value' failed in Item_func_round::date_op
by Sergei Golubchik 13 Jan '23

13 Jan '23

Hi, Sergei, On Jan 13, Sergei Petrunia wrote: > revision-id: 0bef50e50b5 (mariadb-10.4.27-33-g0bef50e50b5) > parent(s): 5db970fc760 > author: Sergei Petrunia > committer: Sergei Petrunia > timestamp: 2023-01-04 16:50:12 +0300 > message: > > MDEV-20501: Assertion `maybe_null || !null_value' failed in Item_func_round::date_op > > When the optimizer finds a constant (or system) table $TBL which is empty > or has no matching row, it would set table->null_row=true. This is done > even for tables with table->maybe_null==0. > > Then, it would proceed to perform query optimization phases (what for?) > which will attempt to evaluate Item expressions referring to $TBL. > > Eventually some Item expression will get the value of $TBL.not_null_field, > get SQL NULL and fail an assertion. > > Fixed by not performing any query optimization steps after we've got > constant/empty tables with no matching rows. > > Test result changes contain a lot of changes like > - ... Impossible WHERE noticed after reading const tables > + ... no matching row in const table > > as well as other changes caused by slightly-different processing of > the special case of empty constant tables. > > diff --git a/sql/sql_select.cc b/sql/sql_select.cc > index 6a441c5047b..57ffd58f8b4 100644 > --- a/sql/sql_select.cc > +++ b/sql/sql_select.cc > @@ -2382,6 +2382,9 @@ int JOIN::optimize_stage2() > if (subq_exit_fl) > goto setup_subq_exit; > > + if (zero_result_cause) > + goto setup_subq_exit; In many other cases it's, like zero_result_cause= "No matching min/max row"; subq_exit_fl= true; that is, subq_exit_fl is set whenever zero_result_cause is set. Meaning, perhaps you should do the same in your new code below and then you wouldn't need those lines you've added above? > if (unlikely(thd->check_killed())) > DBUG_RETURN(1); > > @@ -5392,7 +5398,32 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, > } > } > } while (ref_changed); > - > + > + /* > + ConstRowNotFoundShortcut-1: > + Some constant/system tables have mo matching rows. This means that > + the join operation output will be empty. > + Short-cut further optimization steps. > + Note that some query plan steps will still be performed to handle > + implicit grouping, join result setup, etc. > + See also: ConstRowNotFoundShortcut-{2,3}. > + > + Do not do this if we're optimizing for some UNION's fake_select_lex. > + We might be running UglySubqueryReoptimization (grep for name) and > + in this case constant tables are not reliable. > + */ > + if ((join->const_table_map & ~found_const_table_map) && > + !(join->select_lex->master_unit() && > + join->select_lex->master_unit()->fake_select_lex == join->select_lex)) why wouldn't you check for `describe` here? "reoptimization" hack is only needed for EXPLAIN. > + { > + join->zero_result_cause= "no matching row in const table"; > + join->table_count=0; > + join->const_tables= 0; > + join->join_tab= NULL; > + > + DBUG_RETURN(0); > + } > + > join->sort_by_table= get_sort_by_table(join->order, join->group_list, > join->select_lex->leaf_tables, > join->const_table_map); Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

1 0

Re: [Maria-developers] 5dcde8f6523: MDEV-27653 long uniques don't work with unicode collations
by Sergei Golubchik 06 Jan '23

06 Jan '23

Hi, Alexander, Looks good. A couple of comments, see below On Jan 06, Alexander Barkov wrote: > revision-id: 5dcde8f6523 (mariadb-10.4.26-64-g5dcde8f6523) > parent(s): ce443c85547 > author: Alexander Barkov > committer: Alexander Barkov > timestamp: 2022-10-28 15:37:44 +0400 > message: > > MDEV-27653 long uniques don't work with unicode collations > > diff --git a/mysql-test/main/ctype_utf8.test b/mysql-test/main/ctype_utf8.test > index cc61c2ae0fe..e2d4e4ab906 100644 > --- a/mysql-test/main/ctype_utf8.test > +++ b/mysql-test/main/ctype_utf8.test > @@ -2310,3 +2310,133 @@ VALUES (_latin1 0xDF) UNION VALUES(_utf8'a' COLLATE utf8_bin); > --echo # > --echo # End of 10.3 tests > --echo # > + > + > +--echo # > +--echo # Start of 10.4 tests > +--echo # > + > +--echo # > +--echo # MDEV-27653 long uniques don't work with unicode collations > +--echo # > + > +SET NAMES utf8mb3; > + > +# CHAR > + > +CREATE TABLE t1 ( > + a CHAR(30) COLLATE utf8mb3_general_ci, > + UNIQUE KEY(a) USING HASH > +); > +SHOW CREATE TABLE t1; > +INSERT INTO t1 VALUES ('a'); > +--error ER_DUP_ENTRY > +INSERT INTO t1 VALUES ('ä'); > +SELECT * FROM t1; > +DROP TABLE t1; > + > +CREATE TABLE t1 ( > + a CHAR(30) COLLATE utf8mb3_general_ci, > + UNIQUE KEY(a(10)) USING HASH > +); > +SHOW CREATE TABLE t1; > +INSERT INTO t1 VALUES ('a'); > +--error ER_DUP_ENTRY > +INSERT INTO t1 VALUES ('ä'); > +SELECT * FROM t1; > +DROP TABLE t1; > + > + > +# VARCHAR > + > +CREATE TABLE t1 ( > + a VARCHAR(30) COLLATE utf8mb3_general_ci, > + UNIQUE KEY(a) USING HASH > +); > +SHOW CREATE TABLE t1; > +INSERT INTO t1 VALUES ('a'); > +--error ER_DUP_ENTRY > +INSERT INTO t1 VALUES ('ä'); > +SELECT * FROM t1; > +DROP TABLE t1; > + > +CREATE TABLE t1 ( > + a VARCHAR(30) COLLATE utf8mb3_general_ci, > + UNIQUE KEY(a(10)) USING HASH > +); > +SHOW CREATE TABLE t1; > +INSERT INTO t1 VALUES ('a'); > +--error ER_DUP_ENTRY > +INSERT INTO t1 VALUES ('ä'); > +SELECT * FROM t1; > +DROP TABLE t1; > + > + > +# TEXT > + > +CREATE TABLE t1 (a TEXT COLLATE utf8mb3_general_ci UNIQUE); > +SHOW CREATE TABLE t1; > +INSERT INTO t1 VALUES ('a'); > +--error ER_DUP_ENTRY > +INSERT INTO t1 VALUES ('ä'); > +SELECT * FROM t1; > +DROP TABLE t1; > + > +CREATE TABLE t1 ( > + a LONGTEXT COLLATE utf8mb3_general_ci, > + UNIQUE KEY(a(10)) USING HASH > +); > +SHOW CREATE TABLE t1; > +INSERT INTO t1 VALUES ('a'); > +--error ER_DUP_ENTRY > +INSERT INTO t1 VALUES ('ä'); > +SELECT * FROM t1; > +DROP TABLE t1; > + > + > +# Testing upgrade: > +# Prior to MDEV-27653, the UNIQUE HASH function errorneously > +# took into account string octet length. > +# Old tables should still open and work, but with a wrong results. > + > +copy_file std_data/mysql_upgrade/mdev27653_100422_text.frm $MYSQLD_DATADIR/test/t1.frm; > +copy_file std_data/mysql_upgrade/mdev27653_100422_text.MYD $MYSQLD_DATADIR/test/t1.MYD; > +copy_file std_data/mysql_upgrade/mdev27653_100422_text.MYI $MYSQLD_DATADIR/test/t1.MYI; > +SHOW CREATE TABLE t1; > +SELECT a, OCTET_LENGTH(a) FROM t1 ORDER BY BINARY a; > +CHECK TABLE t1; > + > +# There is already a one byte value 'a' in the table > +--error ER_DUP_ENTRY > +INSERT INTO t1 VALUES ('A'); > + > +# There is already a two-byte value 'ä' in the table > +--error ER_DUP_ENTRY > +INSERT INTO t1 VALUES ('Ä'); > + > +# There were no three-byte values in the table so far. > +# The below value violates UNIQUE, but it gets inserted. > +# This is wrong but expected for a pre-MDEV-27653 table. > +INSERT INTO t1 VALUES ('Ấ'); > +SELECT a, OCTET_LENGTH(a) FROM t1 ORDER BY BINARY a; > +CHECK TABLE t1; > + > +# ALTER FORCE fails: it tries to rebuild the table > +# with a correct UNIQUE HASH function, but there are duplicates! > +--error ER_DUP_ENTRY > +ALTER TABLE t1 FORCE; please, try ALTER IGNORE TABLE too > + > +# Let's remove all duplicate values, so only the one-byte 'a' stays. > +# ALTER..FORCE should work after that. > +DELETE FROM t1 WHERE OCTET_LENGTH(a)>1; > +ALTER TABLE t1 FORCE; > + > +# Make sure that 'a' and 'ä' cannot co-exists any more, > +# because the table was recreated with a correct UNIQUE HASH function. > +--error ER_DUP_ENTRY > +INSERT INTO t1 VALUES ('ä'); > +DROP TABLE t1; > + > +--echo # > +--echo # End of 10.4 tests > +--echo # > diff --git a/sql/table.cc b/sql/table.cc > index b9260853381..97f71284005 100644 > --- a/sql/table.cc > +++ b/sql/table.cc > @@ -1267,7 +1286,11 @@ bool parse_vcol_defs(THD *thd, MEM_ROOT *mem_root, TABLE *table, > list_item= new (mem_root) Item_field(thd, keypart->field); > field_list->push_back(list_item, mem_root); > } > - Item_func_hash *hash_item= new(mem_root)Item_func_hash(thd, *field_list); > + > + Item_func_hash *hash_item= make_unique_hash_func(thd, mem_root, > + table->s->mysql_version, > + field_list); would be good to fix CHECK ... FOR UPGRADE too. > + > Virtual_column_info *v= new (mem_root) Virtual_column_info(); > field->vcol_info= v; > field->vcol_info->expr= hash_item; Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

1 0

Re: [Maria-developers] 92ff948d021: MDEV-29231 View returns wrong value with SQL_MODE 'NO_BACKSLASH_ESCAPES'
by Sergei Golubchik 06 Jan '23

06 Jan '23

Hi, Oleksandr, On Jan 06, Oleksandr Byelkin wrote: > revision-id: 92ff948d021 (mariadb-10.3.37-53-g92ff948d021) > parent(s): 180b2bcd538 > author: Oleksandr Byelkin > committer: Oleksandr Byelkin > timestamp: 2022-12-05 20:42:03 +0100 > message: > > MDEV-29231 View returns wrong value with SQL_MODE 'NO_BACKSLASH_ESCAPES' > > 1. make view printing sql_mode independent > 1.1. Print always current escape character > 1.2. Allow escape character usage independent of sql_mode > 2. Add workaround for parsing "like 3 in (0,1) escape 3" problem > (alwaysuse parances in case of printing ESCAPE) > > diff --git a/mysql-test/main/ctype_cp1250_ch.result b/mysql-test/main/ctype_cp1250_ch.result > index b0aa4cff382..88175ae5013 100644 > --- a/mysql-test/main/ctype_cp1250_ch.result > +++ b/mysql-test/main/ctype_cp1250_ch.result > @@ -129,7 +129,7 @@ EXPLAIN EXTENDED SELECT * FROM t1 WHERE CONCAT(c1)='a' AND CONCAT(c1) LIKE 'a '; > id select_type table type possible_keys key key_len ref rows filtered Extra > 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where > Warnings: > -Note 1003 select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where concat(`test`.`t1`.`c1`) = 'a' and concat(`test`.`t1`.`c1`) like 'a ' > +Note 1003 select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where concat(`test`.`t1`.`c1`) = 'a' and concat(`test`.`t1`.`c1`) like ('a ') escape '\\' I don't think there's a need to print the escape clause here, it only makes the output less readable. > diff --git a/mysql-test/main/default.result b/mysql-test/main/default.result > index c9ac96367aa..5c72b894bf6 100644 > --- a/mysql-test/main/default.result > +++ b/mysql-test/main/default.result > @@ -1936,7 +1936,7 @@ CREATE OR REPLACE TABLE t1 ( col INT DEFAULT ( 1 LIKE ( NOW() BETWEEN '2000-01-0 > SHOW CREATE TABLE t1; > Table Create Table > t1 CREATE TABLE `t1` ( > - `col` int(11) DEFAULT (1 like (current_timestamp() between '2000-01-01' and '2012-12-12')) > + `col` int(11) DEFAULT (1 like (current_timestamp() between '2000-01-01' and '2012-12-12') escape '\\') So, there're basically two approaches to a fix and we're inconsistently using both for different items. 1. one can always print expressions in the environment-independent way - create longer less readable text - not all items support it, new syntax might be needed (cannot emulate NO_BACKSLASH_ESCAPES with the ESCAPE clause) 2. one can set the environment (sql_mode) to the same hard-coded value before parsing views and vcols - one cannot dump the view/table definition in one sql_mode and load in another + this isn't normally a problem, as mysqldump also uses a fixed sql_mode - some item properties (what are they?) might be only set at fix_fields, fixing sql_mode during parsing won't help there Could we try to agree to one approach and use it consistently for all items from now on? > diff --git a/mysql-test/main/parser.result b/mysql-test/main/parser.result > index 0bb4e82c8b8..79ec9f539eb 100644 > --- a/mysql-test/main/parser.result > +++ b/mysql-test/main/parser.result > @@ -1325,11 +1325,11 @@ select 1 between 2 in (3,4) and 5 AS `1 between (2 in (3,4)) and 5` > create or replace view v1 as select 1 between (2 like 3) and 4; > Select view_definition from information_schema.views where table_schema='test' and table_name='v1'; > view_definition > -select 1 between 2 like 3 and 4 AS `1 between (2 like 3) and 4` > +select 1 between 2 like (3) escape '\\' and 4 AS `1 between (2 like 3) and 4` > create or replace view v1 as select 1 not between (2 like 3) and 4; > Select view_definition from information_schema.views where table_schema='test' and table_name='v1'; > view_definition > -select 1 not between 2 like 3 and 4 AS `1 not between (2 like 3) and 4` > +select 1 not between 2 like (3) escape '\\' and 4 AS `1 not between (2 like 3) and 4` > drop view v1; > # > # Start of 10.2 tests > diff --git a/mysql-test/main/precedence.result b/mysql-test/main/precedence.result > index fc6579651b4..ce26ca2acf2 100644 > --- a/mysql-test/main/precedence.result > +++ b/mysql-test/main/precedence.result > @@ -113,7 +113,7 @@ NOT 2 != 3 NOT (2 != 3) (NOT 2) != 3 > create or replace view v1 as select NOT 2 LIKE 3, NOT (2 LIKE 3), (NOT 2) LIKE 3; > Select view_definition from information_schema.views where table_schema='test' and table_name='v1'; > view_definition > -select 2 not like 3 AS `NOT 2 LIKE 3`,2 not like 3 AS `NOT (2 LIKE 3)`,!2 like 3 AS `(NOT 2) LIKE 3` > +select 2 not like (3) escape '\\' AS `NOT 2 LIKE 3`,2 not like (3) escape '\\' AS `NOT (2 LIKE 3)`,!2 like (3) escape '\\' AS `(NOT 2) LIKE 3` > select NOT 2 LIKE 3, NOT (2 LIKE 3), (NOT 2) LIKE 3 union select * from v1; > NOT 2 LIKE 3 NOT (2 LIKE 3) (NOT 2) LIKE 3 > 1 1 0 > diff --git a/mysql-test/main/precedence_bugs.result b/mysql-test/main/precedence_bugs.result > index 723ab823b48..9654e3c31db 100644 > --- a/mysql-test/main/precedence_bugs.result > +++ b/mysql-test/main/precedence_bugs.result > @@ -54,7 +54,7 @@ drop table t1; > create view v1 as select 1 like (now() between '2000-01-01' and '2012-12-12' ); > show create view v1; > View v1 > -Create View CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 like (current_timestamp() between '2000-01-01' and '2012-12-12') AS `1 like (now() between '2000-01-01' and '2012-12-12' )` > +Create View CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 like (current_timestamp() between '2000-01-01' and '2012-12-12') escape '\\' AS `1 like (now() between '2000-01-01' and '2012-12-12' )` > character_set_client latin1 > collation_connection latin1_swedish_ci > drop view v1; if you'll always print ESCAPE clause, it'll make many tests in parser.test, precedence.test and precedence_bugs.test meaningless. Don't just update result files, please, remove tests that no longer make sense. Or rewrite them, when possible (but in precedence.test it's not). > diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc > index b7b0c981c2d..702689a7496 100644 > --- a/sql/item_cmpfunc.cc > +++ b/sql/item_cmpfunc.cc > @@ -5262,10 +5262,15 @@ void Item_func_like::print(String *str, enum_query_type query_type) > str->append(STRING_WITH_LEN(" not ")); > str->append(func_name()); > str->append(' '); > - if (escape_used_in_parsing) > + if (escape_used_in_parsing || (query_type | QT_VIEW_INTERNAL)) > { > - args[1]->print_parenthesised(str, query_type, precedence()); > - str->append(STRING_WITH_LEN(" escape ")); > + /* > + Explicit parencies is workaround against parser bug which üprevent > + patsing "like 3 in (0,1) escape 3" correctly > + */ > + str->append('('); > + args[1]->print(str, query_type); > + str->append(STRING_WITH_LEN(") escape ")); no, this is wrong. Don't just force parentheses there. Instead, fix the precedence, so that the parentheses would be printed automatically when needed. > escape_item->print_parenthesised(str, query_type, higher_precedence()); > } > else > @@ -5390,10 +5395,7 @@ bool fix_escape_item(THD *thd, Item *escape_item, String *tmp_str, > if (escape_str) > { > const char *escape_str_ptr= escape_str->ptr(); > - if (escape_used_in_parsing && ( > - (((thd->variables.sql_mode & MODE_NO_BACKSLASH_ESCAPES) && > - escape_str->numchars() != 1) || > - escape_str->numchars() > 1))) > + if (escape_used_in_parsing && escape_str->numchars() > 1) I still think that ESCAPE '' doesn't mean "no escape character". I tried and in my tests it didn't. > { > my_error(ER_WRONG_ARGUMENTS,MYF(0),"ESCAPE"); > return TRUE; Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

1 0

Re: [Maria-developers] 6ff49e48e4f: MDEV-26161: fix of view protocol
by Sergei Golubchik 06 Jan '23

06 Jan '23

Hi, Oleksandr, On Jan 06, Oleksandr Byelkin wrote: > revision-id: 6ff49e48e4f (mariadb-10.3.37-54-g6ff49e48e4f) > parent(s): 92ff948d021 > author: Oleksandr Byelkin > committer: Oleksandr Byelkin > timestamp: 2022-12-06 10:22:06 +0100 > message: > > MDEV-26161: fix of view protocol could you please amend the commit comment to explain why the statement produces a different error in a view protocol? > diff --git a/mysql-test/main/gis.result b/mysql-test/main/gis.result > index 358be520b06..37613bc71a0 100644 > --- a/mysql-test/main/gis.result > +++ b/mysql-test/main/gis.result > @@ -4980,11 +4980,11 @@ ERROR HY000: Illegal parameter data type geometry for operation 'is_used_lock' > # MDEV-26161 crash in Gis_point::calculate_haversine > # > select st_distance_sphere(x'01030000000400000004000000000000', multipoint(point(124,204)), 10); > -ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field > +Got one of the listed errors > select st_distance_sphere(x'010300000004000000040000', multipoint(point(124,204)), 10); > -ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field > +Got one of the listed errors > select st_distance_sphere(x'010300000001000000040000', multipoint(point(124,204)), 10); > -ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field > +Got one of the listed errors > # > # End of 10.3 tests > # > diff --git a/mysql-test/main/gis.test b/mysql-test/main/gis.test > index 716fab9bfeb..07b207dcdc9 100644 > --- a/mysql-test/main/gis.test > +++ b/mysql-test/main/gis.test > @@ -3093,11 +3093,11 @@ SELECT IS_USED_LOCK(POINT(1,1)); > --echo # > --echo # MDEV-26161 crash in Gis_point::calculate_haversine > --echo # > ---error ER_CANT_CREATE_GEOMETRY_OBJECT > +--error ER_CANT_CREATE_GEOMETRY_OBJECT,ER_INTERNAL_ERROR > select st_distance_sphere(x'01030000000400000004000000000000', multipoint(point(124,204)), 10); > ---error ER_CANT_CREATE_GEOMETRY_OBJECT > +--error ER_CANT_CREATE_GEOMETRY_OBJECT,ER_INTERNAL_ERROR > select st_distance_sphere(x'010300000004000000040000', multipoint(point(124,204)), 10); > ---error ER_CANT_CREATE_GEOMETRY_OBJECT > +--error ER_CANT_CREATE_GEOMETRY_OBJECT,ER_INTERNAL_ERROR > select st_distance_sphere(x'010300000001000000040000', multipoint(point(124,204)), 10); > > --echo # > Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

1 0

Re: [Maria-developers] MDEV-28602 Wrong result with outer join, merged derived table and view
by Sergey Petrunia 04 Jan '23

04 Jan '23

Hi Rex, Please find below input for the commit. (I've actually committed an alternative fix now, but before doing that I wrote all the trivial comments below so I'm sending these anyway in order to show what is expected of a patch. I'm also including the rationale for creating an alternative patch) > commit 75af3195482d3da277825663d1150c0dfc55420a > Author: Rex <rex.johnston(a)mariadb.com> > Date: Fri Dec 23 05:28:59 2022 +1200 > > MDEV-28602 Wrong result with outer join, merged derived table and view > > const item reference being mishandled in outer to inner join on > filling in null values. > First, fixVersion. It is currently set to 10.11, but affectsVersion starts from 10.3 I think the bug and the fix are applicable for all versions. Second: commit comment. This is not descriptive enough: > const item reference being mishandled in outer to inner join on > filling in null values. Something like this would be better: <commit-comment> A LEFT JOIN with a constant as a column of the inner table produced wrong query result if the optimizer had to write the inner table column into a temp table: SELECT ... FROM (SELECT /*non-mergeable select*/ FROM t1 LEFT JOIN (SELECT 'Y' as Val) t2 ON ...) Fixed this by: 1. Making Item_ref::save_in_field() call check_null_ref() to see if the referred table has a NULL-complemented row. 2. In order to do #1, moved null_ref_table() and check_null_ref() from Item_direct_view_ref to Item_ref. </commit-comment> Note: it starts with a siccint problem description, which is followed by a description of the fix. > diff --git a/sql/sql_join_cache.cc b/sql/sql_join_cache.cc > index 620c52a3f40..65bedc3d337 100644 > --- a/sql/sql_join_cache.cc > +++ b/sql/sql_join_cache.cc > @@ -2622,7 +2622,7 @@ enum_nested_loop_state JOIN_CACHE::join_null_complements(bool skip_last) > get_record(); > /* The outer row is complemented by nulls for each inner table */ > restore_record(join_tab->table, s->default_values); > - mark_as_null_row(join_tab->table); > + mark_as_null_row(join_tab->table); In the future please make sure patches do not contain unrelated meaningless changes like this one. > rc= generate_full_extensions(get_curr_rec()); > if (rc != NESTED_LOOP_OK && rc != NESTED_LOOP_NO_MORE_ROWS) > goto finish; > diff --git a/mysql-test/main/merge.test b/mysql-test/main/merge.test > index 0485f3ed1c3..05b05287e1b 100644 > --- a/mysql-test/main/merge.test > +++ b/mysql-test/main/merge.test Why is the patch in the merge.test ? merge.test at the top says clearly "Test of MERGE TABLES". That is, this is a test for tables with ENGINE=MERGE, a feature not related to this bug. The right test files for this bug would be main/join_outer.test and main/derived.test, I would prefer the first one. > @@ -2886,3 +2886,50 @@ drop table tm, t; > --echo # > --echo # End of 10.8 tests > --echo # > + > +--echo # > +--echo # MDEV-28602 Wrong result with outer join, merged derived table and view > +--echo # > + > +drop table if exists t1, t2; ^^^ This is not needed as there is a DROP TABLE right above > +create table t1 ( > + Election int(10) unsigned NOT NULL > +); > + > +insert into t1 (Election) values (1); > + +create table t2 ( + VoteID int(10), + ElectionID int(10), + UserID int(10) +); + +insert into t2 (ElectionID, UserID) values (2, 30), (3, 30); +# INSERT INTO t2 (ElectionID, UserID) VALUES (1, 30); ^^ No need to have commented-out lines in the commit. +drop view if exists v1; ^^^ same as above: this is not needed. +create view v1 as select * from t1 + left join ( select 'Y' AS Voted, ElectionID from t2 ) AS T + on T.ElectionID = t1.Election +limit 9; +# limit X causes merge algorithm select as opposed to temp table +select * from v1; ... > diff --git a/sql/item.h b/sql/item.h > index 2d598546b91..8684477b558 100644 > --- a/sql/item.h > +++ b/sql/item.h > @@ -5531,18 +5531,42 @@ class Item_sp > > class Item_ref :public Item_ident > { > +public: > + > +#define NO_NULL_TABLE (reinterpret_cast<TABLE *>(0x1)) > + > + void set_null_ref_table() > + { > + null_ref_table= NO_NULL_TABLE; > + } > + > + bool check_null_ref() This works, but looks like a change that is 1. Too big 2. Looks like a partial implementation I'm looking at methods of Item_direct_view_ref: - save_val() - save_org_in_field() - save_in_result_field() - val_real() and other val_XXX() and they all follow the same pattern: if (check_null_ref()) produce SQL NULL; else Call Item_direct_ref::same_method(); This suggests we could add Item_direct_view_ref::save_in_field() and use the same approach. Conversely, if we move check_null_ref() to be in Item_ref, we will end up with: - check_null_ref() is in Item_ref - methods that make use of it are in Item_direct_view_ref() - methods of Item_ref() do not make check_null_ref() check, except for save_in_field() which does it. I think the former looks more logical than the latter. I've made the patch for it and I'll ask Sanja (the Item_ref/views expert) to check it. BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net

1 0