midenok / stuff-dbms Goto Github PK

drop function if exists inline_var_test;
delimiter ;;
create function inline_var_test()
returns int unsigned
deterministic
modifies sql data
begin
  declare var int unsigned;
  update table set col1 = (var := col2), col2 = 0 where id = 1;
  return var;
end;;
delimiter ;

drop function if exists inline_var_test;
delimiter ;;
create function inline_var_test()
returns int unsigned
deterministic
modifies sql data
begin
  update table set col1 = (@var := col2), col2 = 0 where id = 1;
  return @var;
end;;
delimiter ;

drop function if exists type_test;
delimiter ;;
create function type_test(n int unsigned)
returns int unsigned
deterministic
modifies sql data
begin
  if n = -1 then
    return 1;
  end if;
  return n;
end;;
delimiter ;

mysql> select type_test(-1);
+---------------+
| type_test(-1) |
+---------------+
|             0 |
+---------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'n' at row 1 |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select type_test(4294967295);
+-----------------------+
| type_test(4294967295) |
+-----------------------+
|            4294967295 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select type_test(42949672950);
+------------------------+
| type_test(42949672950) |
+------------------------+
|             4294967295 |
+------------------------+
1 row in set, 1 warning (0.00 sec)

mysql < src/storage/spider/scripts/install_spider.sql