Обсуждение: BUG #5614: Varchar column (with DEFAULT NULL) stores 'UL' value instead of null

The following bug has been logged online:

Bug reference:      5614
Logged by:          Mariusz Majer
Email address:      mmajer@janmedia.com
PostgreSQL version: 8.3.11
Operating system:   Debian (Linux 2.6.26-1-686-bigmem #1 SMP i686 GNU/Linux)
Description:        Varchar column (with DEFAULT NULL) stores 'UL' value
instead of null
Details:

Varchar column store 'UL' value instead of null

There has been a table ecom2_orders for a while (~0.5m records). After
executing query:

ALTER TABLE ecom2_orders ADD COLUMN password_pdf character varying(50);

when new rows are added, column password_pdf is filled with 'UL' value
rather than null

psql>\d table
(...)
Column description:
password_pdf | character varying(50) | default NULL::character varying

version 8.3.11 - has got above bug
version 8.1.19 - works fine

"Mariusz Majer" <mmajer@janmedia.com> writes:
> There has been a table ecom2_orders for a while (~0.5m records). After
> executing query:

> ALTER TABLE ecom2_orders ADD COLUMN password_pdf character varying(50);

> when new rows are added, column password_pdf is filled with 'UL' value
> rather than null

Works fine here.  Perhaps you have an incorrect build of 8.3.11?

Another possibility is that you omitted some important step.
Ordinarily, \d wouldn't show any explicit "default" modifier for a
column created as above.  Maybe you did an ALTER SET DEFAULT in a
strange way?

            regards, tom lane

More info on this issue:
1. SQL queries where executed using JDBC.
2. I was trying to repeat the bug (executing 'direct queries', from psql)- I
created a new table, added some rows, then alter table adding varchar column
field (password_pdf) with DEFAULT NULL, added some rows problem does not
occur neither in 8.3.11 nor 8.1.19 - so it is not easy to repeat it
3. Another fact: after removing explicit DEFAULT NULL on column, everything
works fine
4. Problem occurs on production database instance so that I cannot test it
there. I will try to copy database on another postgre 8.3.11 instance and
repeat the bug to provide more details on this but this might take a while

> Maybe you did an ALTER SET DEFAULT in a strange way?

Hmm, rather not possible, SQL queries adding column (with DEFAULT NULL) were
executed from the application and the same (for sure) queries were executed
on development (8.1.19), test (8.1.19) and production (8.3.11) database. As
I wrote before only on production site

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/BUG-5614-Varchar-column-with-DEFAULT-NULL-stores-UL-value-instead-of-null-tp2473415p2596150.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

"mmajer@janmedia.com" <mmajer@janmedia.com> writes:
> 1. SQL queries where executed using JDBC.
> 2. I was trying to repeat the bug (executing 'direct queries', from psql)- I
> created a new table, added some rows, then alter table adding varchar column
> field (password_pdf) with DEFAULT NULL, added some rows problem does not
> occur neither in 8.3.11 nor 8.1.19 - so it is not easy to repeat it

Maybe JDBC is an essential aspect somehow?  Although if it had messed up
the ALTER TABLE command you'd think that would be visible in the \d output.

It would be useful to see the stored version of the default in the
misbehaving table, ie

    select * from pg_attrdef where adrelid = 'ecom2_orders'::regclass;

            regards, tom lane

I am using postgresql-8.2-504.jdbc3.jar driver.

I altered table ecom2_orders using psql (not JDBC to exclude its influence):
ALTER TABLE ecom2_orders ALTER COLUMN net_source SET DEFAULT NULL;

Now when you enter command:
\d ecom2_orders
you got:
net_source                      | character varying(100)      | default
NULL::character varying
password_pdf                    | character varying(50)       |

in dump.txt result of execution query you provided
> select * from pg_attrdef where adrelid = 'ecom2_orders'::regclass;

When I add one row from psql:
- in net_source I got null value,
while adding new order from application using JDBC
- in net_source I got 'UL' value
...so in my opinion it might be issue with JDBC (maybe too old JDBC??).

http://postgresql.1045698.n5.nabble.com/file/n2636403/descr.txt descr.txt

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/BUG-5614-Varchar-column-with-DEFAULT-NULL-stores-UL-value-instead-of-null-tp2473415p2636403.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

Mariusz Majer <mmajer@janmedia.com> writes:
> I am using postgresql-8.2-504.jdbc3.jar driver.
> When I add one row from psql:
> - in net_source I got null value,
> while adding new order from application using JDBC
> - in net_source I got 'UL' value
> ...so in my opinion it might be issue with JDBC (maybe too old JDBC??).

Maybe.  These dumps seem to be from a newer-than-8.2 server, so it'd be
a good idea to update to a matching JDBC version in any case.

> in dump.txt result of execution query you provided
>> select * from pg_attrdef where adrelid = 'ecom2_orders'::regclass;
> http://postgresql.1045698.n5.nabble.com/file/n2636403/descr.txt descr.txt

These results look perfectly normal to me.  You're sure you got the data
for a malfunctioning table, not a correct table?

            regards, tom lane