Inconsistency of CREATE/DROP TABLE with attached DBs

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

Inconsistency of CREATE/DROP TABLE with attached DBs

nomad
I ran into an inconsistency? between CREATE and DROP TABLE today:

    # Pipe this example through sed -e 's/^ *//' before running through
    # a shell
    #
    # 1. Set up table a.t1
    rm -f a.sqlite b.sqlite c.sqlite

    cat <<SQL | sqlite3 a.sqlite
    CREATE TABLE t1(id INTEGER);
    SQL

    # 2. Create an unqualified (main.)t1 in presence of a.t1:

    cat <<SQL | sqlite3 b.sqlite
    ATTACH DATABASE 'a.sqlite' AS a;
    CREATE TABLE t1(id INTEGER);
    SQL

    # 3. Drop an unqualified (main.)t1 in presence of a.t1:

    cat <<SQL | sqlite3 c.sqlite
    ATTACH DATABASE 'a.sqlite' AS a;
    DROP TABLE IF EXISTS t1;                  -- Drops a.t1 !!?!
    SQL

This caught me rather by surprise. I guess my expectation was that
since CREATE TABLE works in the main context that DROP TABLE would as
well.

This being SQLite the behaviour is documented[1]. However, might I
suggest to the developers that linking to [1] or adding a small note in
each of the DROP / DELETE / UPDATE documentation pages would be useful
for this (potentially dangerious) behaviour?

[1] https://www.sqlite.org/lang_naming.html

I think I would also appreciate a pragma that requires full schema
paths for a) modification statements and/or b) all statements.

--
Mark Lawrence
_______________________________________________
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: [EXTERNAL] Inconsistency of CREATE/DROP TABLE with attached DBs

Hick Gunter
I don't see any inconsistency here.

1) implicit attach of a.sqlite as main and create a.t1
2) implcit attach b.sqlite as main, attach a.sqlite as a and create (main).t1 (in b.sqlite)
3) implicit attach c.sqlite as main, attaxh a.sqlite as a and drop the only table named t1 from a

c.sqlite never contains a table t1. If it did, then step 3 would drop c.t1 in step 3

see https://sqlite.org/lang_naming.html

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von [hidden email]
Gesendet: Mittwoch, 4. März 2020 18:31
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] [sqlite] Inconsistency of CREATE/DROP TABLE with attached DBs

I ran into an inconsistency? between CREATE and DROP TABLE today:

    # Pipe this example through sed -e 's/^ *//' before running through
    # a shell
    #
    # 1. Set up table a.t1
    rm -f a.sqlite b.sqlite c.sqlite

    cat <<SQL | sqlite3 a.sqlite
    CREATE TABLE t1(id INTEGER);
    SQL

    # 2. Create an unqualified (main.)t1 in presence of a.t1:

    cat <<SQL | sqlite3 b.sqlite
    ATTACH DATABASE 'a.sqlite' AS a;
    CREATE TABLE t1(id INTEGER);
    SQL

    # 3. Drop an unqualified (main.)t1 in presence of a.t1:

    cat <<SQL | sqlite3 c.sqlite
    ATTACH DATABASE 'a.sqlite' AS a;
    DROP TABLE IF EXISTS t1;                  -- Drops a.t1 !!?!
    SQL

This caught me rather by surprise. I guess my expectation was that since CREATE TABLE works in the main context that DROP TABLE would as well.

This being SQLite the behaviour is documented[1]. However, might I suggest to the developers that linking to [1] or adding a small note in each of the DROP / DELETE / UPDATE documentation pages would be useful for this (potentially dangerious) behaviour?

[1] https://www.sqlite.org/lang_naming.html

I think I would also appreciate a pragma that requires full schema paths for a) modification statements and/or b) all statements.

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


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
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: [EXTERNAL] Inconsistency of CREATE/DROP TABLE with attached DBs

Hick Gunter
Addendum: Note that CREATE TABLE has an optional schema name which must be one of

- main (which is the default)
- temp (which is the same as specifying TEMP or TEMPORARY between CREATE and TABLE)
- the name of an attached database

See https://sqlite.org/lang_createtable.html


-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Hick Gunter
Gesendet: Donnerstag, 5. März 2020 08:48
An: SQLite mailing list <[hidden email]>
Betreff: Re: [sqlite] [EXTERNAL] Inconsistency of CREATE/DROP TABLE with attached DBs

I don't see any inconsistency here.

1) implicit attach of a.sqlite as main and create a.t1
2) implcit attach b.sqlite as main, attach a.sqlite as a and create (main).t1 (in b.sqlite)
3) implicit attach c.sqlite as main, attaxh a.sqlite as a and drop the only table named t1 from a

c.sqlite never contains a table t1. If it did, then step 3 would drop c.t1 in step 3

see https://sqlite.org/lang_naming.html

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von [hidden email]
Gesendet: Mittwoch, 4. März 2020 18:31
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] [sqlite] Inconsistency of CREATE/DROP TABLE with attached DBs

I ran into an inconsistency? between CREATE and DROP TABLE today:

    # Pipe this example through sed -e 's/^ *//' before running through
    # a shell
    #
    # 1. Set up table a.t1
    rm -f a.sqlite b.sqlite c.sqlite

    cat <<SQL | sqlite3 a.sqlite
    CREATE TABLE t1(id INTEGER);
    SQL

    # 2. Create an unqualified (main.)t1 in presence of a.t1:

    cat <<SQL | sqlite3 b.sqlite
    ATTACH DATABASE 'a.sqlite' AS a;
    CREATE TABLE t1(id INTEGER);
    SQL

    # 3. Drop an unqualified (main.)t1 in presence of a.t1:

    cat <<SQL | sqlite3 c.sqlite
    ATTACH DATABASE 'a.sqlite' AS a;
    DROP TABLE IF EXISTS t1;                  -- Drops a.t1 !!?!
    SQL

This caught me rather by surprise. I guess my expectation was that since CREATE TABLE works in the main context that DROP TABLE would as well.

This being SQLite the behaviour is documented[1]. However, might I suggest to the developers that linking to [1] or adding a small note in each of the DROP / DELETE / UPDATE documentation pages would be useful for this (potentially dangerious) behaviour?

[1] https://www.sqlite.org/lang_naming.html

I think I would also appreciate a pragma that requires full schema paths for a) modification statements and/or b) all statements.

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


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users