Обсуждение: 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.

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.

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