Create VIEW passing despite invalid column name specified

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Create VIEW passing despite invalid column name specified

Tomasz Kot
Hello,

Beneath sql shall throw an error on  CREATE VIEW statement (as invalid
column is specified), but it passes (SQLite 3.23.1).



CREATE TABLE "testTable" ( "name" text not null );

INSERT INTO testTable values ('Tom');

SELECT * FROM testTable;



CREATE VIEW testView AS SELECT nameWrong FROM testTable;

SELECT * FROM testView;

SELECT * FROM sqlite_master;

Regards,
Tom
_______________________________________________
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: Create VIEW passing despite invalid column name specified

Richard Hipp-3
On 7/26/18, Tomasz Kot <[hidden email]> wrote:
> Hello,
>
> Beneath sql shall throw an error on  CREATE VIEW statement (as invalid
> column is specified), but it passes (SQLite 3.23.1).

The error is deferred until you try to use the view.  The reason for
deferring the error is that the problem might be corrected by
subsequent DDL statements prior to using the view.

--
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: Create VIEW passing despite invalid column name specified

Will Parsons
On Thursday, 26 Jul 2018  7:58 PM -0400, Richard Hipp wrote:
> On 7/26/18, Tomasz Kot <[hidden email]> wrote:
>> Hello,
>>
>> Beneath sql shall throw an error on  CREATE VIEW statement (as invalid
>> column is specified), but it passes (SQLite 3.23.1).
>
> The error is deferred until you try to use the view.  The reason for
> deferring the error is that the problem might be corrected by
> subsequent DDL statements prior to using the view.

And I will add that I've found this behaviour quite useful when
upgrading to a new database schema.

--
Will

_______________________________________________
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: Create VIEW passing despite invalid column name specified

Dominique Devienne
In reply to this post by Richard Hipp-3
On Fri, Jul 27, 2018 at 1:58 AM Richard Hipp <[hidden email]> wrote:

> On 7/26/18, Tomasz Kot <[hidden email]> wrote:
> > Hello,
> >
> > Beneath sql shall throw an error on  CREATE VIEW statement (as invalid
> > column is specified), but it passes (SQLite 3.23.1).
>
> The error is deferred until you try to use the view.  The reason for
> deferring the error is that the problem might be corrected by
> subsequent DDL statements prior to using the view.
>

Would it be possible to have a pragma that easily detects all invalid views?
Does an existing one already do that? That would be useful IMHO.

Someone writing a SQL script to instantiate a schema could run that pragma
at the end,
with -bail perhaps, to eagerly and easily validate the DDL script. My
$0.02. --DD
_______________________________________________
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: Create VIEW passing despite invalid column name specified

R Smith-2
On 2018/07/27 10:40 AM, Dominique Devienne wrote:

> On Fri, Jul 27, 2018 at 1:58 AM Richard Hipp <[hidden email]> wrote:
>
>> On 7/26/18, Tomasz Kot <[hidden email]> wrote:
>>> Hello,
>>>
>>> Beneath sql shall throw an error on  CREATE VIEW statement (as invalid
>>> column is specified), but it passes (SQLite 3.23.1).
>> The error is deferred until you try to use the view.  The reason for
>> deferring the error is that the problem might be corrected by
>> subsequent DDL statements prior to using the view.
>>
> Would it be possible to have a pragma that easily detects all invalid views?
> Does an existing one already do that? That would be useful IMHO.
>
> Someone writing a SQL script to instantiate a schema could run that pragma
> at the end,

I'm sure it is possible, though you will probably receive the usual
"code bloat avoidance" reply (which I tend to agree with).

That said, it's easy to do in a bit of code which you can add to your
libraries as a standard function, which will go something like this (in
pseudo):

function check_views {
   Read (SELECT name FROM sqlite_master WHERE type='view') into VList;
   For each name in VList do
     if sql_prepare('SELECT * FROM ' + name)<> SQL_OK
        log( 'View ' + name + ' is not working.');
}

Which you can spruce up by adding the actual sqlite error return code or
description.
You can also UNION in the sqlite_temp_master to check temporary views if
needed.

One caution, some views that have ordered sub-selects linked to very
large tables (or other stuff I don't know about) might take a long time
to even simply prepare - so perhaps not a good idea to automatically add
this after every script.

Cheers,
Ryan

_______________________________________________
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: Create VIEW passing despite invalid column name specified

Dominique Devienne
On Fri, Jul 27, 2018 at 11:33 AM R Smith <[hidden email]> wrote:

> On 2018/07/27 10:40 AM, Dominique Devienne wrote:
> > On Fri, Jul 27, 2018 at 1:58 AM Richard Hipp <[hidden email]> wrote:
> >> On 7/26/18, Tomasz Kot <[hidden email]> wrote:
> >>> Beneath sql shall throw an error on  CREATE VIEW statement (as invalid
> >>> column is specified), but it passes (SQLite 3.23.1).
> >> The error is deferred until you try to use the view.  The reason for
> >> deferring the error is that the problem might be corrected by
> >> subsequent DDL statements prior to using the view.
> >>
> > Would it be possible to have a pragma that easily detects all invalid
> views?
> > Does an existing one already do that? That would be useful IMHO.
>
> I'm sure it is possible, though you will probably receive the usual
> "code bloat avoidance" reply (which I tend to agree with).
>

Sigh. We already have 4 different check pragmas (cell-size, fk, integrity,
quick).
One more for views seems perfectly in line with those. Did anyone raise the
same
old light-code-bloat argument for those too, in the past? I for one cringe
every time
I read it nowadays. In any case, it's always entirely at the discretion of
DRH, and
although he of course considers the communities views (pun intended) I'm
sure,
at the end of the day he's the sole decider.


> That said, it's easy to do in a bit of code which you can add to your
> libraries as a standard function, which will go something like this (in
> pseudo):
>

Of course it is. But many many people use SQLite entirely via sqlite3[.exe]
with
no C code extensions of their own.


> Which you can spruce up by adding the actual sqlite error return code or
> description.
> You can also UNION in the sqlite_temp_master to check temporary views if
> needed.
>

Some pragmas AFAIK, are already DB aware and can restrict themselves to a
given attached DB.


> One caution, some views that have ordered sub-selects linked to very
> large tables (or other stuff I don't know about) might take a long time
> to even simply prepare - so perhaps not a good idea to automatically add
> this after every script.
>

Really? I've never heard that preparing a statement depended on how large
tables accessed are.
Doesn't prepare only ever cares about data dictionaries (i.e. DDL stuff)
and not actual rows (DML stuff)?

Preparing is not only running the SQL parser and generating the VDBE
program for that statement?
If what you wrote was true, wouldn't that imply the result of prepare could
be different depending if the
prepare happens with a empty or "full" table? Or I completely misunderstood
your "large table" comment?

Thanks, --DD
_______________________________________________
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: Create VIEW passing despite invalid column name specified

R Smith-2

On 2018/07/27 1:59 PM, Dominique Devienne wrote:

>
> Sigh. We already have 4 different check pragmas (cell-size, fk, integrity,
> quick).
> One more for views seems perfectly in line with those. Did anyone raise the
> same
> old light-code-bloat argument for those too, in the past? I for one cringe
> every time
> I read it nowadays. In any case, it's always entirely at the discretion of
> DRH, and
> although he of course considers the communities views (pun intended) I'm
> sure,
> at the end of the day he's the sole decider.

Yeah, I meant Richard (or one of the  devs at least) - apologies, I
assumed that was obvious.


>
>> One caution, some views that have ordered sub-selects linked to very
>> large tables (or other stuff I don't know about) might take a long time
>> to even simply prepare - so perhaps not a good idea to automatically add
>> this after every script.
>>
> Really? I've never heard that preparing a statement depended on how large
> tables accessed are.
> Doesn't prepare only ever cares about data dictionaries (i.e. DDL stuff)
> and not actual rows (DML stuff)?
>
> Preparing is not only running the SQL parser and generating the VDBE
> program for that statement?
> If what you wrote was true, wouldn't that imply the result of prepare could
> be different depending if the
> prepare happens with a empty or "full" table? Or I completely misunderstood
> your "large table" comment?

It's not the size of a direct table that matters, it's only when part of
a sub-query that needs to be evaluated. At least, that's how I have it -
I could be wrong.

I'm hoping someone who understands the internal mechanism more fully can
provide a clear example. It might also be that it is simply required
before the first step() and not during the prepare per sé. Last
discussion on this was many moons and many versions of sqlite ago, too
long for me to trust my memory.

So before propagating any misinformation, I'll appeal to the devs (or
anyone who knows for sure), and ask:
Is there ever a reason the "sqlite3_prepare()" and its ilk can take
longer than a few milliseconds?  If so, how?


_______________________________________________
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: Create VIEW passing despite invalid column name specified

Simon Slavin-3
There is very little pressure to keep the shell tool small.  So the check for views which refer to non-existent tables could be put into there, as part of one of the dot-commands which do checking.

As for the code, it doesn't require anything more than SQL commands.  There's no need for access to any SQLite internals.  The code posted by Ryan Smith upthread is a good demonstration of how to manage that check without using SQLite internals.

But if you distrust your schema so much that you want to sanity-checking VIEWs, you want to sanity-check TRIGGERs too, since they also have deferred entity-checking.  And that is far more difficult.

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