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] Fwd: ON CONFLICT parser bug?
> > > > A SELECT in an UPSERT should always contain a WHERE clause. Like this: > >INSERT INTO t1(a) SELECT a FROM t2 WHERE true ON CONFLICT(A) DO NOTHING; > > Without the WHERE clause, the ON keyword tricks the parser into > thinking it is processing an "ON" join constraint on the FROM clause. > The WHERE clause resolves the ambiguity. The extra no-op WHERE clause > is omitted from the prepared statement by the query optimizer and > hence does not slow down the execution of the statement. > > This is a known limitation of the UPSERT syntax. I had intended to > document it, but I apparently forgot to do so, or at least I cannot > find where I documented it right this second. It is a messy situation > that comes about due to our use of an LALR(1) parser (parsers with > more lookahead also run slower) and by the need to provide full > backwards compatibility with older versions of SQLite. > > Thanks. Easy to live with! The first time I used UPSERT with a SELECT it was a JOIN ending with USING(). Worked like a charm! ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fwd: ON CONFLICT parser bug?
On 7/7/18, Paul van Helden wrote: > Hi, > > I've used the new UPSERT with success with a complex SELECT, then got to > scratch my head for a while on a much simpler query, so assuming this might > be a bug: > > CREATE TABLE T2 (A INTEGER PRIMARY KEY); > INSERT INTO T2 VALUES (1); > CREATE TABLE T1 (A INTEGER PRIMARY KEY); > INSERT INTO T1 VALUES (1); > INSERT INTO T1 (A) SELECT A FROM T2 ON CONFLICT(A) DO NOTHING; > A SELECT in an UPSERT should always contain a WHERE clause. Like this: INSERT INTO t1(a) SELECT a FROM t2 WHERE true ON CONFLICT(A) DO NOTHING; Without the WHERE clause, the ON keyword tricks the parser into thinking it is processing an "ON" join constraint on the FROM clause. The WHERE clause resolves the ambiguity. The extra no-op WHERE clause is omitted from the prepared statement by the query optimizer and hence does not slow down the execution of the statement. This is a known limitation of the UPSERT syntax. I had intended to document it, but I apparently forgot to do so, or at least I cannot find where I documented it right this second. It is a messy situation that comes about due to our use of an LALR(1) parser (parsers with more lookahead also run slower) and by the need to provide full backwards compatibility with older versions of SQLite. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fwd: ON CONFLICT parser bug?
On 7 Jul 2018, at 3:07pm, Paul van Helden wrote: > INSERT INTO T1 (A) SELECT A FROM T2 ON CONFLICT(A) DO NOTHING; It's possible that many people miss this construction because SQLite has INSERT OR IGNORE INTO T1 (A) SELECT A FROM T2; Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Fwd: ON CONFLICT parser bug?
Hi, I've used the new UPSERT with success with a complex SELECT, then got to scratch my head for a while on a much simpler query, so assuming this might be a bug: CREATE TABLE T2 (A INTEGER PRIMARY KEY); INSERT INTO T2 VALUES (1); CREATE TABLE T1 (A INTEGER PRIMARY KEY); INSERT INTO T1 VALUES (1); INSERT INTO T1 (A) SELECT A FROM T2 ON CONFLICT(A) DO NOTHING; An SQL error has occurred: near "DO": syntax error sqlite3_prepare_v2 actually stops the statement after DO, so that pzTail points to " NOTHING". More complex SELECT statements (seems anything after the table name, e.g. WHERE or LIMIT will suffice) works: INSERT INTO T1 (A) SELECT A FROM T2 WHERE 1=1 ON CONFLICT(A) DO NOTHING I'm sure others have found this already? Regards, Paul. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Automatic numbering
2018-07-07 14:57 GMT+02:00 Simon Slavin :
> On 7 Jul 2018, at 12:04pm, Cecil Westerhof wrote:
>
> > I went for the following solution:
> >UPDATE
>
>
> selectRandom
> >SETlastUsed= DATE('now', 'localtime')
> >, lastUsedIdx = (
> >SELECT IFNULL(MAX(lastUsedIdx), 0)
> >FROM selectRandom
> >WHERE lastUsed = DATE('now', 'localtime')
> >) + 1
> >WHERE description = :teaToBrew
> >
> > I would think that is not to convoluted.
>
> Elegant. I understood it. Though I was primed with what you're trying to
> do.
>
I drink a lot of different sorts of tea. Often I have between 20 and 30
different types. To make the choice easier I wrote a little
application
that only shows a few to select from. The longer ago that I drank a tea,
the greater the change it is shown in the list. I also display the latest
tea I drank. (I have several chai teas, but you should not drink it more as
once in three days.) To show them in the correct order (not important, but
nice to have) I wanted this index. For that I use:
SELECT *
FROM teaInAndOutStock
ORDER BY LastUsedDESC
,lastUsedIdx DESC
LIMIT:limitNr
teaInAndOutStock is a view on selectRandom.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Automatic numbering
On 7 Jul 2018, at 12:04pm, Cecil Westerhof wrote:
> I went for the following solution:
>UPDATE selectRandom
>SETlastUsed= DATE('now', 'localtime')
>, lastUsedIdx = (
>SELECT IFNULL(MAX(lastUsedIdx), 0)
>FROM selectRandom
>WHERE lastUsed = DATE('now', 'localtime')
>) + 1
>WHERE description = :teaToBrew
>
> I would think that is not to convoluted.
Elegant. I understood it. Though I was primed with what you're trying to do.
Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Automatic numbering
2018-07-05 5:37 GMT+02:00 Simon Slavin :
> On 5 Jul 2018, at 3:22am, Cecil Westerhof wrote:
>
> > I only want to store a date with a record. But it is possible that more
> as
> > one record will be inserted, so I want to use another field to use as an
> > index. So that the first gets an one, the second a two, etc.
> > Is this possible, or do I just have to check if there is already a date
> and
> > fetch the highest index and increase this with one?
>
> There's no magical shortcut.
>
> I would create an index on (theDate, dateEventNumber). Then do
>
> BEGIN
> SELECT MAX(dateEventNumber) FROM MyTable WHERE theDate = ?1
> [ in your code see whether you got NULL back, substitute 0 ]
> INERT INTO MyTable ... ?1 + 1
> COMMIT
>
> You can combine the two commands into one more complicated thing, but I'd
> do that only if I was sure nobody would ever have to figure out why my code
> wasn't working.
>
I went for the following solution:
UPDATE selectRandom
SETlastUsed= DATE('now', 'localtime')
, lastUsedIdx = (
SELECT IFNULL(MAX(lastUsedIdx), 0)
FROM selectRandom
WHERE lastUsed = DATE('now', 'localtime')
) + 1
WHERE description = :teaToBrew
I would think that is not to convoluted.
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
