summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/main/multi_update.result53
-rw-r--r--mysql-test/main/multi_update.test53
-rw-r--r--mysql-test/suite/innodb/r/foreign_key.result11
-rw-r--r--mysql-test/suite/innodb/r/mvcc_secondary.result24
-rw-r--r--mysql-test/suite/innodb/t/foreign_key.test39
-rw-r--r--mysql-test/suite/innodb/t/mvcc_secondary.test26
-rw-r--r--mysql-test/suite/vcol/r/vcol_syntax.result38
-rw-r--r--mysql-test/suite/vcol/t/vcol_syntax.test44
8 files changed, 288 insertions, 0 deletions
diff --git a/mysql-test/main/multi_update.result b/mysql-test/main/multi_update.result
index d792b2828b8..4001a47ecd4 100644
--- a/mysql-test/main/multi_update.result
+++ b/mysql-test/main/multi_update.result
@@ -982,6 +982,59 @@ drop function f1;
#
# end of 5.5 tests
#
+#
+# MDEV-24823: Invalid multi-table update of view within SP
+#
+create table t1 (id int) engine=myisam;
+insert into t1 values (1),(2),(1);
+create table t2 (pk int, c0 int) engine=myisam;
+insert into t2 values (1,1), (2,3);
+create view v2 as select * from t2;
+create view v3 as select * from t2 where c0 < 3;
+create procedure sp0() update t1, v2 set v2.pk = 1 where v2.c0 = t1.c1;
+call sp0();
+ERROR 42S22: Unknown column 't1.c1' in 'where clause'
+call sp0();
+ERROR 42S22: Unknown column 't1.c1' in 'where clause'
+create procedure sp1() update (t1 join v2 on v2.c0 = t1.c1) set v2.pk = 1;
+call sp1();
+ERROR 42S22: Unknown column 't1.c1' in 'on clause'
+call sp1();
+ERROR 42S22: Unknown column 't1.c1' in 'on clause'
+create procedure sp2() update (t1 join v3 on v3.c0 = t1.c1) set v3.pk = 1;
+call sp2();
+ERROR 42S22: Unknown column 't1.c1' in 'on clause'
+call sp2();
+ERROR 42S22: Unknown column 't1.c1' in 'on clause'
+create procedure sp3()
+update (t1 join v2 on v2.c0 = t1.id) set v2.c0 = v2.c0+1;
+select * from t2;
+pk c0
+1 1
+2 3
+call sp3();
+select * from t2;
+pk c0
+1 2
+2 3
+call sp3();
+select * from t2;
+pk c0
+1 3
+2 3
+create procedure sp4() delete t1 from t1 join v2 on v2.c0 = t1.c1;
+call sp4();
+ERROR 42S22: Unknown column 't1.c1' in 'on clause'
+call sp4();
+ERROR 42S22: Unknown column 't1.c1' in 'on clause'
+drop procedure sp0;
+drop procedure sp1;
+drop procedure sp2;
+drop procedure sp3;
+drop procedure sp4;
+drop view v2,v3;
+drop table t1,t2;
+# End of 10.2 tests
create table t1 (c1 int, c3 int);
insert t1(c3) values (1), (2), (3), (4), (5), (6), (7), (8);
create table t2 select * from t1;
diff --git a/mysql-test/main/multi_update.test b/mysql-test/main/multi_update.test
index 8a32f626818..84f06a7c165 100644
--- a/mysql-test/main/multi_update.test
+++ b/mysql-test/main/multi_update.test
@@ -958,6 +958,59 @@ drop function f1;
--echo # end of 5.5 tests
--echo #
+--echo #
+--echo # MDEV-24823: Invalid multi-table update of view within SP
+--echo #
+
+create table t1 (id int) engine=myisam;
+insert into t1 values (1),(2),(1);
+create table t2 (pk int, c0 int) engine=myisam;
+insert into t2 values (1,1), (2,3);
+create view v2 as select * from t2;
+create view v3 as select * from t2 where c0 < 3;
+
+create procedure sp0() update t1, v2 set v2.pk = 1 where v2.c0 = t1.c1;
+--error ER_BAD_FIELD_ERROR
+call sp0();
+--error ER_BAD_FIELD_ERROR
+call sp0();
+
+create procedure sp1() update (t1 join v2 on v2.c0 = t1.c1) set v2.pk = 1;
+--error ER_BAD_FIELD_ERROR
+call sp1();
+--error ER_BAD_FIELD_ERROR
+call sp1();
+
+create procedure sp2() update (t1 join v3 on v3.c0 = t1.c1) set v3.pk = 1;
+--error ER_BAD_FIELD_ERROR
+call sp2();
+--error ER_BAD_FIELD_ERROR
+call sp2();
+
+create procedure sp3()
+update (t1 join v2 on v2.c0 = t1.id) set v2.c0 = v2.c0+1;
+select * from t2;
+call sp3();
+select * from t2;
+call sp3();
+select * from t2;
+
+create procedure sp4() delete t1 from t1 join v2 on v2.c0 = t1.c1;
+--error ER_BAD_FIELD_ERROR
+call sp4();
+--error ER_BAD_FIELD_ERROR
+call sp4();
+
+drop procedure sp0;
+drop procedure sp1;
+drop procedure sp2;
+drop procedure sp3;
+drop procedure sp4;
+drop view v2,v3;
+drop table t1,t2;
+
+--echo # End of 10.2 tests
+
#
# MDEV-13911 Support ORDER BY and LIMIT in multi-table update
#
diff --git a/mysql-test/suite/innodb/r/foreign_key.result b/mysql-test/suite/innodb/r/foreign_key.result
index 34d0c3dad64..c7332f3dfc6 100644
--- a/mysql-test/suite/innodb/r/foreign_key.result
+++ b/mysql-test/suite/innodb/r/foreign_key.result
@@ -801,6 +801,17 @@ ERROR 23000: Duplicate entry '10' for key 'ind9'
SET FOREIGN_KEY_CHECKS= 0;
ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES t1 (pk);
DROP TABLE t1;
+SET FOREIGN_KEY_CHECKS= 1;
+#
+# MDEV-23455 Hangs + Sig11 in unknown location(s) due to single complex FK query
+#
+Parsing foreign keys 1...
+ERROR HY000: Can't create table `test`.`t0` (errno: 150 "Foreign key constraint is incorrectly formed")
+Parsing foreign keys 2...
+ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
+Parsing foreign keys 3...
+ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
+Parsing foreign keys 4...
# End of 10.2 tests
CREATE TABLE t1 (a GEOMETRY, INDEX(a(8)),
FOREIGN KEY (a) REFERENCES x (xx)) ENGINE=InnoDB;
diff --git a/mysql-test/suite/innodb/r/mvcc_secondary.result b/mysql-test/suite/innodb/r/mvcc_secondary.result
new file mode 100644
index 00000000000..2289742e830
--- /dev/null
+++ b/mysql-test/suite/innodb/r/mvcc_secondary.result
@@ -0,0 +1,24 @@
+#
+# MDEV-25459 MVCC read from index on CHAR or VARCHAR wrongly omits rows
+#
+CREATE TABLE t1 (
+pk int PRIMARY KEY, c varchar(255) UNIQUE,
+d char(255), e varchar(255), f char(255), g char(255)
+) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARACTER SET ucs2;
+INSERT INTO t1 VALUES
+(1,REPEAT('c',248),REPEAT('a',106),REPEAT('b',220),REPEAT('x',14),'');
+BEGIN;
+UPDATE t1 SET c=REPEAT('d',170);
+connect con1,localhost,root,,;
+SELECT pk FROM t1 FORCE INDEX (c);
+pk
+1
+connection default;
+COMMIT;
+connection con1;
+SELECT pk FROM t1 FORCE INDEX (c);
+pk
+1
+disconnect con1;
+connection default;
+DROP TABLE t1;
diff --git a/mysql-test/suite/innodb/t/foreign_key.test b/mysql-test/suite/innodb/t/foreign_key.test
index 41299e4bc35..a413ea43646 100644
--- a/mysql-test/suite/innodb/t/foreign_key.test
+++ b/mysql-test/suite/innodb/t/foreign_key.test
@@ -782,6 +782,45 @@ ALTER TABLE t1 ADD UNIQUE INDEX ind9 (b), LOCK=SHARED;
SET FOREIGN_KEY_CHECKS= 0;
ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES t1 (pk);
DROP TABLE t1;
+SET FOREIGN_KEY_CHECKS= 1;
+
+--echo #
+--echo # MDEV-23455 Hangs + Sig11 in unknown location(s) due to single complex FK query
+--echo #
+let $constr_prefix= aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;
+let $fk_ref= xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx;
+let $fk_field= yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy;
+let $constr_count= 200; # each 100 constrs is 1 sec of test execution
+let $i= 0;
+
+while ($i < $constr_count)
+{
+ let $p= $constr_prefix$i;
+ let $constr= CONSTRAINT $p FOREIGN KEY ($fk_field) REFERENCES t1($fk_ref) ON UPDATE SET NULL;
+ if ($i)
+ {
+ let $constrs= $constrs, $constr;
+ }
+ if (!$i)
+ {
+ let $constrs= $constr;
+ }
+ inc $i;
+}
+--disable_query_log
+--echo Parsing foreign keys 1...
+--error ER_CANT_CREATE_TABLE
+eval create table t0($fk_field int, $constrs) engine innodb;
+--echo Parsing foreign keys 2...
+--error ER_CANT_CREATE_TABLE
+eval create table t1($fk_field int, $constrs) engine innodb;
+--echo Parsing foreign keys 3...
+--error ER_CANT_CREATE_TABLE
+eval create table t1($fk_ref int, $fk_field int, $constrs) engine innodb;
+--echo Parsing foreign keys 4...
+eval create table t1($fk_ref int primary key, $fk_field int, $constrs) engine innodb;
+drop table t1;
+--enable_query_log
--echo # End of 10.2 tests
diff --git a/mysql-test/suite/innodb/t/mvcc_secondary.test b/mysql-test/suite/innodb/t/mvcc_secondary.test
new file mode 100644
index 00000000000..93c91c40076
--- /dev/null
+++ b/mysql-test/suite/innodb/t/mvcc_secondary.test
@@ -0,0 +1,26 @@
+--source include/innodb_page_size_small.inc
+
+--echo #
+--echo # MDEV-25459 MVCC read from index on CHAR or VARCHAR wrongly omits rows
+--echo #
+
+CREATE TABLE t1 (
+ pk int PRIMARY KEY, c varchar(255) UNIQUE,
+ d char(255), e varchar(255), f char(255), g char(255)
+) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARACTER SET ucs2;
+
+INSERT INTO t1 VALUES
+(1,REPEAT('c',248),REPEAT('a',106),REPEAT('b',220),REPEAT('x',14),'');
+
+BEGIN;
+UPDATE t1 SET c=REPEAT('d',170);
+
+connect (con1,localhost,root,,);
+SELECT pk FROM t1 FORCE INDEX (c);
+connection default;
+COMMIT;
+connection con1;
+SELECT pk FROM t1 FORCE INDEX (c);
+disconnect con1;
+connection default;
+DROP TABLE t1;
diff --git a/mysql-test/suite/vcol/r/vcol_syntax.result b/mysql-test/suite/vcol/r/vcol_syntax.result
index 16e30e57230..c8983f34c93 100644
--- a/mysql-test/suite/vcol/r/vcol_syntax.result
+++ b/mysql-test/suite/vcol/r/vcol_syntax.result
@@ -50,3 +50,41 @@ t1 CREATE TABLE "t1" (
)
drop table t1;
set session sql_mode=@OLD_SQL_MODE;
+#
+# MDEV-25091 CREATE TABLE: field references qualified by a wrong table name succeed
+#
+create table t2 (x int);
+create table t1 (x int, y int generated always as (t2.x));
+ERROR 42S22: Unknown column '`t2`.`x`' in 'GENERATED ALWAYS'
+create table t1 (x int, y int check (y > t2.x));
+ERROR 42S22: Unknown column '`t2`.`x`' in 'CHECK'
+create table t1 (x int, y int default t2.x);
+ERROR 42S22: Unknown column '`t2`.`x`' in 'DEFAULT'
+create table t1 (x int, check (t2.x > 0));
+ERROR 42S22: Unknown column '`t2`.`x`' in 'CHECK'
+create table t1 (x int);
+alter table t1 add column y int generated always as (t2.x);
+ERROR 42S22: Unknown column '`t2`.`x`' in 'GENERATED ALWAYS'
+alter table t1 add column y int check (z > t2.x);
+ERROR 42S22: Unknown column '`t2`.`x`' in 'CHECK'
+alter table t1 add column y int default t2.x;
+ERROR 42S22: Unknown column '`t2`.`x`' in 'DEFAULT'
+alter table t1 add constraint check (t2.x > 0);
+ERROR 42S22: Unknown column '`t2`.`x`' in 'CHECK'
+create or replace table t1 (x int, y int generated always as (t1.x));
+create or replace table t1 (x int, y int check (y > t1.x));
+create or replace table t1 (x int, y int default t1.x);
+create or replace table t1 (x int, check (t1.x > 0));
+create or replace table t1 (x int, y int generated always as (test.t1.x));
+create or replace table t1 (x int, y int check (y > test.t1.x));
+create or replace table t1 (x int, y int default test.t1.x);
+create or replace table t1 (x int, check (test.t1.x > 0));
+drop tables t1, t2;
+create table t1 (x int, y int generated always as (test2.t1.x));
+ERROR 42S22: Unknown column '`test2`.`t1`.`x`' in 'GENERATED ALWAYS'
+create table t1 (x int, y int check (y > test2.t1.x));
+ERROR 42S22: Unknown column '`test2`.`t1`.`x`' in 'CHECK'
+create table t1 (x int, y int default test2.t1.x);
+ERROR 42S22: Unknown column '`test2`.`t1`.`x`' in 'DEFAULT'
+create table t1 (x int, check (test2.t1.x > 0));
+ERROR 42S22: Unknown column '`test2`.`t1`.`x`' in 'CHECK'
diff --git a/mysql-test/suite/vcol/t/vcol_syntax.test b/mysql-test/suite/vcol/t/vcol_syntax.test
index 6dc3cf43317..f425b52ab79 100644
--- a/mysql-test/suite/vcol/t/vcol_syntax.test
+++ b/mysql-test/suite/vcol/t/vcol_syntax.test
@@ -28,3 +28,47 @@ show create table t1;
drop table t1;
set session sql_mode=@OLD_SQL_MODE;
+--echo #
+--echo # MDEV-25091 CREATE TABLE: field references qualified by a wrong table name succeed
+--echo #
+create table t2 (x int);
+
+--error ER_BAD_FIELD_ERROR
+create table t1 (x int, y int generated always as (t2.x));
+--error ER_BAD_FIELD_ERROR
+create table t1 (x int, y int check (y > t2.x));
+--error ER_BAD_FIELD_ERROR
+create table t1 (x int, y int default t2.x);
+--error ER_BAD_FIELD_ERROR
+create table t1 (x int, check (t2.x > 0));
+
+create table t1 (x int);
+--error ER_BAD_FIELD_ERROR
+alter table t1 add column y int generated always as (t2.x);
+--error ER_BAD_FIELD_ERROR
+alter table t1 add column y int check (z > t2.x);
+--error ER_BAD_FIELD_ERROR
+alter table t1 add column y int default t2.x;
+--error ER_BAD_FIELD_ERROR
+alter table t1 add constraint check (t2.x > 0);
+
+create or replace table t1 (x int, y int generated always as (t1.x));
+create or replace table t1 (x int, y int check (y > t1.x));
+create or replace table t1 (x int, y int default t1.x);
+create or replace table t1 (x int, check (t1.x > 0));
+
+create or replace table t1 (x int, y int generated always as (test.t1.x));
+create or replace table t1 (x int, y int check (y > test.t1.x));
+create or replace table t1 (x int, y int default test.t1.x);
+create or replace table t1 (x int, check (test.t1.x > 0));
+
+drop tables t1, t2;
+
+--error ER_BAD_FIELD_ERROR
+create table t1 (x int, y int generated always as (test2.t1.x));
+--error ER_BAD_FIELD_ERROR
+create table t1 (x int, y int check (y > test2.t1.x));
+--error ER_BAD_FIELD_ERROR
+create table t1 (x int, y int default test2.t1.x);
+--error ER_BAD_FIELD_ERROR
+create table t1 (x int, check (test2.t1.x > 0));
View Lorry Controller Status