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

Поиск
Список
Период
Сортировка
От the6campbells
Тема Re: data set combination of integer and decimal/numeric returns wrong result type
Дата
Msg-id CAFEjsq4Qc=nf=FO0iAGWZd7oAtxt11pnt7eUsxv1T_wpe=EMHw@mail.gmail.com
обсуждение исходный текст
Ответ на Re: data set combination of integer and decimal/numeric returns wrong result type  (Craig Ringer <ringerc@ringerc.id.au>)
Ответы Re: data set combination of integer and decimal/numeric returns wrong result type  (Craig Ringer <ringerc@ringerc.id.au>)
Список 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
 
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

В списке pgsql-jdbc по дате отправления:

Предыдущее
От: pprotim
Дата:
Сообщение: Re: Not able to insert array of integers into column of type integer array
Следующее
От: Craig Ringer
Дата:
Сообщение: Re: Re: Not able to insert array of integers into column of type integer array