type_newdecimal.test

来自「视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.」· TEST 代码 · 共 1,201 行 · 第 1/3 页

select truncate(5678.123451,-4);
#-- should return 0
#
select truncate(-5678.123451,0);
#-- should return -5678
#
select truncate(-5678.123451,1);
#-- should return -5678.1
#
select truncate(-5678.123451,2);
#-- should return -5678.12
#
select truncate(-5678.123451,3);
#-- should return -5678.123
#
select truncate(-5678.123451,4);
#-- should return -5678.1234
#
select truncate(-5678.123451,5);
#-- should return -5678.12345
#
select truncate(-5678.123451,6);
#-- should return -5678.123451
#
select truncate(-5678.123451,-1);
#-- should return -5670
#
select truncate(-5678.123451,-2);
#-- should return -5600
#
select truncate(-5678.123451,-3);
#-- should return -5000
#
select truncate(-5678.123451,-4);
#-- should return 0
#
#drop table if exists wl1612_4;
create table wl1612_4 (col1 int, col2 decimal(30,25), col3 numeric(30,25));
#
insert into wl1612_4 values(1,0.0123456789012345678912345,0.0123456789012345678912345);
#
select col2/9999999999 from wl1612_4 where col1=1;
#
select col3/9999999999 from wl1612_4 where col1=1;
#
select 9999999999/col2 from wl1612_4 where col1=1;
#
select 9999999999/col3 from wl1612_4 where col1=1;
#
select col2*9999999999 from wl1612_4 where col1=1;
#
select col3*9999999999 from wl1612_4 where col1=1;
#
insert into wl1612_4 values(2,55555.0123456789012345678912345,55555.0123456789012345678912345);
#
select col2/9999999999 from wl1612_4 where col1=2;
#
select col3/9999999999 from wl1612_4 where col1=2;
#
select 9999999999/col2 from wl1612_4 where col1=2;
#
select 9999999999/col3 from wl1612_4 where col1=2;
#
select col2*9999999999 from wl1612_4 where col1=2;
#
select col3*9999999999 from wl1612_4 where col1=2;
#
drop table wl1612_4;
#
#
#
#
#-- Additional tests for WL#1612 Precision math
#
#-- Comparisons should show that a number is
#-- exactly equal to its value as displayed.
#
set sql_mode='';
#
select 23.4 + (-41.7), 23.4 - (41.7) = -18.3;
#
select -18.3=-18.3;
#
select 18.3=18.3;
#
select -18.3=18.3;
#
select 0.8 = 0.7 + 0.1;

#
#-- It should be possible to define a column
#-- with up to 38 digits precision either before
#-- or after the decimal point. Any number which
#-- is inserted, if it's within the range, should
#-- be exactly the same as the number that gets
#-- selected.
#
drop table if exists t1;
#
create table t1 (col1 decimal(38));
#
insert into t1 values (12345678901234567890123456789012345678);
#
select * from t1;
#-- should return:
#+----------------------------------------+
#| col1                                   |
#+----------------------------------------+
#| 12345678901234567890123456789012345678 |
#+----------------------------------------+
#
#drop table t1;
#
#create table t1 (col1 decimal(38,38));
#
#insert into t1 values (.12345678901234567890123456789012345678);
#
#select * from t1;
#-- should return:
#+------------------------------------------+
#| col1                                     |
#+------------------------------------------+
#| 0.12345678901234567890123456789012345678 |
#+------------------------------------------+
#
drop table t1;
#
create table t1 (col1 decimal(31,30));
#
insert into t1 values (0.00000000001);
#
select * from t1;
#-- should return:
#+---------------+
#|col1           |
#+---------------+
#| 0.00000000001 |
#+---------------+
#
drop table t1;
#
#-- The usual arithmetic operators / * + - should work.
#
#select 77777777777777777777777777777777777777 / 7777777777777777777777777777777777777 = 10;
#-- should return 0 (false).
#
select 7777777777777777777777777777777777777 * 10;
#-- should return 77777777777777777777777777777777777770
#
select .7777777777777777777777777777777777777 *
       1000000000000000000;
#-- should return 777777777777777777.7777777777777777777 
#
select .7777777777777777777777777777777777777 - 0.1;
#-- should return .6777777777777777777777777777777777777 
#
select .343434343434343434 + .343434343434343434;
#-- should return .686868686868686868 
#
#-- 5. All arithmetic functions mentioned in the
#MySQL Reference Manual should work.
#
select abs(9999999999999999999999);
#-- should return 9999999999999999999999
#
select abs(-9999999999999999999999);
#-- should return 9999999999999999999999
#
select ceiling(999999999999999999);
select ceiling(99999999999999999999);
#-- should return 99999999999999999999
#
select ceiling(9.9999999999999999999);
#-- should return 10
#
select ceiling(-9.9999999999999999999);
#-- should return 9
#
select floor(999999999999999999);
select floor(9999999999999999999999);
#-- should return 9999999999999999999999
#
select floor(9.999999999999999999999);
#-- should return 9
#
select floor(-9.999999999999999999999);
#-- should return -10
#
select floor(-999999999999999999999.999);
select ceiling(999999999999999999999.999);
#
#
select 99999999999999999999999999999999999999 mod 3;
#-- should return 0
#
select round(99999999999999999.999);
#-- should return 100000000000000000
#
select round(-99999999999999999.999);
#-- should return -100000000000000000
#
select round(99999999999999999.999,3);
#-- should return 100000000000000000.000
#
select round(-99999999999999999.999,3);
#-- should return -100000000000000000.000
#
select truncate(99999999999999999999999999999999999999,31);
#-- should return 99999999999999999999999999999999999999.000
#
select truncate(99.999999999999999999999999999999999999,31);
#-- should return 99.9999999999999999999999999999999
#
select truncate(99999999999999999999999999999999999999,-31);
# should return 90000000000000000000000000000000
#
#-- 6. Set functions (AVG, SUM, COUNT) should work.
#
#drop table if exists t1;
#
#delimiter //
#
#create procedure p1 () begin 
#  declare v1 int default 1; declare v2 decimal(0,38) default 0; 
#  create table t1 (col1 decimal(0,38)); 
#  while v1 <= 10000 do 
#    insert into t1 values (-v2); 
#    set v2 = v2 + 0.00000000000000000000000000000000000001; 
#    set v1 = v1 + 1; 
#  end while;
#  select avg(col1),sum(col1),count(col1) from t1; end;//
#
#call p1()//
#-- should return
#   -- avg(col1)=0.00000000000000000000000000000000000001 added 10,000 times, then divided by 10,000
#   -- sum(col1)=0.00000000000000000000000000000000000001 added 10,000 times
#
#   -- count(col1)=10000
#
#delimiter ;//
#
#drop procedure p1;
#drop table t1;
#
#-- When I say DECIMAL(x) I should be able to store x digits.
#-- If I can't, there should be an error at CREATE time.
#
#drop table if exists t1;
#
#create table t1 (col1 decimal(254));
#-- should return SQLSTATE 22003 numeric value out of range 
#
#-- When I say DECIMAL(x,y) there should be no silent change of precision or
#-- scale.
#
#drop table if exists t1;
#
#create table t1 (col1 decimal(0,38));
#
#show create table t1;
#-- should return:
#+-------+--------------------------------+
#| Table | Create Table                   |
#+-------+--------------------------------+
#| t9    | CREATE TABLE `t1` (            |
#|`s1` decimal(0,38) default NULL         |
#| ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
#+-------+--------------------------------+
#
#drop table t1;
#
#-- From WL#1612 "The future" point 2.:
#-- The standard requires that we treat numbers like "0.5" as
#-- DECIMAL or NUMERIC, not as floating-point.
#
#drop table if exists t1;
#
#
create table t1 as select 0.5;
#
show create table t1;
#-- should return:
#+-------+-----------------------------------+
#| Table | Create Table                      |
#+-------+-----------------------------------+
#| t7 | CREATE TABLE `t1` (                  |
#| `0.5` decimal(3,1) NOT NULL default '0.0' |
#| ) ENGINE=MyISAM DEFAULT CHARSET=latin1    |
#+-------+-----------------------------------+
#
drop table t1;
#
#-- From WL#1612, "The future", point 3.: We have to start rounding correctly.
#
select round(1.5),round(2.5);
#-- should return:
#+------------+------------+
#| round(1.5) | round(2.5) |
#+------------+------------+
#| 2          | 3          |
#+------------+------------+
#
#-- From WL#1612, "The future", point 4.: "select 0.07 * 0.07;" should return 0.0049, not 0.00.
#-- If operand#1 has scale X and operand#2 has scale Y, then result should have scale (X+Y).
#
select 0.07 * 0.07;
#-- should return 0.0049
#
#-- From WL#1612, "The future", point 5.: Division by zero is an error.
#
set sql_mode='traditional';
#
select 1E-500 = 0;
#-- should return 1 (true).
#
select 1 / 1E-500;
#
#-- should return SQLSTATE 22012 division by zero.
#
select 1 / 0;
#-- should return SQLSTATE 22012 division by zero.
#
#+-------+
#| 1 / 0 |
#+-------+
#| NULL  |
#+-------+
#1 row in set, 1 warning (0.00 sec)
#
#-- From WL#1612 "The future" point 6.: Overflow is an error.
#
#set sql_mode='';
#
#select 1E300 * 1E300;
#-- should return SQLSTATE 22003 numeric value out of range 
#
#select 18446744073709551615 + 1;
#-- should return SQLSTATE 22003 numeric value out of range 
#
#-- 14. From WL#1612 "The future" point 7.:
#-- If s1 is INTEGER and s2 is DECIMAL, then
#-- "create table tk7 as select avg(s1),avg(s2) from tk;"
#-- should not create a table with "double(17,4)" data types.
#-- The result of AVG must still be exact numeric, with a
#-- scale the same or greater than the operand's scale.
#-- The result of SUM must still be exact numeric, with
#-- a scale the same as the operand's scale.
#
#drop table if exists t1;
#drop table if exists t2;
#
#create table t1 (col1 int, col2 decimal(5));
#
#create table t2 as select avg(col1),avg(col2) from t1;
#
#
#show create table t2;
#-- should return:
#+-------+---------------------------------+
#| Table | Create Table                    |
#+-------+---------------------------------+
#| t2    | CREATE TABLE `t2` (             |
#| `avg(col1)` decimal(17,4) default NULL, |
#| `avg(col2)` decimal(17,5) default NULL  |
#| ) ENGINE=MyISAM DEFAULT CHARSET=latin1  |
#+-------+---------------------------------+
#
#drop table t2;
#drop table t1;
#
#-- From WL#1612 "The future" point 8.: Stop storing leading "+" signs and
#   leading "0"s.
#
#drop table if exists t1;
#
#create table t1 (col1 decimal(5,2),col2 decimal(5) zerofill, col3 decimal(3,1));
#
#insert into t1 values (1,1,1);
#
#select col1 from t1 union select col2 from t1 union select col3 from t1;
#
#drop table t1;
#
#-- From WL#1612, The future" point 9.:
#-- Accept the data type and precision and scale as the user
#-- asks, or return an error, but don't change to something else.
#
#drop table if exists t1;
#
#create table t1 (col1 numeric(4,2));
#
#show create table t1;
#
#drop table t1;
#
#-- The scripts in the following bugs should work:
#

#BUG#559  Maximum precision for DECIMAL column ...
#BUG#1499 INSERT/UPDATE into decimal field rounding problem
#BUG#1845 Not correctly recognising value for decimal field