DROP TABLE IF EXISTS t1; CREATE TABLE t1 (f FLOAT , f0 FLOAT(0) , r1_1 REAL(1,1) , f23_0 FLOAT(23) , f20_3 FLOAT(20,3) , d DOUBLE , d1_0 DOUBLE(1,0) , d10_10 DOUBLE PRECISION (10,10) , d53 DOUBLE(53,0) , d53_10 DOUBLE(53,10) ) ENGINE= ; SHOW COLUMNS IN t1; Field Type Null Key Default Extra f float # # # f0 float # # # r1_1 double(1,1) # # # f23_0 float # # # f20_3 float(20,3) # # # d double # # # d1_0 double(1,0) # # # d10_10 double(10,10) # # # d53 double(53,0) # # # d53_10 double(53,10) # # # INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10) VALUES (12345.12345,12345.12345,0.9,123456789.123,56789.987,11111111.111,8.0,0.0123456789,1234566789123456789,99999999999999999.99999999); SELECT f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10 FROM t1; f 12345.1 d 11111111.111 d10_10 0.0123456789 d1_0 8 d53 1234566789123456800 d53_10 100000000000000000.0000000000 f0 12345.1 f20_3 56789.988 f23_0 123457000 r1_1 0.9 INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10) VALUES (0,0,0,0,0,0,0,0,0,0); INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10) VALUES ( 99999999999999999999999999999999999999, 99999999999999999999999999999999999999.9999999999999999, 0.9, 99999999999999999999999999999999999999.9, 99999999999999999.999, 999999999999999999999999999999999999999999999999999999999999999999999999999999999, 9, 0.9999999999, 1999999999999999999999999999999999999999999999999999999, 19999999999999999999999999999999999999999999.9999999999 ); Warnings: Warning 1264 Out of range value for column 'd53' at row 1 Warning 1264 Out of range value for column 'd53_10' at row 1 SELECT f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10 FROM t1; f 12345.1 d 0 d 11111111.111 d 1e81 d10_10 0.0000000000 d10_10 0.0123456789 d10_10 0.9999999999 d1_0 0 d1_0 8 d1_0 9 d53 0 d53 100000000000000000000000000000000000000000000000000000 d53 1234566789123456800 d53_10 0.0000000000 d53_10 100000000000000000.0000000000 d53_10 10000000000000000000000000000000000000000000.0000000000 f 0 f 1e38 f0 0 f0 12345.1 f0 1e38 f20_3 0.000 f20_3 56789.988 f20_3 99999998430674940.000 f23_0 0 f23_0 123457000 f23_0 1e38 r1_1 0.0 r1_1 0.9 r1_1 0.9 INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10) VALUES (-999999999999999999999999,-99999999999.999999999999,-0.9,-999.99999999999999999999,-99999999999999999.999,-999999999999999999999999999999999999999999999999999999999999-0.999,-9,-.9999999999,-999999999999999999999999999999.99999999999999999999999,-9999999999999999999999999999999999999999999.9999999999); SELECT f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10 FROM t1; f 12345.1 d -1e60 d 0 d 11111111.111 d 1e81 d10_10 -0.9999999999 d10_10 0.0000000000 d10_10 0.0123456789 d10_10 0.9999999999 d1_0 -9 d1_0 0 d1_0 8 d1_0 9 d53 -1000000000000000000000000000000 d53 0 d53 100000000000000000000000000000000000000000000000000000 d53 1234566789123456800 d53_10 -10000000000000000000000000000000000000000000.0000000000 d53_10 0.0000000000 d53_10 100000000000000000.0000000000 d53_10 10000000000000000000000000000000000000000000.0000000000 f -1e24 f 0 f 1e38 f0 -100000000000 f0 0 f0 12345.1 f0 1e38 f20_3 -99999998430674940.000 f20_3 0.000 f20_3 56789.988 f20_3 99999998430674940.000 f23_0 -1000 f23_0 0 f23_0 123457000 f23_0 1e38 r1_1 -0.9 r1_1 0.0 r1_1 0.9 r1_1 0.9 SELECT MAX(f), MAX(f0), MAX(r1_1), MAX(f23_0), MAX(f20_3), MAX(d), MAX(d1_0), MAX(d10_10), MAX(d53), MAX(d53_10) FROM t1; MAX(f) 9.999999680285692e37 MAX(d) 1e81 MAX(d10_10) 0.9999999999 MAX(d1_0) 9 MAX(d53) 100000000000000000000000000000000000000000000000000000 MAX(d53_10) 10000000000000000000000000000000000000000000.0000000000 MAX(f0) 9.999999680285692e37 MAX(f20_3) 99999998430674940.000 MAX(f23_0) 9.999999680285692e37 MAX(r1_1) 0.9 INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10) SELECT d53_10, d53_10, d53_10, d53_10, d53_10, d53_10, d53_10, d53_10, d53_10, d53_10 FROM t1 ORDER BY d53_10 DESC LIMIT 1; Warnings: Warning 1264 Out of range value for column 'f' at row 1 Warning 1264 Out of range value for column 'f0' at row 1 Warning 1264 Out of range value for column 'r1_1' at row 1 Warning 1264 Out of range value for column 'f23_0' at row 1 Warning 1264 Out of range value for column 'f20_3' at row 1 Warning 1264 Out of range value for column 'd1_0' at row 1 SELECT f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10 FROM t1; f 12345.1 d -1e60 d 0 d 11111111.111 d 1e43 d 1e81 d10_10 -0.9999999999 d10_10 0.0000000000 d10_10 0.0123456789 d10_10 0.9999999999 d10_10 10000000000000000000000000000000000000000000.0000000000 d1_0 -9 d1_0 0 d1_0 8 d1_0 9 d1_0 9 d53 -1000000000000000000000000000000 d53 0 d53 10000000000000000000000000000000000000000000 d53 100000000000000000000000000000000000000000000000000000 d53 1234566789123456800 d53_10 -10000000000000000000000000000000000000000000.0000000000 d53_10 0.0000000000 d53_10 100000000000000000.0000000000 d53_10 10000000000000000000000000000000000000000000.0000000000 d53_10 10000000000000000000000000000000000000000000.0000000000 f -1e24 f 0 f 1e38 f 3.40282e38 f0 -100000000000 f0 0 f0 12345.1 f0 1e38 f0 3.40282e38 f20_3 -99999998430674940.000 f20_3 0.000 f20_3 56789.988 f20_3 99999998430674940.000 f20_3 99999998430674940.000 f23_0 -1000 f23_0 0 f23_0 123457000 f23_0 1e38 f23_0 3.40282e38 r1_1 -0.9 r1_1 0.0 r1_1 0.9 r1_1 0.9 r1_1 0.9 INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10) VALUES ( 999999999999999999999999999999999999999, 999999999999999999999999999999999999999.9999999999999999, 1.9, 999999999999999999999999999999999999999.9, 999999999999999999.999, 9999999999999999999999999999999999999999999999999999999999999999999999999999999999, 99, 1.9999999999, 1999999999999999999999999999999999999999999999999999999, 19999999999999999999999999999999999999999999.9999999999 ); Warnings: Warning 1916 Got overflow when converting '' to DECIMAL. Value truncated Warning 1264 Out of range value for column 'f' at row 1 Warning 1264 Out of range value for column 'f0' at row 1 Warning 1264 Out of range value for column 'r1_1' at row 1 Warning 1264 Out of range value for column 'f23_0' at row 1 Warning 1264 Out of range value for column 'f20_3' at row 1 Warning 1264 Out of range value for column 'd1_0' at row 1 Warning 1264 Out of range value for column 'd10_10' at row 1 Warning 1264 Out of range value for column 'd53' at row 1 Warning 1264 Out of range value for column 'd53_10' at row 1 SELECT f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10 FROM t1; f 12345.1 d -1e60 d 0 d 11111111.111 d 1e43 d 1e65 d 1e81 d10_10 -0.9999999999 d10_10 0.0000000000 d10_10 0.0123456789 d10_10 0.9999999999 d10_10 0.9999999999 d10_10 10000000000000000000000000000000000000000000.0000000000 d1_0 -9 d1_0 0 d1_0 8 d1_0 9 d1_0 9 d1_0 9 d53 -1000000000000000000000000000000 d53 0 d53 10000000000000000000000000000000000000000000 d53 100000000000000000000000000000000000000000000000000000 d53 100000000000000000000000000000000000000000000000000000 d53 1234566789123456800 d53_10 -10000000000000000000000000000000000000000000.0000000000 d53_10 0.0000000000 d53_10 100000000000000000.0000000000 d53_10 10000000000000000000000000000000000000000000.0000000000 d53_10 10000000000000000000000000000000000000000000.0000000000 d53_10 10000000000000000000000000000000000000000000.0000000000 f -1e24 f 0 f 1e38 f 3.40282e38 f 3.40282e38 f0 -100000000000 f0 0 f0 12345.1 f0 1e38 f0 3.40282e38 f0 3.40282e38 f20_3 -99999998430674940.000 f20_3 0.000 f20_3 56789.988 f20_3 99999998430674940.000 f20_3 99999998430674940.000 f20_3 99999998430674940.000 f23_0 -1000 f23_0 0 f23_0 123457000 f23_0 1e38 f23_0 3.40282e38 f23_0 3.40282e38 r1_1 -0.9 r1_1 0.0 r1_1 0.9 r1_1 0.9 r1_1 0.9 r1_1 0.9 ALTER TABLE t1 ADD COLUMN d0_0 DOUBLE(0,0); # ERROR: Statement succeeded (expected results: ER_TOO_BIG_DISPLAYWIDTH) # ------------ UNEXPECTED RESULT ------------ # [ ALTER TABLE t1 ADD COLUMN d0_0 DOUBLE(0,0) ] # The statement|command succeeded unexpectedly. # ALTER TABLE or the mix could be unsupported|malfunctioning, or the problem was caused by previous errors. # You can change the engine code, or create an rdiff, or disable the test by adding it to disabled.def. # Further in this test, the message might sometimes be suppressed; a part of the test might be skipped. # Also, this problem may cause a chain effect (more errors of different kinds in the test). # ------------------------------------------- ALTER TABLE t1 ADD COLUMN n66_6 DECIMAL(256,1); ERROR 42000: Too big precision 256 specified for 'n66_6'. Maximum is 65. ALTER TABLE t1 ADD COLUMN n66_66 DECIMAL(40,35); ERROR 42000: Too big scale 35 specified for 'n66_66'. Maximum is 30. DROP TABLE t1;