Transforming varchars to decimals loses anything after the decimal point

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Upload the Mondial database, and change Cities.longitude from a varchar to a 
decmimal
2. Go the the Cities table and notice that the longitude field seems to consist 
of integers rather than decimals - all detail after the decimal point has been 
lost
3.

What is the expected output? What do you see instead?
the longitude field seems to consist of integers rather than decimals - all 
detail after the decimal point has been lost. This detail should still be there.

Please use labels and text to provide additional information.
Dev 1.0.6 Chrome

Original issue reported on code.google.com by jajwil...@gmail.com on 18 Dec 2014 at 11:55

[GoogleCodeExporter]

In fact, even if you then edit the value of a longitude field in the editing 
window it still abreviates it to an integer value (although it does recognize 
that the field should be 'numeric'.

Original comment by jajwil...@gmail.com on 18 Dec 2014 at 11:57

[GoogleCodeExporter]

Right, it sounds like we'll need to show a warning for that type of change.

I suspect the reason that it's abbreviating to the integer value is because 
you've not specified the field length - you need to specify it in the form x,y 
where x is the total number of digits and y is the number of digits after the 
decimal point.  If this was the case and you weren't aware of it, it sounds 
like we need to make this clear, perhaps with an additional warning message 
when someone tries to change a field to Decimal with no length specified.

Original comment by marxjohn...@gmail.com on 19 Dec 2014 at 9:10

[GoogleCodeExporter]

Yes - that would be the reason. Definitely needs a clearer explanatory message!

Original comment by jajwil...@gmail.com on 19 Dec 2014 at 9:21

[GoogleCodeExporter]

Ok, I've now added a warning that appears when changing to a Decimal but 
leaving the Size empty.  Assigning to Meriel to tweak the wording.

Original comment by marxjohn...@gmail.com on 19 Dec 2014 at 10:09

• Changed state: Started

[GoogleCodeExporter]

(the string is "sddatatypewarning4")

Original comment by marxjohn...@gmail.com on 19 Dec 2014 at 10:09

[GoogleCodeExporter]

This doesn't seem to be happening consistently. I just created a new field 
(called 'decimal') in the mondial table of 'Mondial database 2' in the DHOxSS 
Project on dev, with the data type Decimal and the size field left blank, and 
it didn't seem to have any problems saving what came after the decimal point. I 
then tried changing the data type to Varchar (this necessitated deleting 
'null,null' from the Size box, but was otherwise fine), and then back to 
Decimal, and it was still fine.

I find the PostgreSQL documentation on this point a bit confusing. It says: 
'Specifying NUMERIC without any precision or scale creates a column in which 
numeric values of any precision and scale can be stored, up to the 
implementation limit on precision. A column of this kind will not coerce input 
values to any particular scale, whereas numeric columns with a declared scale 
will coerce input values to that scale. (The SQL standard requires a default 
scale of 0, i.e., coercion to integer precision. We find this a bit useless. If 
you're concerned about portability, always specify the precision and scale 
explicitly.)' 

If I understand that correctly, this means that a Numeric field (which is the 
same as a Decimal one) with no size specified should be able to accommodate as 
many digits as desired (up to the limit of what a Decimal field can hold), but 
that if the database is exported for use in other SQL systems, any entries in 
the field may get truncated to integers. 

Is that right? And do we know why ORDS is seemingly sometimes behaving like a 
PostgreSQL system, and sometimes not like one?

Original comment by meriel.p...@gmail.com on 19 Dec 2014 at 4:50

[GoogleCodeExporter]

I've tweaked the warning text, but it may need revising again depending on what 
happens regarding this issue.

Original comment by meriel.p...@gmail.com on 19 Dec 2014 at 5:27

[GoogleCodeExporter]

This is still assigned to me as high priority, but I'm not sure what's left for 
me to do?

Original comment by marxjohn...@gmail.com on 8 Jan 2015 at 12:04

[GoogleCodeExporter]

I'm not sure either. Let's mark it as resolved and just create a new issue if 
any further problems arise.

Original comment by jajwil...@gmail.com on 8 Jan 2015 at 1:50

• Changed state: Fixed

[GoogleCodeExporter]

Reopening this, as I'm still not sure what's going on with Decimal fields.

When someone creates a new field with the Decimal data type, or converts an 
existing field to this data type, they get a big scary warning telling them 
that if they don't enter a size, their data will be truncated so that just the 
integer part is left. However, in my experience of playing about with changing 
field types, this doesn't actually seem to be true.

Mark, please could you have another look at this and see whether your 
experience matches mine? If there are specific circumstances where data loss 
will occur, obviously we need to warn people about them, but I haven't yet 
managed to find a case where data does actually get truncated. At the moment, 
my impression is that we're scaring people for no good reason.

As I note in comment #6 above, the PostgreSQL documentation on this point is 
not as clear as it could be. If I've understood it correctly, it suggests that 
in PostgreSQL, not specifying a size won't truncate a Decimal field, but that 
if a database is exported for use in other SQL systems, any entries in the 
field may get truncated to integers. If this is true, it's something people 
should be aware of, but it's not the same as saying that people's data will be 
truncated if they change the data type in ORDS.

Mark, can you also comment on whether I have actually understood the PostgreSQL 
documentation on this point? 

(I've just added a new issue (issue 615) noting that I can't currently add data 
to newly created Decimal fields, but I have no idea whether that's in any way 
related to this.)

Original comment by meriel.p...@gmail.com on 6 Feb 2015 at 5:32

• Changed state: Accepted