Re: [sqlite] Idea: defining table-valued functions directly in SQL
On 07/14/18 20:09, E.Pasma wrote:
The new example is clear, and therefore also raises a question:
CREATE TABLE people (name, age);
INSERT INTO people VALUES ('Bob', 33), ('Jen', 19), ('Liz', 30);
CREATE VIEW older PARAMETERS (name, otherName) AS
SELECT t1.age > t2.age AS older
FROM people AS t1 WHERE t1.name = parameters.name
, people AS t2 WHERE t2.name = parameters.otherName;
SELECT t1.name AS name
, t2.name AS otherName
, older(t1.name, t2.name)
FROM people AS t1
, people AS t2
WHERE t1.name != t2.name;
name otherName older
- -
Bob Jen1
Bob Liz1
Jen Bob0
Jen Liz0
Liz Bob0
Liz Jen1
The function "older" is used here in the SELECT part. But as a
table-valued function I think it must be in the FROM part:
SELECT t1.name AS name, t2.name AS otherName, older --column name
FROM people AS t1, people AS t2, older(t1.name, t2.name)
WHERE t1.name != t2.name;
My question is: do you envisage a regular function, returning a single
value, or a table-valued function. (that behaves as a table, even the
parameters may be passed as predicates in the WHERE clause)?
Either! If it returns a single row and a single column, let it be a
regular function. Or if it returns a single row, let it be a row value.
Or multiple rows (or one or even zero, of course), let it be a table.
I think the usage should dictate; no need to declare in advance.
If the subject is "defining regular functions directly in SQL" then
CREATE FUNCTION may be considered. This seems easier to understand and
develop. but in the end it can have an enormous impact, like in
postgres sql-createfunction.html
I'm trying to minimize the new syntax in my proposal. CREATE FUNCTION
seems too large for SQLite as it stands, though perhaps a small subset
could be adopted. I just seen an opportunity to get much of the same
capability with a small addition to existing syntax.
For the sudoku-solver you do use the table-valued function notation:
SELECT s FROM x(sud) WHERE NOT ind;
and the function should possibly return multiple rows here. Like if a
sudoku is ambiguous or if changing the WHERE clause for development.
Right. I don't see why it can't go both ways.
The idea would help me. I wonder if it can also be applied in inline
views.
"Inline view" is a phrase I'm not familiar with. Your example shows a
nested query, augmented with PARAMETERS. I suppose it could be called
an inline view, though.
That could be imagined if PARAMETERS is written after the query
like was an earlier idea.
Right, that's what I first suggested, associating PARAMETERS with SELECT
rather than with CREATE VIEW or WITH common-table-expression. So
perhaps that is a more powerful way to do things after all.
SELECT name, nolder
FROM people
LEFT JOIN (--inline view
SELECT COUNT(*)
FROM people p2
WHERE age>parameter.age
PARAMETERS (age)
) USING (age)
;
Huh! That's neat. For reference, here's how to do the same right now:
CREATE TABLE people (name, age);
INSERT INTO people VALUES ('Bob', 33), ('Jen', 19), ('Liz', 30);
SELECT people.name AS name
, count(other.name) AS nolder
FROM people
LEFT JOIN people AS other ON (other.age > people.age)
GROUP BY people.name;
namenolder
-- --
Bob 0
Jen 2
Liz 1
--
Andy Goth |
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Idea: defining table-valued functions directly in SQL
Hello,
The new example is clear, and therefore also raises a question:
> CREATE TABLE people (name, age);
> INSERT INTO people VALUES ('Bob', 33), ('Jen', 19), ('Liz', 30);
>
> CREATE VIEW older PARAMETERS (name, otherName) AS
> SELECT t1.age > t2.age AS older
> FROM people AS t1 WHERE t1.name = parameters.name
> , people AS t2 WHERE t2.name = parameters.otherName;
>
> SELECT t1.name AS name
> , t2.name AS otherName
> , older(t1.name, t2.name)
> FROM people AS t1
> , people AS t2
> WHERE t1.name != t2.name;
>
> name otherName older
> - -
> Bob Jen1
> Bob Liz1
> Jen Bob0
> Jen Liz0
> Liz Bob0
> Liz Jen1
The function "older" is used here in the SELECT part. But as a table-valued
function I think it must be in the FROM part:
SELECT t1.name AS name, t2.name AS otherName, older --column name
FROM people AS t1, people AS t2, older(t1.name, t2.name)
WHERE t1.name != t2.name;
My question is: do you envisage a regular function, returning a single value,
or a table-valued function. (that behaves as a table, even the parameters may
be passed as predicates in the WHERE clause)?
If the subject is "defining regular functions directly in SQL" then CREATE
FUNTION may be considered. This seems easier to understand and develop. but in
the end it can have an enormous impact, like in postgres
sql-createfunction.html
For the sudoku-solver you do use the table-valued function notation:
SELECT s FROM x(sud) WHERE NOT ind;
and the function should possibly return multiple rows here. Like if a sudoku is
ambiguous or if changing the WHERE clause for development.
The idea would help me. I wonder if it can also be applied in inline views.
That could be imagined if PARAMETERS is written after the query like was an
earlier idea.
SELECT name, nolder
FROM people
LEFT JOIN (--inline view
SELECT COUNT(*)
FROM people p2
WHERE age>parameter.age
PARAMETERS (age)
) USING (age)
;
E. Pasma
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Idea: defining table-valued functions directly in SQL
On 07/07/18 19:47, E.Pasma wrote:
But the ideas allow a parameter name to be identical to a column name,
which must be an error.
While I might prefer that to be the case, it's actually not. We do not
have the ability for column expressions to reference other columns, so
there is no ambiguity even if a parameter were to be named the same as a
column. It's also currently permissible for multiple columns to have
the same name.
CREATE TABLE x (a, b);
INSERT INTO x VALUES (1, 2), (3, 4);
CREATE VIEW y AS SELECT a, b AS a FROM x;
CREATE VIEW z (a, a) AS SELECT * FROM x;
SELECT a, b AS a FROM x;
a a
-- --
1 2
3 4
SELECT * FROM y;
a a:1
-- --
1 2
3 4
SELECT * FROM z;
a a:1
-- --
1 2
3 4
The handling varies a little bit between bare SELECT statements and
SELECTs wrapped in views. The latter appends a suffix to identical
column names, but the former does not.
I think the only rule would be that no two parameters can have the same
name.
So why not a single column list and a modifier to turn a column into
an argument?
CREATE VIEW double (result, arg HIDDEN) AS SELECT arg * 2;
SELECT * FROM numbers, double(x);
I did mention HIDDEN as an option in my original post, though not with
the syntax you suggest:
http://sqlite.1065341.n5.nabble.com/Idea-defining-table-valued-functions-directly-in-SQL-td102368.html
At the time I wasn't considering the optional parenthesized output
column-name list following the view name, since frankly I never use it.
I was thinking only of the SELECT statement, which also supplies the
output column names by means of AS clauses, or defaulting to using the
input expressions themselves as the names. Thus I allowed HIDDEN as a
column modifier in the SELECT clause, roughly copying the way virtual
table-valued functions currently work. (I also suggested saying
PARAMETER instead of HIDDEN to be a bit clearer about its function.)
If I understand you correctly, your idea is essentially to take this
approach but move the HIDDEN columns (i.e., parameters) from the SELECT
statement to the output column name list.
I have two issues with this. One is the fact that the output column
name list is optional, but now it would become required, at the cost of
the user having to retype the output column names even in cases where
the default is already good, or even if the user would have preferred to
use AS to name the output columns (which is what I do).
The second issue is one I had with my own idea as well, though I was
inarticulate about it. Whether the parameters are listed in the output
column name list or in the output column expression list, either way
we're interspersing outputs with inputs.
All that said, I do prefer your idea over mine. Having the parameter
names in the same list as the output column names may be mildly
confusing though overall okay with me, but I find my original idea of
having the parameter names in the same list as the output column
expressions to be off-the-charts weird, which is why I said in my
original email that I do not recommend using HIDDEN.
The bottom line for me is that at this point, my preference still goes
to the following:
CREATE VIEW double (result) PARAMETERS (arg) AS SELECT arg * 2;
SELECT * FROM numbers, double(x);
WITH double (result) PARAMETERS (arg) AS (SELECT arg * 2)
SELECT * FROM numbers, double(x);
What if a parameter name happens to match a column name in one (or
more) of the tables being pulled in by a FROM clause? A table name
qualifier is needed to disambiguate. It could be the function name,
though it could be clearer to do like upsert ("excluded") and have a
special token, e.g. "parameters". (cf.
https://sqlite.org/lang_UPSERT.html)
This side idea is more powerful than just to disambiguate column
names. It tells me that a column is pulled from outside the view. An
explicit parameter list is not even needed.
That's really not what I had in mind. I wasn't thinking of FROM clauses
outside the view, rather those inside.
CREATE TABLE people (name, age);
INSERT INTO people VALUES ('Bob', 33), ('Jen', 19), ('Liz', 30);
CREATE VIEW older PARAMETERS (name, otherName) AS
SELECT t1.age > t2.age AS older
FROM people AS t1 WHERE t1.name = parameters.name
, people AS t2 WHERE t2.name = parameters.name;
SELECT t1.name AS name
, t2.name AS otherName
, older(t1.name, t2.name)
FROM people AS t1
, people AS t2
WHERE t1.name != t2.name;
name otherName older
- -
Bob Jen1
Bob Liz1
Jen Bob0
Jen Liz0
Liz Bob0
Liz Jen1
SELECT t1.name AS senior
, t2.name AS junior
FROM people AS t1
, people AS t2
WHERE older(t1.name, t2.name);
senior junior
-- --
Bob Jen
Bob Liz
Liz Jen
The column must only exist in the view
Re: [sqlite] Idea: defining table-valued functions directly in SQL
> 4 jul. 2018, Andy Goth:
> Revisiting this topic...
>
> On 06/10/18 08:04, sql...@zzo38computer.org wrote:
>> * Perhaps move PARAMETERS before AS, which may make the syntax easier.
>
> Like so?
>
> CREATE VIEW double PARAMETERS (arg) AS SELECT arg * 2;
> SELECT * FROM numbers, double(x);
>
> This is a readability improvement because universally I see function
> names and parameters defined before function bodies. Moving the
> PARAMETERS to the left of AS also represents a conceptual shift from
> PARAMETERS modifying SELECT (which is what I had in mind) to PARAMETERS
> modifying CREATE VIEW (which could well be a better way to look at it).
>
> Now, let's examine the common table expression variant. I'll repeat the
> baseline syntax proposal from my original post:
>
> WITH double AS (SELECT arg * 2 PARAMETERS (arg))
> SELECT * FROM numbers, double(x);
>
> Would your suggestion be the following?
>
> WITH double PARAMETERS (arg) AS (SELECT arg * 2)
> SELECT * FROM numbers, double(x);
>
> Next, what is the interaction with an explicit column-name list? Does
> the PARAMETERS clause come before or after that? Compare:
>
> CREATE VIEW double (result) PARAMETERS (arg) AS SELECT arg * 2;
> SELECT * FROM numbers, double(x);
> WITH double (result) PARAMETERS (arg) AS (SELECT arg * 2)
> SELECT * FROM numbers, double(x);
>
> Versus:
>
> CREATE VIEW double PARAMETERS (arg) (result) AS SELECT arg * 2;
> SELECT * FROM numbers, double(x);
> WITH double PARAMETERS (arg) (result) AS (SELECT arg * 2)
> SELECT * FROM numbers, double(x);
>
> I don't think there's any question the former is superior, but I bring
> this up for two reasons. One, every syntax question needs an answer.
> Two, I wish to highlight the fact that the (existing) syntax for
> column-name list looks very much like what most languages use for a
> parameter list, so there's potential confusion, hence the need for the
> PARAMETERS token.
Hello,
Not any question about what order looks superior.
But the ideas allow a parameter name to be identical to a column name, which
must be an error. So why not a single column list and a modifier to turn a
column into an argument?
CREATE VIEW double (result, arg HIDDEN) AS SELECT arg * 2;
SELECT * FROM numbers, double(x);
>
>> * I do agree that defining table-valued functions in these way can be
>> useful though; I have wanted to define views that take parameters
>> before, and was unable to.
>
> I would love to be able to create functions without writing extensions
> in C, plus this way functions won't require recursive invocation of
> SQLite and won't have to be a barrier to the SQLite optimizer. Rather,
> native functions would be inlined right into the bytecodes of whichever
> queries use them.
>
>> * Another (separate) idea can be "CREATE FUNCTION name(args) AS
>> select_stmt;" to define your own function. [...] Both of these are
>> separate from table-valued functions (parameterized views) though.
>
> Aside from the syntax, is this really a separate idea? What does this
> do that views can't?
>
>> If you write "CREATE AGGREGATE FUNCTION" then the function name can be
>> used as a table name within the select_stmt.
>
> I don't think I understand the part about letting the function name be
> used as a table name. Parameters would already be bound, so there's no
> need for a FROM clause to get at them. That's central to the concept of
> parameters as explored by this email thread; I'm hunting for a practical
> use for situations that would currently give a "no such column" error.
>
> However, you bring up an interesting question, though it's a potential
> issue whether or not the function is an aggregate function. What if a
> parameter name happens to match a column name in one (or more) of the
> tables being pulled in by a FROM clause? A table name qualifier is
> needed to disambiguate. It could be the function name, though it could
> be clearer to do like upsert ("excluded") and have a special token, e.g.
> "parameters". (cf. https://sqlite.org/lang_UPSERT.html)
>
This side idea is more powerful than just to disambiguate column names. It
tells me that a column is pulled from outside the view. An explicit parameter
list is not even needed. The column must only exist in the view specification.
CREATE VIEW double (arg, result) AS SELECT PARAMETERS.arg, PARAMETERS.arg * 2;
SELECT * FROM numbers, double ON arg=x;
A disadvantage is that this view behaves no longer purely relational. For
instance:
select * from double where arg=2;
2|4
select * from double where arg>=2;
select * from double where +arg=2;
select * from double where arg=NULL;
0|0
This behaviour is also intrinsic to user-defined functions, unless extremely
carefully programmed. The outcomes above are equivalent to what generate_series
does with these sort of predicates.
Not sure how bad this is when achievable in plain SQL.
I leave out the remainder of the message and give some personal motivation
instead. I
Re: [sqlite] Idea: defining table-valued functions directly in SQL
Revisiting this topic...
On 06/10/18 08:04, sql...@zzo38computer.org wrote:
* Perhaps move PARAMETERS before AS, which may make the syntax easier.
Like so?
CREATE VIEW double PARAMETERS (arg) AS SELECT arg * 2;
SELECT * FROM numbers, double(x);
This is a readability improvement because universally I see function
names and parameters defined before function bodies. Moving the
PARAMETERS to the left of AS also represents a conceptual shift from
PARAMETERS modifying SELECT (which is what I had in mind) to PARAMETERS
modifying CREATE VIEW (which could well be a better way to look at it).
Now, let's examine the common table expression variant. I'll repeat the
baseline syntax proposal from my original post:
WITH double AS (SELECT arg * 2 PARAMETERS (arg))
SELECT * FROM numbers, double(x);
Would your suggestion be the following?
WITH double PARAMETERS (arg) AS (SELECT arg * 2)
SELECT * FROM numbers, double(x);
Next, what is the interaction with an explicit column-name list? Does
the PARAMETERS clause come before or after that? Compare:
CREATE VIEW double (result) PARAMETERS (arg) AS SELECT arg * 2;
SELECT * FROM numbers, double(x);
WITH double (result) PARAMETERS (arg) AS (SELECT arg * 2)
SELECT * FROM numbers, double(x);
Versus:
CREATE VIEW double PARAMETERS (arg) (result) AS SELECT arg * 2;
SELECT * FROM numbers, double(x);
WITH double PARAMETERS (arg) (result) AS (SELECT arg * 2)
SELECT * FROM numbers, double(x);
I don't think there's any question the former is superior, but I bring
this up for two reasons. One, every syntax question needs an answer.
Two, I wish to highlight the fact that the (existing) syntax for
column-name list looks very much like what most languages use for a
parameter list, so there's potential confusion, hence the need for the
PARAMETERS token.
* I do agree that defining table-valued functions in these way can be
useful though; I have wanted to define views that take parameters
before, and was unable to.
I would love to be able to create functions without writing extensions
in C, plus this way functions won't require recursive invocation of
SQLite and won't have to be a barrier to the SQLite optimizer. Rather,
native functions would be inlined right into the bytecodes of whichever
queries use them.
* Another (separate) idea can be "CREATE FUNCTION name(args) AS
select_stmt;" to define your own function. [...] Both of these are
separate from table-valued functions (parameterized views) though.
Aside from the syntax, is this really a separate idea? What does this
do that views can't?
If you write "CREATE AGGREGATE FUNCTION" then the function name can be
used as a table name within the select_stmt.
I don't think I understand the part about letting the function name be
used as a table name. Parameters would already be bound, so there's no
need for a FROM clause to get at them. That's central to the concept of
parameters as explored by this email thread; I'm hunting for a practical
use for situations that would currently give a "no such column" error.
However, you bring up an interesting question, though it's a potential
issue whether or not the function is an aggregate function. What if a
parameter name happens to match a column name in one (or more) of the
tables being pulled in by a FROM clause? A table name qualifier is
needed to disambiguate. It could be the function name, though it could
be clearer to do like upsert ("excluded") and have a special token, e.g.
"parameters". (cf. https://sqlite.org/lang_UPSERT.html)
The above applies to parameterized views and common table expressions as
well, so replace "function name" with "view name" or "common table
expression name".
As for having to declare a function as an aggregate, I think that could
be inferred from the fact that the function uses aggregate functions on
its parameters. Maybe it would have to also not use GROUP BY on those
parameters, not sure there. What makes an aggregate function, anyway?
It always map multiple input rows to a single output row? Then does
that mean having LIMIT 1 would also make it an aggregate function? But
before we get bogged down in semantics, I ask whether or not this
distinction even matters.
--
Andy Goth |
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Idea: defining table-valued functions directly in SQL
I have some of my own ideas about this. * Perhaps move PARAMETERS before AS, which may make the syntax easier. * You don't need computed columns in tables; use views instead. You can index computed values though. * I do agree that defining table-valued functions in these way can be useful though; I have wanted to define views that take parameters before, and was unable to. * Another (separate) idea can be "CREATE FUNCTION name(args) AS select_stmt;" to define your own function. If you write "CREATE AGGREGATE FUNCTION" then the function name can be used as a table name within the select_stmt. Both of these are separate from table-valued functions (parameterized views) though. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Idea: defining table-valued functions directly in SQL
On Sun, Jun 10, 2018 at 7:48 AM Simon Slavin wrote: > On 10 Jun 2018, at 2:40pm, J Decker wrote: > > > forget contributing code ... but funding? how much can I pay to get > better > > NUL support? > > Can you tell us what's wrong with NUL support ? > I have, repeatedly. > The best changes come free. Because they're bug-fixes, or obvious > improvements worth the increase in code size and processing time. > decrease in processing time. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Idea: defining table-valued functions directly in SQL
On 10 Jun 2018, at 2:40pm, J Decker wrote: > forget contributing code ... but funding? how much can I pay to get better > NUL support? Can you tell us what's wrong with NUL support ? The best changes come free. Because they're bug-fixes, or obvious improvements worth the increase in code size and processing time. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Idea: defining table-valued functions directly in SQL
On Sat, Jun 9, 2018 at 7:37 PM Andy Goth wrote: > > This is probably not going to be added > until SQLite's primary developers themselves decide they need it, or > until someone else decides it's important enough to them to contribute > code and/or funding. > forget contributing code ... but funding? how much can I pay to get better NUL support? and get the table alias name for a column from a prepared statement? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Idea: defining table-valued functions directly in SQL
> What problem are you having with trigger performance? My primary use of triggers in SQLite is on insert/update and using RAISE. Not sure where the trigger overhead comes from, but If I place a simple check in CHECK and have a trigger with the same CHECK then CHECK is way faster than a trigger. (on 3.18) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Idea: defining table-valued functions directly in SQL
Skip that, that is foreign-keys. What problem are you having with trigger performance? --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf >Sent: Saturday, 9 June, 2018 22:06 >To: SQLite mailing list >Subject: Re: [sqlite] Idea: defining table-valued functions directly >in SQL > > >Do you have an appropriate index defined on both the parent and child >keys? > >.lint fkey-indexes > >at a command shell prompt will tell you ... > >--- >The fact that there's a Highway to Hell but only a Stairway to Heaven >says a lot about anticipated traffic volume. > > >>-Original Message- >>From: sqlite-users [mailto:sqlite-users- >>boun...@mailinglists.sqlite.org] On Behalf Of David Burgess >>Sent: Saturday, 9 June, 2018 21:44 >>To: SQLite mailing list >>Subject: Re: [sqlite] Idea: defining table-valued functions directly >>in SQL >> >>> reminder that views can have triggers >> >>Anyone else have an issue with trigger performance in SQLite? >>___ >>sqlite-users mailing list >>sqlite-users@mailinglists.sqlite.org >>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Idea: defining table-valued functions directly in SQL
Do you have an appropriate index defined on both the parent and child keys? .lint fkey-indexes at a command shell prompt will tell you ... --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of David Burgess >Sent: Saturday, 9 June, 2018 21:44 >To: SQLite mailing list >Subject: Re: [sqlite] Idea: defining table-valued functions directly >in SQL > >> reminder that views can have triggers > >Anyone else have an issue with trigger performance in SQLite? >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Idea: defining table-valued functions directly in SQL
> reminder that views can have triggers Anyone else have an issue with trigger performance in SQLite? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Idea: defining table-valued functions directly in SQL
On 6/9/2018 8:31 PM, Simon Slavin wrote: > On 10 Jun 2018, at 2:18am, Andy Goth wrote: >> Skip computed columns in the value list? If two tables have the same >> schema, this should duplicate one into the other, but apparently not: >> >> INSERT INTO table2 SELECT * from table1; > > This syntax, when found with a computed column, would be something > that the SQLite engine would have to notice and act correctly. The > same in the case of CREATE TABLE ... SELECT ... . > > There are a number of other niggles. For instance, creating and > updating an index which includes a VIRTUAL calculated column could be > complicated and time-consuming. It may be that for a computed column > to appear in an index it must be STORED. On the other hand, the way > SQLite works internally might make that unnecessary. I think it's clear why SQLite doesn't already have this feature. The required underlying capability is already available. The remaining bits are a convenience to the end user at the cost of significant added complexity inside the engine. This is probably not going to be added until SQLite's primary developers themselves decide they need it, or until someone else decides it's important enough to them to contribute code and/or funding. SQLite doesn't seek to implement all things SQL, just the parts that have proven to be a workhorse. There's been a gradual shift as more development hours have been invested, but the "Lite" still means something. https://sqlite.org/omitted.html I've watched this list get shorter over the years, but the items that remain are increasingly likely to become permanent fixtures. And hey, that list was a welcome reminder that views can have triggers which can translate back to real DELETE, INSERT, and UPDATE operations on the underlying table. This might be what you want. SQLite's embeddability and ease of integration (particularly with Tcl) makes it very easy to wrap, so you can give yourself commands that generate and access tables using whatever syntax works best for you. You can write code that intercepts and translates the computed column syntax of your choice to automatically generated fancy views with triggers. Once you get the semantics nailed down and others find your work useful, that effort can serve as a reference implementation for actually adding the capability to SQLite itself. -- Andy Goth | signature.asc Description: OpenPGP digital signature ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Idea: defining table-valued functions directly in SQL
create table templog ( datestamp text collate nocase primary key, centTemp REAL not null, fahrTemp REAL ); create trigger TempLogInsert after insert on templog begin update templog set fahrtemp = new.centTemp/9*5+32 where datestamp == new.datestamp; end; create trigger tempLogUpdate after update of centTemp on tempLog begin update templog set fahrtemp = new.centTemp/9*5+32 where datestamp == old.datestamp; end; There ya go. A persistent computed column ... --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Andy Goth >Sent: Saturday, 9 June, 2018 19:18 >To: sqlite-users@mailinglists.sqlite.org >Subject: Re: [sqlite] Idea: defining table-valued functions directly >in SQL > >On 06/09/18 20:10, Simon Slavin wrote: >> On 10 Jun 2018, at 2:00am, Andy Goth >wrote: >>> CREATE TABLE tempLog ( >>>datestamp TEXT COLLATE NOCASE PRIMARY KEY >>> , centTemp REAL); >>> CREATE VIEW tempLogView AS >>> SELECT * >>> , centTemp * 9 / 5 + 32 AS fahrTemp >>>FROM tempLog; >> >> Yes. That is an elegant and compact way to do it with SQLite as it >is >> now. But it's not as satisfying. And it doesn't allow indexing by >> the computed column. You have to define the calculation in the >index >> separately. > >However, views make behavior of INSERT and UPDATE clear, since they >can >only operate on the real table. INSERT or UPDATE become murky when >in >the presence of computed columns. I suppose the only sane thing to >do >is forbid directly setting the value of a computed column, though >what >would the syntax be? Skip computed columns in the value list? If >two >tables have the same schema, this should duplicate one into the >other, >but apparently not: > >INSERT INTO table2 SELECT * from table1; > >-- >Andy Goth | >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Idea: defining table-valued functions directly in SQL
On 10 Jun 2018, at 2:18am, Andy Goth wrote: > However, views make behavior of INSERT and UPDATE clear, since they can > only operate on the real table. INSERT or UPDATE become murky when in > the presence of computed columns. I suppose the only sane thing to do > is forbid directly setting the value of a computed column, though what > would the syntax be? One cannot set the value of a computed column in INSERT or UPDATE. An attempt to do so yields an error in both SQL SERVER and MySQL. Also, computed columns are skipped in the form of INSERT where the columns are not named. > Skip computed columns in the value list? If two > tables have the same schema, this should duplicate one into the other, > but apparently not: > > INSERT INTO table2 SELECT * from table1; This syntax, when found with a computed column, would be something that the SQLite engine would have to notice and act correctly. The same in the case of CREATE TABLE ... SELECT ... . There are a number of other niggles. For instance, creating and updating an index which includes a VIRTUAL calculated column could be complicated and time-consuming. It may be that for a computed column to appear in an index it must be STORED. On the other hand, the way SQLite works internally might make that unnecessary. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Idea: defining table-valued functions directly in SQL
On 06/09/18 20:10, Simon Slavin wrote: On 10 Jun 2018, at 2:00am, Andy Goth wrote: CREATE TABLE tempLog ( datestamp TEXT COLLATE NOCASE PRIMARY KEY , centTemp REAL); CREATE VIEW tempLogView AS SELECT * , centTemp * 9 / 5 + 32 AS fahrTemp FROM tempLog; Yes. That is an elegant and compact way to do it with SQLite as it is now. But it's not as satisfying. And it doesn't allow indexing by the computed column. You have to define the calculation in the index separately. However, views make behavior of INSERT and UPDATE clear, since they can only operate on the real table. INSERT or UPDATE become murky when in the presence of computed columns. I suppose the only sane thing to do is forbid directly setting the value of a computed column, though what would the syntax be? Skip computed columns in the value list? If two tables have the same schema, this should duplicate one into the other, but apparently not: INSERT INTO table2 SELECT * from table1; -- Andy Goth | ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Idea: defining table-valued functions directly in SQL
On 10 Jun 2018, at 2:00am, Andy Goth wrote: > CREATE TABLE tempLog ( > datestamp TEXT COLLATE NOCASE PRIMARY KEY > , centTemp REAL); > CREATE VIEW tempLogView AS > SELECT * > , centTemp * 9 / 5 + 32 AS fahrTemp > FROM tempLog; Yes. That is an elegant and compact way to do it with SQLite as it is now. But it's not as satisfying. And it doesn't allow indexing by the computed column. You have to define the calculation in the index separately. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Idea: defining table-valued functions directly in SQL
On Sat, Jun 9, 2018 at 7:00 PM, Andy Goth wrote: > On 06/09/18 18:04, Simon Slavin wrote: >> >> CREATE TABLE tempLog ( >> datestamp TEXT COLLATE NOCASE PRIMARY KEY, >> centTemp REAL, >> fahrTemp AS (centTemp*9/5 + 32) ) >> >> I'm happy with another syntax as long as it does the same thing. > > > CREATE TABLE tempLog ( >datestamp TEXT COLLATE NOCASE PRIMARY KEY > , centTemp REAL); > CREATE VIEW tempLogView AS > SELECT * > , centTemp * 9 / 5 + 32 AS fahrTemp >FROM tempLog; > >> Niggle 1: Can a computed column refer to a column defined after it ? > > > With the view syntax I showed above, "computed" columns can only refer > to columns that exist in the underlying tables. I wish SELECT statement > expressions could refer not only to input columns but also output > columns that have been named using AS, but we don't have this feature. SELECT *, (computation on Y) AS X FROM ( SELECT *, (some computation) AS Y FROM sometable) It is a little annoying having to nest them, but it works. -- Scott Robison ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Idea: defining table-valued functions directly in SQL
On 06/09/18 18:04, Simon Slavin wrote: CREATE TABLE tempLog ( datestamp TEXT COLLATE NOCASE PRIMARY KEY, centTemp REAL, fahrTemp AS (centTemp*9/5 + 32) ) I'm happy with another syntax as long as it does the same thing. CREATE TABLE tempLog ( datestamp TEXT COLLATE NOCASE PRIMARY KEY , centTemp REAL); CREATE VIEW tempLogView AS SELECT * , centTemp * 9 / 5 + 32 AS fahrTemp FROM tempLog; Niggle 1: Can a computed column refer to a column defined after it ? With the view syntax I showed above, "computed" columns can only refer to columns that exist in the underlying tables. I wish SELECT statement expressions could refer not only to input columns but also output columns that have been named using AS, but we don't have this feature. -- Andy Goth | ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Idea: defining table-valued functions directly in SQL
On 06/09/18 19:46, David Burgess wrote: There are some functions which are banned. Are extension functions permitted? https://sqlite.org/c3ref/create_function.html And how does an extension function author indicate that the function is deterministic? https://sqlite.org/c3ref/c_deterministic.html -- Andy Goth | ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Idea: defining table-valued functions directly in SQL
Notice that the view isn't bound to the numbers table or its x column until it's actually used in the SELECT query. [...] This might also make it possible for a table-valued function to recursively invoke itself, provided that it has a basis case to avoid infinite descent. We already have recursive invocation with common table expressions. Nevertheless, here's another way to look at it: CREATE VIEW range AS SELECT start WHERE CASE WHEN step > 0 THEN start <= stop ELSE start >= stop END UNION ALL range(start + step, stop, step) PARAMETERS (start, stop, step DEFAULT 1 CHECK (step != 0)); Here I say "range(...)" as shorthand for "SELECT * FROM range(...)", by analogy with IN and table-valued functions. With the above, once start exceeds stop, the engine would have to optimize out the infinite sequence of UNION ALL against guaranteed-empty results. It would make sense for table-valued functions to be usable as expressions, provided that they return exactly one row and one column. CREATE VIEW double AS SELECT arg * 2 PARAMETERS (arg); SELECT x, double(x) FROM range(1, 10); How about table-valued functions as row values, provided they return exactly one row? CREATE TABLE foo (a, b, c); CREATE VIEW multiples AS SELECT x, x * 2, x * 3 PARAMETERS (x); UPDATE foo SET (a, b, c) = multiples(5) WHERE a = 0; Table-valued functions are already permitted to be the right-hand of an IN operator, so these new table-valued functions should be no different, provided they return exactly one column. CREATE VIEW multiples AS VALUES (x) UNION ALL VALUES (x * 2) UNION ALL VALUES (x * 3) PARAMETERS (x); SELECT * FROM foo WHERE (a, b, c) IN multiples(a); -- Andy Goth | ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Idea: defining table-valued functions directly in SQL
> There are some functions which are banned. Are extension functions permitted? And how does an extension function author indicate that the function is deterministic? On Sun, Jun 10, 2018 at 10:23 AM, Simon Slavin wrote: > On 10 Jun 2018, at 1:09am, David Burgess wrote: > >> full_account_number AS ( branch || account_number ) >> >> or >> >> branch AS (substr(full_account_number,1,4)), >> account_number AS (substr(full_account_number,5)) > > All the above are allowed in computed columns. There are some functions > which are banned. They are mostly non-deterministic functions. So a > calculation which depends on 'now' would not be allowed. Similarly for > COUNT(*) or the result of a PRAGMA. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Idea: defining table-valued functions directly in SQL
On 10 Jun 2018, at 1:09am, David Burgess wrote: > full_account_number AS ( branch || account_number ) > > or > > branch AS (substr(full_account_number,1,4)), > account_number AS (substr(full_account_number,5)) All the above are allowed in computed columns. There are some functions which are banned. They are mostly non-deterministic functions. So a calculation which depends on 'now' would not be allowed. Similarly for COUNT(*) or the result of a PRAGMA. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Idea: defining table-valued functions directly in SQL
Would schema defined functions fulfill the same requirement (e.g. return val = p1 || p2) ? Computed columns would be of assistance to me, assuming that functions are permitted. e.g. full_account_number CHAR(16), branch CHAR(4), account_number CHAR(12), full_account_number AS ( branch || account_number ) or branch AS (substr(full_account_number,1,4)), account_number AS (substr(full_account_number,5)) and sha_value AS (sha3(full_account_number,512)) I have one database that has several similar concatenated encodings, some with 5 parts. The example given is a simplification, calculated when required would abbreviate much of my retrieval SQL. (Although views provide a partial solution) Niggle 1: I can see why I would like forward references, although not mandatory. Niggle 2: I have no need for stored computed values > computed columns - fahrTemp AS (centTemp*9/5 + 32) > Niggle 1: Can a computed column refer to a column defined after it ? >Niggle 2: Should a computed value be stored with the other values or >calculated when required ? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Idea: defining table-valued functions directly in SQL
On 9 Jun 2018, at 9:21pm, Andy Goth wrote: > I suggest extending SQLite views and common table expressions to be the > vehicle for defining table-valued functions directly in SQL. I'll raise your bid. I want computed columns (SQL SERVER), otherwise known as generated columns (MySQL). CREATE TABLE tempLog ( datestamp TEXT COLLATE NOCASE PRIMARY KEY, centTemp REAL, fahrTemp AS (centTemp*9/5 + 32) ) I'm happy with another syntax as long as it does the same thing. Niggle 1: Can a computed column refer to a column defined after it ? Niggle 2: Should a computed value be stored with the other values or calculated when required ? With regard to Niggle 2, some SQL variants support both answers. SQL SERVER allows you to use PERSISTED after the calculation to say that you want calculations performed and stored on INSERT and UPDATE. Whoever picked the word PERSISTED should be shot. We hates it. MySQL uses either VIRTUAL or STORED, defaulting to VIRTUAL. I prefer those words. This should obsolete indexes on expressions, though it probably wouldn't in SQLite 3 for historical compatibility reasons. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
