Possible bug in Alter Table

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

Possible bug in Alter Table

Balaji Ramanathan
Hi,

    I am writing to report a possible bug in the Alter Table command.  It
seems to fail on a somewhat complex schema with multiple tables and views,
and views that reference other views.  I was not able to come up with a
simple one table, one view schema where this error actually shows up.  That
is why I am not able to post the schema here for you to view directly.  So,
please refer to the database test.db located on Google Drive at the
following address:
https://drive.google.com/open?id=0B5B_T2PA2u7ddTdlc1JST0xyVjg

    In this database, I have a table Trip, that has a column
"StartGMTOffset".  I have multiple views referencing this column, but no
indexes or triggers on this column.  I am running SQLite version 3.25.3.  I
now issue the following command at the SQLite command prompt and I get the
response below:

SQLite> Alter Table Trip rename column StartGMTOffset to StartUTCOffset;
Error: error in view CumulativeStatisticsByPlaceName after rename: no such
column: StartGMTOffset

    Well, duh!  There is no such column because I just renamed it.  And I
expected the Alter Table command to find and replace all occurrences of
that column name in my schema with the new name.  But maybe, it only works
for a table and views that reference it directly, but not views that
reference views that reference the table?  I am not sure.  But I thought I
would report it anyway.

    I apologize if I misunderstood the documentation regarding the alter
table command, or mangled the syntax in some way.  Thank you.

Balaji Ramanathan
_______________________________________________
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: Possible bug in Alter Table

Digital Dog
On Mon, Nov 26, 2018 at 2:52 AM Balaji Ramanathan <
[hidden email]> wrote:

> SQLite> Alter Table Trip rename column StartGMTOffset to StartUTCOffset;
> Error: error in view CumulativeStatisticsByPlaceName after rename: no such
> column: StartGMTOffset
>
> I was able to reproduce this behaviour:

C:\temp>sqlite3 bug.sqlite
SQLite version 3.25.3 2018-11-05 20:37:38
Enter ".help" for usage hints.
sqlite> create table t1 (c1 int);
sqlite> create view v1 as select c1 from t1;
sqlite> create view v11 as select null from t1 left join v1 on v1.c1=t1.c1;
sqlite> alter table t1 rename column c1 to c2;
Error: error in view v11 after rename: no such column: v1.c1
sqlite>
_______________________________________________
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: Possible bug in Alter Table

Richard Hipp-3
In reply to this post by Balaji Ramanathan
On 11/25/18, Balaji Ramanathan <[hidden email]> wrote:
> I expected the Alter Table command to find and replace all occurrences of
> that column name in my schema with the new name.

Here is simplified SQL that illustrates the problem:

CREATE TABLE t1(x);
CREATE VIEW v1 AS SELECT x FROM t1;
CREATE VIEW v2 AS SELECT x FROM v1;
ALTER TABLE t1 RENAME x TO y;

The ALTER TABLE fails because after changing the name of t1.x to t1.y,
the definition of the view v2 is no longer valid.

This is not something we intend to "fix" in SQLite.  The root of the
problem is that the column names for the v1 view are not specified.
And since they are not specified, that means SQLite is free to pick
whatever arbitrary names it wants for those columns.  The definition
of view v2 depends on one particular algorithm for picking the column
names of view v1, but there are no guarantees that every version of
SQLite will use that particular algorithm.  Hence, the definition of
view v2 is under-specified and prone to failure, such as in this case.

If you are careful to defined the names of all columns within your
views, then the problem goes away.  For example:

CREATE TABLE t1(x);
CREATE VIEW v1(y) AS SELECT x FROM t1;
CREATE VIEW v2(z) AS SELECT y FROM v1;
ALTER TABLE t1 RENAME x TO y;

Or:

CREATE TABLE t1(x);
CREATE VIEW v1 AS SELECT x AS y FROM t1;
CREATE VIEW v2 AS SELECT y AS z FROM v1;
ALTER TABLE t1 RENAME x TO y;

If you do not specify the names of columns in views, then SQLite is
free to choose whatever names it wants for those columns, and the
choices might shift after an ALTER TABLE, which could then break
queries and/or downstream views.  So it is best not to do that.

Admittedly, this is not well-documented.  I will strive to improve the
documentation for the next release.  Perhaps I will also add a
"warning" mechanism to alert programmers to gotchas like this in some
subsequent release, though there probably is not time to get warnings
in to the forthcoming 3.26.0 release.

--
D. Richard Hipp
[hidden email]
_______________________________________________
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: Possible bug in Alter Table

Petite Abeille-2


> On Nov 26, 2018, at 20:11, Richard Hipp <[hidden email]> wrote:
>
> though there probably is not time to get warnings in to the forthcoming 3.26.0 release.

Talking of which, the CLI doesn’t seem to handle the following statement very gracefully:

sqlite> select DATE '1998-12-25’;
  ...>
  ...>
  …>

Note how the CLI doesn’t recognize the semicolon marking the end-of-statement and expects more input.  

sqlite3 -version

3.25.3 2018-11-05 20:37:38 89e099fbe5e13c33e683bef07361231ca525b88f7907be7092058007b75036f2
_______________________________________________
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: Possible bug in Alter Table

Richard Hipp-3
On 11/26/18, Petite Abeille <[hidden email]> wrote:
> the CLI doesn’t seem to handle the following statement
> very gracefully:
>
> sqlite> select DATE '1998-12-25’;
>   ...>
>   ...>
>   …>

Unable to repro:

SQLite version 3.25.3 2018-11-05 20:37:38
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select DATE'1998-12-25';
Error: no such column: DATE
sqlite> select DATE '1998-12-25';
Error: no such column: DATE
sqlite>



--
D. Richard Hipp
[hidden email]
_______________________________________________
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: Possible bug in Alter Table

Petite Abeille-2


> On Nov 26, 2018, at 20:25, Richard Hipp <[hidden email]> wrote:
>
> Unable to repro:

Hmmm… ok… then… local problem of some type…

$ uname -a
Darwin 18.2.0 Darwin Kernel Version 18.2.0: Fri Oct  5 19:41:49 PDT 2018; root:xnu-4903.221.2~2/RELEASE_X86_64 x86_64

$ brew info sqlite3
sqlite: stable 3.25.3 (bottled) [keg-only]

==> Dependencies
Required: readline ✔
==> Options
--with-fts
        Enable the FTS3 module
--with-fts5
        Enable the FTS5 module (experimental)
--with-functions
        Enable more math and string functions for SQL queries
--with-json1
        Enable the JSON1 extension


No clue what could interfere with the CLI.



_______________________________________________
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: Possible bug in Alter Table

Shawn Wagner
In reply to this post by Petite Abeille-2
It's waiting on a plain single quote to end the string. You have a Unicode
smart quote character U+2019 (’) instead of a ' at the end before the
semicolon, which doesn't count.

On Mon, Nov 26, 2018, 11:19 AM Petite Abeille <[hidden email]
wrote:

>
>
> > On Nov 26, 2018, at 20:11, Richard Hipp <[hidden email]> wrote:
> >
> > though there probably is not time to get warnings in to the forthcoming
> 3.26.0 release.
>
> Talking of which, the CLI doesn’t seem to handle the following statement
> very gracefully:
>
> sqlite> select DATE '1998-12-25’;
>   ...>
>   ...>
>   …>
>
> Note how the CLI doesn’t recognize the semicolon marking the
> end-of-statement and expects more input.
>
> sqlite3 -version
>
> 3.25.3 2018-11-05 20:37:38
> 89e099fbe5e13c33e683bef07361231ca525b88f7907be7092058007b75036f2
> _______________________________________________
> 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: Possible bug in Alter Table

Petite Abeille-2


> On Nov 26, 2018, at 21:16, Shawn Wagner <[hidden email]> wrote:
>
> It's waiting on a plain single quote to end the string. You have a Unicode
> smart quote character U+2019 (’) instead of a ' at the end before the
> semicolon, which doesn't count.

D’oh. Facepalm. Right you are. Long live Unicode! :)

_______________________________________________
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: Possible bug in Alter Table

Keith Medcalf
In reply to this post by Petite Abeille-2

On Monday, 26 November, 2018 12:19, Petite Abeille <[hidden email]> wrote:

...

> Talking of which, the CLI doesn’t seem to handle the following
> statement very gracefully:
>
> sqlite> select DATE '1998-12-25’;
>   ...>
>   ...>
>   …>

>Note how the CLI doesn’t recognize the semicolon marking the end-of-
>statement and expects more input.

>sqlite3 -version
>
>3.25.3 2018-11-05 20:37:38
>89e099fbe5e13c33e683bef07361231ca525b88f7907be7092058007b75036f2

That is because you are using a "goofy quote" and not an ASCII Quotation mark.  You can tell cuz it is all curvy and cutie-pie -- it does *not* close the quoted string because it is just-another-unicode-character with no special meaning ... you need to enter a closing quote-mark in order to terminate the quoted string ...

’ is not a closing quote.  A closing quote looks like this ' and matches the opening quote that you used.  I also doubt that a text mode program used the … character in its prompt for more input (especially since the two lines above that use three ascii periods (...) and not the single-character-unicode … .  

You have something mucking about and "helping you" to be cutie-pie.  If you turn that crap off, your problems will go away...

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.





_______________________________________________
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: Possible bug in Alter Table

wmertens
>
>
> You have something mucking about and "helping you" to be cutie-pie.  If
> you turn that crap off, your problems will go away...
>

If it's on a mac, this terrible misfeature can be turned off in system
preferences - keyboard - text - smart quotes.

I lost a couple hours this way too, I paired with a colleague on something
and then sent the result to me. Sometime later I notice that CSS is broken
for our app and I finally figure out it's a cute quote disabling all the
rules from where it is. Grr.

Wout.

>
_______________________________________________
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: Possible bug in Alter Table

Petite Abeille-2


> On Nov 27, 2018, at 06:16, Wout Mertens <[hidden email]> wrote:
>
> If it's on a mac, this terrible misfeature can be turned off in system
> preferences - keyboard - text - smart quotes.

Oh my... right you are :|

Grrr indeed.

_______________________________________________
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: Possible bug in Alter Table

Balaji Ramanathan
In reply to this post by Balaji Ramanathan
Thank you, Richard.  I can understand how not naming the columns of a view
can lead to ambiguities and other problems down the line.  Hopefully the
documentation will be updated so that users are aware that the alter table
command can't really deal with cascading effects such as in the case of
views referring to other views, etc.

Is there a way to unravel a complex schema so that you can identify which
views are based directly on tables and which views refer to other views,
and what those other views are?  At this point, the simple solution seems
to be to just use .dump to dump out the contents of the database into a
text file, use search and replace to do the column rename, and then use
.read to read it back into a SQLite database.  Anything I have to watch out
for if I do the above?

Thank you.

Balaji Ramanathan

From: Richard Hipp <[hidden email]>
To: SQLite mailing list <[hidden email]>
Cc:
Bcc:
Date: Mon, 26 Nov 2018 14:11:54 -0500
Subject: Re: [sqlite] Possible bug in Alter Table
On 11/25/18, Balaji Ramanathan <[hidden email]> wrote:
> I expected the Alter Table command to find and replace all occurrences of
> that column name in my schema with the new name.

Here is simplified SQL that illustrates the problem:

CREATE TABLE t1(x);
CREATE VIEW v1 AS SELECT x FROM t1;
CREATE VIEW v2 AS SELECT x FROM v1;
ALTER TABLE t1 RENAME x TO y;

The ALTER TABLE fails because after changing the name of t1.x to t1.y,
the definition of the view v2 is no longer valid.

This is not something we intend to "fix" in SQLite.  The root of the
problem is that the column names for the v1 view are not specified.
And since they are not specified, that means SQLite is free to pick
whatever arbitrary names it wants for those columns.  The definition
of view v2 depends on one particular algorithm for picking the column
names of view v1, but there are no guarantees that every version of
SQLite will use that particular algorithm.  Hence, the definition of
view v2 is under-specified and prone to failure, such as in this case.

If you are careful to defined the names of all columns within your
views, then the problem goes away.  For example:

CREATE TABLE t1(x);
CREATE VIEW v1(y) AS SELECT x FROM t1;
CREATE VIEW v2(z) AS SELECT y FROM v1;
ALTER TABLE t1 RENAME x TO y;

Or:

CREATE TABLE t1(x);
CREATE VIEW v1 AS SELECT x AS y FROM t1;
CREATE VIEW v2 AS SELECT y AS z FROM v1;
ALTER TABLE t1 RENAME x TO y;

If you do not specify the names of columns in views, then SQLite is
free to choose whatever names it wants for those columns, and the
choices might shift after an ALTER TABLE, which could then break
queries and/or downstream views.  So it is best not to do that.

Admittedly, this is not well-documented.  I will strive to improve the
documentation for the next release.  Perhaps I will also add a
"warning" mechanism to alert programmers to gotchas like this in some
subsequent release, though there probably is not time to get warnings
in to the forthcoming 3.26.0 release.

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