UPDATE on NOT NULL FIELDS

Hi!

When I set a field to NULL which is defined as NOT NULL via UPDATE the field
is set to an empty string or to 0, instead to give an error.
INSERT displays an error.

How-to-repeat:

mysql> create table bar (foo varchar(10));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into bar values("MySQL");
Query OK, 1 row affected (0.00 sec)

mysql> drop table bar;
Query OK, 0 rows affected (0.00 sec)

mysql> create table foo (bar varchar(10) not null);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into foo values ("lenz");
Query OK, 1 row affected (0.00 sec)

mysql> update foo set bar=NULL;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1

mysql> select * from foo;
+-----+
| bar |
+-----+
| |
+-----+
1 row in set (0.00 sec)

mysql> insert into foo values(NULL);
ERROR 1048: Column 'bar' cannot be null


Regards

Georg




------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13275@lists.mysql.com
To unsubscribe, e-mail

Re: UPDATE on NOT NULL FIELDS

On Saturday 14 December 2002 21:25, you wrote:

Hello Sinisa,


> I do not think that this is as easily doable as with INSERT's.
>
> Problem is that UPDATE can update lot's of rows and simultaneously get
> valuses from other columns, so we would face a problem of having to
> abort after N rows have been already updated on a non-transactional
> table.


We have the same problem with INSERT into SELECT from ...
I agree thats a problem for non transactional tables, but it doesn't work
for transactional tables (innodb) too.

Maybe it's better to let the row or column unchanged and give a warning.
NULL is not defined - and not an empty string or 0.

I just tested in Postgres, and it's ok there.

Regards

Georg


filter:how-to-repeat


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13280@lists.mysql.com
To unsubscribe, e-mail

Re: UPDATE on NOT NULL FIELDS

Georg Richter writes:
> Hi!
>
> When I set a field to NULL which is defined as NOT NULL via UPDATE the field
> is set to an empty string or to 0, instead to give an error.
> INSERT displays an error.
>
> How-to-repeat:
>

[skip]

>
> mysql> create table foo (bar varchar(10) not null);
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> insert into foo values ("lenz");
> Query OK, 1 row affected (0.00 sec)
>
> mysql> update foo set bar=NULL;
> Query OK, 1 row affected (0.00 sec)
> Rows matched: 1 Changed: 1 Warnings: 1
>
> mysql> select * from foo;
> +-----+
> | bar |
> +-----+
> | |
> +-----+
> 1 row in set (0.00 sec)
>
> mysql> insert into foo values(NULL);
> ERROR 1048: Column 'bar' cannot be null
>
>
> Regards
>
> Georg

HI!

I do not think that this is as easily doable as with INSERT's.

Problem is that UPDATE can update lot's of rows and simultaneously get
valuses from other columns, so we would face a problem of having to
abort after N rows have been already updated on a non-transactional
table.


--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13278@lists.mysql.com
To unsubscribe, e-mail

Re: UPDATE on NOT NULL FIELDS

Hi Sinisa,

I don't understand why ?
Why not checking if "bar" column can accept 'NULL' and test if the inserted
field is a NULL value (since we have &fields and &values in mysql_update
inside sql_update.cc, we just need to iterate through each fields/values and
test if (value->save_in_field(field, 1) > 0), and in this case return an
error ?)

Regards,
Jocelyn
----- Original Message -----
From: "Sinisa Milivojevic"
To:
Cc:
Sent: Saturday, December 14, 2002 8:25 PM
Subject: Re: UPDATE on NOT NULL FIELDS


> Georg Richter writes:
> > Hi!
> >
> > When I set a field to NULL which is defined as NOT NULL via UPDATE the
field
> > is set to an empty string or to 0, instead to give an error.
> > INSERT displays an error.
> >
> > How-to-repeat:
> >
>
> [skip]
>
> >
> > mysql> create table foo (bar varchar(10) not null);
> > Query OK, 0 rows affected (0.00 sec)
> >
> > mysql> insert into foo values ("lenz");
> > Query OK, 1 row affected (0.00 sec)
> >
> > mysql> update foo set bar=NULL;
> > Query OK, 1 row affected (0.00 sec)
> > Rows matched: 1 Changed: 1 Warnings: 1
> >
> > mysql> select * from foo;
> > +-----+
> > | bar |
> > +-----+
> > | |
> > +-----+
> > 1 row in set (0.00 sec)
> >
> > mysql> insert into foo values(NULL);
> > ERROR 1048: Column 'bar' cannot be null
> >
> >
> > Regards
> >
> > Georg
>
> HI!
>
> I do not think that this is as easily doable as with INSERT's.
>
> Problem is that UPDATE can update lot's of rows and simultaneously get
> valuses from other columns, so we would face a problem of having to
> abort after N rows have been already updated on a non-transactional
> table.
>
>
> --
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
> /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
> <___/ www.mysql.com
>
> Join MySQL Users Conference and Expo:
> http://www.mysql.com/events/uc2003/
>
>
> ------------------------------------------------------------ ---------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail bugs-thread13278@lists.mysql.com
> To unsubscribe, e-mail
>
>
>
>


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13279@lists.mysql.com
To unsubscribe, e-mail

Re: UPDATE on NOT NULL FIELDS

Jocelyn Fournier writes:
> Hi Sinisa,
>
> I don't understand why ?
> Why not checking if "bar" column can accept 'NULL' and test if the inserted
> field is a NULL value (since we have &fields and &values in mysql_update
> inside sql_update.cc, we just need to iterate through each fields/values and
> test if (value->save_in_field(field, 1) > 0), and in this case return an
> error ?)
>
> Regards,
> Jocelyn

I said it is possible, but not as easy.

If you have columns a, b and c in table t1, you can do this:

update t1 set a=b where c > 1;

And if a is NOT NULL and at 1000th row value of column b is NULL, and
then what to do ??

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13281@lists.mysql.com
To unsubscribe, e-mail

Re: UPDATE on NOT NULL FIELDS

Georg Richter writes:
> On Saturday 14 December 2002 21:25, you wrote:
>
> Hello Sinisa,
>
> We have the same problem with INSERT into SELECT from ...
> I agree thats a problem for non transactional tables, but it doesn't work
> for transactional tables (innodb) too.
>
> Maybe it's better to let the row or column unchanged and give a warning.
> NULL is not defined - and not an empty string or 0.
>
> I just tested in Postgres, and it's ok there.
>
> Regards
>
> Georg
>
>
> filter:how-to-repeat
>
>

Yes, with transactional tables it would be much easier.

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13282@lists.mysql.com
To unsubscribe, e-mail

Re: UPDATE on NOT NULL FIELDS

----- Original Message -----
From: "Sinisa Milivojevic"
To:
Cc: ;
Sent: Saturday, December 14, 2002 9:02 PM
Subject: Re: UPDATE on NOT NULL FIELDS


> Jocelyn Fournier writes:
> > Hi Sinisa,
> >
> > I don't understand why ?
> > Why not checking if "bar" column can accept 'NULL' and test if the
inserted
> > field is a NULL value (since we have &fields and &values in mysql_update
> > inside sql_update.cc, we just need to iterate through each fields/values
and
> > test if (value->save_in_field(field, 1) > 0), and in this case return an
> > error ?)
> >
> > Regards,
> > Jocelyn
>
> I said it is possible, but not as easy.
>
> If you have columns a, b and c in table t1, you can do this:
>
> update t1 set a=b where c > 1;
>
> And if a is NOT NULL and at 1000th row value of column b is NULL, and
> then what to do ??

Perhaps as Georg was suggesting just not update this row value and return a
warning ?

Regards,
Jocelyn


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13283@lists.mysql.com
To unsubscribe, e-mail

Re: UPDATE on NOT NULL FIELDS

On Saturday 14 December 2002 22:59, Georg Richter wrote:

>
> > I do not think that this is as easily doable as with INSERT's.
> >
> > Problem is that UPDATE can update lot's of rows and simultaneously get
> > valuses from other columns, so we would face a problem of having to
> > abort after N rows have been already updated on a non-transactional
> > table.
>
>
> We have the same problem with INSERT into SELECT from ...
> I agree thats a problem for non transactional tables, but it doesn't work
> for transactional tables (innodb) too.
>
> Maybe it's better to let the row or column unchanged and give a warning.
> NULL is not defined - and not an empty string or 0.
>
> I just tested in Postgres, and it's ok there.

I just shall know this is known problem and there are the plans to fix it for
transaction tables, according to my information.

I can't give exact timelines however.

--
MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Peter Zaitsev
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Moscow, Russia
<___/ www.mysql.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13288@lists.mysql.com
To unsubscribe, e-mail