Code

Ticket #19625: django19625.more-superfun-with-mysql5.1.txt

File django19625.more-superfun-with-mysql5.1.txt, 4.0 KB (added by wdoekes, 15 months ago)

More mysql oddness, captured in a log.

Line 
1mysql> create table abc (value decimal(31,0) not null);
2Query OK, 0 rows affected (0.13 sec)
3
4mysql> insert into abc values (1);insert into abc values (12);insert into abc values (123);insert into abc values (1234);insert into abc values (12345);insert into abc values (123456);insert into abc values (1234567);insert into abc values (12345678);insert into abc values (123456789);insert into abc values (1234567890);insert into abc values (12345678901);insert into abc values (123456789012);insert into abc values (1234567890123);insert into abc values (12345678901234);insert into abc values (123456789012345);insert into abc values (1234567890123456);insert into abc values (12345678901234567);insert into abc values (123456789012345678);insert into abc values (1234567890123456789);insert into abc values (12345678901234567890);insert into abc values (123456789012345678901);insert into abc values (1234567890123456789012);insert into abc values (12345678901234567890123);insert into abc values (123456789012345678901234);insert into abc values (1234567890123456789012345);insert into abc values (12345678901234567890123456);insert into abc values (123456789012345678901234567);insert into abc values (1234567890123456789012345678);insert into abc values (12345678901234567890123456789);insert into abc values (123456789012345678901234567890);
5
6mysql> select * from abc where value = '12345678901234567';
7+-------------------+
8| value             |
9+-------------------+
10| 12345678901234567 |
11+-------------------+
121 row in set (0.00 sec)
13
14mysql> select * from abc where value = '123456789012345678';
15Empty set (0.00 sec)
16
17mysql> select * from abc where value = '1234567890123456789';
18Empty set (0.00 sec)
19
20mysql> select * from abc where value = '12345678901234567890';
21Empty set (0.00 sec)
22
23mysql> select * from abc where value = '123456789012345678901';
24+-----------------------+
25| value                 |
26+-----------------------+
27| 123456789012345678901 |
28+-----------------------+
291 row in set (0.00 sec)
30
31mysql> alter table abc add index (value);
32Query OK, 30 rows affected (0.08 sec)
33Records: 30  Duplicates: 0  Warnings: 0
34
35mysql> select * from abc where value = cast('12345678901234567890' as decimal(31,0));
36+----------------------+
37| value                |
38+----------------------+
39| 12345678901234567890 |
40+----------------------+
411 row in set, 1 warning (0.00 sec)
42
43mysql> show warnings;
44+-------+------+---------------------------------------+
45| Level | Code | Message                               |
46+-------+------+---------------------------------------+
47| Error | 1292 | Truncated incorrect DECIMAL value: '' |
48+-------+------+---------------------------------------+
491 row in set (0.00 sec)
50
51mysql> truncate abc;
52Query OK, 0 rows affected (0.00 sec)
53
54mysql> insert into abc values (12345678901234567890);
55Query OK, 1 row affected (0.00 sec)
56
57mysql> select * from abc where value = '12345678901234567890';
58Empty set (0.00 sec)
59
60mysql> select * from abc where value = 12345678901234567890;
61+----------------------+
62| value                |
63+----------------------+
64| 12345678901234567890 |
65+----------------------+
661 row in set (0.00 sec)
67
68mysql> select * from abc where value = cast('12345678901234567890' as decimal(31,0));
69+----------------------+
70| value                |
71+----------------------+
72| 12345678901234567890 |
73+----------------------+
741 row in set (0.01 sec)
75
76mysql> insert into abc values (1);
77Query OK, 1 row affected (0.00 sec)
78
79mysql> select * from abc where value = cast('12345678901234567890' as decimal(31,0));
80+----------------------+
81| value                |
82+----------------------+
83| 12345678901234567890 |
84+----------------------+
851 row in set, 1 warning (0.00 sec)
86
87mysql> show warnings;
88+-------+------+---------------------------------------+
89| Level | Code | Message                               |
90+-------+------+---------------------------------------+
91| Error | 1292 | Truncated incorrect DECIMAL value: '' |
92+-------+------+---------------------------------------+
931 row in set (0.00 sec)
94