Bug #10976 Stored procedures: "warnings" aren't caught by SQLWARNING

• Submitted: 30 May 2005 21:04
• Modified: 20 Jan 2006 9:19
• Reporter: Peter Gulutzan
• Status: Duplicate
• Category: MySQL Server: Stored Routines
• Severity: S3 (Non-critical)
• Version: 5.0.7-beta-debug, 5.0.14-rc
• OS: Linux (SUSE 9.2)
• Assigned to: Assigned Account
• CPU Architecture: Any

[30 May 2005 21:04] Peter Gulutzan

Description:
An SQLWARNING handler doesn't catch a decimal overflow which is supposedly a warning.

Incidentally, in traditional mode the overflow should cause an error. It doesn't.
But I believe there's some other bug report for that.

An SQLEXCEPTION handler does catch the condition.
In other words, the problem isn't that handlers aren't working,
the problem is that the conditions are "warnings" outside
stored procedures, but they're "errors" inside stored procedures.

How to repeat:
mysql> create table tno (s1 decimal(38));
Query OK, 0 rows affected (0.07 sec)

mysql> insert into tno values (12345678901234567890123456789012345678),(12345678901234567890123456789012345678);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select variance(s1) from tno;
+----------------------------------------------------------------------------------+
| variance(s1)                                                                     |
+----------------------------------------------------------------------------------+
| 148148146814814804879621854003514651080099786588925528265859187877110349824.0000 |
+----------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> delimiter //
mysql> create procedure pno () begin declare continue handler for sqlwarning select 'x'; select variance(s1) from tno; end//
Query OK, 0 rows affected (0.02 sec)

mysql> call pno()//
+----------------------------------------------------------------------------------+
| variance(s1)                                                                     |
+----------------------------------------------------------------------------------+
| 148148146814814804879621854003514651080099786588925528265859187877110349824.0000 |
+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

/* notice that the "select 'x'" didn't happen */

[30 May 2005 21:25] MySQL Verification Team

Thank you for the bug report.

[4 Aug 2005 14:14] Anders Karlsson

This should not be assigned to me.

[16 Sep 2005 16:36] Valeriy Kravchuk

Reverified on 5.0.14-rc. Still exists:

mysql> create table tno (s1 decimal(38));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tno values
    -> (12345678901234567890123456789012345678),(1234567890123456789012345678901
2345678
    -> );
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select variance(s1) from tno;
+-------------------------------------------------------------------------------
---+
| variance(s1)
   |
+-------------------------------------------------------------------------------
---+
| 148148146814814804879621854003514651080099786588925528265859187877110349824.00
00 |
+-------------------------------------------------------------------------------
---+
1 row in set, 1 warning (0.01 sec)

mysql> show warnings;
+-------+------+---------------------------------------+
| Level | Code | Message                               |
+-------+------+---------------------------------------+
| Error | 1292 | Truncated incorrect DECIMAL value: '' |
+-------+------+---------------------------------------+
1 row in set (0.00 sec)

mysql> delimiter //
mysql> create procedure pno () begin declare continue handler for sqlwarning
    -> select 'x'; select variance(s1) from tno; end//
Query OK, 0 rows affected (0.00 sec)

mysql> call pno()//
+-------------------------------------------------------------------------------
---+
| variance(s1)
   |
+-------------------------------------------------------------------------------
---+
| 148148146814814804879621854003514651080099786588925528265859187877110349824.00
00 |
+-------------------------------------------------------------------------------
---+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select version()//
+-----------+
| version() |
+-----------+
| 5.0.14-rc |
+-----------+
1 row in set (0.00 sec)

[18 Jan 2006 20:50] Konstantin Osipov

The test case:

create table t1 (s1 decimal(38));
insert into t1 values (12345678901234567890123456789012345678),                                       (12345678901234567890123456789012345678);
select variance(s1) from t1;
show warnings;
delimiter //
drop procedure if exists pno//
create procedure pno() begin declare continue handler for sqlwarning
select 'x'; select variance(s1) from t1; end//
delimiter ;
call pno();
show warnings;
select version();

[20 Jan 2006 9:19] Per-Erik Martin

This is a duplicate of BUG#9857: Stored procedures: handler for sqlwarning ignored
(which is "to be fixed later").