You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@trafodion.apache.org by "Liu, Yuan (Yuan)" <yu...@esgyn.cn> on 2017/12/12 04:00:49 UTC

cast float as varchar show scientific notation result

Hi Trafodioneers,

I tried to cast float value 123.4 to varchar, but the output format is scientific notation format. Is it by design? I think if the output 123.4 is better, agree?

SQL>create table test7( a float);
SQL>insert into test7 values(123.4);
SQL>select * from test7;

A
------------------------
                   123.4
SQL>select cast(a as varchar(30)) from test7;

(EXPR)
------------------------------
1.23400000000000000E+002

SQL>select cast(a as varchar(9)) from test7;

(EXPR)
---------
1.2E+002

--- 1 row(s) selected.

SQL>select cast(a as varchar(8)) from test7;

*** ERROR[8402] A string overflow occurred during the evaluation of a character expression. Conversion of Source Type:IEEE DOUBLE PRECISION(REC_IEEE_FLOAT64) Source Value:1.23400000000000000E+002 to Target Type:VARCHAR(REC_BYTE_V_ASCII). [2017-12-12 11:55:47]


Best regards,
Yuan

RE: cast float as varchar show scientific notation result

Posted by "Liu, Yuan (Yuan)" <yu...@esgyn.cn>.
Sorry, just noticed what you said before. Please ignore my last question.

The conversion error is also correct as the cast is expecting the target to be able to contain all possible float values.



Best regards,
Yuan

-----Original Message-----
From: Liu, Yuan (Yuan) [mailto:yuan.liu@esgyn.cn] 
Sent: Tuesday, December 12, 2017 1:56 PM
To: dev@trafodion.incubator.apache.org
Subject: RE: cast float as varchar show scientific notation result

Hi Anoop,

Thanks for your answer. But why the output of "select * from test7" is 123.4, rather than a scientific value such as 1.23400000000000000E+002?


Best regards,
Yuan

-----Original Message-----
From: Anoop Sharma [mailto:anoop.sharma@esgyn.com] 
Sent: Tuesday, December 12, 2017 1:33 PM
To: dev@trafodion.incubator.apache.org
Subject: RE: cast float as varchar show scientific notation result

A value stored in a float, real or double precision column is stored in approximate numeric format. That means that the stored value may be approximate especially if the scale exceeds the max scale for that column or if precision/scale is truncated.

It doesn't matter the format of value that was inserted(10 or 10.234 or 2e3), they are all stored as approximate datatype. The selected value may also not be exactly the same as what was inserted.

When this float value is converted to char/varchar, the default conversion format is approximate or scientific. (<sign>N.NE<sign>MM). This matches the value that was stored.

A frontend client may choose to display the approx float value in other convenient formats, like 1.23. This is what you see from trafci output.

The conversion error is also correct as the cast is expecting the target to be able to contain all possible float values.

To convert and display an approx/float values as an exact numeric, one would need to cast it to a numeric with scale before casting it to char/varchar.
Something like:
  select cast(cast(a as numeric(10,3)) as varchar(30)) from test7;

anoop

-----Original Message-----
From: Liu, Yuan (Yuan) [mailto:yuan.liu@esgyn.cn]
Sent: Monday, December 11, 2017 8:01 PM
To: dev@trafodion.incubator.apache.org
Subject: cast float as varchar show scientific notation result

Hi Trafodioneers,

I tried to cast float value 123.4 to varchar, but the output format is scientific notation format. Is it by design? I think if the output 123.4 is better, agree?

SQL>create table test7( a float);
SQL>insert into test7 values(123.4);
SQL>select * from test7;

A
------------------------
                   123.4
SQL>select cast(a as varchar(30)) from test7;

(EXPR)
------------------------------
1.23400000000000000E+002

SQL>select cast(a as varchar(9)) from test7;

(EXPR)
---------
1.2E+002

--- 1 row(s) selected.

SQL>select cast(a as varchar(8)) from test7;

*** ERROR[8402] A string overflow occurred during the evaluation of a character expression. Conversion of Source Type:IEEE DOUBLE PRECISION(REC_IEEE_FLOAT64) Source Value:1.23400000000000000E+002 to Target Type:VARCHAR(REC_BYTE_V_ASCII). [2017-12-12 11:55:47]


Best regards,
Yuan

RE: cast float as varchar show scientific notation result

Posted by Anoop Sharma <an...@esgyn.com>.
-->A frontend client may choose to display the approx float value in other convenient formats, like 1.23. This is what you see from trafci output.
It depends on the client. trafci chooses to display it like that by default. Maybe there is a setting to display in float format.

sqlci(another client) displays it in native approx. format.
      Apache Trafodion Conversational Interface 2.3.0
      Copyright (c) 2015-2017 Apache Software Foundation
      >>select 1.23e10 from dual;

      (EXPR)
      -------------------------

       1.23000000000000000E+010

      --- 1 row(s) selected.
      >>

-----Original Message-----
From: Liu, Yuan (Yuan) [mailto:yuan.liu@esgyn.cn]
Sent: Monday, December 11, 2017 9:56 PM
To: dev@trafodion.incubator.apache.org
Subject: RE: cast float as varchar show scientific notation result

Hi Anoop,

Thanks for your answer. But why the output of "select * from test7" is 123.4, rather than a scientific value such as 1.23400000000000000E+002?


Best regards,
Yuan

-----Original Message-----
From: Anoop Sharma [mailto:anoop.sharma@esgyn.com]
Sent: Tuesday, December 12, 2017 1:33 PM
To: dev@trafodion.incubator.apache.org<ma...@trafodion.incubator.apache.org>
Subject: RE: cast float as varchar show scientific notation result

A value stored in a float, real or double precision column is stored in approximate numeric format. That means that the stored value may be approximate especially if the scale exceeds the max scale for that column or if precision/scale is truncated.

It doesn't matter the format of value that was inserted(10 or 10.234 or 2e3), they are all stored as approximate datatype. The selected value may also not be exactly the same as what was inserted.

When this float value is converted to char/varchar, the default conversion format is approximate or scientific. (<sign>N.NE<sign>MM). This matches the value that was stored.

A frontend client may choose to display the approx float value in other convenient formats, like 1.23. This is what you see from trafci output.

The conversion error is also correct as the cast is expecting the target to be able to contain all possible float values.

To convert and display an approx/float values as an exact numeric, one would need to cast it to a numeric with scale before casting it to char/varchar.
Something like:
  select cast(cast(a as numeric(10,3)) as varchar(30)) from test7;

anoop

-----Original Message-----
From: Liu, Yuan (Yuan) [mailto:yuan.liu@esgyn.cn]
Sent: Monday, December 11, 2017 8:01 PM
To: dev@trafodion.incubator.apache.org<ma...@trafodion.incubator.apache.org>
Subject: cast float as varchar show scientific notation result

Hi Trafodioneers,

I tried to cast float value 123.4 to varchar, but the output format is scientific notation format. Is it by design? I think if the output 123.4 is better, agree?

SQL>create table test7( a float);
SQL>insert into test7 values(123.4);
SQL>select * from test7;

A
------------------------
                   123.4
SQL>select cast(a as varchar(30)) from test7;

(EXPR)
------------------------------
1.23400000000000000E+002

SQL>select cast(a as varchar(9)) from test7;

(EXPR)
---------
1.2E+002

--- 1 row(s) selected.

SQL>select cast(a as varchar(8)) from test7;

*** ERROR[8402] A string overflow occurred during the evaluation of a character expression. Conversion of Source Type:IEEE DOUBLE PRECISION(REC_IEEE_FLOAT64) Source Value:1.23400000000000000E+002 to Target Type:VARCHAR(REC_BYTE_V_ASCII). [2017-12-12 11:55:47]


Best regards,
Yuan

RE: cast float as varchar show scientific notation result

Posted by "Liu, Yuan (Yuan)" <yu...@esgyn.cn>.
Hi Anoop,

Thanks for your answer. But why the output of "select * from test7" is 123.4, rather than a scientific value such as 1.23400000000000000E+002?


Best regards,
Yuan

-----Original Message-----
From: Anoop Sharma [mailto:anoop.sharma@esgyn.com] 
Sent: Tuesday, December 12, 2017 1:33 PM
To: dev@trafodion.incubator.apache.org
Subject: RE: cast float as varchar show scientific notation result

A value stored in a float, real or double precision column is stored in approximate numeric format. That means that the stored value may be approximate especially if the scale exceeds the max scale for that column or if precision/scale is truncated.

It doesn't matter the format of value that was inserted(10 or 10.234 or 2e3), they are all stored as approximate datatype. The selected value may also not be exactly the same as what was inserted.

When this float value is converted to char/varchar, the default conversion format is approximate or scientific. (<sign>N.NE<sign>MM). This matches the value that was stored.

A frontend client may choose to display the approx float value in other convenient formats, like 1.23. This is what you see from trafci output.

The conversion error is also correct as the cast is expecting the target to be able to contain all possible float values.

To convert and display an approx/float values as an exact numeric, one would need to cast it to a numeric with scale before casting it to char/varchar.
Something like:
  select cast(cast(a as numeric(10,3)) as varchar(30)) from test7;

anoop

-----Original Message-----
From: Liu, Yuan (Yuan) [mailto:yuan.liu@esgyn.cn]
Sent: Monday, December 11, 2017 8:01 PM
To: dev@trafodion.incubator.apache.org
Subject: cast float as varchar show scientific notation result

Hi Trafodioneers,

I tried to cast float value 123.4 to varchar, but the output format is scientific notation format. Is it by design? I think if the output 123.4 is better, agree?

SQL>create table test7( a float);
SQL>insert into test7 values(123.4);
SQL>select * from test7;

A
------------------------
                   123.4
SQL>select cast(a as varchar(30)) from test7;

(EXPR)
------------------------------
1.23400000000000000E+002

SQL>select cast(a as varchar(9)) from test7;

(EXPR)
---------
1.2E+002

--- 1 row(s) selected.

SQL>select cast(a as varchar(8)) from test7;

*** ERROR[8402] A string overflow occurred during the evaluation of a character expression. Conversion of Source Type:IEEE DOUBLE PRECISION(REC_IEEE_FLOAT64) Source Value:1.23400000000000000E+002 to Target Type:VARCHAR(REC_BYTE_V_ASCII). [2017-12-12 11:55:47]


Best regards,
Yuan

RE: cast float as varchar show scientific notation result

Posted by Anoop Sharma <an...@esgyn.com>.
A value stored in a float, real or double precision column is stored in approximate
numeric format. That means that the stored value may be approximate
especially if the scale exceeds the max scale for that column or if precision/scale is
truncated.

It doesn't matter the format of value that was inserted(10 or 10.234 or 2e3),
they are all stored as approximate datatype. The selected value may also not be
exactly the same as what was inserted.

When this float value is converted to char/varchar, the default conversion format is 
approximate or scientific. (<sign>N.NE<sign>MM). This matches the value that was stored.

A frontend client may choose to display the approx float value in other convenient
formats, like 1.23. This is what you see from trafci output.

The conversion error is also correct as the cast is expecting the target to be able
to contain all possible float values.

To convert and display an approx/float values as an exact numeric, one would
need to cast it to a numeric with scale before casting it to char/varchar.
Something like:
  select cast(cast(a as numeric(10,3)) as varchar(30)) from test7;

anoop

-----Original Message-----
From: Liu, Yuan (Yuan) [mailto:yuan.liu@esgyn.cn] 
Sent: Monday, December 11, 2017 8:01 PM
To: dev@trafodion.incubator.apache.org
Subject: cast float as varchar show scientific notation result

Hi Trafodioneers,

I tried to cast float value 123.4 to varchar, but the output format is scientific notation format. Is it by design? I think if the output 123.4 is better, agree?

SQL>create table test7( a float);
SQL>insert into test7 values(123.4);
SQL>select * from test7;

A
------------------------
                   123.4
SQL>select cast(a as varchar(30)) from test7;

(EXPR)
------------------------------
1.23400000000000000E+002

SQL>select cast(a as varchar(9)) from test7;

(EXPR)
---------
1.2E+002

--- 1 row(s) selected.

SQL>select cast(a as varchar(8)) from test7;

*** ERROR[8402] A string overflow occurred during the evaluation of a character expression. Conversion of Source Type:IEEE DOUBLE PRECISION(REC_IEEE_FLOAT64) Source Value:1.23400000000000000E+002 to Target Type:VARCHAR(REC_BYTE_V_ASCII). [2017-12-12 11:55:47]


Best regards,
Yuan