Postgres Pro
Downloads
Home   >   mailing lists

Thread: data set combination of integer and decimal/numeric returns wrong result type

data set combination of integer and decimal/numeric returns wrong result type

From
the6campbells
Date:
integer
union 
decimal/numeric (7,2)

select tint.cint from tint union select tdec.cdec from tdec 

no precision is returned as expected etc 
display size is odd



ColumnIndexgetColumnNamegetColumnTypeNamegetPrecisiongetScaleisNullablegetTableNamegetSchemaNamegetCatalogNamegetColumnClassNamegetColumnDisplaySizegetColumnLabelgetColumnTypeisAutoIncrementisCaseSensitiveisCurrencyisDefinitelyWritable isReadOnlyisSearchableisSignedisWritable
1cintnumeric002   java.math.BigDecimal131089cint2falsefalsefalsefalsefalsetruetruetrue

Re: data set combination of integer and decimal/numeric returns wrong result type

From
Craig Ringer
Date:
On 09/25/2012 09:33 AM, the6campbells wrote:
> integer
> union
> decimal/numeric (7,2)

I'm not sure what you're getting at with this one; can you flesh it out
into a complete SQL statement to define the schema you're testing?

Re: data set combination of integer and decimal/numeric returns wrong result type

From
Craig Ringer
Date:
On 09/29/2012 07:11 AM, the6campbells wrote:
> select intcolumn from t1
> union
> select decimalcolumn from t2
>
> select intcolumn from t1
> union
> select numericcolum from t2
>
> then look at nullif, coalesce, case when end variants using combinations
> of the two data types

Those aren't complete, runnable statements. Provide table definitions
and real statements if reporting a bug, please. Show expected vs actual
results on PgJDBC and (if possible) other DBs and JDBC drivers. If
possible provide standalone Java code that compiles and runs to
demonstrate the bug; that way the code can be run against any patched
driver versions to see if they fix the issue.

Also, please reply to the mailing list, not directly to me.

> compare the specification in ISO-SQL 20xx to Postgres in general and as
> applicable via the Postgres JDBC driver if that is colouring the issue etc

Quotes and page numbers / section numbers?

Please be more specific. While I could probably find the details, it's
currently time I cannot spend on that.

--
Craig Ringer

Re: data set combination of integer and decimal/numeric returns wrong result type

From
the6campbells
Date:
The test case can be shown with one table that contains a column for each supported Postgres type that maps to ISO-SQL or one table per type.
 
This example compares an integer and a precise numeric type.
 
create table t1 ( c1 integer )
insert into t1 ( c1 ) values ( 1 )
 
create table t2 ( c2 decimal (7,2))
insert into t1 ( c2 ) values ( 1.0 )
 
Test statements including these. Prepare and describe or prepare-execute-describe these using the Postgres 9.x JDBC drivers
 
select c1 from t1
union
select c2 from t2
 
select c2 from t2
union
select c1 from t1
 
select coalesce ( c1, c2) from t1, t2
 
select coalesce  (c1, c2) from t1, t2
 
select nullif (c1, c2) from t1, t2
select nullif (c2, c1) from t1, t2
 
select case when 1=1 then c1 else c2 end from t1, t2
select case when 1=1 then c2 else c1 end from t1, t2
 
refer to the ISO-SQL 20xx sql foundation specification 9.5 "Result of data type combinations"
A few examples of vendors.
 
Union
 
DB2
 
ColumnIndexgetColumnNamegetColumnTypeNamegetPrecisiongetScaleisNullablegetTableNamegetSchemaNamegetCatalogNamegetColumnClassNamegetColumnDisplaySizegetColumnLabelgetColumnTypeisAutoIncrementisCaseSensitiveisCurrencyisDefinitelyWritableisReadOnlyisSearchableisSignedisWritable
1CINTDECIMAL1321  TESTjava.math.BigDecimal15CINT3falsefalsefalsefalsetruetruetruefalse
 
Postgres
 
ColumnIndexgetColumnNamegetColumnTypeNamegetPrecisiongetScaleisNullablegetTableNamegetSchemaNamegetCatalogNamegetColumnClassNamegetColumnDisplaySizegetColumnLabelgetColumnTypeisAutoIncrementisCaseSensitiveisCurrencyisDefinitelyWritableisReadOnlyisSearchableisSignedisWritable
1cintnumeric002   java.math.BigDecimal131089cint2falsefalsefalsefalsefalsetruetruetrue
 
Informix
 
ColumnIndexgetColumnNamegetColumnTypeNamegetPrecisiongetScaleisNullablegetTableNamegetSchemaNamegetCatalogNamegetColumnClassNamegetColumnDisplaySizegetColumnLabelgetColumnTypeisAutoIncrementisCaseSensitiveisCurrencyisDefinitelyWritableisReadOnlyisSearchableisSignedisWritable
1cintdecimal1221   java.math.BigDecimal12cint3falsetruefalsetruefalsetruetruetrue
 
Netezza
 
ColumnIndexgetColumnNamegetColumnTypeNamegetPrecisiongetScaleisNullablegetTableNamegetSchemaNamegetCatalogNamegetColumnClassNamegetColumnDisplaySizegetColumnLabelgetColumnTypeisAutoIncrementisCaseSensitiveisCurrencyisDefinitelyWritableisReadOnlyisSearchableisSignedisWritable
1CINTNUMERIC1121   java.math.BigDecimal13CINT2falsefalsefalsefalsefalsetruetruetrue
 

On Mon, Oct 1, 2012 at 8:03 AM, Craig Ringer <ringerc@ringerc.id.au> wrote:
On 09/29/2012 07:11 AM, the6campbells wrote:
select intcolumn from t1
union
select decimalcolumn from t2

select intcolumn from t1
union
select numericcolum from t2

then look at nullif, coalesce, case when end variants using combinations
of the two data types

Those aren't complete, runnable statements. Provide table definitions and real statements if reporting a bug, please. Show expected vs actual results on PgJDBC and (if possible) other DBs and JDBC drivers. If possible provide standalone Java code that compiles and runs to demonstrate the bug; that way the code can be run against any patched driver versions to see if they fix the issue.

Also, please reply to the mailing list, not directly to me.


compare the specification in ISO-SQL 20xx to Postgres in general and as
applicable via the Postgres JDBC driver if that is colouring the issue etc

Quotes and page numbers / section numbers?

Please be more specific. While I could probably find the details, it's currently time I cannot spend on that.

--
Craig Ringer

Re: data set combination of integer and decimal/numeric returns wrong result type

From
Craig Ringer
Date:
On 10/02/2012 01:47 AM, the6campbells wrote:
> The test case can be shown with one table that contains a column for
> each supported Postgres type that maps to ISO-SQL or one table per type.
> This example compares an integer and a precise numeric type.

OK, so your issue is that PgJDBC reports zero scale and precision for
numerics as results when the numeric didn't come directly from a column
with defined scale and precision. Right?

If so, this has nothing to do with integer combinations, it's *any*
operation on numeric. It isn't a JDBC driver issue as such, it's the
behaviour of the underlying PostgreSQL backend.

Witness:

regress=# CREATE TABLE plain AS SELECT NUMERIC(6,4) '12.345678' ;
SELECT 1
regress=# CREATE TABLE modified AS SELECT ABS(NUMERIC(6,4) '12.345678') ;
SELECT 1

regress=# \d plain
         Table "public.plain"
  Column  |     Type     | Modifiers
---------+--------------+-----------
  numeric | numeric(6,4) |

regress=# \d modified
    Table "public.modified"
  Column |  Type   | Modifiers
--------+---------+-----------
  abs    | numeric |



As such there isn't tons the JDBC driver can do about it. The scale and
precision aren't preserved in results, so the JDBC driver has no way to
obtain them.

Would it be better if PgJDBC reported these numerics as having the
maximum supported size?


--
Craig Ringer