mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2026-03-18 14:28:43 +01:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/suite/sql_sequence/next.result
Aleksey Midenkov 6fcd87ba4d MDEV-33985 Server crashes at Item_func_nextval::val_int 
Pure aliases are not handled properly by Item_func_nextval::val_int().

add_table_to_list() does not create MDL request for pure aliases,
i.e. when there is no table_list->db set or TL_OPTION_ALIAS was
set. When the expression is not inside CTE the case with empty db is
handled by:

  else if (!lex->with_cte_resolution && lex->copy_db_to(&db))
    DBUG_RETURN(0);

So, table_list gets current database name and the query is failed with
ER_NO_SUCH_TABLE error.

The fix adds the case of is_pure_alias() for
Item_func_nextval::val_int() and fails it with ER_NOT_SEQUENCE2 error.

Note: semantics for TL_OPTION_ALIAS cannot be based on empty db, only
parser can set TL_OPTION_ALIAS as resolve_references_to_cte() relies
on TL_OPTION_ALIAS after copy_db_to().
2026-01-23 14:48:24 +03:00

614 lines
14 KiB
Text
Raw Permalink Blame History

CREATE SEQUENCE t1 start with 1 minvalue 1 maxvalue 10 increment by 1 cache 2 cycle;
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
select next value for t1;
next value for t1
1
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
3 0
select next value for t1;
next value for t1
2
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
3 0
select next value for t1;
next value for t1
3
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
5 0
select next value for t1;
next value for t1
4
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
5 0
select next value for t1;
next value for t1
5
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
7 0
select next value for t1;
next value for t1
6
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
7 0
select next value for t1;
next value for t1
7
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
9 0
select next value for t1;
next value for t1
8
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
9 0
select next value for t1;
next value for t1
9
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
11 0
select next value for t1;
next value for t1
10
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
11 0
select next value for t1;
next value for t1
1
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
3 1
select NEXT VALUE for t1,seq from seq_1_to_20;
NEXT VALUE for t1 seq
2 1
3 2
4 3
5 4
6 5
7 6
8 7
9 8
10 9
1 10
2 11
3 12
4 13
5 14
6 15
7 16
8 17
9 18
10 19
1 20
drop sequence t1;
CREATE SEQUENCE t1 minvalue 1 maxvalue 10 increment by -1 cache 2 cycle engine=aria;
select next value for t1;
next value for t1
10
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
8 0
select next value for t1;
next value for t1
9
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
8 0
select next value for t1;
next value for t1
8
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
6 0
select next value for t1;
next value for t1
7
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
6 0
select next value for t1;
next value for t1
6
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
4 0
select next value for t1;
next value for t1
5
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
4 0
select next value for t1;
next value for t1
4
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
2 0
select next value for t1;
next value for t1
3
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
2 0
select next value for t1;
next value for t1
2
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
0 0
select next value for t1;
next value for t1
1
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
0 0
select next value for t1;
next value for t1
10
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
8 1
select NEXT VALUE for t1,seq from seq_1_to_20;
NEXT VALUE for t1 seq
9 1
8 2
7 3
6 4
5 5
4 6
3 7
2 8
1 9
10 10
9 11
8 12
7 13
6 14
5 15
4 16
3 17
2 18
1 19
10 20
drop sequence t1;
CREATE SEQUENCE t1 start with 8 minvalue 1 maxvalue 10 increment by 1 cache 2 nocycle;
select next value for t1;
next value for t1
8
select next value for t1;
next value for t1
9
select next value for t1;
next value for t1
10
select previous value for t1;
previous value for t1
10
select next value for t1;
ERROR HY000: Sequence 'test.t1' has run out
select previous value for t1;
previous value for t1
NULL
select next value for t1;
ERROR HY000: Sequence 'test.t1' has run out
drop sequence t1;
create sequence s1 start with 1 cache 2 maxvalue 5;
select next value for s1;
next value for s1
1
select next value for s1;
next value for s1
2
select next value for s1;
next value for s1
3
select next value for s1;
next value for s1
4
select next value for s1;
next value for s1
5
select next value for s1;
ERROR HY000: Sequence 'test.s1' has run out
drop sequence s1;
CREATE SEQUENCE t1 start with 1 minvalue 1 maxvalue 100 increment by 1 cache 10;
select next value for t1;
next value for t1
1
select * from t1;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
11 1 100 1 1 10 0 0
flush tables;
select next value for t1;
next value for t1
11
select nextval(t1);
nextval(t1)
12
drop sequence t1;
CREATE SEQUENCE t9 start with 1 minvalue 1 maxvalue 10 increment by 1 cache 5 cycle;
select previous value for t9;
previous value for t9
NULL
select next value for t9;
next value for t9
1
select previous value for t9, lastval(t9);
previous value for t9 lastval(t9)
1 1
select next value for t9;
next value for t9
2
select previous value for t9, lastval(t9);
previous value for t9 lastval(t9)
2 2
select seq, previous value for t9, NEXT VALUE for t9, previous value for t9 from seq_1_to_20;
seq previous value for t9 NEXT VALUE for t9 previous value for t9
1 2 3 3
2 3 4 4
3 4 5 5
4 5 6 6
5 6 7 7
6 7 8 8
7 8 9 9
8 9 10 10
9 10 1 1
10 1 2 2
11 2 3 3
12 3 4 4
13 4 5 5
14 5 6 6
15 6 7 7
16 7 8 8
17 8 9 9
18 9 10 10
19 10 1 1
20 1 2 2
select * from t9;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
6 1 10 1 1 5 1 2
drop sequence t9;
CREATE SEQUENCE s1 cache=0;
select * from s1;
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
select next value for s1;
next value for s1
1
select next_not_cached_value from s1;
next_not_cached_value
2
select next value for s1;
next value for s1
2
select next_not_cached_value from s1;
next_not_cached_value
3
DROP SEQUENCE s1;
CREATE SEQUENCE s1 cache=1;
select next_not_cached_value from s1;
next_not_cached_value
1
select next value for s1;
next value for s1
1
select next_not_cached_value from s1;
next_not_cached_value
2
select next value for s1;
next value for s1
2
select next_not_cached_value from s1;
next_not_cached_value
3
DROP SEQUENCE s1;
CREATE SEQUENCE s1 cache=2;
select next_not_cached_value from s1;
next_not_cached_value
1
select next value for s1;
next value for s1
1
select next_not_cached_value from s1;
next_not_cached_value
3
select next value for s1;
next value for s1
2
select next_not_cached_value from s1;
next_not_cached_value
3
DROP SEQUENCE s1;
CREATE SEQUENCE s1;
select next value for s1;
next value for s1
1
select next value for s1;
next value for s1
2
select next value for s1;
next value for s1
3
select next value for s1;
next value for s1
4
alter sequence s1 increment -2;
select * from s1;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
1001 1 9223372036854775806 1 -2 1000 0 0
select next value for s1;
next value for s1
1001
select next value for s1;
next value for s1
999
alter sequence s1 restart 6;
select next value for s1;
next value for s1
6
select next value for s1;
next value for s1
4
select next value for s1;
next value for s1
2
select next value for s1;
ERROR HY000: Sequence 'test.s1' has run out
DROP SEQUENCE s1;
CREATE SEQUENCE t1 start with 1 minvalue 1 maxvalue 10 increment by 1 cache 5 cycle;
select next value for t1;
next value for t1
1
select previous value for t1;
previous value for t1
1
flush tables;
select previous value for t1;
previous value for t1
1
drop sequence t1;
select previous value for t1;
ERROR 42S02: Unknown SEQUENCE: 't1'
CREATE SEQUENCE t1 start with 5 minvalue 1 maxvalue 10 increment by 1 cache 5 cycle;
select previous value for t1;
previous value for t1
NULL
select next value for t1;
next value for t1
5
select previous value for t1;
previous value for t1
5
drop sequence t1;
CREATE or replace SEQUENCE s1 MINVALUE 1 MAXVALUE 9999999999
INCREMENT BY 1 START WITH 3984356 CACHE 20 CYCLE engine=innodb;
show create table s1;
Table Create Table
s1 CREATE TABLE `s1` (
`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=InnoDB SEQUENCE=1
select * from s1;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
3984356 1 9999999999 3984356 1 20 1 0
select NEXT VALUE FOR s1;
NEXT VALUE FOR s1
3984356
select NEXT VALUE FOR s1;
NEXT VALUE FOR s1
3984357
select NEXT VALUE FOR s1;
NEXT VALUE FOR s1
3984358
select * from s1;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
3984376 1 9999999999 3984356 1 20 1 0
FLUSH TABLES;
select * from s1;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
3984376 1 9999999999 3984356 1 20 1 0
select NEXT VALUE FOR s1;
NEXT VALUE FOR s1
3984376
select * from s1;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
3984396 1 9999999999 3984356 1 20 1 0
drop sequence s1;
CREATE SEQUENCE t1 start with 5 minvalue 1 maxvalue 10 increment by 1 cache 5 cycle;
explain select next value for t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
explain select next value for t1, minimum_value from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1
drop table t1;
CREATE SEQUENCE s1;
CREATE TABLE t1 (a int);
insert into t1 values (next value for s1);
insert into t1 values (next value for s1);
select * from t1;
a
1
2
drop table t1,s1;
CREATE SEQUENCE s1;
CREATE TABLE t1 (a int primary key auto_increment, b int default 0) engine=myisam;
insert into t1 values (),(),(),(),(),(),();
update t1 set b= next value for s1 where a <= 3;
select * from t1;
a b
1 1
2 2
3 3
4 0
5 0
6 0
7 0
drop table t1,s1;
CREATE OR REPLACE SEQUENCE s1 MINVALUE 1 MAXVALUE 9999999999 INCREMENT BY 1 START WITH 3984356 nocache CYCLE engine='innodb';
select * from s1;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
3984356 1 9999999999 3984356 1 0 1 0
select next value for s1;
next value for s1
3984356
explain extended select next value for s1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select nextval(`test`.`s1`) AS `next value for s1`
explain extended select previous value for s1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select lastval(`test`.`s1`) AS `previous value for s1`
drop sequence s1;
create table t1 (a int);
select next value for t1;
ERROR 42S02: 'test.t1' is not a SEQUENCE
drop table t1;
create sequence t1;
select next value for t1;
next value for t1
1
select next value for t1, minimum_value;
ERROR 42S22: Unknown column 'minimum_value' in 'SELECT'
drop sequence t1;
#
# MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions
#
CREATE SEQUENCE s1;
SELECT
NEXT VALUE FOR s1,
PREVIOUS VALUE FOR s1;
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
def NEXT VALUE FOR s1 8 20 1 Y 32896 0 63
def PREVIOUS VALUE FOR s1 8 20 1 Y 32896 0 63
NEXT VALUE FOR s1 PREVIOUS VALUE FOR s1
1 1
DROP SEQUENCE s1;
#
# MDEV-13720 ER_NOT_SEQUENCE for temporary table
#
create temporary table tmp (i int);
select next value for tmp;
ERROR 42S02: 'test.tmp' is not a SEQUENCE
drop table tmp;
#
# Test negative numbers
#
create sequence s start with 1 minvalue=-1000 maxvalue=1000 increment -1;
select next value for s;
next value for s
1
select next value for s;
next value for s
0
flush tables;
select next value for s;
next value for s
-999
drop sequence s;
#
# MDEV-23823 NEXT VALUE crash on locked view
#
CREATE VIEW v AS SELECT 1;
LOCK TABLE v READ;
SELECT NEXT VALUE FOR v;
ERROR 42S02: 'test.v' is not a SEQUENCE
#
# MDEV-24018: SIGSEGV in Item_func_nextval::update_table on SELECT SETVAL
#
SELECT SETVAL (v,0);
ERROR 42S02: 'test.v' is not a SEQUENCE
UNLOCK TABLES;
DROP VIEW v;
#
# MDEV-28650 Server crashes in Item_func_nextval::val_int after select from view
#
create sequence s;
create table t1 (a int not null default nextval(s));
insert into t1 values(),();
flush tables;
select default(a) from t1;
default(a)
1001
1002
create algorithm=merge view v1 as select default(a) from t1;
select * from v1;
default(a)
1003
1004
Select * from v1;
default(a)
1005
1006
prepare ps from 'SELECT * from v1';
execute ps;
default(a)
1007
1008
Execute ps;
default(a)
1009
1010
drop prepare ps;
create or replace algorithm=temptable view v1 as select default(a) from t1;
select * from v1;
default(a)
1011
1012
Select * from v1;
default(a)
1013
1014
prepare ps from 'SELECT * from v1';
execute ps;
default(a)
1015
1016
Execute ps;
default(a)
1017
1018
drop prepare ps;
drop view v1;
drop table t1;
drop sequence s;
#
# MDEV-33985 Server crashes at Item_func_nextval::val_int
#
select * from (select 1 as a) x where (nextval(x));
ERROR 42S02: Table 'test.x' doesn't exist
select (with x(x) as (select 1) select * from x where (nextval(x)));
ERROR 42S02: 'x' is not a SEQUENCE
create sequence s;
select next value for s1 from s as s1;
ERROR 42S02: Table 'test.s1' doesn't exist
drop sequence s;
# End of 10.11 tests
Reference in a new issue View git blame Copy permalink