On Thursday, 28 March, 2019 13:21, Mark Wagner <m...@google.com> wrote:

>Imagine I have these two tables and one view defining a join.

>CREATE TABLE t (foo);
>CREATE TABLE s (bar);
>CREATE VIEW v as select * from t join s on (foo = q);

>I appear to be able to do this query:

>select 20 as q, * from t join s on (foo=q);

Really, this is:

select 20 as q, *
  from t, s
 where foo == q;

q is an alias for the constant 20.  So what you are really saying is this:

select 20 as q, *
  from t, s
 where foo == 20;

which is valid.

>But apparently I cannot do this:

>sqlite> select 20 as q, * from v;
>Error: no such column: q

No, because q is not a column in either t or s.

>It's interesting because it allows me to define the view and at that
>point it knows nothing about q so I would have assumed it could be
>"supplied" later.

Defining a View is nothing more than storing the definition of the view in a 
table.  It is not parsed until you use it (though it is syntax checked so if 
you make a syntax error you will be told about then when you attempt to create 
the view).  You can define the view before defining the tables s and t, or 
after, or betwixt defining them.  You can even drop the tables (either or both) 
and recreate them (or not).  However, at the time you want to EXECUTE (use) the 
view v the tables s and t must exist (or you will get a no such table error), 
and the columns foo and q must be defined in one of those tables (or you get a 
no such column error).

>Is this just how it is or perhaps my syntax is off?  Or maybe I'm
>just confused.

---
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to