Re: NUMERIC field contents
Agreed. Definitely odd that it doesnt behave exactly as documented, but it does provide (9,2) precision, non? The fact that you can squeeze (10,2) into the column in certain cases doesnt bother me. Of course, if you came up with a patch to fix it, I dont think anyone would complain. dpk - Original Message - From: Chris Johnson [EMAIL PROTECTED] To: Jim Dickenson [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, September 18, 2001 8:01 AM Subject: Re: NUMERIC field contents On Sun, Sep 16, 2001 at 02:11:20PM -0700, Jim Dickenson wrote: Does anyone have an opinion about the following? Are people using decimal type fields? Is it important for the database to allow only allowed data? I use decimal datatype fields all the time for my monetary amounts. I've always treated them as behaving the way you suggest they should behave, i.e. no extra digit space is provided by using the sign location. That MySQL behaves otherwise seems a bit counter-intuitive. I design my tables and software to treat it otherwise and also to never expect the database to provide the limits. My software insures the limits are never reached. -- ..chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: NUMERIC field contents
The problem is that a number that is in the MySQL database might be a magnitude of 10 times larger than a number that is in the PostgreSQL database. Could you explain this? dpk - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: NUMERIC field contents
On 9/18/01 11:25 AM, Dana Powers at [EMAIL PROTECTED] wrote: The problem is that a number that is in the MySQL database might be a magnitude of 10 times larger than a number that is in the PostgreSQL database. Could you explain this? dpk - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php I create a table in PostgreSQL as a single field with numeric(10,2). I create the same table in MySQL. I then run an ODBC application that calls SQLDescribeCol to find out what items are returned from select * from table. After getting a fix for MyODBC both data sources return a precision of 10 and a scale of 2. This is as I would expect. I then know to add room for the decimal point and the sign character. Even though I have room for a 12 digit number I expect the leftmost position to be used only for the sign. In PostgreSQL 7.1.3, using psql, if I try to put 123456789.12 into the table I get an error that the number is too big to fit. This is as expected. If I use mysql, Ver 11.15 Distrib 3.23.42 for pc-linux-gnu (i686), to put the same number into the table I do not get an error and the data is put into the database. This is not as expected. The reason I see this as a problem is that numeric(10,2) says that there are 10 digits in the number and two of them are decimal digits. The valid range is -99,999,999.99 through 99,999,999.99 but MySQL is allowing 11 digits to be put into the database. If my ODBC application expects data in a certain range based on the information returned from SQLDescribeCol then it will not be expecting to see a number with 11 digits in it. This is what I think is wrong with the behavior of MySQL. -- Jim Dickenson mailto:[EMAIL PROTECTED] Computers for Marketing Corporation http://www.cfmc.com/ eFax: 1-419-791-8924 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: NUMERIC field contents
And my question is, if you've defined your column to have (10,2) precision, why would you try to insert a higher precision number? dpk - Original Message - From: Jim Dickenson [EMAIL PROTECTED] To: Dana Powers [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, September 18, 2001 1:35 PM Subject: Re: NUMERIC field contents On 9/18/01 11:25 AM, Dana Powers at [EMAIL PROTECTED] wrote: The problem is that a number that is in the MySQL database might be a magnitude of 10 times larger than a number that is in the PostgreSQL database. Could you explain this? dpk - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php I create a table in PostgreSQL as a single field with numeric(10,2). I create the same table in MySQL. I then run an ODBC application that calls SQLDescribeCol to find out what items are returned from select * from table. After getting a fix for MyODBC both data sources return a precision of 10 and a scale of 2. This is as I would expect. I then know to add room for the decimal point and the sign character. Even though I have room for a 12 digit number I expect the leftmost position to be used only for the sign. In PostgreSQL 7.1.3, using psql, if I try to put 123456789.12 into the table I get an error that the number is too big to fit. This is as expected. If I use mysql, Ver 11.15 Distrib 3.23.42 for pc-linux-gnu (i686), to put the same number into the table I do not get an error and the data is put into the database. This is not as expected. The reason I see this as a problem is that numeric(10,2) says that there are 10 digits in the number and two of them are decimal digits. The valid range is -99,999,999.99 through 99,999,999.99 but MySQL is allowing 11 digits to be put into the database. If my ODBC application expects data in a certain range based on the information returned from SQLDescribeCol then it will not be expecting to see a number with 11 digits in it. This is what I think is wrong with the behavior of MySQL. -- Jim Dickenson mailto:[EMAIL PROTECTED] Computers for Marketing Corporation http://www.cfmc.com/ eFax: 1-419-791-8924 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: NUMERIC field contents
On 9/18/01 1:45 PM, Dana Powers at [EMAIL PROTECTED] wrote: And my question is, if you've defined your column to have (10,2) precision, why would you try to insert a higher precision number? dpk - Original Message - From: Jim Dickenson [EMAIL PROTECTED] To: Dana Powers [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, September 18, 2001 1:35 PM Subject: Re: NUMERIC field contents On 9/18/01 11:25 AM, Dana Powers at [EMAIL PROTECTED] wrote: The problem is that a number that is in the MySQL database might be a magnitude of 10 times larger than a number that is in the PostgreSQL database. Could you explain this? dpk - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php I create a table in PostgreSQL as a single field with numeric(10,2). I create the same table in MySQL. I then run an ODBC application that calls SQLDescribeCol to find out what items are returned from select * from table. After getting a fix for MyODBC both data sources return a precision of 10 and a scale of 2. This is as I would expect. I then know to add room for the decimal point and the sign character. Even though I have room for a 12 digit number I expect the leftmost position to be used only for the sign. In PostgreSQL 7.1.3, using psql, if I try to put 123456789.12 into the table I get an error that the number is too big to fit. This is as expected. If I use mysql, Ver 11.15 Distrib 3.23.42 for pc-linux-gnu (i686), to put the same number into the table I do not get an error and the data is put into the database. This is not as expected. The reason I see this as a problem is that numeric(10,2) says that there are 10 digits in the number and two of them are decimal digits. The valid range is -99,999,999.99 through 99,999,999.99 but MySQL is allowing 11 digits to be put into the database. If my ODBC application expects data in a certain range based on the information returned from SQLDescribeCol then it will not be expecting to see a number with 11 digits in it. This is what I think is wrong with the behavior of MySQL. -- Jim Dickenson mailto:[EMAIL PROTECTED] Computers for Marketing Corporation http://www.cfmc.com/ eFax: 1-419-791-8924 Maybe because I test the software I run to make sure that it is working the way I think it should. I am learning ODBC and have run into various problems interacting with different ODBC drivers. Additionally the person doing the entry may or may not know all the parameters defined in the table and I would hope that the database would not allow illegal data to be put into the database. -- Jim Dickenson mailto:[EMAIL PROTECTED] Computers for Marketing Corporation http://www.cfmc.com/ eFax: 1-419-791-8924 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: NUMERIC field contents
On Tue, Sep 18, 2001 at 01:45:30PM -0700, Dana Powers wrote: And my question is, if you've defined your column to have (10,2) precision, why would you try to insert a higher precision number? Perhaps he's writing a report, and the application needs to know the size of the data to expect. It's not unreasonable to expect that if the database says a field is a certain width, then you won't get data wider than that. Sure, someone inserted data into the database that was too wide. But for the database to give that data back, wider than it says is allowed, is a plain ol' bug. --Pete - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
NUMERIC field contents
Does anyone have an opinion about the following? Are people using decimal type fields? Is it important for the database to allow only allowed data? From http://www.mysql.com/doc/N/u/Numeric_types.html The NUMERIC and DECIMAL types are implemented as the same type by MySQL, as permitted by the SQL92 standard. They are used for values for which it is important to preserve exact precision, for example with monetary data. When declaring a column of one of these types the precision and scale can be (and usually is) specified; for example: salary DECIMAL(9,2) In this example, 9 (precision) represents the number of significant decimal digits that will be stored for values, and 2 (scale) represents the number of digits that will be stored following the decimal point. In this case, therefore, the range of values that can be stored in the salary column is from -999.99 to 999.99. (MySQL can actually store numbers up to 999.99 in this column because it doesn't have to store the sign for positive numbers) First I am not sure if you intended 999.99 to be .99 in (MySQL can actually store numbers up to 999.99 in this column because it doesn't have to store the sign for positive numbers) If so then I do not think this is correct. I think that decimal(9,2) means there is an optional sign character, up to 7 whole digits, a decimal point and up to two decimal digits. I think the whole parenthetical statement should be removed. Red Hat Linux 7.1 2.96-85 mysql-3.23.42 (Official MySQL RPM) Configure command: ./configure --disable-shared --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --without-berkeley-db --without-innodb --enable-assembler --with-mysqld-user=mysql --with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/ --with-extra-charsets=complex --exec-prefix=/usr --libexecdir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share --localstatedir=/var/lib/mysql --infodir=/usr/info --includedir=/usr/include --mandir=/usr/man '--with-comment=Official MySQL RPM' This shows the problem: mysql -p dickensn Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 18 to server version: 3.23.42 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql create table t1 (num1 numeric(9,2)); Query OK, 0 rows affected (0.00 sec) mysql insert into t1 values (1234567.12); Query OK, 1 row affected (0.00 sec) mysql select * from t1; ++ | num1 | ++ | 1234567.12 | ++ 1 row in set (0.00 sec) mysql update t1 set num1=12345678.12; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from t1; +-+ | num1| +-+ | 12345678.12 | +-+ 1 row in set (0.00 sec) mysql update t1 set num1=-1234567.12; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from t1; +-+ | num1| +-+ | -1234567.12 | +-+ 1 row in set (0.00 sec) mysql update t1 set num1=-12345678.12; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 1 mysql select * from t1; +-+ | num1| +-+ | -999.99 | +-+ 1 row in set (0.00 sec) mysql quit Bye I think the first update should get an error as I specified a maximum of 9 digits. Per your description of numeric() the value should be set to 999.99. The fact that there is room for a sign character should not increase the number of digits allowed in the field. -- Jim Dickenson mailto:[EMAIL PROTECTED] Computers for Marketing Corporation http://www.cfmc.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
NUMERIC field contents
From http://www.mysql.com/doc/N/u/Numeric_types.html The NUMERIC and DECIMAL types are implemented as the same type by MySQL, as permitted by the SQL92 standard. They are used for values for which it is important to preserve exact precision, for example with monetary data. When declaring a column of one of these types the precision and scale can be (and usually is) specified; for example: salary DECIMAL(9,2) In this example, 9 (precision) represents the number of significant decimal digits that will be stored for values, and 2 (scale) represents the number of digits that will be stored following the decimal point. In this case, therefore, the range of values that can be stored in the salary column is from -999.99 to 999.99. (MySQL can actually store numbers up to 999.99 in this column because it doesn't have to store the sign for positive numbers) First I am not sure if you intended 999.99 to be .99 in (MySQL can actually store numbers up to 999.99 in this column because it doesn't have to store the sign for positive numbers) If so then I do not think this is correct. I think that decimal(9,2) means there is an optional sign character, up to 7 whole digits, a decimal point and up to two decimal digits. I think the whole parenthetical statement should be removed. Red Hat Linux 7.1 2.96-85 mysql-3.23.42 (Official MySQL RPM) Configure command: ./configure --disable-shared --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --without-berkeley-db --without-innodb --enable-assembler --with-mysqld-user=mysql --with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/ --with-extra-charsets=complex --exec-prefix=/usr --libexecdir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share --localstatedir=/var/lib/mysql --infodir=/usr/info --includedir=/usr/include --mandir=/usr/man '--with-comment=Official MySQL RPM' This shows the problem: mysql -p dickensn Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 18 to server version: 3.23.42 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql create table t1 (num1 numeric(9,2)); Query OK, 0 rows affected (0.00 sec) mysql insert into t1 values (1234567.12); Query OK, 1 row affected (0.00 sec) mysql select * from t1; ++ | num1 | ++ | 1234567.12 | ++ 1 row in set (0.00 sec) mysql update t1 set num1=12345678.12; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from t1; +-+ | num1| +-+ | 12345678.12 | +-+ 1 row in set (0.00 sec) mysql update t1 set num1=-1234567.12; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from t1; +-+ | num1| +-+ | -1234567.12 | +-+ 1 row in set (0.00 sec) mysql update t1 set num1=-12345678.12; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 1 mysql select * from t1; +-+ | num1| +-+ | -999.99 | +-+ 1 row in set (0.00 sec) mysql quit Bye I think the first update should get an error as I specified a maximum of 9 digits. Per your description of numeric() the value should be set to 999.99. The fact that there is room for a sign character should not increase the number of digits allowed in the field. -- Jim Dickenson mailto:[EMAIL PROTECTED] Computers for Marketing Corporation http://www.cfmc.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
NUMERIC field contents
From http://www.mysql.com/doc/N/u/Numeric_types.html The NUMERIC and DECIMAL types are implemented as the same type by MySQL, as permitted by the SQL92 standard. They are used for values for which it is important to preserve exact precision, for example with monetary data. When declaring a column of one of these types the precision and scale can be (and usually is) specified; for example: salary DECIMAL(9,2) In this example, 9 (precision) represents the number of significant decimal digits that will be stored for values, and 2 (scale) represents the number of digits that will be stored following the decimal point. In this case, therefore, the range of values that can be stored in the salary column is from -999.99 to 999.99. (MySQL can actually store numbers up to 999.99 in this column because it doesn't have to store the sign for positive numbers) First I am not sure if you intended 999.99 to be .99 in (MySQL can actually store numbers up to 999.99 in this column because it doesn't have to store the sign for positive numbers) If so then I do not think this is correct. I think that decimal(9,2) means there is an optional sign character, up to 7 whole digits, a decimal point and up to two decimal digits. I think the whole parenthetical statement should be removed. Red Hat Linux 7.1 2.96-85 mysql-3.23.42 (Official MySQL RPM) Configure command: ./configure --disable-shared --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --without-berkeley-db --without-innodb --enable-assembler --with-mysqld-user=mysql --with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/ --with-extra-charsets=complex --exec-prefix=/usr --libexecdir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share --localstatedir=/var/lib/mysql --infodir=/usr/info --includedir=/usr/include --mandir=/usr/man '--with-comment=Official MySQL RPM' This shows the problem: mysql -p dickensn Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 18 to server version: 3.23.42 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql create table t1 (num1 numeric(9,2)); Query OK, 0 rows affected (0.00 sec) mysql insert into t1 values (1234567.12); Query OK, 1 row affected (0.00 sec) mysql select * from t1; ++ | num1 | ++ | 1234567.12 | ++ 1 row in set (0.00 sec) mysql update t1 set num1=12345678.12; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from t1; +-+ | num1| +-+ | 12345678.12 | +-+ 1 row in set (0.00 sec) mysql update t1 set num1=-1234567.12; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from t1; +-+ | num1| +-+ | -1234567.12 | +-+ 1 row in set (0.00 sec) mysql update t1 set num1=-12345678.12; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 1 mysql select * from t1; +-+ | num1| +-+ | -999.99 | +-+ 1 row in set (0.00 sec) mysql quit Bye I think the first update should get an error as I specified a maximum of 9 digits. Per your description of numeric() the value should be set to 999.99. The fact that there is room for a sign character should not increase the number of digits allowed in the field. -- Jim Dickenson mailto:[EMAIL PROTECTED] Computers for Marketing Corporation http://www.cfmc.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
