Re: [sqlite] Why did 3.5.7 pass the following test in alter2

[Stephen Oberholtzer]

>  sqlite> CREATE TABLE abc2(a, b, c);
>
>  sqlite> CREATE VIEW abc2_v AS SELECT * FROM abc2;
>  sqlite> SELECT * FROM abc2_v;
>
>  sqlite> ALTER TABLE abc2 ADD COLUMN d;
>
>  
>  I would expect the view to give 4 columns after the alter, not 3.
>  as the following direct select shows.

I started out by explaining why SQLite was not doing what you
expected, and then I found out that there does in fact seem to be a
bug afoot.

There are two reasonable behaviors here:

1. Preserving the "*"ness, so that adding a column to the relevant
source table will add the column to the view
2. Expand the '*' at view-creation-time, so that adding columns to the
source table(s) does not affect the view (I'm pretty sure PostgreSQL
works like this)

It looks like SQLite mostly does the first one.  However, if a table
is altered, the schema cache is not flushed.  Watch!

D:\>sqlite3 tmp.db
SQLite version 3.5.6
Enter ".help" for instructions
sqlite> .headers on
sqlite> create table abc2 (a,b,c);
sqlite> insert into abc2 values(1,2,3);
sqlite> create view abc2_v as select * from abc2;
sqlite> select * from abc2_v;
a|b|c
1|2|3
sqlite> alter table abc2 add column d;
sqlite> select * from abc2_v;
a|b|c
1|2|3


As you can see, SQLite hasn't realized that abc2_v needs to be
updated. However, if I open another command prompt and run:

D:\>sqlite3 tmp.db
SQLite version 3.5.6
Enter ".help" for instructions
sqlite> create table xyz(a);

This increments the schema version, which will invalidate the schema
cache (and any prepared statements, but that doesn't really apply to
sqlite3.)
So, back in the original terminal:

sqlite> select * from abc2_v;
a|b|c|d
1|2|3|

Looking at the 'alter2.test' source at
http://www.sqlite.org/cvstrac/fileview?f=sqlite/test/alter2.test=1.13,
I can see that the "alter_table" function appears to open a dedicated
connection to the database, which means when the "execsql" function is
then called, it is not on the same connection as the "alter_table" one
and cannot take advantage of the database cache.

I think we might need an alter2b.test, and maybe even an
alternot2b.test (ba-dum-pshh!)

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why did 3.5.7 pass the following test in alter2

[Noah Hart]

do_test alter2-2.1 {
execsql {
  CREATE TABLE abc2(a, b, c);
  INSERT INTO abc2 VALUES(1, 2, 10);
  INSERT INTO abc2 VALUES(3, 4, NULL);
  INSERT INTO abc2 VALUES(5, 6, NULL);
  CREATE VIEW abc2_v AS SELECT * FROM abc2;
  SELECT * FROM abc2_v;
}
  } {1 2 10 3 4 {} 5 6 {}}
  do_test alter2-2.2 {
# ALTER TABLE abc ADD COLUMN d;
alter_table abc2 {CREATE TABLE abc2(a, b, c, d);}
execsql {
  SELECT * FROM abc2_v;
}
  } {1 2 10 {} 3 4 {} {} 5 6 {} {}}

SQLite version 3.5.7
Enter ".help" for instructions
sqlite> CREATE TABLE abc2(a, b, c);
sqlite> INSERT INTO abc2 VALUES(1, 2, 10);
sqlite> INSERT INTO abc2 VALUES(3, 4, NULL);
sqlite> INSERT INTO abc2 VALUES(5, 6, NULL);
sqlite> CREATE VIEW abc2_v AS SELECT * FROM abc2;
sqlite> SELECT * FROM abc2_v;
1|2|10
3|4|
5|6|
sqlite> ALTER TABLE abc2 ADD COLUMN d;
sqlite> SELECT * FROM abc2_v;
1|2|10
3|4|
5|6|

I would expect the view to give 4 columns after the alter, not 3.  
as the following direct select shows.

sqlite> SELECT * FROM abc2;
1|2|10|
3|4||
5|6||



Regards,

Noah



CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users