From 72ebb0aadad120b73be53d727880dd44aa6fe4ad Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 11 Jan 2008 20:10:54 +0300 Subject: Bug#29477: Not all fields of the target table were checked to have a default value when inserting into a view. The mysql_prepare_insert function checks all fields of the target table that directly or indirectly (through a view) are specified in the INSERT statement to have a default value. This check can be skipped if the INSERT statement doesn't mention any insert fields. In case of a view this allows fields that aren't mentioned in the view to bypass the check. Now fields of the target table are always checked to have a default value when insert goes into a view. mysql-test/t/view.test: Added a test case for the bug#29477: Not all fields of the target table were checked to have a default value when inserting into a view. mysql-test/r/view.result: Added a test case for the bug#29477: Not all fields of the target table were checked to have a default value when inserting into a view. sql/sql_insert.cc: Bug#29477: Not all fields of the target table were checked to have a default value when inserting into a view. Now fields of the target table are always checked to have a default value when insert goes into a view. --- mysql-test/r/view.result | 16 ++++++++++++++++ mysql-test/t/view.test | 14 ++++++++++++++ 2 files changed, 30 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 0e3d650c571..fb36304e562 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -3602,4 +3602,20 @@ DROP VIEW v1; DROP VIEW v2; DROP VIEW v3; DROP TABLE t1; +# +# Bug#29477: Not all fields of the target table were checked to have +# a default value when inserting into a view. +# +create table t1(f1 int, f2 int not null); +create view v1 as select f1 from t1; +insert into v1 values(1); +Warnings: +Warning 1423 Field of view 'test.v1' underlying table doesn't have a default value +set @old_mode=@@sql_mode; +set @@sql_mode=traditional; +insert into v1 values(1); +ERROR HY000: Field of view 'test.v1' underlying table doesn't have a default value +set @@sql_mode=@old_mode; +drop view v1; +drop table t1; End of 5.0 tests. diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 0faa8e7a785..340a34db5a1 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -3456,5 +3456,19 @@ DROP VIEW v2; DROP VIEW v3; DROP TABLE t1; +--echo # +--echo # Bug#29477: Not all fields of the target table were checked to have +--echo # a default value when inserting into a view. +--echo # +create table t1(f1 int, f2 int not null); +create view v1 as select f1 from t1; +insert into v1 values(1); +set @old_mode=@@sql_mode; +set @@sql_mode=traditional; +--error ER_NO_DEFAULT_FOR_VIEW_FIELD +insert into v1 values(1); +set @@sql_mode=@old_mode; +drop view v1; +drop table t1; --echo End of 5.0 tests. -- cgit v1.2.1 From c71a6428d822f60932e5cea0d395a06d777d2b9c Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 18 Jan 2008 22:50:36 +0300 Subject: BUG#33794 "MySQL crashes executing specific query": The problem occurred when one had a subquery that had an equality X=Y where Y referred to a named select list expression from the parent select. MySQL crashed when trying to use the X=Y equality for ref-based access. Fixed by allowing non-Item_field items in the described case. mysql-test/r/subselect.result: BUG#33794 "MySQL crashes executing specific query" - Testcase mysql-test/t/subselect.test: BUG#33794 "MySQL crashes executing specific query" - Testcase sql/sql_select.cc: BUG#33794 "MySQL crashes executing specific query" get_store_key() assumed that if it got a reference t.key=Item_outer_ref(Item_direct_ref(x)) then x was an Item_field object, which is not the case when one refers to a named select list expression out ot subquery. --- mysql-test/r/subselect.result | 48 +++++++++++++++++++++++++++++++++++++ mysql-test/t/subselect.test | 55 +++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 103 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 75df77b0790..05acbe6457a 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -4392,4 +4392,52 @@ select count(*) from t1 where f12 = count(*) 3 drop table t1,t2; +CREATE TABLE t4 ( +f7 varchar(32) collate utf8_bin NOT NULL default '', +f10 varchar(32) collate utf8_bin default NULL, +PRIMARY KEY (f7) +); +INSERT INTO t4 VALUES(1,1), (2,null); +CREATE TABLE t2 ( +f4 varchar(32) collate utf8_bin NOT NULL default '', +f2 varchar(50) collate utf8_bin default NULL, +f3 varchar(10) collate utf8_bin default NULL, +PRIMARY KEY (f4), +UNIQUE KEY uk1 (f2) +); +INSERT INTO t2 VALUES(1,1,null), (2,2,null); +CREATE TABLE t1 ( +f8 varchar(32) collate utf8_bin NOT NULL default '', +f1 varchar(10) collate utf8_bin default NULL, +f9 varchar(32) collate utf8_bin default NULL, +PRIMARY KEY (f8) +); +INSERT INTO t1 VALUES (1,'P',1), (2,'P',1), (3,'R',2); +CREATE TABLE t3 ( +f6 varchar(32) collate utf8_bin NOT NULL default '', +f5 varchar(50) collate utf8_bin default NULL, +PRIMARY KEY (f6) +); +INSERT INTO t3 VALUES (1,null), (2,null); +SELECT +IF(t1.f1 = 'R', a1.f2, t2.f2) AS a4, +IF(t1.f1 = 'R', a1.f3, t2.f3) AS f3, +SUM( +IF( +(SELECT VPC.f2 +FROM t2 VPC, t4 a2, t2 a3 +WHERE +VPC.f4 = a2.f10 AND a3.f2 = a4 +LIMIT 1) IS NULL, +0, +t3.f5 +) +) AS a6 +FROM +t2, t3, t1 JOIN t2 a1 ON t1.f9 = a1.f4 +GROUP BY a4; +a4 f3 a6 +1 NULL NULL +2 NULL NULL +DROP TABLE t1, t2; End of 5.0 tests. diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 88e4f683e9e..509f0969ca4 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -3252,4 +3252,59 @@ select count(*) from t1 where f12 = (select f22 from t2 where f22 = f12 order by f21 desc, f22, f23 limit 1); drop table t1,t2; + +# +# BUG#33794 "MySQL crashes executing specific query on specific dump" +# +CREATE TABLE t4 ( + f7 varchar(32) collate utf8_bin NOT NULL default '', + f10 varchar(32) collate utf8_bin default NULL, + PRIMARY KEY (f7) +); +INSERT INTO t4 VALUES(1,1), (2,null); + +CREATE TABLE t2 ( + f4 varchar(32) collate utf8_bin NOT NULL default '', + f2 varchar(50) collate utf8_bin default NULL, + f3 varchar(10) collate utf8_bin default NULL, + PRIMARY KEY (f4), + UNIQUE KEY uk1 (f2) +); +INSERT INTO t2 VALUES(1,1,null), (2,2,null); + +CREATE TABLE t1 ( + f8 varchar(32) collate utf8_bin NOT NULL default '', + f1 varchar(10) collate utf8_bin default NULL, + f9 varchar(32) collate utf8_bin default NULL, + PRIMARY KEY (f8) +); +INSERT INTO t1 VALUES (1,'P',1), (2,'P',1), (3,'R',2); + +CREATE TABLE t3 ( + f6 varchar(32) collate utf8_bin NOT NULL default '', + f5 varchar(50) collate utf8_bin default NULL, + PRIMARY KEY (f6) +); +INSERT INTO t3 VALUES (1,null), (2,null); + +SELECT + IF(t1.f1 = 'R', a1.f2, t2.f2) AS a4, + IF(t1.f1 = 'R', a1.f3, t2.f3) AS f3, + SUM( + IF( + (SELECT VPC.f2 + FROM t2 VPC, t4 a2, t2 a3 + WHERE + VPC.f4 = a2.f10 AND a3.f2 = a4 + LIMIT 1) IS NULL, + 0, + t3.f5 + ) + ) AS a6 +FROM + t2, t3, t1 JOIN t2 a1 ON t1.f9 = a1.f4 +GROUP BY a4; + +DROP TABLE t1, t2; --echo End of 5.0 tests. + -- cgit v1.2.1