Unexpected or wrong result and no warning/error, bug?

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Unexpected or wrong result and no warning/error, bug?

Tony Papadimitriou
create table t(s varchar(5));

insert into t values('US'),('USA');

update t set s = replace(s, 'USA', '___'),
             s = replace(s,'US','USA'),
             s = replace(s,'___','USA');

select * from t;

-- Expected answer:
-- USA
-- USA
--------------------------------------------------
-- MySQL gets it right
-- Postgres prints error about setting the same column multiple times
-- SQLite3 (latest and older) no changes or wrong result but no error/warning
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Unexpected or wrong result and no warning/error, bug?

Shawn Wagner
From the documentation (https://www.sqlite.org/lang_update.html)

If a single column-name appears more than once in the list of assignment
expressions, all but the rightmost occurrence is ignored.


On Fri, Apr 12, 2019, 9:00 AM Tony Papadimitriou <[hidden email]> wrote:

> create table t(s varchar(5));
>
> insert into t values('US'),('USA');
>
> update t set s = replace(s, 'USA', '___'),
>              s = replace(s,'US','USA'),
>              s = replace(s,'___','USA');
>
> select * from t;
>
> -- Expected answer:
> -- USA
> -- USA
> --------------------------------------------------
> -- MySQL gets it right
> -- Postgres prints error about setting the same column multiple times
> -- SQLite3 (latest and older) no changes or wrong result but no
> error/warning
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Unexpected or wrong result and no warning/error, bug?

Chris Locke-3
> create table t(s varchar(5));

Also note that SQLite doesn't 'understand' varchar (it uses text) and it
doesn't limit the entry to 5 characters.
This doesn't help your issue directly, but does highlight that you've not
read the SQLite documentation, and aren't creating tables properly.


On Fri, Apr 12, 2019 at 5:06 PM Shawn Wagner <[hidden email]>
wrote:

> From the documentation (https://www.sqlite.org/lang_update.html)
>
> If a single column-name appears more than once in the list of assignment
> expressions, all but the rightmost occurrence is ignored.
>
>
> On Fri, Apr 12, 2019, 9:00 AM Tony Papadimitriou <[hidden email]> wrote:
>
> > create table t(s varchar(5));
> >
> > insert into t values('US'),('USA');
> >
> > update t set s = replace(s, 'USA', '___'),
> >              s = replace(s,'US','USA'),
> >              s = replace(s,'___','USA');
> >
> > select * from t;
> >
> > -- Expected answer:
> > -- USA
> > -- USA
> > --------------------------------------------------
> > -- MySQL gets it right
> > -- Postgres prints error about setting the same column multiple times
> > -- SQLite3 (latest and older) no changes or wrong result but no
> > error/warning
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Unexpected or wrong result and no warning/error, bug?

Tony Papadimitriou
I know this, thanks.  I simply made a test case that can be run in MySQL,
Postgreq and SQLite3.

-----Original Message-----
From: Chris Locke

> create table t(s varchar(5));

Also note that SQLite doesn't 'understand' varchar (it uses text) and it
doesn't limit the entry to 5 characters.
This doesn't help your issue directly, but does highlight that you've not
read the SQLite documentation, and aren't creating tables properly.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Unexpected or wrong result and no warning/error, bug?

Simon Slavin-3
In reply to this post by Tony Papadimitriou
On 12 Apr 2019, at 5:00pm, Tony Papadimitriou <[hidden email]> wrote:

> update t set s = replace(s, 'USA', '___'),
>             s = replace(s,'US','USA'),
>             s = replace(s,'___','USA');

To add to the answers other people gave, there's no set order for SQL to process these changes.  The SQL definition doesn't specify that they'll be done in any particular order.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Unexpected or wrong result and no warning/error, bug?

Tony Papadimitriou
True, but SQLite3 is known to provide several conveniences that are not
necessarily standard SQL.

-----Original Message-----
From: Simon Slavin
Sent: Friday, April 12, 2019 7:11 PM

On 12 Apr 2019, at 5:00pm, Tony Papadimitriou <[hidden email]> wrote:

> update t set s = replace(s, 'USA', '___'),
>             s = replace(s,'US','USA'),
>             s = replace(s,'___','USA');

To add to the answers other people gave, there's no set order for SQL to
process these changes.  The SQL definition doesn't specify that they'll be
done in any particular order.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users