Thread: Bug in DatabaseMetaData.getColumns() with columns based on domains
Hi, consider the following table and domain: CREATE DOMAIN salary_domain AS numeric(12,2) NOT NULL CHECK (value > 0); CREATE TABLE employee (id integer not null, salary salary_domain); DatabaseMetaData.getColumns(null, "public", "employee", "%"); returns "YES" for the column IS_NULLABLE in the ResultSet whereas it should flag that column as not nullable. Regards Thomas
Thomas Kellerer wrote: > Hi, > > consider the following table and domain: > > CREATE DOMAIN salary_domain AS numeric(12,2) NOT NULL CHECK (value > 0); > CREATE TABLE employee (id integer not null, salary salary_domain); > > DatabaseMetaData.getColumns(null, "public", "employee", "%"); > > returns "YES" for the column IS_NULLABLE in the ResultSet whereas it > should flag that column as not nullable. > > Regards > Thomas > > Perhaps you could provide the Database and JDBC versions to help those that will need to review the report. I have confirmed on PostgreSQL 9.0.1 and JDBC postgresql-9.0-801.jdbc3. Output: Connection Created SELECT * FROM "public"."employee" LIMIT 1 Column Name: id IS_NULLABLE: NO Column Name: salary IS_NULLABLE: YES Connection Closed Attached files needed to setup test case and other reviewed data. danap.
Attachment
dmp wrote on 21.10.2010 19:57: >> consider the following table and domain: >> >> CREATE DOMAIN salary_domain AS numeric(12,2) NOT NULL CHECK (value > 0); >> CREATE TABLE employee (id integer not null, salary salary_domain); >> >> DatabaseMetaData.getColumns(null, "public", "employee", "%"); >> >> returns "YES" for the column IS_NULLABLE in the ResultSet whereas it >> should flag that column as not nullable. >> > Perhaps you could provide the Database and JDBC versions to help those that > will need to review the report. I have confirmed on PostgreSQL 9.0.1 and > JDBC postgresql-9.0-801.jdbc3. Sorry, you are right I should have mentioned that. I tried it with the exact same version as you (PG 9.0.1 and JDBC 9.0-801) Regards Thomas
Thomas Kellerer wrote on 21.10.2010 14:57: > Hi, > > consider the following table and domain: > > CREATE DOMAIN salary_domain AS numeric(12,2) NOT NULL CHECK (value > 0); > CREATE TABLE employee (id integer not null, salary salary_domain); > > DatabaseMetaData.getColumns(null, "public", "employee", "%"); > > returns "YES" for the column IS_NULLABLE in the ResultSet whereas it should flag that column as not nullable. > OK, I realized that not even psql or pgAdmin will display such a column as "not null" So it is not a JDBC driver issue (but rather a PostgreSQL "core" issue) Sorry for the noise Thomas
Thomas Kellerer wrote: > Thomas Kellerer wrote on 21.10.2010 14:57: >> Hi, >> >> consider the following table and domain: >> >> CREATE DOMAIN salary_domain AS numeric(12,2) NOT NULL CHECK (value > 0); >> CREATE TABLE employee (id integer not null, salary salary_domain); >> >> DatabaseMetaData.getColumns(null, "public", "employee", "%"); >> >> returns "YES" for the column IS_NULLABLE in the ResultSet whereas it >> should flag that column as not nullable. >> > > OK, I realized that not even psql or pgAdmin will display such a column > as "not null" > So it is not a JDBC driver issue (but rather a PostgreSQL "core" issue) > > Sorry for the noise > Thomas > > When the table field salary is defined directly the correct attribute for IS_NULLABLE is returned by the DatabaseMetaData.getColumns(). When the field is defined indirectly through the DOMAIN then the database appears to be not setting the attnotnull entry correspondingly. As you indicated I would also say it is the database. The attached files indicate the sql statement generated by the JDBC and the result set for the getColumns() for the table. The early attached file query for the column's information was from the information_schema.columns table and does show the correct result for the salary field IS_NULLABLE. danap.
Attachment
On Thu, 21 Oct 2010, dmp wrote: > The early attached file query for the column's information was from the > information_schema.columns table and does show the correct result for the > salary field IS_NULLABLE. > Thanks for the hint. Doing the same thing that the information_schema does was pretty easy. I've fixed this in CVS and I've also adjusted ResultSetMetaData.isNullable to have the same behavior. I've backpatched this to 8.4 because that's as far as was easy. Here's a jar you can test with: http://ejurka.com/pgsql/jars/domnn/ Kris Jurka
> On Thu, 21 Oct 2010, dmp wrote: > >> The early attached file query for the column's information was from >> the information_schema.columns table and does show the correct result >> for the >> salary field IS_NULLABLE. >> > > Kris Jurka wrote: > Thanks for the hint. Doing the same thing that the information_schema > does was pretty easy. I've fixed this in CVS and I've also adjusted > ResultSetMetaData.isNullable to have the same behavior. I've backpatched > this to 8.4 because that's as far as was easy. > > Here's a jar you can test with: > > http://ejurka.com/pgsql/jars/domnn/ > > Kris Jurka > When I looked at the code and obtained the sql that was used to get the columns IS_NULLABLE it was clear the difference in the methods that the JDBC and I used in my project to create table definitions. At that time I was advised either here or in the general forum to use the Information_schema. That way it was indicated that the table definition generation would suffer less from internal database table changes. I do not know if this is valid for the JDBC, but this is why I sent along that last piece of information. MyJSQLView though limited was generating the correct column default result for the salary field. I will update my local copy and take a look at your changes. danap.
Kris Jurka wrote on 23.10.2010 09:37: > Thanks for the hint. Doing the same thing that the information_schema > does was pretty easy. I've fixed this in CVS and I've also adjusted > ResultSetMetaData.isNullable to have the same behavior. I've > backpatched this to 8.4 because that's as far as was easy. > > Here's a jar you can test with: > > http://ejurka.com/pgsql/jars/domnn/ > Thanks for the quick reaction. I didn't expect this to be a client-side only problem (including psql ;) With my little example, the nullable attribute is displayed correctly with your version of the driver. Could you also look into the values reported for the TYPE_NAME in getColumns() for domains? I posted a bug reported that the domain's name is not fully qualified so that it's impossible to distinguish between thesame domains in different schemas... Regards Thomas