is this possible

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

is this possible

Mark Wagner
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);

But apparently I cannot do this:

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

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.

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

Just curious.

Thanks!

-- Mark
_______________________________________________
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: is this possible

Amit Yaron
 From the page https://sqlite.org/lang_createview.html :
"The CREATE VIEW command assigns a name to a pre-packaged SELECT
statement ..."

So, it seems that the command "CREATE VIEW"  just creates a name for a
SELECT statement, and checks nothing more than syntax.

On 28.3.2019 21:21, Mark Wagner wrote:
> CREATE VIEW v as select * from t join s on (foo = q);


_______________________________________________
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: is this possible

Igor Tandetnik-2
In reply to this post by Mark Wagner
On 3/28/2019 3:21 PM, Mark Wagner 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);

Surprisingly, this last statement succeeds. But if you then close the database and try to open it again, it'll fail with "no such column: q". So, don't do this - you are creating an unusable database file with corrupted schema.

I don't believe you can create a parameterized view.
--
Igor Tandetnik

_______________________________________________
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: is this possible

Keith Medcalf
In reply to this post by Mark Wagner
On Thursday, 28 March, 2019 13:21, Mark Wagner <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: is this possible

Dan Kennedy-4
In reply to this post by Igor Tandetnik-2

On 29/3/62 03:00, Igor Tandetnik wrote:

> On 3/28/2019 3:21 PM, Mark Wagner 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);
>
> Surprisingly, this last statement succeeds. But if you then close the
> database and try to open it again, it'll fail with "no such column:
> q". So, don't do this - you are creating an unusable database file
> with corrupted schema.


The error doesn't occur unless you actually query the view though, correct?

Here, I get:

   $ ./sqlite3 x.db
   SQLite version 3.27.2 2019-02-25 16:06:06
   Enter ".help" for usage hints.
   sqlite> CREATE TABLE t (foo);
   sqlite> CREATE TABLE s (bar);
   sqlite> CREATE VIEW v as select * from t join s on (foo = q);
   sqlite>
   $ ./sqlite3 x.db
   SQLite version 3.27.2 2019-02-25 16:06:06
   Enter ".help" for usage hints.
   sqlite> SELECT * FROM t;
   sqlite> SELECT * FROM s;
   sqlite> SELECT * FROM v;
   Error: no such column: q


Dan.



_______________________________________________
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: is this possible

Igor Tandetnik-2
On 3/29/2019 9:55 AM, Dan Kennedy wrote:

>
> On 29/3/62 03:00, Igor Tandetnik wrote:
>> On 3/28/2019 3:21 PM, Mark Wagner 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);
>>
>> Surprisingly, this last statement succeeds. But if you then close the database and try to open it again, it'll fail with "no such column: q". So, don't do this - you are creating an unusable database file with corrupted schema.
>
>
> The error doesn't occur unless you actually query the view though, correct?

Yes, I think the tool I was using tries to use the view right after opening the database, probably to figure out what columns it offers. This led me to erroneously conclude that merely opening the database triggers the error.
--
Igor Tandetnik


_______________________________________________
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: is this possible

Simon Slavin-3
In reply to this post by Dan Kennedy-4
On 29 Mar 2019, at 1:55pm, Dan Kennedy <[hidden email]> wrote:

> The error doesn't occur unless you actually query the view though, correct?

My theory on SQLite was that the VIEW definition was only syntax-checked and not compiled in any way.  So you could do

CREATE VIEW v as select * from t ORDER BY a;
CREATE TABLE t (a INTEGER UNIQUE);
... use view v a few times ...
DROP TABLE t;
CREATE TABLE t (a TEXT, b REAL);
... use view v a few more times ...

changing what the view meant, whether a temporary index was needed, and how many columns would be returned, without having to recreate the view.

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