Syntax. table-function-name vs table-naming-function-name

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Syntax. table-function-name vs table-naming-function-name

petern
Consider the "table-or-subquery" syntax chart linked below.

https://www.sqlite.org/syntax/table-or-subquery.html

A modest proposal.

In the "table-or-subquery" syntax there exists a branch for
"table-function-name", aka table valued virtual tables.

Why can't we have a parallel syntax branch for scalar valued
"table-naming-function-name"?  In other words, why not have support for
simply naming an existing table or view by return value of a scalar
function?

There are very good reasons why this relatively simple change is a
worthwhile improvement.

1. Since a named table or view is created by executing ordinary data
definition SQL statements inside the table-naming-function, the dynamically
named table/view and indexes can directly and fully participate in query
optimization of the outer scope.

2. There are many dynamic table applications where a light wrapper on an
ordinary table is all that is necessary.  But, under the current API, one
must write a lot of finicky optimizer hint code to expose a dynamically
generated ordinary table through the requisite virtual table API.

3.  I will speculate a bit.  I see that DRH, with great surprise, very
recently asked a participant on this forum why they are using such an old
version of SQLite.  Based on both the frequent need for ordinary but
dynamic tables and the lack of a thin wrapper for such tables, there surely
must be a lot of otherwise unnecessary "heavy" virtual table code in the
wild.  For credible production use, this heavy code must undergo a
prohibitively time consuming requalification process after every engine
update.  Thus, it's no wonder that important SQLite applications cannot
keep up with engine releases.

I look forward to reading the forum comments on this modest proposal.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Syntax. table-function-name vs table-naming-function-name

Richard Hipp-3
On 3/25/17, petern <[hidden email]> wrote:
>
> Why can't we have a parallel syntax branch for scalar valued
> "table-naming-function-name"?  In other words, why not have support for
> simply naming an existing table or view by return value of a scalar
> function?
>

The easiest way for me to answer this is to ask you to provide a
sample implementation.  After you've work on the problem for a while,
I think you will begin to understand why it is not possible.  I can't
seem to come up with words to help make that realization any easier.

--
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
|  
Report Content as Inappropriate

Re: Syntax. table-function-name vs table-naming-function-name

petern
Richard, thank you for your reply.  I really appreciate it.  The fact that
you have carefully thought about how to cross the FROM clause barrier with
expressions is itself a useful fact.  If you say the current implementation
is painted into a corner on this issue I believe you.  It would be
impossible to deduce this fact by simply studying documentation or source
code.  Thank you very much for taking my question.

At some point I may try what you suggest and see what I can learn.  In the
meantime, the CSV virtual table example appears to be closest to where I'd
like to go next.

https://sqlite.org/csv.html

That example allows the caller to specify a creation schema.  Expanding on
the schema specifier, perhaps there is a way to make xBestIndex() and
xFilter() work more generally in the problem domain of lightly encapsulated
dynamic tables.







On Sun, Mar 26, 2017 at 11:44 AM, Richard Hipp <[hidden email]> wrote:

> On 3/25/17, petern <[hidden email]> wrote:
> >
> > Why can't we have a parallel syntax branch for scalar valued
> > "table-naming-function-name"?  In other words, why not have support for
> > simply naming an existing table or view by return value of a scalar
> > function?
> >
>
> The easiest way for me to answer this is to ask you to provide a
> sample implementation.  After you've work on the problem for a while,
> I think you will begin to understand why it is not possible.  I can't
> seem to come up with words to help make that realization any easier.
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...