bk commit into 5.0 tree (malff:1.2288) BUG#20028

[marc.alff]

Below is the list of changes that have just been committed into a local
5.0 repository of marcsql. When marcsql does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2006-10-19 11:39:51-07:00, malff@weblab.(none) +8 -0
  Bug#20028 (Function with select return no data)
  
  This patch reverts a change introduced by Bug 6951, which incorrectly
  set thd->abort_on_warning for stored procedures.
  
  As per internal discussions about the SQL_MODE=TRADITIONAL,
  the correct behavior is to *not* abort on warnings even inside an INSERT/UPDATE
  trigger.
  
  Tests for Stored Procedures, Stored Functions, Triggers involving SQL_MODE
  have been included or revised, to reflect the intended behavior.
  
  (reposting approved patch, to work around source control issues, no review needed)

  mysql-test/include/sp-vars.inc@stripped, 2006-10-19 11:39:48-07:00, malff@weblab.(none) +9 -0
    Tests for SQL_MODE='TRADITIONAL'

  mysql-test/r/sp-vars.result@stripped, 2006-10-19 11:39:48-07:00, malff@weblab.(none) +23 -0
    Tests for SQL_MODE='TRADITIONAL'

  mysql-test/r/sp.result@stripped, 2006-10-19 11:39:48-07:00, malff@weblab.(none) +129 -0
    Tests for SQL_MODE='TRADITIONAL'

  mysql-test/r/trigger.result@stripped, 2006-10-19 11:39:48-07:00, malff@weblab.(none) +60 -4
    Tests for SQL_MODE='TRADITIONAL'

  mysql-test/t/sp-vars.test@stripped, 2006-10-19 11:39:48-07:00, malff@weblab.(none) +10 -0
    Tests for SQL_MODE='TRADITIONAL'

  mysql-test/t/sp.test@stripped, 2006-10-19 11:39:48-07:00, malff@weblab.(none) +131 -0
    Tests for SQL_MODE='TRADITIONAL'

  mysql-test/t/trigger.test@stripped, 2006-10-19 11:39:48-07:00, malff@weblab.(none) +61 -2
    Tests for SQL_MODE='TRADITIONAL'

  sql/sp_head.cc@stripped, 2006-10-19 11:39:48-07:00, malff@weblab.(none) +1 -2
    For SQL_MODE='TRADITIONAL',
    thd->abort_on_warning should be set only when assigning a *column*

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	malff
# Host:	weblab.(none)
# Root:	/home/marcsql/TREE/mysql-5.0-20028_again

--- 1.1/mysql-test/include/sp-vars.inc	2006-10-19 11:40:00 -07:00
+++ 1.2/mysql-test/include/sp-vars.inc	2006-10-19 11:40:00 -07:00
@@ -119,4 +119,13 @@ END|
 
 ---------------------------------------------------------------------------
 
+CREATE FUNCTION sp_vars_div_zero() RETURNS INTEGER
+BEGIN
+  DECLARE div_zero INTEGER;
+  SELECT 1/0 INTO div_zero;
+  RETURN div_zero;
+END|
+
+---------------------------------------------------------------------------
+
 delimiter ;|

--- 1.4/mysql-test/r/sp-vars.result	2006-10-19 11:40:00 -07:00
+++ 1.5/mysql-test/r/sp-vars.result	2006-10-19 11:40:00 -07:00
@@ -4,6 +4,7 @@ DROP FUNCTION IF EXISTS sp_vars_check_re
 DROP FUNCTION IF EXISTS sp_vars_check_ret2;
 DROP FUNCTION IF EXISTS sp_vars_check_ret3;
 DROP FUNCTION IF EXISTS sp_vars_check_ret4;
+DROP FUNCTION IF EXISTS sp_vars_div_zero;
 SET @@sql_mode = 'ansi';
 CREATE PROCEDURE sp_vars_check_dflt()
 BEGIN
@@ -88,6 +89,12 @@ CREATE FUNCTION sp_vars_check_ret4() RET
 BEGIN
 RETURN 12 * 10 + 34 + 0.1234;
 END|
+CREATE FUNCTION sp_vars_div_zero() RETURNS INTEGER
+BEGIN
+DECLARE div_zero INTEGER;
+SELECT 1/0 INTO div_zero;
+RETURN div_zero;
+END|
 
 ---------------------------------------------------------------
 Calling the routines, created in ANSI mode.
@@ -172,6 +179,9 @@ sp_vars_check_ret4()
 154.12
 Warnings:
 Note	1265	Data truncated for column 'sp_vars_check_ret4()' at row 1
+SELECT sp_vars_div_zero();
+sp_vars_div_zero()
+NULL
 SET @@sql_mode = 'traditional';
 
 ---------------------------------------------------------------
@@ -257,12 +267,16 @@ sp_vars_check_ret4()
 154.12
 Warnings:
 Note	1265	Data truncated for column 'sp_vars_check_ret4()' at row 1
+SELECT sp_vars_div_zero();
+sp_vars_div_zero()
+NULL
 DROP PROCEDURE sp_vars_check_dflt;
 DROP PROCEDURE sp_vars_check_assignment;
 DROP FUNCTION sp_vars_check_ret1;
 DROP FUNCTION sp_vars_check_ret2;
 DROP FUNCTION sp_vars_check_ret3;
 DROP FUNCTION sp_vars_check_ret4;
+DROP FUNCTION sp_vars_div_zero;
 CREATE PROCEDURE sp_vars_check_dflt()
 BEGIN
 DECLARE v1 TINYINT DEFAULT 1e200;
@@ -346,6 +360,12 @@ CREATE FUNCTION sp_vars_check_ret4() RET
 BEGIN
 RETURN 12 * 10 + 34 + 0.1234;
 END|
+CREATE FUNCTION sp_vars_div_zero() RETURNS INTEGER
+BEGIN
+DECLARE div_zero INTEGER;
+SELECT 1/0 INTO div_zero;
+RETURN div_zero;
+END|
 
 ---------------------------------------------------------------
 Calling the routines, created in TRADITIONAL mode.
@@ -366,6 +386,8 @@ sp_vars_check_ret4()
 154.12
 Warnings:
 Note	1265	Data truncated for column 'sp_vars_check_ret4()' at row 1
+SELECT sp_vars_div_zero();
+ERROR 22012: Division by 0
 SET @@sql_mode = 'ansi';
 DROP PROCEDURE sp_vars_check_dflt;
 DROP PROCEDURE sp_vars_check_assignment;
@@ -373,6 +395,7 @@ DROP FUNCTION sp_vars_check_ret1;
 DROP FUNCTION sp_vars_check_ret2;
 DROP FUNCTION sp_vars_check_ret3;
 DROP FUNCTION sp_vars_check_ret4;
+DROP FUNCTION sp_vars_div_zero;
 
 ---------------------------------------------------------------
 BIT data type tests

--- 1.3/mysql-test/t/sp-vars.test	2006-10-19 11:40:00 -07:00
+++ 1.4/mysql-test/t/sp-vars.test	2006-10-19 11:40:00 -07:00
@@ -15,6 +15,7 @@ DROP FUNCTION IF EXISTS sp_vars_check_re
 DROP FUNCTION IF EXISTS sp_vars_check_ret2;
 DROP FUNCTION IF EXISTS sp_vars_check_ret3;
 DROP FUNCTION IF EXISTS sp_vars_check_ret4;
+DROP FUNCTION IF EXISTS sp_vars_div_zero;
 
 --enable_warnings
 
@@ -49,6 +50,8 @@ SELECT sp_vars_check_ret3();
 
 SELECT sp_vars_check_ret4();
 
+SELECT sp_vars_div_zero();
+
 # Check that changing sql_mode after creating a store procedure does not
 # matter.
 
@@ -72,6 +75,8 @@ SELECT sp_vars_check_ret3();
 
 SELECT sp_vars_check_ret4();
 
+SELECT sp_vars_div_zero();
+
 # Create the procedure in TRADITIONAL mode. Check that error will be thrown on
 # execution.
 
@@ -81,6 +86,7 @@ DROP FUNCTION sp_vars_check_ret1;
 DROP FUNCTION sp_vars_check_ret2;
 DROP FUNCTION sp_vars_check_ret3;
 DROP FUNCTION sp_vars_check_ret4;
+DROP FUNCTION sp_vars_div_zero;
 
 --source include/sp-vars.inc
 
@@ -110,6 +116,9 @@ SELECT sp_vars_check_ret3();
 
 SELECT sp_vars_check_ret4();
 
+--error ER_DIVISION_BY_ZERO
+SELECT sp_vars_div_zero();
+
 SET @@sql_mode = 'ansi';
 
 #
@@ -122,6 +131,7 @@ DROP FUNCTION sp_vars_check_ret1;
 DROP FUNCTION sp_vars_check_ret2;
 DROP FUNCTION sp_vars_check_ret3;
 DROP FUNCTION sp_vars_check_ret4;
+DROP FUNCTION sp_vars_div_zero;
 
 ###########################################################################
 #

--- 1.47/mysql-test/r/trigger.result	2006-10-19 11:40:00 -07:00
+++ 1.48/mysql-test/r/trigger.result	2006-10-19 11:40:00 -07:00
@@ -1073,10 +1073,11 @@ SELECT @x;
 NULL
 SET @x=2;
 UPDATE t1 SET i1 = @x;
-ERROR 22012: Division by 0
+Warnings:
+Error	1365	Division by 0
 SELECT @x;
 @x
-2
+NULL
 SET SQL_MODE='';
 SET @x=3;
 INSERT INTO t1 VALUES (@x);
@@ -1085,10 +1086,12 @@ SELECT @x;
 NULL
 SET @x=4;
 UPDATE t1 SET i1 = @x;
-ERROR 22012: Division by 0
+Warnings:
+Error	1365	Division by 0
+Error	1365	Division by 0
 SELECT @x;
 @x
-4
+NULL
 SET @@sql_mode=@save_sql_mode;
 DROP TRIGGER t1_ai;
 DROP TRIGGER t1_au;
@@ -1173,6 +1176,59 @@ TRIGGER t2_bi BEFORE INSERT ON t2 FOR EA
 ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY'
is too long for host name (should be no longer than 60)
 DROP TABLE t1;
 DROP TABLE t2;
+drop table if exists t1;
+drop table if exists t2;
+drop table if exists t3;
+drop table if exists t4;
+SET @save_sql_mode=@@sql_mode;
+SET sql_mode='TRADITIONAL'|
+create table t1 (id int(10) not null primary key, v int(10) )|
+create table t2 (id int(10) not null primary key, v int(10) )|
+create table t3 (id int(10) not null primary key, v int(10) )|
+create table t4 (c int)|
+create trigger t4_bi before insert on t4 for each row set @t4_bi_called:=1|
+create trigger t4_bu before update on t4 for each row set @t4_bu_called:=1|
+insert into t1 values(10, 10)|
+set @a:=1/0|
+Warnings:
+Error	1365	Division by 0
+select 1/0 from t1|
+1/0
+NULL
+Warnings:
+Error	1365	Division by 0
+create trigger t1_bi before insert on t1 for each row set @a:=1/0|
+insert into t1 values(20, 20)|
+Warnings:
+Error	1365	Division by 0
+drop trigger t1_bi|
+create trigger t1_bi before insert on t1 for each row
+begin
+insert into t2 values (new.id, new.v);
+update t2 set v=v+1 where id= new.id;
+replace t3 values (new.id, 0);
+update t2, t3 set t2.v=new.v, t3.v=new.v where t2.id=t3.id;
+create temporary table t5 select * from t1;
+delete from t5;
+insert into t5 select * from t1;
+insert into t4 values (0);
+set @check= (select count(*) from t5);
+update t4 set c= @check;
+drop temporary table t5;
+set @a:=1/0;
+end|
+set @check=0, @t4_bi_called=0, @t4_bu_called=0|
+insert into t1 values(30, 30)|
+Warnings:
+Error	1365	Division by 0
+select @check, @t4_bi_called, @t4_bu_called|
+@check	@t4_bi_called	@t4_bu_called
+2	1	1
+SET @@sql_mode=@save_sql_mode;
+drop table t1;
+drop table t2;
+drop table t3;
+drop table t4;
 drop table if exists t1;
 create table t1 (i int, j int key);
 insert into t1 values (1,1), (2,2), (3,3);

--- 1.53/mysql-test/t/trigger.test	2006-10-19 11:40:00 -07:00
+++ 1.54/mysql-test/t/trigger.test	2006-10-19 11:40:00 -07:00
@@ -1274,7 +1274,6 @@ INSERT INTO t1 VALUES (@x);
 SELECT @x;
 
 SET @x=2;
---error ER_DIVISION_BY_ZERO
 UPDATE t1 SET i1 = @x;
 SELECT @x;
 
@@ -1285,7 +1284,6 @@ INSERT INTO t1 VALUES (@x);
 SELECT @x;
 
 SET @x=4;
---error ER_DIVISION_BY_ZERO
 UPDATE t1 SET i1 = @x;
 SELECT @x;
 
@@ -1420,6 +1418,67 @@ CREATE DEFINER=some_user_name@1234567890
 DROP TABLE t1;
 DROP TABLE t2;
 
+#
+# Bug#20028 Function with select return no data
+# 
+
+--disable_warnings
+drop table if exists t1;
+drop table if exists t2;
+drop table if exists t3;
+drop table if exists t4;
+--enable_warnings
+
+SET @save_sql_mode=@@sql_mode;
+
+delimiter |;
+SET sql_mode='TRADITIONAL'|
+create table t1 (id int(10) not null primary key, v int(10) )|
+create table t2 (id int(10) not null primary key, v int(10) )|
+create table t3 (id int(10) not null primary key, v int(10) )|
+create table t4 (c int)|
+
+create trigger t4_bi before insert on t4 for each row set @t4_bi_called:=1|
+create trigger t4_bu before update on t4 for each row set @t4_bu_called:=1|
+
+insert into t1 values(10, 10)|
+set @a:=1/0|
+select 1/0 from t1|
+
+create trigger t1_bi before insert on t1 for each row set @a:=1/0|
+
+insert into t1 values(20, 20)|
+
+drop trigger t1_bi|
+create trigger t1_bi before insert on t1 for each row
+begin
+  insert into t2 values (new.id, new.v);
+  update t2 set v=v+1 where id= new.id;
+  replace t3 values (new.id, 0);
+  update t2, t3 set t2.v=new.v, t3.v=new.v where t2.id=t3.id;
+  create temporary table t5 select * from t1;
+  delete from t5;
+  insert into t5 select * from t1;
+  insert into t4 values (0);
+  set @check= (select count(*) from t5);
+  update t4 set c= @check;
+  drop temporary table t5;
+
+  set @a:=1/0;
+end|
+
+set @check=0, @t4_bi_called=0, @t4_bu_called=0|
+insert into t1 values(30, 30)|
+select @check, @t4_bi_called, @t4_bu_called|
+
+delimiter ;|
+
+SET @@sql_mode=@save_sql_mode;
+
+drop table t1;
+drop table t2;
+drop table t3;
+drop table t4;
 
 #
 # Bug#20670 "UPDATE using key and invoking trigger that modifies

--- 1.214/mysql-test/r/sp.result	2006-10-19 11:40:00 -07:00
+++ 1.215/mysql-test/r/sp.result	2006-10-19 11:40:00 -07:00
@@ -5470,6 +5470,135 @@ CAD
 CHF
 DROP FUNCTION bug21493|
 DROP TABLE t3,t4|
+drop function if exists func_20028_a|
+drop function if exists func_20028_b|
+drop function if exists func_20028_c|
+drop procedure if exists proc_20028_a|
+drop procedure if exists proc_20028_b|
+drop procedure if exists proc_20028_c|
+drop table if exists table_20028|
+create table table_20028 (i int)|
+SET @save_sql_mode=@@sql_mode|
+SET sql_mode=''|
+create function func_20028_a() returns integer
+begin
+declare temp integer;
+select i into temp from table_20028 limit 1;
+return ifnull(temp, 0);
+end|
+create function func_20028_b() returns integer
+begin
+return func_20028_a();
+end|
+create function func_20028_c() returns integer
+begin
+declare div_zero integer;
+set SQL_MODE='TRADITIONAL';
+select 1/0 into div_zero;
+return div_zero;
+end|
+create procedure proc_20028_a()
+begin
+declare temp integer;
+select i into temp from table_20028 limit 1;
+end|
+create procedure proc_20028_b()
+begin
+call proc_20028_a();
+end|
+create procedure proc_20028_c()
+begin
+declare div_zero integer;
+set SQL_MODE='TRADITIONAL';
+select 1/0 into div_zero;
+end|
+select func_20028_a()|
+func_20028_a()
+0
+Warnings:
+Warning	1329	No data - zero rows fetched, selected, or processed
+select func_20028_b()|
+func_20028_b()
+0
+Warnings:
+Warning	1329	No data - zero rows fetched, selected, or processed
+select func_20028_c()|
+ERROR 22012: Division by 0
+call proc_20028_a()|
+Warnings:
+Warning	1329	No data - zero rows fetched, selected, or processed
+call proc_20028_b()|
+Warnings:
+Warning	1329	No data - zero rows fetched, selected, or processed
+call proc_20028_c()|
+ERROR 22012: Division by 0
+SET sql_mode='TRADITIONAL'|
+drop function func_20028_a|
+drop function func_20028_b|
+drop function func_20028_c|
+drop procedure proc_20028_a|
+drop procedure proc_20028_b|
+drop procedure proc_20028_c|
+create function func_20028_a() returns integer
+begin
+declare temp integer;
+select i into temp from table_20028 limit 1;
+return ifnull(temp, 0);
+end|
+create function func_20028_b() returns integer
+begin
+return func_20028_a();
+end|
+create function func_20028_c() returns integer
+begin
+declare div_zero integer;
+set SQL_MODE='';
+select 1/0 into div_zero;
+return div_zero;
+end|
+create procedure proc_20028_a()
+begin
+declare temp integer;
+select i into temp from table_20028 limit 1;
+end|
+create procedure proc_20028_b()
+begin
+call proc_20028_a();
+end|
+create procedure proc_20028_c()
+begin
+declare div_zero integer;
+set SQL_MODE='';
+select 1/0 into div_zero;
+end|
+select func_20028_a()|
+func_20028_a()
+0
+Warnings:
+Warning	1329	No data - zero rows fetched, selected, or processed
+select func_20028_b()|
+func_20028_b()
+0
+Warnings:
+Warning	1329	No data - zero rows fetched, selected, or processed
+select func_20028_c()|
+func_20028_c()
+NULL
+call proc_20028_a()|
+Warnings:
+Warning	1329	No data - zero rows fetched, selected, or processed
+call proc_20028_b()|
+Warnings:
+Warning	1329	No data - zero rows fetched, selected, or processed
+call proc_20028_c()|
+SET @@sql_mode=@save_sql_mode|
+drop function func_20028_a|
+drop function func_20028_b|
+drop function func_20028_c|
+drop procedure proc_20028_a|
+drop procedure proc_20028_b|
+drop procedure proc_20028_c|
+drop table table_20028|
 drop procedure if exists proc_21462_a|
 drop procedure if exists proc_21462_b|
 create procedure proc_21462_a()

--- 1.203/mysql-test/t/sp.test	2006-10-19 11:40:00 -07:00
+++ 1.204/mysql-test/t/sp.test	2006-10-19 11:40:00 -07:00
@@ -6421,6 +6421,137 @@ DROP FUNCTION bug21493|
 DROP TABLE t3,t4|
 
 #
+# Bug#20028 Function with select return no data
+#
+
+--disable_warnings
+drop function if exists func_20028_a|
+drop function if exists func_20028_b|
+drop function if exists func_20028_c|
+drop procedure if exists proc_20028_a|
+drop procedure if exists proc_20028_b|
+drop procedure if exists proc_20028_c|
+drop table if exists table_20028|
+--enable_warnings
+
+create table table_20028 (i int)|
+
+SET @save_sql_mode=@@sql_mode|
+
+SET sql_mode=''|
+
+create function func_20028_a() returns integer
+begin
+  declare temp integer;
+  select i into temp from table_20028 limit 1;
+  return ifnull(temp, 0);
+end|
+
+create function func_20028_b() returns integer
+begin
+  return func_20028_a();
+end|
+
+create function func_20028_c() returns integer
+begin
+  declare div_zero integer;
+  set SQL_MODE='TRADITIONAL';
+  select 1/0 into div_zero;
+  return div_zero;
+end|
+
+create procedure proc_20028_a()
+begin
+  declare temp integer;
+  select i into temp from table_20028 limit 1;
+end|
+
+create procedure proc_20028_b()
+begin
+  call proc_20028_a();
+end|
+
+create procedure proc_20028_c()
+begin
+  declare div_zero integer;
+  set SQL_MODE='TRADITIONAL';
+  select 1/0 into div_zero;
+end|
+
+select func_20028_a()|
+select func_20028_b()|
+--error ER_DIVISION_BY_ZERO
+select func_20028_c()|
+call proc_20028_a()|
+call proc_20028_b()|
+--error ER_DIVISION_BY_ZERO
+call proc_20028_c()|
+
+SET sql_mode='TRADITIONAL'|
+
+drop function func_20028_a|
+drop function func_20028_b|
+drop function func_20028_c|
+drop procedure proc_20028_a|
+drop procedure proc_20028_b|
+drop procedure proc_20028_c|
+
+create function func_20028_a() returns integer
+begin
+  declare temp integer;
+  select i into temp from table_20028 limit 1;
+  return ifnull(temp, 0);
+end|
+
+create function func_20028_b() returns integer
+begin
+  return func_20028_a();
+end|
+
+create function func_20028_c() returns integer
+begin
+  declare div_zero integer;
+  set SQL_MODE='';
+  select 1/0 into div_zero;
+  return div_zero;
+end|
+
+create procedure proc_20028_a()
+begin
+  declare temp integer;
+  select i into temp from table_20028 limit 1;
+end|
+
+create procedure proc_20028_b()
+begin
+  call proc_20028_a();
+end|
+
+create procedure proc_20028_c()
+begin
+  declare div_zero integer;
+  set SQL_MODE='';
+  select 1/0 into div_zero;
+end|
+
+select func_20028_a()|
+select func_20028_b()|
+select func_20028_c()|
+call proc_20028_a()|
+call proc_20028_b()|
+call proc_20028_c()|
+
+SET @@sql_mode=@save_sql_mode|
+
+drop function func_20028_a|
+drop function func_20028_b|
+drop function func_20028_c|
+drop procedure proc_20028_a|
+drop procedure proc_20028_b|
+drop procedure proc_20028_c|
+drop table table_20028|
+
+#
 # Bug#21462 Stored procedures with no arguments require parenthesis
 #
 

--- 1.224/sql/sp_head.cc	2006-10-19 11:40:00 -07:00
+++ 1.225/sql/sp_head.cc	2006-10-19 11:40:00 -07:00
@@ -991,8 +991,7 @@ sp_head::execute(THD *thd)
   save_sql_mode= thd->variables.sql_mode;
   thd->variables.sql_mode= m_sql_mode;
   save_abort_on_warning= thd->abort_on_warning;
-  thd->abort_on_warning=
-    (m_sql_mode & (MODE_STRICT_TRANS_TABLES | MODE_STRICT_ALL_TABLES));
+  thd->abort_on_warning= 0;
 
   /*
     It is also more efficient to save/restore current thd->lex once when