Skip site navigation (1) Skip section navigation (2)

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

From: the6campbells <the6campbells(at)gmail(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: data set combination of integer and decimal/numeric returns wrong result type
Date: 2012-10-01 17:47:56
Message-ID: CAFEjsq4Qc=nf=FO0iAGWZd7oAtxt11pnt7eUsxv1T_wpe=EMHw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
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

  ColumnIndex getColumnName getColumnTypeName getPrecision getScale
isNullable getTableName getSchemaName getCatalogName getColumnClassName
getColumnDisplaySize getColumnLabel getColumnType isAutoIncrement
isCaseSensitive isCurrency isDefinitelyWritable isReadOnly isSearchable
isSigned isWritable 1 CINT DECIMAL 13 2 1     TEST java.math.BigDecimal 15
CINT 3 false false false false true true true false

Postgres

  ColumnIndex getColumnName getColumnTypeName getPrecision getScale
isNullable getTableName getSchemaName getCatalogName getColumnClassName
getColumnDisplaySize getColumnLabel getColumnType isAutoIncrement
isCaseSensitive isCurrency isDefinitelyWritable isReadOnly isSearchable
isSigned isWritable 1 cint numeric 0 0 2       java.math.BigDecimal 131089
cint 2 false false false false false true true true

Informix

  ColumnIndex getColumnName getColumnTypeName getPrecision getScale
isNullable getTableName getSchemaName getCatalogName getColumnClassName
getColumnDisplaySize getColumnLabel getColumnType isAutoIncrement
isCaseSensitive isCurrency isDefinitelyWritable isReadOnly isSearchable
isSigned isWritable 1 cint decimal 12 2 1       java.math.BigDecimal 12 cint
3 false true false true false true true true

Netezza

  ColumnIndex getColumnName getColumnTypeName getPrecision getScale
isNullable getTableName getSchemaName getCatalogName getColumnClassName
getColumnDisplaySize getColumnLabel getColumnType isAutoIncrement
isCaseSensitive isCurrency isDefinitelyWritable isReadOnly isSearchable
isSigned isWritable 1 CINT NUMERIC 11 2 1       java.math.BigDecimal 13 CINT
2 false false false false false true true true


On Mon, Oct 1, 2012 at 8:03 AM, Craig Ringer <ringerc(at)ringerc(dot)id(dot)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
>

In response to

Responses

pgsql-jdbc by date

Next:From: Craig RingerDate: 2012-10-02 01:33:24
Subject: Re: Re: Not able to insert array of integers into column of type integer array
Previous:From: pprotimDate: 2012-10-01 16:08:55
Subject: Re: Not able to insert array of integers into column of type integer array

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group