[BUGS] JDBC: 2 bugs: Getting a smallint array actually gets an integer array and return type of a boolean array is bit.
Hi,
I think I found two bugs (and I hope I am not wasting everyone's time).
One is minor: the base type of a boolean[] is java.sql.Types.BIT instead or
java.sql.Types.BOOLEAN. At the very least shouldn't these be aliases for
the same type?
And secondly the returned type from a smallint[] is an Integer[] instead of
a Short[].
So running this code:
--
public class NewClass {
public static void main(String[] args) {
try {
Class.forName(org.postgresql.Driver);
java.sql.Connection conn = java.sql.DriverManager.getConnection(
jdbc:postgresql://localhost:5432/dev, dev, devmm );
java.util.Enumerationjava.sql.Driver drivers
= java.sql.DriverManager.getDrivers();
while( drivers.hasMoreElements() ) {
java.sql.Driver d = drivers.nextElement();
System.out.println(
d.toString() + : +
d.getMajorVersion() + . +
d.getMinorVersion() );
}
java.sql.PreparedStatement ps_ver = conn.prepareStatement(
SELECT version() );
java.sql.ResultSet rs = ps_ver.executeQuery();
while(rs.next()) System.out.println(rs.getString(1));
java.sql.PreparedStatement ps_create = conn.prepareStatement(
CREATE TABLE public.aab ( +
ia integer[], +
sa smallint[], +
ba boolean[] ) );
ps_create.executeUpdate();
java.sql.PreparedStatement ps_insert = conn.prepareStatement(
INSERT INTO public.aab (ia,sa,ba) VALUES (?,?,?) );
ps_insert.setArray( 1,
conn.createArrayOf( int4, new Integer[] { 1, 2 } ) );
ps_insert.setArray( 2,
conn.createArrayOf( int2, new Short[] { 100, 200 } ) );
ps_insert.setArray( 3,
conn.createArrayOf( bool, new Boolean[] {false,true} ) );
ps_insert.executeUpdate();
java.sql.PreparedStatement ps_select = conn.prepareStatement(
SELECT ia,sa,ba FROM public.aab );
rs = ps_select.executeQuery();
java.sql.Array jdbcArr;
while(rs.next()) {
System.out.println(Integer[]);
jdbcArr = rs.getArray(ia);
Integer[] javaIntArr = (Integer[]) jdbcArr.getArray();
System.out.println(javaIntArr[0] + + javaIntArr[1]);
System.out.println(String.format(
%s: %d %d,
jdbcArr.getBaseTypeName(),
java.sql.Types.INTEGER,
jdbcArr.getBaseType()));
System.out.println(END Integer[]);
System.out.println(Boolean[]);
jdbcArr = rs.getArray(ba);
Boolean[] javaBooArr = (Boolean[]) jdbcArr.getArray();
System.out.println(javaBooArr[0] + + javaBooArr[1]);
System.out.println(String.format(
%s: %d %d %d,
jdbcArr.getBaseTypeName(),
java.sql.Types.BOOLEAN,
jdbcArr.getBaseType(),
java.sql.Types.BIT));
System.out.println(END Boolean[]);
System.out.println(Short[]);
jdbcArr = rs.getArray(sa);
Short[] javaShoArr = (Short[]) jdbcArr.getArray();
System.out.println(javaShoArr[0] + + javaShoArr[1]);
System.out.println(String.format(
%s: %d %d,
jdbcArr.getBaseTypeName(),
java.sql.Types.SMALLINT,
jdbcArr.getBaseType()));
System.out.println(END Short[]);
} catch( Exception e ) { ; }
} // main
} // class
--
Prints the following for me:
--
sun.jdbc.odbc.jdbcodbcdri...@de6f34: 2.1
org.postgresql.dri...@47b480: 8.4
PostgreSQL 8.4.2 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2
20071124 (Red Hat 4.1.2-42), 64-bit
Integer[]
1 2
int4: 4 4
END Integer[]
Boolean[]
false true
bool: 16 -7 -7
END Boolean[]
Short[]
java.lang.ClassCastException: [Ljava.lang.Integer; cannot be cast to
[Ljava.lang.Short;
at NewClass.main(NewClass.java:90)
[Ljava.lang.Integer; cannot be cast to [Ljava.lang.Short;
--
Thanks for any help,
--
Saneesh Apte
510-642-5478
http://www.calccit.org
California Center for Innovative Transportation
University of California, Berkeley, MC3830
2105 Bancroft Way, Suite 300
Berkeley, CA
[BUGS] Function works in 8.4 but not in 9.0 beta2 ERROR: structure of query does not match function result type
Hi I've started testing our applications against PostgreSQL 9.0 beta2 and found that this function now does not work anymore(rewritten to be as small and anonymous as possible) CREATE TYPE test_type AS( product text, amount numeric(30,4) ); CREATE FUNCTION test_func() RETURNS SETOF test_type AS $$ BEGIN RETURN QUERY SELECT 'test'::text, 30.2::numeric; END; $$ LANGUAGE plpgsql STABLE; Output in postgresql 8.4: postgres=# SELECT version(); version PostgreSQL 8.4.1 on i386-apple-darwin10.0.0, compiled by GCC i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5646), 64-bit (1 row) postgres=# SELECT test_func(); test_func - (test,30.2) (1 row) postgres=# And in postgresql 9.0 beta2 version - PostgreSQL 9.0beta2 on x86_64-apple-darwin10.4.0, compiled by GCC i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5659), 64-bit (1 row) postgres=# \set VERBOSITY verbose postgres=# SELECT test_func(); ERROR: 42804: structure of query does not match function result type DETAIL: Returned type numeric does not match expected type numeric(30,4) in column 2. CONTEXT: PL/pgSQL function test_func line 2 at RETURN QUERY LOCATION: convert_tuples_by_position, tupconvert.c:112 postgres=# Currently unning Mac OS X 10.6 Marcel Asio Network System Administrator Redbet Technology Mobile: +46 (0)709 13 04 01 Work: +46 (0)8 12 09 99 41 marcel.a...@redbet.com This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden.
Re: [BUGS] JDBC: 2 bugs: Getting a smallint array actually gets an integer array and return type of a boolean array is bit.
Saneesh Apte s...@calccit.org wrote: the base type of a boolean[] is java.sql.Types.BIT instead or java.sql.Types.BOOLEAN. At the very least shouldn't these be aliases for the same type? And secondly the returned type from a smallint[] is an Integer[] instead of a Short[]. Should the objects in the array returned by getArray be typed according to the rules of an individual value returned by getObject? (I couldn't find anything explicit on that, but it seems reasonable.) If that *is* true, the controlling part of the spec is: http://java.sun.com/javase/6/docs/technotes/guides/jdbc/getstart/mapping.html#table3 That maps SQL types TINYINT, SMALLINT, AND INTEGER to Java Integer. It also maps SQL BIT to Java Boolean. The SQL type of BOOLEAN was a latecomer to the SQL spec, and it appears that JDBC hasn't yet added it to the mappings. Do you have a reference to something which indicates that getArray should use a different mapping? Maybe someone will see it differently, but I don't think I see a bug here. Compliance with the spec is not a bug, even if the spec is a bit odd ;-) -Kevin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Function works in 8.4 but not in 9.0 beta2 ERROR: structure of query does not match function result type
Marcel Asio marcel.a...@redbet.com writes: I've started testing our applications against PostgreSQL 9.0 beta2 and found that this function now does not work anymore(rewritten to be as small and anonymous as possible) CREATE TYPE test_type AS( product text, amount numeric(30,4) ); CREATE FUNCTION test_func() RETURNS SETOF test_type AS $$ BEGIN RETURN QUERY SELECT 'test'::text, 30.2::numeric; END; $$ LANGUAGE plpgsql STABLE; You need to actually coerce the 30.2 to numeric(30,4), not just numeric. The former behavior wasn't self-consistent. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Function works in 8.4 but not in 9.0 beta2 ERROR: structure of query does not match function result type
Hi Tom, Yes I managed to figured that out, but when was this changed? It was working in 8.4 but not 9.0 and I could not find anything about this in the release notes. On Jun 29, 2010, at 17:32 , Tom Lane wrote: Marcel Asio marcel.a...@redbet.com writes: I've started testing our applications against PostgreSQL 9.0 beta2 and found that this function now does not work anymore(rewritten to be as small and anonymous as possible) CREATE TYPE test_type AS( product text, amount numeric(30,4) ); CREATE FUNCTION test_func() RETURNS SETOF test_type AS $$ BEGIN RETURN QUERY SELECT 'test'::text, 30.2::numeric; END; $$ LANGUAGE plpgsql STABLE; You need to actually coerce the 30.2 to numeric(30,4), not just numeric. The former behavior wasn't self-consistent. regards, tom lane Regards Marcel Asio Network System Administrator Redbet Technology Mobile: +46 (0)709 13 04 01 Work: +46 (0)8 12 09 99 41 marcel.a...@redbet.com This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden.
Re: [BUGS] BUG #5520: PG unable to find java stored procs without input parameters
On Tue, Jun 22, 2010 at 8:30 AM, Peter Mengaziol pmengaz...@electrainfo.com wrote: The following bug has been logged online: Bug reference: 5520 Logged by: Peter Mengaziol Email address: pmengaz...@electrainfo.com PostgreSQL version: 8.4.4 Operating system: OS X 10.4.13 Description: PG unable to find java stored procs without input parameters Details: Under PG 8.4.1 and pl/java 1.4.0 have been unable to get a jsproc to be registered and found when there is not a single input parameter. The same code with a dummy input parameter is found and executed sucessfully. Since there's been no response here you might want to try the JDBC mailing list: http://archives.postgresql.org/pgsql-jdbc/ You might also want to provide some more details, like a complete test case that you think should work but doesn't. http://wiki.postgresql.org/wiki/Guide_to_reporting_problems -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [JDBC] [BUGS] JDBC: 2 bugs: Getting a smallint array actually gets an integer array and return type of a boolean array is bit.
On Mon, 28 Jun 2010, Saneesh Apte wrote: One is minor: the base type of a boolean[] is java.sql.Types.BIT instead or java.sql.Types.BOOLEAN. At the very least shouldn't these be aliases for the same type? These are aliases for the same type. I believe we accept either BOOLEAN or BIT as equivalent in all cases. We default to BIT for historical reasons because it was defined first in the JDBC2 spec while BOOLEAN came around in the JDBC3 version. And secondly the returned type from a smallint[] is an Integer[] instead of a Short[]. The JDBC spec says that the result of getObject on a Types.SMALLINT value should return Integer, so we have followed that for array types as well. The spec contains this historical note: The JDBC 1.0 specification defined the Java object mapping for the SMALLINT and TINYINT JDBC types to be Integer. The Java language did not include the Byte and Short data types when the JDBC 1.0 specification was finalized. The mapping of SMALLINT and TINYINT to Integer is maintained to preserve backwards compatibility For more information see table B-3 in the JDBC4.0 spec. Kris Jurka -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Function works in 8.4 but not in 9.0 beta2 ERROR: structure of query does not match function result type
Marcel Asio marcel.a...@redbet.com writes: Yes I managed to figured that out, but when was this changed? It was working in 8.4 but not 9.0 and I could not find anything about this in the release notes. The 9.0 release notes are not really up to snuff yet :-(. The only thing in the notes about it is Allow PL/pgSQL to handle row types with dropped columns which is a rather inadequate description of the consequences of that patch. We're probably going to need to call it out as an incompatibility. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Function works in 8.4 but not in 9.0 beta2 ERROR: structure of query does not match function result type
I was suspecting that it was incompatibility that hadn't been documented correctly, just wanted to make sure that this was the case. It probably should go into the release notes too since it is bound to affect others Thankfully Marcel Asio Network System Administrator Redbet Technology Mobile: +46 (0)709 13 04 01 Work: +46 (0)8 12 09 99 41 marcel.a...@redbet.com This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden. On 29 June 2010 19:38, Tom Lane t...@sss.pgh.pa.us wrote: Marcel Asio marcel.a...@redbet.com writes: Yes I managed to figured that out, but when was this changed? It was working in 8.4 but not 9.0 and I could not find anything about this in the release notes. The 9.0 release notes are not really up to snuff yet :-(. The only thing in the notes about it is Allow PL/pgSQL to handle row types with dropped columns which is a rather inadequate description of the consequences of that patch. We're probably going to need to call it out as an incompatibility. regards, tom lane
Re: [JDBC] [BUGS] JDBC: 2 bugs: Getting a smallint array actually gets an integer array and return type of a boolean array is bit.
Hi,
I think I found two bugs (and I hope I am not wasting everyone's
time).
One is minor: the base type of a boolean[] is java.sql.Types.BIT
instead or java.sql.Types.BOOLEAN. At the very least shouldn't these
be aliases for the same type?
And secondly the returned type from a smallint[] is an Integer[]
instead of a Short[].
So running this code:
The running of the supplied code also gives the same results with
PostgreSQL 8.3.3
results:
sun.jdbc.odbc.jdbcodbcdri...@fc9944: 2.1
org.postgresql.dri...@8b819f: 8.4
PostgreSQL 8.3.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.6
Integer[]
1 2
int4: 4 4
END Integer[]
Boolean[]
false true
bool: 16 -7 -7
END Boolean[]
Short[]
java.lang.ClassCastException: [Ljava.lang.Integer; cannot be cast to
[Ljava.lang.Short;
The 8.4 JDBC Driver though does gives a consistent result as past
versions via the
tableMetaData.getColumnClassName() tableMetaData.getColumnTypeName()
for the base types, integer, smallint, and boolean.
System.out.println(i + + colNameString + +
columnClass + + columnType + +
columnSize);
3 int_type java.lang.Integer int4 11
2 smallint_type java.lang.Integer int2 6
21 boolean_type java.lang.Boolean bool 1
1 ia java.sql.Array _int4 11
2 sa java.sql.Array _int2 6
3 ba java.sql.Array _bool 1
Attached slight modification to NewClass to correctly compile and drop
table if run
more than once.
danap.
public class NewClass2
{
public static void main(String[] args)
{
try
{
Class.forName(org.postgresql.Driver);
java.sql.Connection conn = java.sql.DriverManager.getConnection(
jdbc:postgresql://192.168.157.32:5432/dev, dev, devmm);
java.util.Enumerationjava.sql.Driver drivers =
java.sql.DriverManager.getDrivers();
while (drivers.hasMoreElements())
{
java.sql.Driver d = drivers.nextElement();
System.out.println(d.toString() + : + d.getMajorVersion() + .
+ d.getMinorVersion());
}
java.sql.PreparedStatement ps_ver = conn.prepareStatement(SELECT
version());
java.sql.ResultSet rs = ps_ver.executeQuery();
while (rs.next())
System.out.println(rs.getString(1));
java.sql.Statement dbStatement = conn.createStatement();
dbStatement.executeUpdate(DROP TABLE IF EXISTS public.aab);
java.sql.PreparedStatement ps_create = conn.prepareStatement(CREATE
TABLE public.aab (
+ ia integer[], + sa smallint[],
+ ba boolean[] ));
ps_create.executeUpdate();
java.sql.PreparedStatement ps_insert = conn
.prepareStatement(INSERT INTO public.aab (ia,sa,ba) VALUES
(?,?,?));
ps_insert.setArray(1, conn.createArrayOf(int4, new Integer[] {1,
2}));
ps_insert.setArray(2, conn.createArrayOf(int2, new Short[] {100,
200}));
ps_insert.setArray(3, conn.createArrayOf(bool, new Boolean[] {false,
true}));
ps_insert.executeUpdate();
java.sql.PreparedStatement ps_select = conn.prepareStatement(SELECT
ia,sa,ba FROM public.aab);
rs = ps_select.executeQuery();
java.sql.Array jdbcArr;
while (rs.next())
{
System.out.println(Integer[]);
jdbcArr = rs.getArray(ia);
Integer[] javaIntArr = (Integer[]) jdbcArr.getArray();
System.out.println(javaIntArr[0] + + javaIntArr[1]);
System.out.println(String.format(%s: %d %d,
jdbcArr.getBaseTypeName(), java.sql.Types.INTEGER,
jdbcArr.getBaseType()));
System.out.println(END Integer[]);
System.out.println(Boolean[]);
jdbcArr = rs.getArray(ba);
Boolean[] javaBooArr = (Boolean[]) jdbcArr.getArray();
System.out.println(javaBooArr[0] + + javaBooArr[1]);
System.out.println(String.format(%s: %d %d %d,
jdbcArr.getBaseTypeName(),
java.sql.Types.BOOLEAN, jdbcArr.getBaseType(),
java.sql.Types.BIT));
System.out.println(END Boolean[]);
System.out.println(Short[]);
jdbcArr = rs.getArray(sa);
Short[] javaShoArr = (Short[]) jdbcArr.getArray();
System.out.println(javaShoArr[0] + + javaShoArr[1]);
System.out.println(String.format(%s: %d %d,
jdbcArr.getBaseTypeName(), java.sql.Types.SMALLINT,
jdbcArr.getBaseType()));
System.out.println(END Short[]);
}
rs.close();
dbStatement.close();
conn.close();
}
catch (Exception e)
{
System.out.println(e.toString());
}
} // main
} // class
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your
[BUGS] BUG #5530: Default cost of crypt causes poor decisions
The following bug has been logged online: Bug reference: 5530 Logged by: Baegle Email address: a...@wifiny.org PostgreSQL version: 8.4.4 Operating system: Linux - Ubuntu - Lucid Lynx - 2.6.31-19-generic Description:Default cost of crypt causes poor decisions Details: The default cost of crypt() is 1, but crypt is intentionally a high cost function. This mis-match between assigned cost and actual cost causes the query planner to use crypt too soon and slows down execution of queries significantly. The cost of the function should be more congruent with it's intended cost. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Function works in 8.4 but not in 9.0 beta2 ERROR: structure of query does not match function result type
Marcel Asio marcel.a...@redbet.com writes: I was suspecting that it was incompatibility that hadn't been documented correctly, just wanted to make sure that this was the case. It probably should go into the release notes too since it is bound to affect others Yeah, done. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5530: Default cost of crypt causes poor decisions
Baegle a...@wifiny.org writes: The default cost of crypt() is 1, but crypt is intentionally a high cost function. This mis-match between assigned cost and actual cost causes the query planner to use crypt too soon and slows down execution of queries significantly. The cost of the function should be more congruent with it's intended cost. Perhaps, but the same could be said of most of the contrib/pgcrypto functions, no? Which ones do you think should have a nondefault cost, and what should we set it to? Also, it'd be easier to persuade people this is worth worrying about if you showed a real-world case where it's a problem. crypt() doesn't seem like a function that would be very likely to be used in contexts where this'd matter. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [JDBC] [BUGS] JDBC: 2 bugs: Getting a smallint array actually gets an integer array and return type of a boolean array is bit.
Saneesh Apte wrote: I think I found two bugs (and I hope I am not wasting everyone's time). One is minor: the base type of a boolean[] is java.sql.Types.BIT instead or java.sql.Types.BOOLEAN. At the very least shouldn't these be aliases for the same type? Types.BOOLEAN does not exist before JDBC3. Client code can use either, but we have to pick one or the other when returning a value, so we return BIT. And secondly the returned type from a smallint[] is an Integer[] instead of a Short[]. smallint is a Types.SMALLINT which is mapped to java.lang.Integer by the JDBC spec. See appendix B of the spec: Note – The JDBC 1.0 specification defined the Java object mapping for the SMALLINT and TINYINT JDBC types to be Integer. The Java language did not include the Byte and Short data types when the JDBC 1.0 specification was finalized. The mapping of SMALLINT and TINYINT to Integer is maintained to preserve backwards compatibility. -O -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
