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

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
>