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

mysql> create table abc (value decimal(31,0) not null);
Query OK, 0 rows affected (0.13 sec)

mysql> 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);

mysql> select * from abc where value = '12345678901234567';
+-------------------+
| value             |
+-------------------+
| 12345678901234567 |
+-------------------+
1 row in set (0.00 sec)

mysql> select * from abc where value = '123456789012345678';
Empty set (0.00 sec)

mysql> select * from abc where value = '1234567890123456789';
Empty set (0.00 sec)

mysql> select * from abc where value = '12345678901234567890';
Empty set (0.00 sec)

mysql> select * from abc where value = '123456789012345678901';
+-----------------------+
| value                 |
+-----------------------+
| 123456789012345678901 |
+-----------------------+
1 row in set (0.00 sec)

mysql> alter table abc add index (value);
Query OK, 30 rows affected (0.08 sec)
Records: 30  Duplicates: 0  Warnings: 0

mysql> select * from abc where value = cast('12345678901234567890' as decimal(31,0));
+----------------------+
| value                |
+----------------------+
| 12345678901234567890 |
+----------------------+
1 row in set, 1 warning (0.00 sec)

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

mysql> truncate abc;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into abc values (12345678901234567890);
Query OK, 1 row affected (0.00 sec)

mysql> select * from abc where value = '12345678901234567890';
Empty set (0.00 sec)

mysql> select * from abc where value = 12345678901234567890;
+----------------------+
| value                |
+----------------------+
| 12345678901234567890 |
+----------------------+
1 row in set (0.00 sec)

mysql> select * from abc where value = cast('12345678901234567890' as decimal(31,0));
+----------------------+
| value                |
+----------------------+
| 12345678901234567890 |
+----------------------+
1 row in set (0.01 sec)

mysql> insert into abc values (1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from abc where value = cast('12345678901234567890' as decimal(31,0));
+----------------------+
| value                |
+----------------------+
| 12345678901234567890 |
+----------------------+
1 row in set, 1 warning (0.00 sec)

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