Re: [sqlite] Changes on sqlite3 parser and why not ?

[petern]

Some points on working table alias:

1. If upsert goes to trunk, there is already a INSERT table alias test case
(do_execsql_test upsert3-210)  that must succeed:

https://www.sqlite.org/src/info/907b5a37c539ea67

2. Obviously PostgreSQL already supports a working table alias universally:

https://www.postgresql.org/docs/9.5/static/sql-insert.html
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]

https://www.postgresql.org/docs/9.5/static/sql-update.html
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]

https://www.postgresql.org/docs/9.5/static/sql-delete.html
DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]

3. In tables with serially related data it can be cumbersome to reference
columns in the working table when columns must be computed from existing
rows in the same table.  The INSERT/UPDATE/DELETE working table alias would
be very helpful and informative for readability in these situations.

Peter



On Tue, Apr 17, 2018 at 9:58 AM, Domingo Alvarez Duarte 
wrote:

> Hello Richard !
>
> Now that you are making changes on sqlite3 parser could you please add the
> table alias to delete/insert/update ?
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] exit status of command shell

[petern]

Roman.  That's a good one.  It affects the command status of well formed
SQL as well:

sqlite-src-323/bld$ echo 'SELECT * FROM sqlite_monster' |
./sqlite3;echo $?
Error: near line 1: no such table: sqlite_monster
0

sqlite-src-323/bld$ echo 'SELECT * FROM sqlite_monster;' |
./sqlite3;echo $?
Error: near line 1: no such table: sqlite_monster
1

Piped SQL lacking a trailing semicolon does indeed cause the shell to
report the wrong last command status.

Peter



On Wed, Apr 4, 2018 at 4:46 PM, Roman Fleysher <
roman.fleys...@einstein.yu.edu> wrote:

> Dear SQLiters,
>
>
> I am using sqlite3 shell from bash scripts and I stumbled on what I think
> is incorrect exit code on error. In the first scenario, on error the exit
> code is 1 -- expected, in the second it is 0 -- unexpected. The error
> message is the same in both. Is that normal?
>
>
> echo -e "ww; \n.exit" | sqlite3
>
> Error: near line 1: near "ww": syntax error
>
> echo $?
>
> 1
>
>
> echo -e "ww" | sqlite3
>
> Error: near line 1: near "ww": syntax error
>
> echo $?
>
> 0
>
> Thank you,
>
> Roman
>
> ___
> 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] Access to sqlite3_api_routines outside of a loadable extension context

[petern]

Max. You are free to export your code differently by preprocessor
directives.  One binary of your code can be an extension and another can be
an ordinary library.  The loadable version binary simply needs to export a
working sqlite3_extension_init() C entrypoint as described here:

https://www.sqlite.org/loadext.html#programming_loadable_extensions

Here is another reply about reusing code between a SQLite server and client
that should give you some ideas:

http://sqlite.1065341.n5.nabble.com/Function-design-question-tc100925.html#a100933

Peter



On Thu, Apr 5, 2018 at 8:23 AM, Max Vlasov  wrote:

> Hi,
> I'm considering creating a virtual table or user function that might
> possible work either as a loadable extension or as a general, statically
> created one. In order to avoid repeating during developing, I thought that
> I might use sqlite3_api_routines structure as a universal access to sqlite
> code routines in both cases. But it seems it is impossible to access the
> structure outside of a loadable extension context. Or am I missing
> something?
>
> Probably some direct access to sqlite3Apis variable is possible, but I
> suspect such practice isn't  recommended.
>
> Thanks
> ___
> 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] exit status of command shell

[petern]

Roman.  That's a good one.  It affects the command status of well formed
SQL as well:

sqlite-src-323/bld$ echo 'SELECT * FROM sqlite_monster' |
./sqlite3;echo $?
Error: near line 1: no such table: sqlite_monster
0

sqlite-src-323/bld$ echo 'SELECT * FROM sqlite_monster;' |
./sqlite3;echo $?
Error: near line 1: no such table: sqlite_monster
1

Piped SQL lacking a trailing semicolon does indeed cause the shell to
report the wrong last command status.

Peter

On Wed, Apr 4, 2018 at 4:46 PM, Roman Fleysher <
roman.fleys...@einstein.yu.edu> wrote:

> Dear SQLiters,
>
>
> I am using sqlite3 shell from bash scripts and I stumbled on what I think
> is incorrect exit code on error. In the first scenario, on error the exit
> code is 1 -- expected, in the second it is 0 -- unexpected. The error
> message is the same in both. Is that normal?
>
>
> echo -e "ww; \n.exit" | sqlite3
>
> Error: near line 1: near "ww": syntax error
>
> echo $?
>
> 1
>
>
> echo -e "ww" | sqlite3
>
> Error: near line 1: near "ww": syntax error
>
> echo $?
>
> 0
>
> Thank you,
>
> Roman
>
> ___
> 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] generate_series can theoretically behave differently in SQLite 3.23.0

[petern]

Two points.

1. If any NULL constraint on any vtable is to return no rows, then why does
the vtable code get called in the first place?

2. The shipped extension series.c had some NULL default constraint
behavior.  Are such constraint defaults now bad form?

In other words, the LEFT strength reduction compatibility refinement you
just made fixed the problem.

Since it now works, why is series.c also being changed here?
https://www.sqlite.org/src/info/3328e828e28ca719

[Also, why the zeal on series.c but no effort toward an actual segmentation
fault I reported in another shipped extension, eval.c?]

Peter



On Tue, Apr 3, 2018 at 6:50 AM, Richard Hipp  wrote:

> On 4/3/18, Richard Hipp  wrote:
> >
> > Probably there will be a 3.23.1 patch release later today.
> >
>
> Or, maybe not.
>
> If the series.c file is compiled with -DSQLITE_SERIES_CONSTRAINT_VERIFY=1
> then
> the generate_series() virtual table behaves correctly, and Edzard's
> example gives a correct answer both before and after the new LEFT JOIN
> strength reduction optimization is added.
> Without the -DSQLITE_SERIES_CONSTRAINT_VERIFY=1 compile-time option,
> the test case consistently gives the wrong answer.  Here is an
> alternative test case:
>
>   WITH
> t1(x) AS (VALUES(1),(2)),
> t2(y,z) AS (VALUES(2,1))
>   SELECT * FROM t1 LEFT JOIN t2 ON x=y JOIN generate_series
>WHERE start=z AND stop=2;
>
> The query above should return only two rows:
>
>2 2 1 1
>2 2 1 2
>
> But it instead returns 5 rows, because the generate_series virtual
> table is telling the code generate that it does not need to check the
> start=z constraint.  When the start=z constraint is not checked, then
> indeed 5 rows are generated because the query becomes equivalent to
> this:
>
>   WITH
> t1(x) AS (VALUES(1),(2)),
> t2(y,z) AS (VALUES(2,1))
>   SELECT * FROM t1 LEFT JOIN t2 ON x=y JOIN generate_series
>WHERE start=coalesce(z,0) AND stop=2;
>
> I'm testing a patch now that causes the LEFT JOIN strength reduction
> optimization to assume that NULL arguments to a virtual table
> constraint can return a TRUE result.  But I'm wondering, since this is
> really a work-around to problems in virtual table implementations, if
> this change warrants a patch release?
>
> Your thoughts?
>
> Should we issue 3.23.1 just to work around dodgy virtual table
> implementations?  Or should we just check-in the change and let those
> who want to continue using their dodgy virtual tables either patch the
> issue themselves or wait for 3.24.0?
>
> --
> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] generate_series can theoretically behave differently in SQLite 3.23.0

[petern]

I think your left join reduction regression change happens on any vtable
hidden column filter reference to an outer scope column.  A CTE duplicates
your finding below.

SQLite 3.23.0 2018-04-02 11:04:16 736b53f57f70b23172c30880186dce
7ad9baa3b74e3838cae5847cffb98f5cd2
sqlite> WITH t1(x) AS (VALUES(1),(2)), t2(x,y) AS (VALUES(1,4)) SELECT
x,value FROM t1 LEFT JOIN t2 USING(x) JOIN generate_series ON start=t2.y
AND stop=5;
1,4
1,5

SQLite 3.22.0 2018-01-22 18:45:57 0c55d179733b46d8d0ba4d88e01a25
e10677046ee3da1d5b1581e86726f2alt1
sqlite> WITH t1(x) AS (VALUES(1),(2)), t2(x,y) AS (VALUES(1,4)) SELECT
x,value FROM t1 LEFT JOIN t2 USING(x) JOIN generate_series ON start=t2.y
AND stop=5;
x,value
1,4
1,5
2,0
2,1
2,2
2,3
2,4
2,5

Peter


On Tue, Apr 3, 2018 at 12:22 AM, E.Pasma  wrote:

> Hello, below is a case where the generate_series virtual table behaves
> differently with SQLite 3.23. It is a purely artificial case. Does anyoone
> have an opinion about it?
>
> The issue comes from the fact that generate_series does not check if a
> supposed integer input value happens to be NULL. And continues as if zero
> is passed in. This way a WHERE condition like
>
> =
>
> can be true. Regular tables require "IS" instead of "=" here.
> Theoretically the left join reduction prover can now be misleaded. As is
> the case below,
>
> Is this a (obscure) bug in generate_series?
>
> E Pasma
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Before Insert/Update Trigger

[petern]

Hi Thomas.   Below is a toy "records" table example which illustrates the
INSTEAD OF pattern.

---
CREATE TABLE records(rowid INTEGER PRIMARY KEY, data INTEGER, change_date
TEXT DEFAULT CURRENT_TIMESTAMP);

CREATE VIEW instead_of_records AS SELECT * FROM records;

CREATE TRIGGER instead_of_records_update INSTEAD OF UPDATE ON
instead_of_records
BEGIN
  UPDATE records SET (data,change_date)=(NEW.data,CURRENT_TIMESTAMP)
  WHERE rowid=NEW.rowid;
END;
--FYI: Only the UPDATE trigger was needed here since the schema supports
DEFAULT values.

sqlite> INSERT INTO records(data) VALUES(99);
sqlite> SELECT * FROM records;
rowid,data,change_date
1,99,"2018-04-02 21:52:26"

sqlite> UPDATE instead_of_records SET data=100 WHERE rowid=1;
sqlite> SELECT * FROM records;
rowid,data,change_date
1,100,"2018-04-02 21:53:41"
--

Regarding http://www.sqlite.org/lang_createtrigger.html

The documentation can be very dense and succinct.  When in doubt about how
something works, do give the command line a try.

If you run into a bug or genuine show stopper, please do post it to the
list.

Peter






On Mon, Apr 2, 2018 at 4:36 AM, Thomas Kurz  wrote:

> Dear Peter,
>
> please apologize me replying directly as I wrote my initial post as an
> unregistered user and do not know how to reply to the list keeping the
> thread reference intact.
>
> You wrote:
>
> > SQLite has the INSTEAD OF trigger to intercept/modify/compose NEW values
>
> This sounds interesting and indeed being what I'm looking for. However, I
> must admit that from the explanation of INSTEAD OF trigger I couldn't image
> that this could be working.
>
> This is the actual situation I'm faced with: I want to create this trigger:
>
> CREATE TRIGGER records_insert BEFORE INSERT/UPDATE ON records FOR EACH ROW
> set NEW.change_date:=CURRENT_TIMESTAMP
>
> The problem: When I implement this with "FOR EACH ROW UPDATE records SET
> change_date=CURRENT_TIMESTAMP WHERE id=OLD.id", an infinite recursive loop
> occurs.
>
> Currently, I solve this by using UPDATE OF and specifying all columns but
> change_date. It would imho be more elegant to allow the NEW record to be
> modified. Unfortunately, I do not see how I could resolve this issue with
> an INSTEAD OF trigger. It's a table that is being modified and my
> understanding about INSTEAD OF was that they are primarily invented as a
> replacement for updateable views.
>
> Kind regards,
> Thomas
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Before Insert/Update Trigger

[petern]

Thomas, SQLite has the INSTEAD OF trigger to intercept/modify/compose NEW
values:

https://www.sqlite.org/lang_createtrigger.html#instead_of_trigger

I've found most situations are well handled by the INSTEAD OF trigger.   It
is powerful and somewhat comparable in functionality to stored procedure in
other SQL engines.

Since INSTEAD OF is available, the BEFORE trigger comes up only in rare
initialization or validation cases.

Do you have an example where the INSTEAD OF trigger solution is ruled out?

Peter

On Fri, Mar 30, 2018 at 11:50 PM, Thomas Kurz 
wrote:

> Other DBMS support the following construct in a trigger:
>
> CREATE TRIGGER name BEFORE UPDATE ON table
> FOR EACH ROW
> BEGIN
>   SET NEW.column = anyvalue
> END;
>
> In SQLite, the NEW record appearently is read-only.
>
> Support for changeable NEW records would however be graceful as it
> automatically prevents an infinite loop (recursive trigger) that can will
> more or less automatically occur otherwise when working with additional
> UPDATE-statements.
>
> ___
> 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] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

[petern]

Peter, is "INSTEAD OF" trigger not available on your version of SQLite?

https://sqlite.org/lang_createtrigger.html#instead_of_trigger

CREATE VIEW mytable_UPSERT AS SELECT * FROM mytable;
CREATE TRIGGER mytable_UPSERT INSTEAD OF INSERT ON mytable_UPSERT BEGIN
-->INSERT OR IGNORE ... ;
-->UPDATE  ;
END;

INSERT INTO mytable_UPSERT 

Peter

On Thu, Mar 22, 2018 at 12:18 PM, Peter Michaux 
wrote:

> I think there are a couple main offenders with
>
> > BEGIN;
> > INSERT OR IGNORE ... ;
> > UPDATE  ;
> > COMMIT;
>
> The first is that it is bulky. If this is in the application code then it
> has to be repeated for each desired UPSERT and it has to be repeated in the
> code of each application that uses the database.
>
> The second is that it seems so inefficient in the case of a new row being
> inserted. The row is inserted and then immediately updated. Why do both
> operations when only one is needed?
>
> Is it possible to write a stored procedure that checks a result of the
> INSERT OR IGNORE and only attempts the UPDATE if the row already existed?
> That would at least move the bulky code out of the application and into the
> database. Also it seems it would be more efficient.
>
> Thanks.
>
> Peter
>
>
> Peter
>
>
>
> On Wed, Mar 21, 2018 at 6:15 PM, Kees Nuyt  wrote:
>
> > On Wed, 21 Mar 2018 14:05:07 -0700, Peter Michaux
> >  wrote:
> >
> > > You are right that my purpose does seem kind of confusing.
> > >
> > > What I really want is UPSERT to avoid doing an UPDATE, checking if the
> > > number of rows affected is zero, then doing an INSERT. The lack of
> UPSERT
> > > leads to a lot more application code using the UPDATE/INSERT
> combination.
> > > UPSERT doesn't exist in SQLite so I was trying to work around that by
> > using
> > > INSERT OR REPLACE which is not the same thing. I can see from another
> > > recent thread that some others also think that UPSERT would be a
> valuable
> > > addition to SQLite.
> >
> > I fail to see the problem in
> > BEGIN;
> > INSERT OR IGNORE ... ;
> > UPDATE  ;
> > COMMIT;
> > Simple code, no need to test number of affected rows, and pretty
> > fast because the relevant pages will be in cache.
> >
> > Or use an updatable view with an INSTEAD OF INSERT trigger.
> >
> > I did notice that attempts to define a proper UPSERT syntax
> > opened a can of worms by itself because it (also) has to provide
> > two colum lists, one for a full INSERT if the row with that PK
> > doesn't exist, and another one for the columns to be updated
> > when the row already exists. So, I don't see a big advantage in
> > UPSERT.
> >
> > My humble two cents,
> >
> > --
> > Regards,
> > Kees Nuyt
> > ___
> > 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] How many AUTOINCREMENT tables are in your schema?

[petern]

Regarding SQLite "next_val()", the following works with or without "NOT
NULL":

CREATE TABLE t(rowid INTEGER PRIMARY KEY NOT NULL);
INSERT INTO t VALUES (NULL),(NULL);
SELECT * FROM t;
--rowid
--1
--2
DELETE FROM t WHERE rowid=1;
INSERT INTO t VALUES (NULL);
SELECT * FROM t;
--rowid
--2
--3

But these do not work at all:

CREATE TABLE t(rowid INT PRIMARY KEY);
INSERT INTO t VALUES (NULL),(NULL);
SELECT * FROM t;
--rowid
--
--

CREATE TABLE t(rowid INT PRIMARY KEY NOT NULL);
INSERT INTO t VALUES (NULL),(NULL);
--Error: NOT NULL constraint failed: t.rowid

CREATE TABLE t(rowid INTEGER PRIMARY KEY) WITHOUT ROWID;
INSERT INTO t VALUES (NULL),(NULL);
--Error: NOT NULL constraint failed: t.rowid


Peter


On Tue, Mar 20, 2018 at 9:44 AM, Chris Locke 
wrote:

> >  some people seem to think that an int primary key can be auto
> incrementing, it can't
>
> But it works in the same way  sort of.  Its auto incrementing, with the
> caveat that if the last row is deleted, the previous number will be used
> again.  Depending on the database schema, this may or may not cause issues.
>
>
> Thanks,
> Chris
>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] possible bug: select clause column alias shadowing

[petern]

Compared to PostgreSQL, SQLite does a better job here when there is no
input column collision.

The column collision case below returns no rows in both SQLite and
PostgreSQL:

WITH t(a) AS (VALUES ('foo')) SELECT a||'!' AS a FROM t WHERE a='foo!';

But the following edit with intermediating alias column b produces 'ERROR:
column "b" does not exist' in PostgreSQL:

sqlite> WITH t(a) AS (VALUES ('foo')) SELECT a||'!' AS b FROM t WHERE
b='foo!';
b
foo!

A safer coding style would be to use an intermediating query/view/cte when
any input column's meaning is being modified:

sqlite> WITH t(a) AS (VALUES ('foo')), u AS (SELECT a||'!' AS a FROM t)
SELECT a FROM u WHERE a='foo!';
a
foo!

Peter



On Sun, Mar 18, 2018 at 2:31 AM, Moritz Bruder 
wrote:

> Hi,
>
> I just came across a strange behaviour in sqlite3 (3.22.0 2018-01-22
> 18:45:57 0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2171d).
> Consider the following test case:
>
> CREATE TABLE test (name varchar);
> INSERT INTO test VALUES ("foo"),("bar");
>
> -- Returns a single row with a single column: 'foo!'
> SELECT (test.name || '!') AS tname
> FROM test
> WHERE tname = 'foo!'
>
> --Returns an empty result.
> SELECT (test.name || '!') AS name
> FROM test
> WHERE name = 'foo!';
>
> What happens is that the identifier "name", defined in the SELECT-clause,
> gets shadowed by the table's column "name". I'm not exactly sure what the
> SQL standard says but it is wrong in my opinion. I expect it to be the
> other way round.Let me know whether you consider it a bug.
>
>
> Best wishes,
>
> Moritz
> ___
> 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] How many AUTOINCREMENT tables are in your schema?

[petern]

0 AUTOINCREMENT columns.   A per column overload-able nextValue() interface
could have its uses though.

On Fri, Mar 16, 2018 at 8:37 AM, Richard Hipp  wrote:

> This is a survey, the results of which will help us to make SQLite faster.
>
> How many tables in your schema(s) use AUTOINCREMENT?
>
> I just need a single integer, the count of uses of the AUTOINCREMENT
> in your overall schema.  You might compute this using:
>
>sqlite3 yourfile.db '.schema --indent' | grep -i autoincrement | wc -l
>
> Private email to me is fine.  Thanks for participating in this survey!
> --
> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Function design question

[petern]

Simply bracket your server code with preprocessor directives like so:

static void my_universal_function(sqlite3_context *context, int argc,
sqlite3_value **argv) {

#ifdef MY_SQLITE_EXTENSION_MODE
  
#else
  
#endif
}

Then create another makefile rule target or IDE project which defines the
'MY_SQLITE_EXTENSION_MODE' to compile the common code file(s) as an
extension module for use on the command line.  Read about extensions here:

https://sqlite.org/loadext.html

By default, SQLite will return NULL when a function makes no return value
call.  BTW, per your preference, the logically complementary directive
#ifndef might work better for you.

Obviously you are free to choose more meaningful and compact macro
directive name than 'MY_SQLITE_EXTENSION_MODE'.  I chose that macro name
only to make this reply clear to the complete novice.

In practice you may find a lot of code will be able to meaningfully run in
the command line context - or possibly communicate with a running
application server instance capable of computing answers for the command
line context.

Peter



On Thu, Mar 15, 2018 at 4:40 AM, Toby Dickenson  wrote:

> I am using a trigger to maintain some audit information, for example
> updating row modification time on any insert or update. I now want to
> extend that mechanism to include other information from my
> application, for example logged in user name. I can do that by
> registering new functions to pass the extra information from the
> application to the trigger.
>
> But I also want to update these tables from the command line, when
> these functions wont be available. Is there a way to construct the
> trigger so that I get NULLs instead of errors when a custom function
> doesnt exist? Or a better way to pass information into the trigger
> other than functions?
>
> Thanks,
> ___
> 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] How to use WITH CLAUSE in a UPDATE statement?

[petern]

WITH "cte" is a table only WRT the UPDATE's RHS input space.  eg:

CREATE TABLE t AS SELECT (column1)i,(NULL)a FROM (VALUES (1),(2),(3));

WITH cte(i,a) AS (VALUES (1,10),(2,20)) UPDATE t SET a=(SELECT a FROM cte
WHERE i=t.i);

SELECT * FROM t;
i,a
1,10
2,20
3,

[FYI.  WITH ... INSERT/UPDATE/DELETE is not supported in a trigger body.]


On Thu, Mar 8, 2018 at 8:09 PM, sanhua.zh  wrote:

> I find that a UPDATE statement with WITH CLAUSE always fails, although I
> use the syntax as SQLite syntax suggested.
> Also, the `lang_with.html` do no show a UPDATE statement with WITH CLAUSE.
> They all run in a SELECT statement.
>
>
> Here is the sample SQL I tried:
> ```
> CREATE TABLE t(i INTEGER);
> INSERT INTO t VALUES(1);
> WITH cte AS(SELECT i FROM t)
> UPDATE cte SET i = 0; // Failed with "Error: no such table: cte"
> ```
>
>
> Note that the sample WITH CLAUSE make no sense. It's just for testing. But
> still failed.
> Did I use it in a wrong way? What's the suggesting rule?
> ___
> 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] Bug (or feature?) Trailing comment becomes part of column name

[petern]

sqlite> .mode column
sqlite> SELECT sqlite_source_id()--sql_comment
   ...> ;
sqlite_source_id()--sql_comment


2018-01-22 18:45:57
0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2alt1

sqlite> SELECT sqlite_source_id()/*sql_comment*/
   ...> ;
sqlite_source_id()/*sql_comment*/


2018-01-22 18:45:57
0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2alt1

However, with column alias present, column name is as expected:

sqlite> SELECT sqlite_source_id()ssid/*sql_comment*/
   ...> ;
ssid


2018-01-22 18:45:57
0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2alt1
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Missing "pushDownWhereTerms" optimisation on recursive CTE

[petern]

Some observations.  It seems the WHERE pushdown optimization you cited only
applies to subqueries with existing WHERE clause.  In your example without
WHERE, the SELECT specifies the whole table as the left hand side of the
UNION.  Scanning the whole table is likely more efficient than using an
index to retrieve every row.  Do you have a better example of the problem?


[Another suggestion in the form of a question:  Is the more efficient UNION
ALL completely ruled out because of duplicates?]

Peter





On Thu, Mar 1, 2018 at 2:37 AM, Adrián Medraño Calvo  wrote:

> Dear SQLite,
>
> The following SQL script shows a query selecting data from a recursive CTE
> and filtering it.  I expected the optimizer to apply the filter to the
> recursive CTE directly, and indeed the documentation of pushDownWhereTerms
> (src/select.c:3833) indicates this possibility when various conditions are
> satisfied.  As far as I can see, the conditions are satisfied, but the
> query is nonetheless not optimized.  This indicates a misunderstanding on
> my part, or an oversight in SQLite.
>
> -- A table containing some numbers.
> CREATE TABLE t (v INT PRIMARY KEY);
> INSERT INTO t
> VALUES (0), (1), (2), (3), (4), (5);
>
> -- Recursive query relating a number a sequence of numbers from "t" equal
> or
> -- greater than it.
> EXPLAIN QUERY PLAN
> WITH RECURSIVE
> eqgrseq(initial, next) AS (SELECT v, v
> FROM   t
> UNION
> SELECT eqgrseq.initial, t.v
> FROM   eqgrseq
> JOIN   t
> ON (t.v = eqgrseq.next + 1))
> SELECT eqgrseq.initial, eqgrseq.next
> FROM   eqgrseq
> WHERE  eqgrseq.initial = :initial;
> -- selectid,order,from,detail
> -- 2,0,0,"SCAN TABLE t"
> -- 3,0,0,"SCAN TABLE eqgrseq"
> -- 3,1,1,"SEARCH TABLE t USING COVERING INDEX sqlite_autoindex_t_1 (v=?)"
> -- 1,0,0,"COMPOUND SUBQUERIES 0 AND 0 USING TEMP B-TREE (UNION)"
> -- 0,0,0,"SCAN SUBQUERY 1”
>
> -- The same query with the WHERE condition manually placed in the
> recursive CTE's
> -- initial clause.
> EXPLAIN QUERY PLAN
> WITH RECURSIVE
> eqgrseq(initial, next) AS (SELECT v, v
> FROM   t
> WHERE v = :initial
> UNION
> SELECT eqgrseq.initial, t.v
> FROM   eqgrseq
> JOIN   t
> ON (t.v = eqgrseq.next + 1))
> SELECT eqgrseq.initial, eqgrseq.next
> FROM   eqgrseq
> WHERE  eqgrseq.initial = :initial;
> -- selectid,order,from,detail
> -- 2,0,0,"SEARCH TABLE t USING COVERING INDEX sqlite_autoindex_t_1 (v=?)"
> -- 3,0,0,"SCAN TABLE eqgrseq"
> -- 3,1,1,"SEARCH TABLE t USING COVERING INDEX sqlite_autoindex_t_1 (v=?)"
> -- 1,0,0,"COMPOUND SUBQUERIES 0 AND 0 USING TEMP B-TREE (UNION)"
> -- 0,0,0,"SCAN SUBQUERY 1”
>
> Note the query plan difference: the first scans the “t” table, therefore
> recurses for every value, while the second only recurses for the filtered
> ones.
>
> In our application, the recursive CTE is hidden behind a view in order to
> abstract over the details; manually inserting the WHERE clause would not be
> possible while maintaining the view, as far as I can see.
>
> Thank you,
> Adrián.
> ___
> 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] Improper error message

[petern]

Further to the earlier replies, here is a funny SQLite demo of function,
keyword, and column names that is informative about the possibilities.

sqlite> .load distinct.so
sqlite> SELECT DISTINCT "distinct"() "distinct" WHERE [distinct] NOT NULL;
distinct
"fn distinct was called"

distinct.c extension function source
#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1
static void distinct(sqlite3_context *context, int argc, sqlite3_value
**argv) {
  sqlite3_result_text(context, "fn distinct was called", 22,
SQLITE_TRANSIENT);
}
int sqlite3_distinct_init(sqlite3 *db, char **pzErrMsg, const
sqlite3_api_routines *pApi) {
  SQLITE_EXTENSION_INIT2(pApi);
  int rc = sqlite3_create_function(db, "distinct", -1, SQLITE_UTF8, 0,
distinct, 0, 0);
  return rc;
}


On Tue, Feb 20, 2018 at 2:59 PM, Etienne Sanchez 
wrote:

> Hi,
>
> Some other DBMS such as Postgres and SQL-Server implement the functions
> "left" and "right". SQLite does not. But why do I get confusing error
> messages when I (mistakenly) attempt to use them?
>
> To illustrate my point:
>
>
> select foo('abc', 2)
> Error: no such function: foo
>
> select left('abc', 2)
> Error: near "(": syntax error
>
> select right('abc', 2)
> Error: near "(": syntax error
>
>
> In the 2nd and 3rd cases I would expect a "no such function" error. The
> "syntax error" messages are slightly misleading, they lead the user to
> think that he has forgotten a parenthesis or a comma somewhere in the
> query.
>
> As for "left", it's maybe due to the ambiguity with "left join", but then
> what about "right"? (There is no ambiguity with "right join" since it is
> not supported.)
>
> Thanks,
> ___
> 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] printf() problem padding multi-byte UTF-8 code points

[petern]

There are other uses for padding strings besides user reports.  Consider
scalar representations of computations for example. Also:

1.There was no mention of user display formatting in Ralf's original
report.  It was a bug report about missing inverse functionality for
padding/trimming strings.
2.The proposed functions fully exist in the PostgreSQL archetype.  Is
PostgreSQL wrong?
3. Why can't SQLite have the expected common static SQL functions for
getting rapid development done without external tools?
Is the goal to reduce SQL portability and increase development effort just
to see some representative output results?

I don't think anybody is trying to create production grade displays within
SQL but being able to produce representative output and having the expected
nucleus of built-in SQL functions (including canonical inverses) is still a
sensible goal.

On Mon, Feb 19, 2018 at 6:06 PM, Simon Slavin <slav...@bigfraud.org> wrote:

> On 20 Feb 2018, at 1:38am, petern <peter.nichvolo...@gmail.com> wrote:
>
> > Yet even so, as Ralf pointed out, the PostgreSQL lpad() and rpad() fill
> > with arbitrary string functionality would still be missing despite the
> > checked in printf() being more directly equivalent to the PostgreSQL
> > format() function.  First things first I suppose...
> >
> > PostgreSQL lpad() and rpad() documentation is here:
> > https://www.postgresql.org/docs/9.5/static/functions-string.html
>
> The problem with string length and padding was pointed out upthread.
> Padding strings to a length was useful in the days of fixed-width fonts.
> We don't do that much these days.  And even if you could equip SQLite with
> functions which did those things, to do it properly you'd need routines
> which understood Unicode characters, combinations, accents and the sort of
> diacritics used for Hebrew and Arabic vowels.  Without that, you fancy new
> feature is just going to trigger hundreds of bug reports.
>
> String width functions now days take two parameters, the string (in some
> flavour of Unicode) and a font descriptor (font, size, emphasis and other
> options) and return the width of the string in points, taking into account
> not only Unicode features but font features like kern hinting and
> ligatures.  And you will find these features in your operating system.
>
> So please, folks, don't try to do this in a purposely tiny DBMS.  Do it
> using OS calls, as the people who designed your OS intended.
>
> 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] printf() problem padding multi-byte UTF-8 code points

[petern]

FYI.  See http://www.sqlite.org/src/timeline for the equivalent DRH
checkins:  http://www.sqlite.org/src/info/c883c4d33f4cd722
Hopefully that branch will make a forthcoming trunk merge.   [Printing
explicit nul terminator by formatting an interesting twist.]

Yet even so, as Ralf pointed out, the PostgreSQL lpad() and rpad() fill
with arbitrary string functionality would still be missing despite the
checked in printf() being more directly equivalent to the PostgreSQL
format() function.  First things first I suppose...

PostgreSQL lpad() and rpad() documentation is here:
https://www.postgresql.org/docs/9.5/static/functions-string.html

Peter

On Mon, Feb 19, 2018 at 4:38 PM, Cezary H. Noweta 
wrote:

> Hello,
>
> On 2018-02-17 18:39, Ralf Junker wrote:
>
>> Example SQL:
>>
>> select
>>length(printf ('%4s', 'abc')),
>>length(printf ('%4s', 'äöü')),
>>length(printf ('%-4s', 'abc')),
>>length(printf ('%-4s', 'äöü'))
>>
>> Output is 4, 3, 4, 3. Padding seems to take into account UTF-8 bytes
>> instead of UTF-8 code points.
>>
>> Should padding not work on code points and output 4 in all cases as
>> requested?
>>
>
> If you are interested in a patch extending a functionality of ``printf()''
> then http://sqlite.chncc.eu/utf8printf/. Adding ``l'' length modifier
> makes width/precision specifications being treated as numbers of UTF-8
> chars -- not bytes. ``SELECT length(printf ('%4ls', 'äöü'));'' will give 4.
>
> -- best regards
>
> Cezary H. Noweta
> ___
> 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] printf() problem padding multi-byte UTF-8 code points

[petern]

As d3ck0r suggested.  adding a byte_length() function would enable padding
of spaces [but not general padding with arbitrary characters as lpad() and
rpad() afford].

WITH points(p) AS (VALUES ('abc'), ('äöü'), ('です'))
,format(f) AS (VALUES ('%*s'), ('%-*s'))
,pad AS (SELECT p, f, printf(f,byte_length(p)+(4-length(p)),p)pad FROM
points CROSS JOIN format)
SELECT p,f,pad,length(pad)len FROM pad;

'p','f','pad','len'
'abc','%*s',' abc',4
'abc','%-*s','abc ',4
'äöü','%*s',' äöü',4
'äöü','%-*s','äöü ',4
'です','%*s','  です',4
'です','%-*s','です  ',4

A new byte_length() function is a great idea but for getting action on
publishing it and the requisite help page entry.
I recently asked to add 1 protection source line in the eval() function
against segmentation fault but got neither action nor reply.
Experience suggests you will have to add the 3 source lines to your local
copy of SQLite if you must to pad strings containing high code points:

static void byte_length(sqlite3_context *context, int argc, sqlite3_value
**argv) {
  sqlite3_result_int(context, sqlite3_value_bytes(argv[0]));
}

Peter





On Mon, Feb 19, 2018 at 12:43 AM, Ralf Junker  wrote:

> On 18.02.2018 00:36, Richard Hipp wrote:
>
> The current behavior of the printf() function in SQLite, goofy though
>> it may be, exactly mirrors the behavior of the printf() C function in
>> the standard library in this regard.
>>
>
> SQLite3 is not C. SQLite3 text storage is always Unicode. Thus SQL text
> processing functions should work on Unicode. The current implementation
> of the SQLite3 SQL printf() can not reliably be used for string padding.
> And there is no simple alternative, AFAICS.
>
> PostgreSQL returns 4 in all cases:
>
> select
>length(format ('%4s', 'abc')),
>length(format ('%4s', 'äöü')),
>length(format ('%-4s', 'abc')),
>length(format ('%-4s', 'äöü'))
>
> MySQL has lpad() and rpad() to achieve the same and also returns 4 in
> all cases:
>
> select
>length(lpad ('abc', 4, ' ')),
>length(lpad ('äöü', 4, ' ')),
>length(rpad ('abc', 4, ' ')),
>length(rpad ('äöü', 4, ' '))
>
> I strongly believe that SQLite3 should follow suit.
>
> Ralf
> ___
> 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] Segmentation fault in 3.22 eval.c under PRAGMA empty_result_callbacks=1

[petern]

sqlite> .version
SQLite 3.22.0 2018-01-22 18:45:57
0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2alt1
zlib version 1.2.8
gcc-4.8.4

sqlite> .load eval.so
sqlite> PRAGMA empty_result_callbacks=1;
sqlite> SELECT eval('SELECT 1 WHERE 0');
... Segmentation fault

If a new protection line 35:"if(!argv) return 0;" or equivalent is being
added to 'eval.c', please also consider the enhancement of implementing an
optional 3rd argument eg. eval(X,Y,Z) where Z is the row separator.
eval(X[,Y[,Z]]) with  both optional Y column separator and optional Z row
separator will make eval() more general and useful in all situations.

Thanks.
Peter
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Static sqlite3 library for Linux

[petern]

Petros, FYI.  gcc also has several different switches for object ouput:
eg. "gcc -c -static  ..."

It might help to investigate these options during steps to compile sqlite.c
and your main program to avoid dynamic/static symbol conflicts.

   -static
   On systems that support dynamic linking, this prevents linking
with the shared libraries.  On other systems, this option has no effect.

   -shared
   Produce a shared object which can then be linked with other
objects to form an executable.  Not all systems support this option.  For
predictable results,
   you must also specify the same set of options used for
compilation (-fpic, -fPIC, or model suboptions) when you specify this
linker option.[1]

   -shared-libgcc
   -static-libgcc
   On systems that provide libgcc as a shared library, these
options force the use of either the shared or static version,
respectively.  If no shared version
   of libgcc was built when the compiler was configured, these
options have no effect.

   There are several situations in which an application should use
the shared libgcc instead of the static version.  The most common of these
is when the
   application wishes to throw and catch exceptions across
different shared libraries.  In that case, each of the libraries as well as
the application itself
   should use the shared libgcc.

   Therefore, the G++ and GCJ drivers automatically add
-shared-libgcc whenever you build a shared library or a main executable,
because C++ and Java programs
   typically use exceptions, so this is the right thing to do.

--
I have a side question for you, if you can answer.
How did Omilia implement their speaker independent speech recognition
corpus?  Was it licensed from elsewhere or developed in house?

Peter



On Wed, Feb 14, 2018 at 8:27 AM, Petros Marinos  wrote:

> Thank you Arjen and Simon for your answers, really helpful!
>
> While there was progress by following the two commands noted in Arjen’s
> answer and creating the libsqlite.a file, I stumbled upon the following
> errors:
>
> [LD] astdb2sqlite3.o db1-ast/libdb1.a -> astdb2sqlite3
> /usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libpthread.a(libpthread.o):
> In function `__libc_sigaction':
> (.text+0x89f0): multiple definition of `__libc_sigaction'
> /usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/
> libc.a(sigaction.o):(.text+0x20): first defined here
> /usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libpthread.a(libpthread.o):
> In function `__libc_fsync':
> (.text+0x8100): multiple definition of `__libc_fsync'
> /usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libc.a(fsync.o):(.text+0x0):
> first defined here
> /usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libpthread.a(libpthread.o):
> In function `__connect_nocancel':
> (.text+0x7dc9): multiple definition of `__connect_nocancel'
> /usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libc.a(connect.o):(.text+0x9):
> first defined here
> /usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libpthread.a(libpthread.o):
> In function `__libc_fcntl':
> (.text+0x7c40): multiple definition of `__libc_fcntl'
> /usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libc.a(fcntl.o):(.text+0xa0):
> first defined here
> /usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libpthread.a(libpthread.o):
> In function `__fsync_nocancel':
> (.text+0x8109): multiple definition of `__fsync_nocancel'
> /usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libc.a(fsync.o):(.text+0x9):
> first defined here
> /usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libpthread.a(libpthread.o):
> In function `_IO_funlockfile':
> (.text+0x8990): multiple definition of `_IO_funlockfile'
> /usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/
> libc.a(funlockfile.o):(.text+0x0): first defined here
> /usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libpthread.a(libpthread.o):
> In function `__libc_nanosleep':
> (.text+0x8220): multiple definition of `__libc_nanosleep'
> /usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/
> libc.a(nanosleep.o):(.text+0x0): first defined here
> /usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libpthread.a(libpthread.o):
> In function `__read':
> (.text+0x7ae0): multiple definition of `__libc_read'
> /usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libc.a(read.o):(.text+0x0):
> first defined here
> /usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libpthread.a(libpthread.o):
> In function `__open_nocancel':
> (.text+0x8289): multiple definition of `__open_nocancel'
> /usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libc.a(open.o):(.text+0x9):
> first defined here
> /usr/lib/gcc/x86_64-redhat-linux/4.8.5/../../../../lib64/libpthread.a(libpthread.o):
> In function `__lseek_nocancel':
> (.text+0x8169): multiple definition of 

Re: [sqlite] How to parameterize a loadable extension at runtime

[petern]

Simon has the correct idea.   If you have a function x(), you are free to
define another in the same extension called function x_config().
This x_config() function is free to change global runtime preference
variables of the x() function based on the  passed into the last call
of x_config().   SQLITE_DETERMINISTIC is merely a performance hint for
expression evaluation within a single statement.  Deterministic functions
may be called multiple times anyway and every distinct statement where
output depends on a function, deterministic or not, will cause that
function to be evaluated at least once.

The only problem will be if a thread in your process calls the x_config()
function while the x() function has a different thread context.  If
overlapping multithreaded usage is anticipated, global configuration
variable access must be serialized by the sqlite3 mutex API or other
critical section mechanism.

Peter

On Tue, Feb 6, 2018 at 1:24 AM, Ulrich Telle  wrote:

> > Simon Slavin wrote:
> >
> > On 6 Feb 2018, at 8:33am, Ulrich Telle wrote:
> >
> > > Another possibility would be to add a user-defined function for the
> > > configuration of the extension that could be called from a SELECT
> > > statement:
> > >
> > > SELECT myextension_config('param-name', 'param-value');
> >
> > I've seen this done before.  Of course it means that your normal
> function is not deterministic, so you may no longer use
> SQLITE_DETERMINISTIC .  This is in contrast to a function where parameters
> are set during compilation.
>
> Well, actually my goal is not to have an extension with non-deterministic
> functions. The parameters have mostly the purpose to initialize the
> extension (things similar to what you do to SQLite itself with pragmas like
> "PRAGMA cache_size", or "PRAGMA data_store_directory"). The extension would
> accept changes to the parameters only before the first invocation of the
> extension functions.
>
> Regards,
>
> Ulrich
> ___
> 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] sqlite3_set_last_insert_rowid

[petern]

For one, within a function that does an INSERT, set_last_insert_rowid makes
it possible to "pop" last_insert_rowid.
Consider a function which INSERT's into the model and then INSERT's a log
table row.  The caller probably isn't interested in the log rowid.

A better question to ask is always why an orthogonal or inverse operation
is missing rather than why it is present.
https://en.wikipedia.org/wiki/Orthogonality_(programming)

Peter

On Tue, Jan 30, 2018 at 7:03 AM, Bart Smissaert 
wrote:

> Just wonder what the possible use for this is.
>
> RBS
> ___
> 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] regression since 3.20.0

[petern]

Confirmed.  3.22 build with -DSQLITE_ENABLE_STAT4 remarkably produces the
other answer:
cdid
1
4
5
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] regression since 3.20.0

[petern]

Kenichi. Nice report.  I pasted your code into my console and do see the
correct output you expected:

cdid
4
5

sqlite> .version
SQLite 3.22.0 2018-01-22 18:45:57
0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2alt1
zlib version 1.2.8
gcc-4.8.4

Maybe others can try it on their consoles?
Peter

On Fri, Jan 26, 2018 at 7:18 PM, Kenichi Ishigaki 
wrote:

> Hi,
>
> I've got a regression report from a DBIx::Class perl module maintainer
> that recent SQLite (3.20.0 and onward) returns a different result from
> the previous versions.
>
> https://rt.cpan.org/Public/Bug/Display.html?id=124227
>
> Condition:
>
> CREATE TABLE cd ( cdid INTEGER PRIMARY KEY NOT NULL, genreid integer );
> CREATE INDEX cd_idx_genreid ON cd (genreid);
> INSERT INTO cd  ( cdid, genreid ) VALUES
>( 1,1 ),
>( 2, NULL ),
>( 3, NULL ),
>( 4, NULL ),
>( 5, NULL );
>
> SQL in question:
>
> SELECT cdid
>   FROM cd me
> WHERE 2 > (
>   SELECT COUNT( * )
> FROM cd rownum__emulation
>   WHERE
> (
>   me.genreid IS NOT NULL
> AND
>   rownum__emulation.genreid IS NULL
> )
>   OR
> (
>   me.genreid IS NOT NULL
> AND
>   rownum__emulation.genreid IS NOT NULL
> AND
>   rownum__emulation.genreid < me.genreid
> )
>   OR
> (
>   ( me.genreid = rownum__emulation.genreid OR ( me.genreid IS NULL
> AND rownum__emulation.genreid IS NULL ) )
> AND
>   rownum__emulation.cdid > me.cdid
> )
> )
>
> Expected Result (3.19.3 and prior):
>
> 4, 5
>
> Current Result (3.20.0 and onward)
>
> 1, 4, 5
>
> If cd_idx_genreid index is not created, SQLite 3.20.0 and onward also
> return the expected one.
>
> Best regards,
>
> Kenichi Ishigaki
> ___
> 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] UTF8 and NUL

[petern]

Maybe you'll still miss my point if I don't explain about this.  I
understand you're talking about UTF8.  However,
you are free to define a user defined function which converts whatever
literal arguments into BLOB space and back again.

eg:  D3KRUTF8() -> TYPED POINTER (BLOB)

where TYPED POINTER is the D3KRUTF8 secret sauce UTF8 encoding that
supports NULs and anything else.

In the D3KRUTF8 BLOB space, any operation whatsoever is possible. Moreover,
queries are free to pass around the D3KRUTF8 BLOB to a nucleus of other
functions which provide a parallel implementation to the familar SUBSTR(),
LIKE(), LENGTH() SQL functions.  What's missing is the ability to overload
the punctuation operators like "||" and "=".

Richard,  why can't UDF's overload '||' (concat()) and '=" (equals())  ?

Peter



On Fri, Jan 26, 2018 at 7:09 PM, petern <peter.nichvolo...@gmail.com> wrote:

> BLOB will store UTF8 or any encoding for that matter.   Are you familiar
> with the general concept of operator overloading?
> https://en.wikipedia.org/wiki/Operator_overloading
>
> Arbitrary BLOB types including unterminated strings could be supported as
> first class object through user defined functions and more universal
> operator overloading.  Although they are presently crippled, user named
> BLOB types are already supported.
>
> https://www.sqlite.org/bindptr.html
> https://www.sqlite.org/c3ref/value_subtype.html
>
> For an example of overloading see the LIKE operator:[from
> https://sqlite.org/lang_expr.html]
> -
>  The sqlite3_create_function()
> <https://sqlite.org/c3ref/create_function.html> interface can be used to
> override the like() function and thereby change the operation of the LIKE
> <https://sqlite.org/lang_expr.html#like> operator. When overriding the
> like() function, it may be important to override both the two and three
> argument versions of the like() function. Otherwise, different code may be
> called to implement the LIKE <https://sqlite.org/lang_expr.html#like>
> operator depending on whether or not an ESCAPE clause was specified.
> 
>
> BTW, a question for anyone familiar with it.  If one overloads the like()
> function, how can one call the default implementation from the overloaded
> function?
> "like()" isn't an API export, and calling exec() on the same DB handle
> will only reenter the user defined like().
> If calling the default implementation from the overloaded one is
> impossible, I'd say the LIKE overload system has a bug.
>
>
> Peter
>
>
>
>
>
>
>
>
>
> On Fri, Jan 26, 2018 at 5:42 PM, J Decker <d3c...@gmail.com> wrote:
>
>> char inserts two chars for these... so it's hard to generate a sequence
>> that looks like '1' for length function... inserting a C string that was
>> 'hi\xc2\x93\x93\x93\x93\x93\x93' length would be 3.
>> 'hi\x93\x93\x93\x93\x93\x93' length would be 9.
>>
>> but without bind... this is as much as I can show.
>>
>> insert into test (a) values
>> ('hi'||char(192)||char(150)||char(150)||char(150)||char(150)||'there' );
>> insert into test (a) values ('hi'||char(0)||'there' );
>> select length(a),a from test;
>> (output to windows terminal)
>> 12|hiÀ––––there
>> 2|hi
>>
>> (in notepad++, )
>> 12|hiÀthere
>> 2|hi
>>
>> (although that is what I would expect.  I wouldn't suggest changing any of
>> that, well ya, ...)
>>
>> 2|hi'||char(0)||'there
>>
>> would look better - but in the select output context there aren't
>> quotes although that does work to preserve data for sqlite backup.
>>
>> On Fri, Jan 26, 2018 at 5:22 PM, petern <peter.nichvolo...@gmail.com>
>> wrote:
>>
>> > That's an interesting idea, using BLOBs.  BLOB strings would be more
>> > practical if common SQL scalar operators { || , LIKE, =, <>,...} could
>> be
>> > overloaded with user definable BLOB specific implementations.  At the
>> same
>> > time subtype and pointer type would have to be improved to work in all
>> > cases.  This would be far more general solution than messing up the API
>> > with ubiquitous length argument.
>> >
>> > FYI, here are some current very serious deficiencies with BLOB type
>> system
>> > including detailed test programs:
>> >
>>
>> Blob also is binary, and I'm not dealing with binary, i'm dealing with
>> UTF8
>> Text.  It is a totally different sort of thing than a BLOB would be.
>>
>>
>> >
>> > http://sqlite.1065341.n5.nabble.com/sqlite3-value-
>> > pointer-metadata-is-also-stripped-by-

Re: [sqlite] UTF8 and NUL

[petern]

BLOB will store UTF8 or any encoding for that matter.   Are you familiar
with the general concept of operator overloading?
https://en.wikipedia.org/wiki/Operator_overloading

Arbitrary BLOB types including unterminated strings could be supported as
first class object through user defined functions and more universal
operator overloading.  Although they are presently crippled, user named
BLOB types are already supported.

https://www.sqlite.org/bindptr.html
https://www.sqlite.org/c3ref/value_subtype.html

For an example of overloading see the LIKE operator:[from
https://sqlite.org/lang_expr.html]
-
 The sqlite3_create_function()
<https://sqlite.org/c3ref/create_function.html> interface can be used to
override the like() function and thereby change the operation of the LIKE
<https://sqlite.org/lang_expr.html#like> operator. When overriding the
like() function, it may be important to override both the two and three
argument versions of the like() function. Otherwise, different code may be
called to implement the LIKE <https://sqlite.org/lang_expr.html#like>
operator depending on whether or not an ESCAPE clause was specified.


BTW, a question for anyone familiar with it.  If one overloads the like()
function, how can one call the default implementation from the overloaded
function?
"like()" isn't an API export, and calling exec() on the same DB handle will
only reenter the user defined like().
If calling the default implementation from the overloaded one is
impossible, I'd say the LIKE overload system has a bug.


Peter









On Fri, Jan 26, 2018 at 5:42 PM, J Decker <d3c...@gmail.com> wrote:

> char inserts two chars for these... so it's hard to generate a sequence
> that looks like '1' for length function... inserting a C string that was
> 'hi\xc2\x93\x93\x93\x93\x93\x93' length would be 3.
> 'hi\x93\x93\x93\x93\x93\x93' length would be 9.
>
> but without bind... this is as much as I can show.
>
> insert into test (a) values
> ('hi'||char(192)||char(150)||char(150)||char(150)||char(150)||'there' );
> insert into test (a) values ('hi'||char(0)||'there' );
> select length(a),a from test;
> (output to windows terminal)
> 12|hiÀ––––there
> 2|hi
>
> (in notepad++, )
> 12|hiÀthere
> 2|hi
>
> (although that is what I would expect.  I wouldn't suggest changing any of
> that, well ya, ...)
>
> 2|hi'||char(0)||'there
>
> would look better - but in the select output context there aren't
> quotes although that does work to preserve data for sqlite backup.
>
> On Fri, Jan 26, 2018 at 5:22 PM, petern <peter.nichvolo...@gmail.com>
> wrote:
>
> > That's an interesting idea, using BLOBs.  BLOB strings would be more
> > practical if common SQL scalar operators { || , LIKE, =, <>,...} could be
> > overloaded with user definable BLOB specific implementations.  At the
> same
> > time subtype and pointer type would have to be improved to work in all
> > cases.  This would be far more general solution than messing up the API
> > with ubiquitous length argument.
> >
> > FYI, here are some current very serious deficiencies with BLOB type
> system
> > including detailed test programs:
> >
>
> Blob also is binary, and I'm not dealing with binary, i'm dealing with UTF8
> Text.  It is a totally different sort of thing than a BLOB would be.
>
>
> >
> > http://sqlite.1065341.n5.nabble.com/sqlite3-value-
> > pointer-metadata-is-also-stripped-by-trivial-cross-
> > join-Defect-tt10.html
> >
> > http://sqlite.1065341.n5.nabble.com/Defect-trivial-
> cross-join-strips-BLOB-
> > subtype-tt99982.html
> >
> > Still waiting for an answer about why BLOB types are lost in a trivial
> > cross join.  No answer.
> >
>
> That's interesting; I have seen, in the shell, that if the column is blob,
> it is just not shown.
>
>
> >
> > Is there a legitimate reason why BLOB type information cannot be passed
> > through a join?
> >
> >
>
>
>
> > Peter
> >
> > On Fri, Jan 26, 2018 at 4:36 PM, Keith Medcalf <kmedc...@dessus.com>
> > wrote:
> >
> > >
> > > I do not understand this at all.
> > >
> > > If the definition of a C-String is a "bunch-a-non-zero-byes-
> > terminated-by-a-zero-byte",
> > > then how is it possible to have a zero/null byte "embedded" within a
> > > C-Style String?
> > >
> > > Similarly, if a C-Style-Wide-String is defined as a
> > > "bunch-a-non-zero-words-terminated-by-a-zero-word", then how is it
> > > possible to have a zero/null word "embedded" within a
> > C-Style-Wide-String?
> > &g

Re: [sqlite] UTF8 and NUL

[petern]

That's an interesting idea, using BLOBs.  BLOB strings would be more
practical if common SQL scalar operators { || , LIKE, =, <>,...} could be
overloaded with user definable BLOB specific implementations.  At the same
time subtype and pointer type would have to be improved to work in all
cases.  This would be far more general solution than messing up the API
with ubiquitous length argument.

FYI, here are some current very serious deficiencies with BLOB type system
including detailed test programs:

http://sqlite.1065341.n5.nabble.com/sqlite3-value-pointer-metadata-is-also-stripped-by-trivial-cross-join-Defect-tt10.html

http://sqlite.1065341.n5.nabble.com/Defect-trivial-cross-join-strips-BLOB-subtype-tt99982.html

Still waiting for an answer about why BLOB types are lost in a trivial
cross join.  No answer.

Is there a legitimate reason why BLOB type information cannot be passed
through a join?

Peter

On Fri, Jan 26, 2018 at 4:36 PM, Keith Medcalf  wrote:

>
> I do not understand this at all.
>
> If the definition of a C-String is a 
> "bunch-a-non-zero-byes-terminated-by-a-zero-byte",
> then how is it possible to have a zero/null byte "embedded" within a
> C-Style String?
>
> Similarly, if a C-Style-Wide-String is defined as a
> "bunch-a-non-zero-words-terminated-by-a-zero-word", then how is it
> possible to have a zero/null word "embedded" within a C-Style-Wide-String?
>
> Given that SQLite3 is written in C and uses C-Strings or
> C-Style-Wide-Strings, then you cannot have zero/null bytes embedded in
> those strings.
>
> You may of course argue that perhaps SQLite3 should use something other
> than C-Style-Strings, however, this is not what seems to be proposed.  It
> seems to be proposing the use of some magical C-Style-String that is not
> actually a C-Style-String, without explicitly stating this.
>
> SQLite3 does handle non-C-Ctyle-Strings.  They are called "blobs".
>
> ---
> 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 J Decker
> >Sent: Friday, 26 January, 2018 17:18
> >To: SQLite mailing list
> >Subject: Re: [sqlite] UTF8 and NUL
> >
> >On Fri, Jan 26, 2018 at 3:56 PM, Peter Da Silva <
> >peter.dasi...@flightaware.com> wrote:
> >
> >> On 2018-01-26, at 17:05, J Decker  wrote:
> >> > On Fri, Jan 26, 2018 at 1:21 PM, Peter Da Silva <
> >> > peter.dasi...@flightaware.com> wrote:
> >> >> Sqlite uses NUL as the string terminator internally, the
> >published API
> >> >> specifies has stuff like this all over the place:
> >>
> >> >>> In those routines that have a fourth argument, its value is the
> >number
> >> of bytes in the parameter. To be clear: the value is the number of
> >bytes in
> >> the value, not the number of characters. If the fourth parameter to
> >> sqlite3_bind_text() or sqlite3_bind_text16() is negative, then the
> >length
> >> of the string is the number of bytes UP TO THE FIRST ZERO
> >TERMINATOR.
> >>
> >> > You stressed the wrong part there - *IS NEGATIVE*
> >>
> >> Why? Passing -1 as the length is a common way to tell sqlite3 to
> >calculate
> >> the length itself. It's a documented and widely used part of the
> >API.
> >
> >
> >Exactly, so on neither side, input or output is there a problem
> >storing a
> >length of valid characters.
> >The deficiency is 1) the command line tool for diagnostics
> >2) always scanning for a nul in prepare() unless the length is before
> >that.  It's simple to add an option that could change that behavior;
> >or
> >move the string measuring up to prepare[_v2,_v3,_v4] and even add a
> >V5 that
> >just passes the length passed without a scan.
> >
> >The input is read by a tokenizer that returns in-buffer references to
> >the
> >next SQL token by length.
> >Some tokens can be quoted, and those end up being a copy of the
> >original;
> >but the length of the SQL statement should already be known, so it
> >doesn't
> >need to scan for 0.
> >
> >Once tokenized it's converted into expressions; those expressions
> >(have
> >previously) stored only the char*.  It's not a lot of places to
> >change to
> >include storing the length; which is often known unless the mprintf
> >internals are used; then any token passed through that does not pass
> >%s.
> >So %s cannot be used for UTF8 strings; but rather the literal string
> >fwrite( buf, 1, stringlen,  >treatment as the file was opened with (O_BINARY or not, "b" or "t"
> >specifiers for fopen, or stderr ).
> >
> >fprintf( out, "%s", (string) );
> >is exactly the same as
> >fwrite( out, 1, strlen( string ), string );
> >
> >(Can anyone dispute that?  I doubt that's specified)
> >
> >Other than, the fwrite will include outputing the NUL character and
> >trust
> >the length given to it. \n will still get promoted to \r\n depending
> 

Re: [sqlite] Atoi64 bug(s)

[petern]

Cezary.  Your short form fix for the spurious NUMERIC CAST due to trailing
space was definitely received in your original posting.

I am interested to see your solution where NUMERIC CAST has sensible
interpretation between MAX_INT and MAX_REAL.
IMO, simple INT saturation is not convenient for easy overflow detection in
SQL.   So there is work to be done where the upcasted number is large but
not quite large enough for REAL saturation. Nearby upcasted INTs must sort
sensibly.

Also, what happens to overflowing hex constants and from BLOB casts?

It is important to curate such patches in case the priority for execution
speed/size cannot be reconciled with accuracy and generality.  If your
improvements make v3.23 slower or larger than v3.22, they may be rejected.
Nevertheless, I think users who prioritize dependability, accuracy, and
generality over slightly degraded executable speed/size will be very
interested to have your long form improvements.

Best regards.
Peter

On Thu, Jan 25, 2018 at 3:15 PM, Cezary H. Noweta <c...@poczta.onet.pl>
wrote:

> Hello,
>
> On 2018-01-25 22:58, petern wrote:
>
>> Thank you for expanding on your detailed observations.
>> If you can, please post the long patch at your customary patch site
>> http://sqlite.chncc.eu/
>>
> I was convinced that I had publicized my patch already. For the people who
> are interested in the patch, please give me a few hours to cut my new
> not-so-completely implemented functionalities from my draft version.
>
> -- best regards
>
> Cezary H. Noweta
> ___
> 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] Atoi64 bug(s)

[petern]

Thank you for expanding on your detailed observations.
If you can, please post the long patch at your customary patch site
http://sqlite.chncc.eu/
Also, in the link text, please make note of the exact SQLite version the
patch is for.

The lesson I draw is that unconditionally correct queries must check the
input type for all id range and offset computations.
So unfortunately, if the query depends on id input, it must be wrapped by
inefficient CASE typeof() protection statements.
On the bright side, INT overflow is at least detectable:

.mode line
WITH id AS (SELECT (0x7FFF)id)  SELECT
id,typeof(id),id+1,typeof(id+1) FROM id;
-- id = 9223372036854775807
-- typeof(id) = integer
-- id+1 = 9.22337203685478e+18
-- typeof(id+1) = real

Peter

On Thu, Jan 25, 2018 at 12:36 PM, Cezary H. Noweta <c...@poczta.onet.pl>
wrote:

> Hello,
>
> On 2018-01-25 19:54, petern wrote:
>
>> CREATE TABLE IF NOT EXISTS a (a INTEGER);
>>
>
> INTEGER == NUMERIC in case of column declarations.
>
> -- Note however, the constant table expression works fine...
>>
>> SELECT CAST(column1 AS INTEGER) FROM (VALUES
>> ('901'),('901 '));
>> -- "CAST(column1 AS INTEGER)"
>> -- 901
>> -- 901
>>
>
> This is due a fact that ``CAST AS INTEGER'' blindly calls ``Atoi64()'' and
> returns its result.
>
> INTEGER/FLOAT handling/recognition is a bit more complicated in other
> places, what causes that '901 ' will become 9e18.
>
> For the same reason ``CAST ('901X' AS INTEGER)'' gives INT
> 901, while ``SELECT CAST ('901X' AS
> NUMERIC);'' gives FLOAT 9e18.
>
> Due to a bit disordered treatment of values, my own patch involves many
> changes. The ``concise'' patch, which I proposed in my original post,
> eliminates: (1) (mod 2^64) bug, and (2) an erratic treatment of INTs and
> FLOATs in some (not all) places. It changes only one line and adds one, and
> does not change affinity/type system at all. (As opposed to my ``long''
> version patch).
>
> 1. IMHO (mod 2^64) bug is serious. Speculative example: my app has a
> dangerous number ``1234'' and is checking input text against it; then
> ``295147905179352827090'' -- OK, go on -- says my app; then suddenly
> ``CAST('295147905179352827090' AS INTEGER)'' ==> BOOM: 1234.
>
> 2. STRING to be INT must be reacting to ``attention'' command immediately
> (even small, chaste space at the end will bother); to be FLOAT, it can
> carousel from dusk till next dusk all the time.
>
> There was no noticeable side effects (besides a performance) in old 32bit
> INT days, however now, 10 bits of each INT can be going to a vacuum.
>
> -- best regards
>
> Cezary H. Noweta
> ___
> 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] Atoi64 bug(s)

[petern]

Confirmed.
sqlite> .version
SQLite 3.22.0 2018-01-22 18:45:57
0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2alt1
zlib version 1.2.8
gcc-4.8.4

--FYI some background:
--

--min 64b signed int:
SELECT CAST(0x8000 AS INTEGER);
-- "CAST(0x8000 AS INTEGER)"
-- -9223372036854775808

--max 64b signed int:
SELECT CAST(0x7FFF AS INTEGER);
-- "CAST(0x7FFF AS INTEGER)"
-- 9223372036854775807

-- Note how place counts align with numeric example:

-- 9223372036854775807
-- 901

-- I reproduce problem with spurious CAST due to trailing zero as follows:
---

CREATE TABLE IF NOT EXISTS a (a INTEGER);
INSERT INTO a VALUES ('901'),('901 ');
SELECT * FROM a;
-- a
-- 901
-- 900

-- Note however, the constant table expression works fine...

SELECT CAST(column1 AS INTEGER) FROM (VALUES
('901'),('901 '));
-- "CAST(column1 AS INTEGER)"
-- 901
-- 901

---
Peter














On Thu, Jan 25, 2018 at 9:14 AM, Stephen Chrzanowski 
wrote:

> RE the first question, the largest positive 64-bit number is
> 18446744073709551615, or, 18,446,744,073,709,551,615.  Your number is
> overloading that value.
> 12,345,678,901,234,567,890,123
> vs
> 18,446,744,073,709,551,615
>
> AFAIK, SQLite uses max of 64-bit integer math, not 128.
>
>
> On Thu, Jan 25, 2018 at 10:04 AM, Cezary H. Noweta 
> wrote:
>
> > Hello,
> >
> > About year age I reported some strange behavior:
> >
> > 1.
> >
> > https://www.sqlite.org/lang_expr.html#castexpr:
> >
> > INTEGER: ``When casting a TEXT value to INTEGER, the longest possible
> > prefix of the value that can be interpreted as an integer number is
> > extracted from the TEXT value and the remainder ignored. [...] If there
> is
> > no prefix that can be interpreted as an integer number, the result of the
> > conversion is 0.''
> >
> > sqlite> SELECT CAST('12345678901234567890123' AS INTEGER);
> > 4807115922877859019
> >
> > What prefix of ``12345678901234567890123'' can be interpreted as
> > ``4807115922877859019''?
> >
> > 2.
> >
> > Why trailing spaces are allowed in FLOATs and not in INTEGERs? It would
> > not be a problem, if INTs were 32bit. Now, a value is lost:
> >
> > sqlite> CREATE TABLE a (a NUMERIC);
> > sqlite> INSERT INTO a VALUES ('901'),('
> 901
> > ');
> > sqlite> SELECT * FROM a;
> > 901
> > 900
> >
> > 2a.
> >
> > https://www.sqlite.org/lang_expr.html#castexpr:
> >
> > NUMERIC: ``Casting a TEXT or BLOB value into NUMERIC first does a forced
> > conversion into REAL but then further converts the result into INTEGER if
> > and only if the conversion from REAL to INTEGER is lossless and
> > reversible.''
> >
> > Why a cast to universal AFFINITY (NUMERIC -- designed to represent both
> > INTEGERs as well as FLOATs) could be skewed into FLOAT direction loosing
> a
> > INTEGER's LSBits? INT is not 32bit wide. Hopefully above is not true:
> >
> > sqlite> SELECT CAST('901' AS NUMERIC);
> > 901
> >
> > However due to pt 2.:
> >
> > sqlite> SELECT CAST('901 ' AS NUMERIC);
> > 900
> >
> > The most concise patch (without, for example ``SELECT
> > CAST('901X' AS NUMERIC);'') contains only two lines:
> > ==
> > --- sqlite-src-322/src/util.c   2018-01-23 01:57:26.0
> +0100
> > +++ sqlite-src-322/src/util.c   2018-01-25 14:22:18.428460300
> +0100
> > @@ -625,6 +625,7 @@
> >  zNum += (enc&1);
> >}
> >while( zNum > +  while( zNum >if( zNum >  if( *zNum=='-' ){
> >neg = 1;
> > @@ -638,7 +639,7 @@
> >for(i=0; [i]='0' && c<='9'; i+=incr){
> >  u = u*10 + c - '0';
> >}
> > -  if( u>LARGEST_INT64 ){
> > +  if( 19 < i || u>LARGEST_INT64 ){
> >  *pNum = neg ? SMALLEST_INT64 : LARGEST_INT64;
> >}else if( neg ){
> >  *pNum = -(i64)u;
> > ==
> >
> > -- best regards
> >
> > Cezary H. Noweta
> > ___
> > 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

[sqlite] SQLite DELUXE Re: sqlite command line tool NUL support

[petern]

Have you worked out an automated way for your changes to shadow and
auto-merge from the official trunk?  That is, aside from collision edits,
is it automated?  Longer term, your shadow distribution also needs
new/merged test cases and a regression test run to re-qualify the merged
changes at each cycle.

It seems to me there are many small usability changes which could be
incorporated into a more comprehensive SQLite shadow release,  call it
"SQLite DELUXE".  For example, here is another straightforward and useful
change by Cezary that was never adopted despite having zero impact by
conditional compilation switch:  http://sqlite.chncc.eu/

The argument against improving generality of the official release because
it slows down some phones will never go away.  Your changes may have to
shadow the official release forever.

Peter

On Wed, Jan 24, 2018 at 11:46 AM, J Decker  wrote:

> This is a picture.  This is a tortoise git log view of merges.
>
> https://drive.google.com/open?id=1RosGlprJRoutFsou2XDRlflxc8xWoHks
>
> On Wed, Jan 24, 2018 at 8:59 AM, J Decker  wrote:
>
> >
> > So for output of a select in the shell ( unlike .dump ) is this.
> >
> > for(i=1; i >   utf8_printf(p->out, ",%s", sqlite3_column_text(pSelect, i));
> > }
> >
> > option 1) add text conversion in the column_text to escape NUL
> characters.
> > in TEXT... then everyone everywhere would inherit the change.
> > 'asdf' is as valid as ''||'a'||'s'||'d'||'f'||''
> >
> > ( the postgres/sqlite way is to use  "\'||CHAR(0)||\'" (as a C string) if
> > '\0' is required outside of a bind )
> >
> > 2) output literal characters regardless of terminal...
> > //utf8_printf(p->out, "%s", z);
> > raw_fwrite( z, 1, sqlite3_column_bytes(pSelect, 0), p->out);
> >  for(i=1; i >   //utf8_printf(p->out, ",%s", sqlite3_column_text(pSelect, i));
> >   raw_fputc(p->out, ',');
> >   raw_fwrite( sqlite3_column_text(pSelect, i), 1,
> >   sqlite3_column_bytes(pSelect, 0), p->out);
> > }
> >
> > 3)
> >
> > .dump uses
> > output_quoted_string
> > output_quoted_escaped_string
> > (the latter of which will wrap "text\r\n" with
> > replace(replace("text\\r\\n" ),'\\r',char(13)),'\\n',char(10))
> > so it could additionally wrap that with
> >
> > the latter of which will wrap "u\0tf\r\n" with
> > replace(replace(replace("u\\0tf\\r\\n" ),'\\r',char(13)),'\\n',char(1
> > 0)),'\\0',char(0))
> >
> > instead of
> > replace(replace("u'||CHAR(0)||'tf\\r\\n" ),'\\r',char(13)),'\\n',char(
> 10))
> >
> >
> >
> >
> > --
> > * Change select * output to write full length text value
> > * Change .dump output to include supporting char(0) substitution ( !
> Shell
> > Newlines )
> > * Change other .dump output to inline subst '||CHAR(0)||'  (
> > ShellHasFlag(p, SHFLG_Newlines) )
> >
> > https://drive.google.com/open?id=1UXaFBRoNypZBhkpfPh7TAwncJMbBT1cH
> >
> >
> > Sample output (shell without newlines)
> > "d\n and" has a char \x14
> >
> > (shell only; doesn't change test1.c)
> >
> > SQLite version 3.23.0 2018-01-24 01:58:49
> > Enter ".help" for usage hints.
> > sqlite> .dump test
> > PRAGMA foreign_keys=OFF;
> > BEGIN TRANSACTION;
> > CREATE TABLE test (a);
> > INSERT INTO test VALUES(0);
> > INSERT INTO test VALUES(replace('TEST\0NUL\0','\0',char(0)));
> > INSERT INTO test VALUES(replace('TEST\0\0NUL\0','\0',char(0)));
> > INSERT INTO test VALUES(0);
> > INSERT INTO test VALUES(0);
> > INSERT INTO test VALUES(replace(replace(replace('char test\rand\n\0 and
> > all\0','\r',char(13)),'\n',char(10)),'\0',char(0)));
> > INSERT INTO test VALUES(replace(replace('char test\rand\n and
> > all(null)','\r',char(13)),'\n',char(10)));
> > INSERT INTO test VALUES(replace(replace('char test\rand\n and
> > all(null)','\r',char(13)),'\n',char(10)));
> > INSERT INTO test VALUES(replace(replace(replace('char test\rand\n\0 and
> > all 23\0','\r',char(13)),'\n',char(10)),'\0',char(0)));
> > INSERT INTO test VALUES(replace(replace(replace('char test\rand\n\0 and
> > all merged content\0','\r',char(13)),'\n',char(10)),'\0',char(0)));
> > COMMIT;
> > sqlite> select * from test
> >...> ;
> > 0
> > TEST NUL
> > TEST  NUL
> > 0
> > 0
> > andr test
> >   and all
> > andr test
> > and all
> > andr test
> > and all
> > andr test
> >   and all 23
> > andr test
> >   and all merged content
> > 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] Using SQLite internal recognizers eg: SQLITE_PRIVATE int sqlite3AtoF()

[petern]

I am drawing from parallel functionality in the existing API.Roughly,
the API sqlite3_buffer_numeric_type() would simply be the buffer input
version of the existing API sqlite3_value_numeric_type().  But instead of
operating on a sqlite3_value parameter, it would read from a pzBuffer
parameter (plus optional length and optional encoding) to return one of
SQLITE_FLOAT, SQLITE_INTEGER, or SQLITE_NULL by directly calling on the
internal recognizers.






On Tue, Jan 23, 2018 at 6:09 PM, Richard Hipp <d...@sqlite.org> wrote:

> On 1/23/18, petern <peter.nichvolo...@gmail.com> wrote:
> > Any chance of publishing a modest but hardened "int
> > sqlite3_numeric_buffer_type(const char*pBuffer,int length,int encoding)"
> > API that extensions can use?
>
> I'm not sure what "sqlite3_numeric_buffer_type()" is suppose to do?
> --
> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite internal recognizers eg: SQLITE_PRIVATE int sqlite3AtoF()

[petern]

Any chance of publishing a modest but hardened "int
sqlite3_numeric_buffer_type(const char*pBuffer,int length,int encoding)"
API that extensions can use?

On Tue, Jan 23, 2018 at 4:43 PM, Richard Hipp <d...@sqlite.org> wrote:

> On 1/23/18, petern <peter.nichvolo...@gmail.com> wrote:
> > What is the fastest forward compatible way to gain use of the internal
> > buffer value recognizers such as "SQLITE_PRIVATE int sqlite3AtoF()" in
> > external C programs?
> >
>
> There is no forwards-compatible way to do that.  We reserve the right
> to change the design and/or behavior of all internal interfaces at any
> time and for any reason.  And we do.  Usually there are some internal
> interface changes on every release.  With a quick glance, I count 10
> separate, incompatble changes in the 3.22.0 release, with no telling
> how many others I have overlooked.
>
> Furthermore, the internal interfaces are not hardened for general use
> and are not general purpose.  They will typically have quirks and
> caveats and corner-cases that need to be avoided.
> --
> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Using SQLite internal recognizers eg: SQLITE_PRIVATE int sqlite3AtoF()

[petern]

What is the fastest forward compatible way to gain use of the internal
buffer value recognizers such as "SQLITE_PRIVATE int sqlite3AtoF()" in
external C programs?

The goal is to efficiently compute exactly how SQLite would taxonomically
classify {numeric,float,integer,...} a buffer string value if it were used
in a statement.

Obviously the buffer under test could simply be composed into a "SELECT
typeof('$buffer')" statement and the result string read back from the db
exection step().  However prepare() + bind() + step() is slow compared to
directly calling natively compiled recognizer functions.

The computationally faster alternatives are (1) patch out SQLITE_PRIVATE on
the recognizer functions in a custom build to export them or (2)
copy/paste/fixup the recognizer function snippets into the external
compilation unit and hope they don't change too much.

What other alternatives are possible?

Peter
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in unique index

[petern]

The second UNIQUE(v2,v1) constraint is redundant and equivalent to
UNIQUE(v1,v2)

Also consider that {(1,2),(2,1)} has no duplicates:

sqlite> WITH test(v1,v2) AS (VALUES (1,2),(2,1)) SELECT DISTINCT * FROM
test;
v1,v2
1,2
2,1

Peter


On Tue, Jan 23, 2018 at 8:35 AM, Domingo Alvarez Duarte 
wrote:

> Hello !
>
> Maybe I found a bug in sqlite3 unique index, see example bellow:
>
> bug-unique.sql
>
> drop table if exists test;
> create table test(
> id integer primary key,
> v1 integer not null,
> v2 integer not null constraint not_equal check(v1 != v2),
> unique(v1, v2),
> unique(v2, v1)
> );
>
> insert into test values(1, 1, 2);
> insert into test values(2, 2, 1);
> select * from test;
>
> 
>
> sqlite3 < bug-unique.sql
>
> output
>
> 1|1|2
> 2|2|1
> 
>
> I was expecting to have an error trying to insert the second row but
> sqlite3 accepted the duplicated index without error.
>
> Cheers !
>
>
> ___
> 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] unexpected row value error

[petern]

Confirmed that way too.

CREATE TABLE x ( a, b, PRIMARY KEY (a, b) );
CREATE TABLE y ( a );

INSERT INTO x VALUES (1, 1), (1, 2);
INSERT INTO y VALUES (1);

SELECT * FROM x JOIN y ON y.a = x.a WHERE (x.a, x.b) IN (VALUES (1,2));
--Error: sub-select returns 2 columns - expected 1

SELECT * FROM x JOIN y ON y.a = x.a WHERE (x.a, x.b) IN ((VALUES (1,2)));
--a,b,a
--1,2,1

.version
--SQLite 3.22.0 2018-01-12 23:38:10
dec3ea4e4e6c4b1761ddc883a29eaa50dcd663ce6199667cc0ff82f7849d4f2a


On Tue, Jan 23, 2018 at 7:12 AM, curmudgeon  wrote:

> Probably won't help but the final one works with SELECT in double brackets
>
> SELECT * FROM x
> JOIN y ON y.a = x.a
> WHERE (x.a, x.b) IN ( ( SELECT a, b FROM z ) );
> .
>
>
>
>
> --
> Sent from: http://sqlite.1065341.n5.nabble.com/
> ___
> 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] unexpected row value error

[petern]

Confirmed.  SQLite 3.22.0 2018-01-12 23:38:10
dec3ea4e4e6c4b1761ddc883a29eaa50dcd663ce6199667cc0ff82f7849d4f2a

CREATE TABLE x ( a, b, PRIMARY KEY (a, b) );
CREATE TABLE y ( a );
CREATE TABLE z ( a, b );

INSERT INTO x VALUES (1, 1), (1, 2);
INSERT INTO y VALUES (1);
INSERT INTO z VALUES (1, 1), (1, 2);

SELECT * FROM x JOIN y ON y.a = x.a WHERE (x.a,2) IN (VALUES (1,2));
Error: sub-select returns 2 columns - expected 1

SELECT * FROM z JOIN y ON y.a = z.a WHERE (z.a,2) IN (VALUES (1,2));
a,b,a
1,1,1
1,2,1


On Tue, Jan 23, 2018 at 4:55 AM, Mark Brand  wrote:

> Hi,
>
> The 6th SELECT example below throws an error. This seems unexpected,
> especially given the contrast with example 3, which differs only in lacking
> a seemingly unrelated JOIN.  Am I overlooking something?
>
> Removing the PRIMARY KEY from table x also avoids the error somehow.
>
> Seen on version 3.22.0, and also on 3.19.3.
>
> Mark
>
> CREATE TABLE x ( a, b, PRIMARY KEY (a, b) );
> CREATE TABLE y ( a );
> CREATE TABLE z ( a, b );
>
> INSERT INTO x VALUES (1, 1), (1, 2);
> INSERT INTO y VALUES (1);
> INSERT INTO z VALUES (1, 1), (1, 2);
>
> SELECT sqlite_version();
>
> -- CASE 1: OK
> SELECT * FROM x
> WHERE (SELECT x.a, x.b) IN ( SELECT a, b FROM z );
>
> -- CASE 2: OK
> SELECT * FROM x
> WHERE (VALUES(x.a, x.b)) IN ( SELECT a, b FROM z );
>
> -- CASE 3: OK
> SELECT * FROM x
> WHERE (x.a, x.b) IN ( SELECT a, b FROM z );
>
> -- CASE 4: OK
> SELECT * FROM x
> JOIN y ON y.a = x.a
> WHERE (SELECT x.a, x.b) IN ( SELECT a, b FROM z );
>
> -- CASE 5: OK
> SELECT * FROM x
> JOIN y ON y.a = x.a
> WHERE (VALUES(x.a, x.b)) IN ( SELECT a, b FROM z );
>
> -- CASE 6: ERROR
> SELECT * FROM x
> JOIN y ON y.a = x.a
> WHERE (x.a, x.b) IN ( SELECT a, b FROM z );
>
> /*
> sqlite_version()
> 3.22.0
> a|b
> 1|1
> 1|2
> a|b
> 1|1
> 1|2
> a|b
> 1|1
> 1|2
> a|b|a
> 1|1|1
> 1|2|1
> a|b|a
> 1|1|1
> 1|2|1
> Error: near line 34: sub-select returns 2 columns - expected 1
> */
>
> ___
> 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] .DUMP displays floats differently from SELECT

[petern]

FYI. There's no need to pin back the whole db version just to get the old
style dbdump format.
The original dump is available from the distribution as a standalone
program here:

http://www.sqlite.org/src/artifact/819eb33f6ff788a4

--dbdump.c--

** If this file is compiled with -DDBDUMP_STANDALONE then a "main()" routine
** is included so that this routine becomes a command-line utility.  The
** command-line utility takes two or three arguments which are the name
** of the database file, the schema, and optionally the table

Compile and enjoy.
Peter


On Mon, Jan 22, 2018 at 11:46 AM, Tony Papadimitriou  wrote:

> I reported this same issue in May 2017 (http://mailinglists.sqlite.or
> g/cgi-bin/mailman/private/sqlite-users/2017-May/072714.html)
> I too consider this a problem (diffing dumps is one good reason why) but,
> unfortunately, this was an intentional change by this check-in:
>
> [7359fcac] Increase the number of significant digits in floating point
> literals on ".dump" output from the shell.
>
> Tony
> -Original Message- From: Iulian Onofrei
>
> This is clearly a bug, as it outputs incorrect and different output from
> the
> previous versions.
>
> I have a "REAL" column with float values having up to 2 decimals, and using
> ".dump" with the latest version incorrectly converts them like this:
>
> "0.05" -> "0.050002775"
>
> I rely on dumps to track changes to some databases, so this breaks it
> completely, and I currently had to pin sqlite to an older version which
> isn't desired.
>
> Thank you,
> iulianOnofrei
>
> ___
> 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] .DUMP displays floats differently from SELECT

[petern]

FYI.  There's no need to pin the whole db version back to get original
dbdump formatting.
The original distribution standalone dump is still available in its full
glory here:

http://www.sqlite.org/src/artifact/819eb33f6ff788a4

--dbdump.c--

** If this file is compiled with -DDBDUMP_STANDALONE then a "main()" routine
** is included so that this routine becomes a command-line utility.  The
** command-line utility takes two or three arguments which are the name
** of the database file, the schema, and optionally the table

Peter




On Mon, Jan 22, 2018 at 11:46 AM, Tony Papadimitriou  wrote:

> I reported this same issue in May 2017 (http://mailinglists.sqlite.or
> g/cgi-bin/mailman/private/sqlite-users/2017-May/072714.html)
> I too consider this a problem (diffing dumps is one good reason why) but,
> unfortunately, this was an intentional change by this check-in:
>
> [7359fcac] Increase the number of significant digits in floating point
> literals on ".dump" output from the shell.
>
> Tony
> -Original Message- From: Iulian Onofrei
>
> This is clearly a bug, as it outputs incorrect and different output from
> the
> previous versions.
>
> I have a "REAL" column with float values having up to 2 decimals, and using
> ".dump" with the latest version incorrectly converts them like this:
>
> "0.05" -> "0.050002775"
>
> I rely on dumps to track changes to some databases, so this breaks it
> completely, and I currently had to pin sqlite to an older version which
> isn't desired.
>
> Thank you,
> iulianOnofrei
>
> ___
> 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] Unexpected column scoping in GROUP BY produces wrong answer.

[petern]

OK.  The fact is still surprising considering the near column alias has
precedence in every other situation [including the bug fix for CREATE TABLE
... AS SELECT]

The SQLite documentation could use a sentence about how SQLite's enhanced
SQL GROUP BY name precedence works.

https://www.postgresql.org/docs/9.5/static/sql-select.html
"In case of ambiguity, a GROUP BY name will be interpreted as an
input-column name rather than an output-column name."

Peter

On Mon, Jan 22, 2018 at 3:07 AM, Dan Kennedy <danielk1...@gmail.com> wrote:

> On 01/21/2018 07:21 AM, petern wrote:
>
>> SQLite 3.22.0 2018-01-12 23:38:10
>> dec3ea4e4e6c4b1761ddc883a29eaa50dcd663ce6199667cc0ff82f7849d4f2a
>>
>> WITH t(j,k) AS (VALUES (2,4),(3,2),(3,8),(4,7)) SELECT max(j,k) AS j FROM
>> t
>> GROUP BY j;
>> j
>> 4
>> 8
>> 7
>> --Wrong answer.
>> --GROUP BY unexpectedly scopes outer source table column j rather than the
>> nearer local column alias j.
>>
>> WITH t(j,k) AS (VALUES (2,4),(3,2),(3,8),(4,7)) SELECT max(j,k) AS x FROM
>> t
>> GROUP BY x;
>> x
>> 3
>> 4
>> 7
>> 8
>> --Correct answer when the local column alias happens to be unique.
>>
>
> I think technically allowing an expression alias to be used in the GROUP
> BY clause like that is not standard SQL. It just has to be supported for
> backwards-compatibility. So SQLite tries to process the standard as regular
> SQL before falling back to interpreting the identifier as an alias.
>
> Dan.
>
>
> ___
> 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] Feature request for the Shell Tool: .mode json

[petern]

>Just the data returned by the SELECT command, expressed as an array of
objects, one object per row.

That's what shell_callback() does inside shell.c.  It outputs one row at a
time in the current mode selected by the cases of a big switch()
statement.  Not sure I follow how your code would be subject to other
intellectual property claims.  You would merely be copying and pasting code
from SQLite itself.  For one, below is the row output code for MODE_html in
shell.c.  Your proposed MODE_json is probably a similar pattern with
different decorations.   The other modes are also there to be studied and
copied.

   case MODE_Html: {
  if( p->cnt++==0 && p->showHeader ){
raw_printf(p->out,"");
for(i=0; iout,"");
  output_html_string(p->out, azCol[i]);
  raw_printf(p->out,"\n");
}
raw_printf(p->out,"\n");
  }
  if( azArg==0 ) break;
  raw_printf(p->out,"");
  for(i=0; iout,"");
output_html_string(p->out, azArg[i] ? azArg[i] : p->nullValue);
raw_printf(p->out,"\n");
  }
  raw_printf(p->out,"\n");
  break;
}

Just copy, edit, compile and test until it produces sensible output you can
demonstrate in an email.   Incorporating relevant features from MySQL JSON
output mode documentation that others will miss is also a good idea
considering the entire json1.c API model was also cloned from MySQL.

The only thing not to like about it would be if your design decisions make
something that isn't generally useful.   Not a waste of time if other JSON
users are interested enough in your idea to provide feedback on the
features they need.  Most of the development work here is figuring out
'what' and 'why' rather than 'how'.  The 'how' is trivial.

Peter

On Sun, Jan 21, 2018 at 3:04 PM, Simon Slavin  wrote:

>
>
> On 21 Jan 2018, at 11:01pm, Simon Slavin  wrote:
>
> > Just the data that is stored in the table, expressed as a JSON object,
> not an array.
>
> Sorry, what I meant was
>
> Just the data returned by the SELECT command, expressed as an array of
> objects, one object per row.
>
> 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] Feature request for the Shell Tool: .mode json

[petern]

Simon.   You want something like MySQL but using SQLite's shallower column
type awareness?  Reference:

https://dev.mysql.com/doc/refman/5.7/en/mysql-shell-json-output.html

Would you include a header variable when headers are turned on? Column
types too?
There are a number of design choices to work out, but the new code and test
cases would be entirely isolated to shell.c

The output mode is just formatting and indentation changes and the input
mode can crib from json1.c input routine.
Have you considered writing a reference implementation for a new mode_json
case of shell_callback to work out a practical design?

The other possibility would be to upgrade json1.c to allow
update/insert/delete and readout/writeout of current table in JSON format.
Given the trouble with integrating SQLite shell into every situation, such
a readout mode for json1.c could have wider utility.  Others have run into
the same portability problem with the read only csv.c extension.

Peter





On Sun, Jan 21, 2018 at 11:54 AM, Simon Slavin  wrote:

>
>
> On 21 Jan 2018, at 6:56pm, Brian Curley  wrote:
>
> > In short, yes...you can get jq to convert both ways.
> >
> > It's not exactly as simple as just piping it through jq though, just to
> > reiterate my earlier self-correction.
>
> Hi, Brian.  Thanks for your detailed example which I read.  I can see that
> the tasks can be done by jq.  But I think the SQLite shell tool, which
> knows which key/column names to use, will do them faster and with far less
> work from the user.
>
> 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] Defect: Redundant CTE table materialization gives wrong answer.

[petern]

Besides PostrgreSQL, MySQL also says x is TABLE with exactly one row as x
UNION x = x

WITH x AS (SELECT rand()) SELECT * FROM x UNION SELECT * FROM x;
rand()
0.6710336931711377

Peter

On Sat, Jan 20, 2018 at 7:31 PM, Cory Nelson <phro...@gmail.com> wrote:

> CTEs are not as-if-memoized in SQL Server either. I can't find any
> standards language saying if they should or shouldn't be, which
> typically indicates "anything goes".
>
> On Sat, Jan 20, 2018 at 5:57 PM, petern <peter.nichvolo...@gmail.com>
> wrote:
> > Exactly.  But that doesn't resolve the problem of duplicated
> > non-deterministic tables in a CTE.
> > CTE is the acryonym for Common TABLE Expression, not Common View
> > Expression.
> >
> > eg:  WITH x AS ()
> >SELECT * FROM x UNION SELECT * FROM x; --Should return just x, not
> > x1 UNION x2.
> >
> > Peter
> >
> > On Sat, Jan 20, 2018 at 3:17 PM, Shane Dev <devshan...@gmail.com> wrote:
> >
> >> The following statement executes the random() function twice -
> >>
> >> sqlite> select random() union all select random();
> >> random()
> >> 2678358683566407062
> >> -5528866137931448843
> >> sqlite> explain select random() union all select random();
> >> addr  opcode p1p2p3p4 p5  comment
> >>   -        -  --  -
> >> 0 Init   0 1 000
> >> 1 Function0  0 0 1 random(0)  00
> >> 2 ResultRow  1 1 000
> >> 3 Function0  0 0 1 random(0)  00
> >> 4 ResultRow  1 1 000
> >> 5 Halt   0 0 000
> >>
> >> but if random() is replaced with round(1.1) -
> >>
> >> sqlite> select round(1.1) union all select round(1.1);
> >> round(1.1)
> >> 1.0
> >> 1.0
> >> sqlite> explain select round(1.1) union all select round(1.1);
> >> addr  opcode p1p2p3p4 p5  comment
> >>   -        -  --  -
> >> 0 Init   0 6 000
> >> 1 Copy   2 1 000
> >> 2 ResultRow  1 1 000
> >> 3 Copy   2 1 000
> >> 4 ResultRow  1 1 000
> >> 5 Halt   0 0 0    00
> >> 6 Real   0 3 0 1.100
> >> 7 Function0  1 3 2 round(1)   01
> >> 8 Goto   0 1 000
> >>
> >> It seems to be executed once only. Does this happen because random() is
> >> flagged non-deterministic?
> >>
> >>
> >> On 19 January 2018 at 09:10, Clemens Ladisch <clem...@ladisch.de>
> wrote:
> >>
> >> > petern wrote:
> >> > > WITH flips(s) AS (VALUES (random()>0), (random()>0), (random()>0))
> >> > > SELECT sum(s),(SELECT sum(s) FROM flips) FROM flips;
> >> > > sum(s),"(SELECT sum(s) FROM flips)"
> >> > > 1,3
> >> > > --Expected output is 1,1.
> >> > >
> >> > > Why isn't the constant notional table table [flips] materialized
> just
> >> > once
> >> > > per CTE?
> >> > >
> >> > > FYI.  PostgreSQL 9.6 materializes [flips] exactly once per CTE-users
> >> >
> >> > Its documentation says:
> >> > <https://www.postgresql.org/docs/9.6/static/queries-with.html>
> >> > | A useful property of WITH queries is that they are evaluated only
> once
> >> > | per execution of the parent query, even if they are referred to more
> >> > | than once by the parent query or sibling WITH queries. Thus,
> expensive
> >> > | calculations that are needed in multiple places can be placed within
> >> > | a WITH query to avoid redundant work. Another possible application
> is
> >> > | to prevent unwanted multiple evaluations of functions with side-
> >> > | effects. However, the other side of this coin is that the optimizer
> is
> >> > | less able to push restrictions from the parent query down into a
> WITH
> >> > | query than an ordinary

[sqlite] Unexpected column scoping in GROUP BY produces wrong answer.

[petern]

SQLite 3.22.0 2018-01-12 23:38:10
dec3ea4e4e6c4b1761ddc883a29eaa50dcd663ce6199667cc0ff82f7849d4f2a

WITH t(j,k) AS (VALUES (2,4),(3,2),(3,8),(4,7)) SELECT max(j,k) AS j FROM t
GROUP BY j;
j
4
8
7
--Wrong answer.
--GROUP BY unexpectedly scopes outer source table column j rather than the
nearer local column alias j.

WITH t(j,k) AS (VALUES (2,4),(3,2),(3,8),(4,7)) SELECT max(j,k) AS x FROM t
GROUP BY x;
x
3
4
7
8
--Correct answer when the local column alias happens to be unique.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Defect: Redundant CTE table materialization gives wrong answer.

[petern]

Exactly.  But that doesn't resolve the problem of duplicated
non-deterministic tables in a CTE.
CTE is the acryonym for Common TABLE Expression, not Common View
Expression.

eg:  WITH x AS ()
   SELECT * FROM x UNION SELECT * FROM x; --Should return just x, not
x1 UNION x2.

Peter

On Sat, Jan 20, 2018 at 3:17 PM, Shane Dev <devshan...@gmail.com> wrote:

> The following statement executes the random() function twice -
>
> sqlite> select random() union all select random();
> random()
> 2678358683566407062
> -5528866137931448843
> sqlite> explain select random() union all select random();
> addr  opcode p1p2p3p4 p5  comment
>   -        -  --  -
> 0 Init   0 1 000
> 1 Function0  0 0 1 random(0)  00
> 2 ResultRow  1 1 000
> 3 Function0  0 0 1 random(0)  00
> 4 ResultRow  1 1 000
> 5 Halt   0 0 000
>
> but if random() is replaced with round(1.1) -
>
> sqlite> select round(1.1) union all select round(1.1);
> round(1.1)
> 1.0
> 1.0
> sqlite> explain select round(1.1) union all select round(1.1);
> addr  opcode p1p2p3p4 p5  comment
>   -        -  --  -
> 0 Init   0 6 000
> 1 Copy   2 1 000
> 2 ResultRow  1 1 000
> 3 Copy   2 1 000
> 4 ResultRow  1 1 000
> 5 Halt   0 0 000
> 6 Real   0 3 0 1.100
> 7 Function0  1 3 2 round(1)   01
> 8 Goto   0 1 000
>
> It seems to be executed once only. Does this happen because random() is
> flagged non-deterministic?
>
>
> On 19 January 2018 at 09:10, Clemens Ladisch <clem...@ladisch.de> wrote:
>
> > petern wrote:
> > > WITH flips(s) AS (VALUES (random()>0), (random()>0), (random()>0))
> > > SELECT sum(s),(SELECT sum(s) FROM flips) FROM flips;
> > > sum(s),"(SELECT sum(s) FROM flips)"
> > > 1,3
> > > --Expected output is 1,1.
> > >
> > > Why isn't the constant notional table table [flips] materialized just
> > once
> > > per CTE?
> > >
> > > FYI.  PostgreSQL 9.6 materializes [flips] exactly once per CTE-users
> >
> > Its documentation says:
> > <https://www.postgresql.org/docs/9.6/static/queries-with.html>
> > | A useful property of WITH queries is that they are evaluated only once
> > | per execution of the parent query, even if they are referred to more
> > | than once by the parent query or sibling WITH queries. Thus, expensive
> > | calculations that are needed in multiple places can be placed within
> > | a WITH query to avoid redundant work. Another possible application is
> > | to prevent unwanted multiple evaluations of functions with side-
> > | effects. However, the other side of this coin is that the optimizer is
> > | less able to push restrictions from the parent query down into a WITH
> > | query than an ordinary subquery.
> >
> > This is an implementation detail of Postgres, and it is not required by
> > the SQL specification.  SQLite chose the other side of the coin.
> >
> >
> > Regards,
> > Clemens
> > ___
> > 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] Defect: Redundant CTE table materialization gives wrong answer.

[petern]

Another implementation detail?  I was wondering where you were, Clemens.
You're entitled to your opinion but I'd rather hear from somebody with
actual source check-in privileges.

I've posted at least 5 legitimate defects which triangulate on a
consistency problem when trying to access properties of the current
statement by any of CTE, cross join, aux_data, pointer, and subtype API's.

What response did I get?  Crickets - and then Clemens telling me I'm
imagining things because these are ALL implementation details.

Well, Clemens you are definitely wrong about the CTE.  If table aliases of
CTE's were intended as ordinary independent views, the CTE syntax makes no
sense.
Every other implementation agrees with the expected set-mathematical
definition.   SQLite is producing the wrong answer and I'm looking forward
to seeing the trouble ticket that fixes this problem.

Peter








On Fri, Jan 19, 2018 at 12:10 AM, Clemens Ladisch <clem...@ladisch.de>
wrote:

> petern wrote:
> > WITH flips(s) AS (VALUES (random()>0), (random()>0), (random()>0))
> > SELECT sum(s),(SELECT sum(s) FROM flips) FROM flips;
> > sum(s),"(SELECT sum(s) FROM flips)"
> > 1,3
> > --Expected output is 1,1.
> >
> > Why isn't the constant notional table table [flips] materialized just
> once
> > per CTE?
> >
> > FYI.  PostgreSQL 9.6 materializes [flips] exactly once per CTE-users
>
> Its documentation says:
> <https://www.postgresql.org/docs/9.6/static/queries-with.html>
> | A useful property of WITH queries is that they are evaluated only once
> | per execution of the parent query, even if they are referred to more
> | than once by the parent query or sibling WITH queries. Thus, expensive
> | calculations that are needed in multiple places can be placed within
> | a WITH query to avoid redundant work. Another possible application is
> | to prevent unwanted multiple evaluations of functions with side-
> | effects. However, the other side of this coin is that the optimizer is
> | less able to push restrictions from the parent query down into a WITH
> | query than an ordinary subquery.
>
> This is an implementation detail of Postgres, and it is not required by
> the SQL specification.  SQLite chose the other side of the coin.
>
>
> Regards,
> Clemens
> ___
> 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] Defect: Redundant CTE table materialization gives wrong answer.

[petern]

NO. I wrote that to rule out the objection that PostrgeSQL is getting the
right answer because random() is only being computed once per statement.
I naturally DO expect random() to run every time it is called.  What I
wrote is a formality for those thinking ahead about what else could be
happening.



On Thu, Jan 18, 2018 at 10:46 PM, Simon Slavin <slav...@bigfraud.org> wrote:

> On 19 Jan 2018, at 5:04am, petern <peter.nichvolo...@gmail.com> wrote:
>
> > WITH flips(s) AS (VALUES (random()), (random()), (random()))
> > SELECT * FROM flips;
> >
> > s
> > 0.760850821621716
> > 0.9941047639586031
> > 0.48273737309500575
>
> Are you expecting the three values to be the same ?  What about
>
> INSERT INTO MyTable VAKUES (random(), random(), random())
>
> ?  Would you expect those three values to be the same ?
>
> If you think of random() as an external function, do you think of it as
> marked SQLITE_DETERMINISTIC as defined in the following page ?
>
> <https://sqlite.org/c3ref/create_function.html>
>
> 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] Defect: single row table cross join causes infinite loop

[petern]

This also works as expected in PostreSQL but not in SQLite.

WITH RECURSIVE params(n) AS (
   VALUES (5)
  ),
  coinflip(flip,side) AS (
SELECT 1, random()>0.5
UNION ALL
SELECT flip+1, random()>0.5 FROM coinflip
  )
SELECT flip,side FROM coinflip,params WHERE flip%n=0 LIMIT 3;

flip side
5 true
10 false
15 true
--no infinite loop here.  On the other hand, SQLite 3.22 beta plans an
infinite loop.



On Mon, Jan 15, 2018 at 1:10 AM, Clemens Ladisch <clem...@ladisch.de> wrote:

> petern wrote:
> > there is an infinite loop when params table column "n" is used.
>
> >  WITH params(n) AS (
> >VALUES (5)
> >  ),
> >  coinflip(flip,side) AS (
> >SELECT 1, random()>0
> >UNION ALL
> >SELECT flip+1, random()>0 FROM coinflip
> >  )
> >  SELECT flip,side FROM coinflip,params WHERE flip%n=0 LIMIT 3;
>
> > So, what's going on here?  [params] is a constant one row table!
>
> But "coinflip" is infinitely large.  And the EXPLAIN output shows that
> the database tries to compute the entire table before doing the join.
>
> The only reliable way to prevent the infinite loop is to put a LIMIT (or
> a WHERE on a counter) inside the recursive CTE.
>
>
> Regards,
> Clemens
> ___
> 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] Can a record count column be added to this random hierarchical view?

[petern]

>I was hoping to find a way to force the query planner to evaluate
v_random_hierarchy only once.

There is a way with a CTE if the defect I just reported is fixed.
Replying to my bug report with your vote to fix the problem can help.

I think PostgreSQL, the model for SQLite, has it correct.
Once computed, a named CTE table should be constant until the end of the
statement compilation boundary.

Peter


On Thu, Jan 18, 2018 at 9:26 PM, Shane Dev <devshan...@gmail.com> wrote:

> On 19 January 2018 at 05:41, petern <peter.nichvolo...@gmail.com> wrote:
>
> > Were you expecting random() to return the same sequence when the view
> > materialized again in the subquery?
> >
>
> I was hoping to find a way to force the query planner to evaluate
> v_random_hierarchy
> only once. Perhaps this is not possible since it uses the non-deterministic
> random() function
>
>
> > Your ultimate query works fine when the random view is materialized once
> > into a table.
>
>
> Correct, but I work hoping to avoid the I/O penalty of this strategy when
> generating a large hierarchy
>
>
> >
> >
> FYI. as demonstrated above, to also count the NULL parent, use "IS" instead
> > of "=".
> >
> >
> I didn't know IS also worked with integers, good tip.
> ___
> 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] Defect: Redundant CTE table materialization gives wrong answer.

[petern]

WITH flips(s) AS (VALUES (random()>0), (random()>0), (random()>0))
SELECT sum(s),(SELECT sum(s) FROM flips) FROM flips;
sum(s),"(SELECT sum(s) FROM flips)"
1,3
--Expected output is 1,1.

Why isn't the constant notional table table [flips] materialized just once
per CTE?

FYI.  PostgreSQL 9.6 materializes [flips] exactly once per CTE:

WITH flips(s) AS (VALUES (random()), (random()), (random()))
SELECT sum(s),(SELECT sum(s) FROM flips) FROM flips;
sum sum
1.503042308613658 1.503042308613658

Also FYI, double checked:  random() is indeed run 3 times by PostgreSQL.

WITH flips(s) AS (VALUES (random()), (random()), (random()))
SELECT * FROM flips;

s
0.760850821621716
0.9941047639586031
0.48273737309500575

Peter
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can a record count column be added to this random hierarchical view?

[petern]

Were you expecting random() to return the same sequence when the view
materialized again in the subquery?
Your ultimate query works fine when the random view is materialized once
into a table.

CREATE TABLE v_random_hierarchy AS
WITH r(parent, child) as (select null, 1 union all select
abs(random())%child+1, child+1 from r)
SELECT * FROM r LIMIT 5;

select v1.parent, v1.child,
  (select count(child) from v_random_hierarchy as v2 where v2.parent IS
v1.parent) as number_children_of_parent
from v_random_hierarchy as v1;
parent,child,number_children_of_parent
,1,1
1,2,3
1,3,3
3,4,1
1,5,3

FYI. as demonstrated above, to also count the NULL parent, use "IS" instead
of "=".

Some style suggestions.  Uppercase SQL keywords, linebreaks, and
indentation will make your SQL far more readable and easier to see what's
wrong.

SELECT  
  ,
   ...
FROM 
  
  ...
WHERE 
  

Peter


On Thu, Jan 18, 2018 at 2:18 PM, Shane Dev  wrote:

> Hello,
>
> Here is a view which assigns randomly chosen parents to a sequence of
> children -
>
> CREATE VIEW v_random_hierarchy as with r(parent, child) as (select null, 1
> union all select abs(random())%child+1, child+1 from r) select * from r
> limit 5;
>
> sqlite> select * from v_random_hierarchy;
> parent  child
> 1
> 1   2
> 1   3
> 1   4
> 4   5
>
> Can a column be added to this view which counts the number of children for
> each parent?
>
> Here is my failed attempt -
>
> sqlite> select v1.parent, v1.child, (select count(*) from
> v_random_hierarchy as v2 where v2.parent=v1.parent) as
> number_children_of_parent from v_random_hierarchy as v1;
> parent  child   number_children_of_parent
> 1   0
> 1   2   3
> 2   3   0
> 3   4   0
> 3   5   1
>
> in this case, it should be -
>
> parent  child   number_children_of_parent
> 1   1
> 1   2   1
> 2   3   1
> 3   4   2
> 3   5   2
> ___
> 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] IN clause

[petern]

Of the IN clause under PostgreSQL 9.6 (sqlfiddle.com), both syntax variants
return true without error:

SELECT (1,2) IN ((1,2),(3,4));
SELECT (1,2) IN (VALUES (1,2),(3,4));

A clone of PostgreSQL would also have optional VALUES table alias and
column name specifiers as observed earlier.



On Thu, Jan 18, 2018 at 11:59 AM, petern <peter.nichvolo...@gmail.com>
wrote:

> >I am open to enhancing the syntax here, but not right now because we
> are trying to get the 3.22.0 release out - this would need to be
> during the next cycle.  Also, I'll need to check to see what
> PostgreSQL does first, and emulate them.
>
> Yes please!  Thank you for getting around to this:
>
> (VALUES ) AS  ()
>
> In PostgreSQL 9.6 (sqlfiddle.com), apparently where '' is
> specified, 'AS' and '()' are optional.
>
> Reference: https://www.postgresql.org/docs/9.5/static/queries-values.html
>
> ---quote---
>
> By default, PostgreSQL assigns the names column1, column2, etc. to the
> columns of a VALUES table. The column names are not specified by the SQL
> standard and different database systems do it differently, so it's usually
> better to override the default names with a table alias list, like this:
>
> => SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t 
> (num,letter);
>  num | letter
> -+
>1 | one
>2 | two
>3 | three
> (3 rows)
>
> -
>
> Peter
>
>
>
> On Thu, Jan 18, 2018 at 10:58 AM, Richard Hipp <d...@sqlite.org> wrote:
>
>> On 1/18/18, Szyk Cech <szykc...@spoko.pl> wrote:
>> > Hi
>> >
>> > My concern is about a Primary Key in two columns (integer values) which
>> > I want type explicitly in my query (like in example "Not works"). My
>> > example only shows problem and it is not real case (however database is
>> > real).
>> >
>> > Not works:
>> >
>> > select * from card where (statNumber, question) in ((2211, 'psuć się'),
>> > (2542, 'kontynuować'), (1449, 'wymrzeć'))
>>
>> Try it this way:
>>
>>   SELECT * FROM card WHERE (statNumber,question) IN
>> (VALUES(2211,'psuc sei'),(2542,'kontynuowac'), (1449,'wymrzec'));
>>
>> I am open to enhancing the syntax here, but not right now because we
>> are trying to get the 3.22.0 release out - this would need to be
>> during the next cycle.  Also, I'll need to check to see what
>> PostgreSQL does first, and emulate them.
>>
>> >
>> > Works:
>> >
>> > select * from card where (statNumber, question) in (select statNumber,
>> > question from card)
>> >
>> > Why?
>> >
>> > thanks and best regards
>> >
>> > Szyk Cech
>> >
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@mailinglists.sqlite.org
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> >
>>
>>
>> --
>> 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
>>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] IN clause

[petern]

>I am open to enhancing the syntax here, but not right now because we
are trying to get the 3.22.0 release out - this would need to be
during the next cycle.  Also, I'll need to check to see what
PostgreSQL does first, and emulate them.

Yes please!  Thank you for getting around to this:

(VALUES ) AS  ()

In PostgreSQL 9.6 (sqlfiddle.com), apparently where '' is
specified, 'AS' and '()' are optional.

Reference: https://www.postgresql.org/docs/9.5/static/queries-values.html

---quote---

By default, PostgreSQL assigns the names column1, column2, etc. to the
columns of a VALUES table. The column names are not specified by the SQL
standard and different database systems do it differently, so it's usually
better to override the default names with a table alias list, like this:

=> SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t
(num,letter);
 num | letter
-+
   1 | one
   2 | two
   3 | three
(3 rows)

-

Peter



On Thu, Jan 18, 2018 at 10:58 AM, Richard Hipp  wrote:

> On 1/18/18, Szyk Cech  wrote:
> > Hi
> >
> > My concern is about a Primary Key in two columns (integer values) which
> > I want type explicitly in my query (like in example "Not works"). My
> > example only shows problem and it is not real case (however database is
> > real).
> >
> > Not works:
> >
> > select * from card where (statNumber, question) in ((2211, 'psuć się'),
> > (2542, 'kontynuować'), (1449, 'wymrzeć'))
>
> Try it this way:
>
>   SELECT * FROM card WHERE (statNumber,question) IN
> (VALUES(2211,'psuc sei'),(2542,'kontynuowac'), (1449,'wymrzec'));
>
> I am open to enhancing the syntax here, but not right now because we
> are trying to get the 3.22.0 release out - this would need to be
> during the next cycle.  Also, I'll need to check to see what
> PostgreSQL does first, and emulate them.
>
> >
> > Works:
> >
> > select * from card where (statNumber, question) in (select statNumber,
> > question from card)
> >
> > Why?
> >
> > thanks and best regards
> >
> > Szyk Cech
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
> --
> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] c program which performs the same function as the SQLite shell command ".import"

[petern]

FYI. 2D/3D game usability is extremely sensitive to response time.  A stock
in-memory SQLite database with plenty of memory is still too slow for
tracking the state of an interactive graphical game especially on portable
grade cpus.  Any practical realtime video game using SQLite is probably
doing so only to save and restore the game board between games.   The
actual game time logic and player movements are entirely coordinated by a
custom engine which interfaces directly between highly optimized in-memory
data structures and the graphics library.   The equivalent loop in SQLite
would be a statement that has already been prepared and is receiving rows
from the database without interruption for the entire game duration.
Without heavy rework of the whole database concept, that simply can't work
because a statement's results are isolated from subsequent model changes
while result rows are being read.   Even a cursory look into production
quality video game development will tell you that a database is the wrong
technology to base a video game engine on.







On Wed, Jan 17, 2018 at 2:43 PM, Shane Dev <devshan...@gmail.com> wrote:

> In my day job, I am an SAP consultant - for over 20 years. Production
> quality code? Yes, but only within the companies where I have worked - tax,
> banking, inventory, procurement, sales, etc.
>
> My interest in SQLite is a personal hobby project at the moment. I have a
> couple of ideas for end user applications - a game (tentatively called
> "Canibal Ants") and a planning tool. Both of them would modeled with graphs
> (as in graph theory). Given the choice of complex core application code or
> a complex DB schema, I prefer the latter.
>
> At this stage, I am trying to understand the strategies used by experienced
> SQLite library users to solve common programming problems. I will
> investigate the shell_callback function.
>
> On 17 January 2018 at 19:21, petern <peter.nichvolo...@gmail.com> wrote:
>
> > Take a look at the function shell_callback for hints. See the MODE_Csv
> > case.
> > You could start by cribbing the functions MODE_Csv uses for your own row
> > handler and then see what you'll have to figure out yourself.
> >
> > Typically, if you are a serious product developer at a frontier in the
> > market, you will have to fairly expertly code and deploy your own
> > program(s) for every target platform on which you want your application
> to
> > get off the ground.  If what you're doing is worthwhile, then you will
> have
> > to somehow develop the code to make it happen.  That's the development
> > process.
> >
> > Let me ask some questions anybody reading your posts is definitely
> > wondering about.
> >
> > What is your background?  Have you done production quality software
> > development work before?
> >
> > Is your application worthwhile?  If you can say, what does your
> application
> > do for the end user that they couldn't do without it?
> >
> >
> >
> >
> >
> >
> >
> >
> > On Wed, Jan 17, 2018 at 2:54 AM, Shane Dev <devshan...@gmail.com> wrote:
> >
> > > On 17 January 2018 at 08:45, petern <peter.nichvolo...@gmail.com>
> wrote:
> > >
> > > > Shane. Expect to do a lot of hacking on shell.c.  It's not intended
> as
> > a
> > > > library but as the main program of a console application.
> > >
> > >
> > > That's a shame. I try very hard not to reinvent the wheel especially
> when
> > > the wheel question (shell.c) is widely used, flexible and presumably
> > > thoroughly debugged.
> > >
> > > However, I can't be the only one trying to programmatically exchange
> data
> > > between SQLite and a delimited text file. For importing, the CSV
> virtual
> > > table works well for multi-column CSVs, thanks again for the tip. For
> > > exporting, I could retrieve the data using sqlite3_exec and build a
> > string
> > > from the 3rd and 4th parameters of the callback function. Then I would
> > need
> > > to code logic to insert the column and line separators and handle edge
> > > cases (fields containing separators or double quotes, single column
> > tables,
> > > etc) and finally write the string to a file.
> > >
> > > Is this most efficient approach?
> > > ___
> > > 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] c program which performs the same function as the SQLite shell command ".import"

[petern]

csv.c isn't a writer.  Shane expected to write the file by inserting rows
into the vtable.  He has no application whatsoever but for the shell.

Richard, since you're responding to questions, let me ask again about 3.22
INTROPECTION_PRAGMAS release.
Will function_list() be progressing at all toward outputing a composite key
for the function?  What about a module name column in 3.22?
Seeing which module currently controls the function name would be a helpful
diagnostic at least.

Peter




On Wed, Jan 17, 2018 at 10:43 AM, Richard Hipp <d...@sqlite.org> wrote:

> On 1/17/18, petern <peter.nichvolo...@gmail.com> wrote:
> > Take a look at the function shell_callback for hints.
>
> If the goal is to create a TSV reader/writer, it seems like the CVS
> reader/writer might be a better starting point, as it is unencumbered
> by lots of other unrelated features as is the shell.  You might be
> able to get cvs.c to work simply by changing a single instance of a
> ',' literal into '\t'.  Probably a little more work than that will be
> involved, but not too much more.
>
> --
> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] c program which performs the same function as the SQLite shell command ".import"

[petern]

Take a look at the function shell_callback for hints. See the MODE_Csv
case.
You could start by cribbing the functions MODE_Csv uses for your own row
handler and then see what you'll have to figure out yourself.

Typically, if you are a serious product developer at a frontier in the
market, you will have to fairly expertly code and deploy your own
program(s) for every target platform on which you want your application to
get off the ground.  If what you're doing is worthwhile, then you will have
to somehow develop the code to make it happen.  That's the development
process.

Let me ask some questions anybody reading your posts is definitely
wondering about.

What is your background?  Have you done production quality software
development work before?

Is your application worthwhile?  If you can say, what does your application
do for the end user that they couldn't do without it?








On Wed, Jan 17, 2018 at 2:54 AM, Shane Dev <devshan...@gmail.com> wrote:

> On 17 January 2018 at 08:45, petern <peter.nichvolo...@gmail.com> wrote:
>
> > Shane. Expect to do a lot of hacking on shell.c.  It's not intended as a
> > library but as the main program of a console application.
>
>
> That's a shame. I try very hard not to reinvent the wheel especially when
> the wheel question (shell.c) is widely used, flexible and presumably
> thoroughly debugged.
>
> However, I can't be the only one trying to programmatically exchange data
> between SQLite and a delimited text file. For importing, the CSV virtual
> table works well for multi-column CSVs, thanks again for the tip. For
> exporting, I could retrieve the data using sqlite3_exec and build a string
> from the 3rd and 4th parameters of the callback function. Then I would need
> to code logic to insert the column and line separators and handle edge
> cases (fields containing separators or double quotes, single column tables,
> etc) and finally write the string to a file.
>
> Is this most efficient approach?
> ___
> 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] c program which performs the same function as the SQLite shell command ".import"

[petern]

Shane. Expect to do a lot of hacking on shell.c.  It's not intended as a
library but as the main program of a console application.  Another way
involves controlling the IO handles of your process and sending strings but
that will probably run into portability problems that are even a bigger
headache.

Proceeding from the assumption the main program will be in a different
compilation unit, that function you want to call will have to be exportable
instead of static and you'll have to get a valid ShellState from
somewhere.  You could start your hacking by adding an exportable function
that takes only zLine and a forged ShellState to pass into the ultimate
do_meta_command() call.  Shell.c is interactive so you have to watch out
for it messing around with your program's IO handles.  If you plan to use
other shell.c functions and/or keep up with changes/fixes to the original
shell.c, try to isolate your changes to just the exported functions you add
and figure out some minimal #ifdef changes to disable the IO interactions.

There is no guarantee your additions and #ifdefs will continue to work if
shell.c has changes/fixes.  Expect merge headaches every time there's a new
release you want for your product.   If you only need the .import function,
cloning that code into your program could be easier to maintain but
probably more work up front.

Peter





On Tue, Jan 16, 2018 at 10:13 PM, Shane Dev  wrote:

> Apparently the CSV virtual table supports neither changes (INSERT, UPDATE,
> DELETE), nor reading single column csv files.
>
> What I really want is the functionality of .import and .output SQLite shell
> commands. Maybe a better strategy would be to compile shell.c with my c
> program and call the function do_meta_command(char *zLine, ShellState *p).
>
> To those familiar with shell.c, is this a reasonable approach?
>
> On 17 January 2018 at 00:15, Richard Hipp  wrote:
>
> > On 1/16/18, Shane Dev  wrote:
> > > I tried -
> > >
> > > sqlite> CREATE VIRTUAL TABLE temp.t1 USING csv(filename='test.tsv');
> > >
> > > where test.tsv is a tab separated table. However
> > >
> > > select count(*) from t1;
> > >
> > > goes into an infinite loop. Do you how to specify a separator other
> than
> > > ","?
> >
> > The "C" in CSV stands for "Comma".  That is the only separator
> > supported.  But, you can probably edit the source code to do something
> > different.
> >
> > --
> > 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
> >
> ___
> 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] Speed issue of SELECT in my application

[petern]

Vague.  Some thoughts:  How long is the text?  A million?  A billion?
If a million, does SQLite take what you consider a long time to
receive/display results from a TEXT row?
SELECT printf('%100s');
--...
Run Time: real 0.854 user 0.016000 sys 0.008000
--vs:
INSERT INTO t1(e) SELECT printf('%100s');
SELECT * from t1;
--...
Run Time: real 0.884 user 0.008000 sys 0.008000



On Tue, Jan 16, 2018 at 7:52 PM, Nick  wrote:

> I have a table below in my application:
>
> CREATE TABLE t1 (
> a INTEGER PRIMARY KEY AUTOINCREMENT,
> b INTEGER NOT NULL UNIQUE,
> c INTEGER NOT NULL,
> d INTEGER,
> e TEXT,
> f INTEGER,
> g INTEGER,
> h TEXT,
> i INTEGER,
> UNIQUE(b, i)
> );
> And I’ve got some speed issues when I query the db:
> SELECT b, c, d, e, f, g, h FROM t1 WHERE b >= 1;
>
> It needs almost 60ms as there are about 100 records with some long TEXT
> data
> in the TEXT columns.
>
> I am wondering if it is needed to add ANY INDEX to improve the performance
> of the SELECT?
>
> Thanks.
>
>
>
> --
> Sent from: http://sqlite.1065341.n5.nabble.com/
> ___
> 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] c program which performs the same function as the SQLite shell command ".import"

[petern]

FYI. csv.c is already a separate C program which imports CSV files without
necessity of the SQLite shell:

https://sqlite.org/csv.html

On Tue, Jan 16, 2018 at 12:47 AM, Shane Dev  wrote:

> Hi,
>
> I am looking for an efficient way to write a c program which performs the
> same function as the SQLite shell command ".import"
>
> My initial strategy is to include the sqlite library source files and copy
> the control block from shell.c that begins after
>
> if( c=='i' && strncmp(azArg[0], "import", n)==0 ){
>
> up to and the line -
>
> if( needCommit ) sqlite3_exec(p->db, "COMMIT", 0, 0, 0);
>
> (i.e lines 5858-6052 in version sqlite-amalgamation-321 of shell.c )
>
> Is this a reasonable approach? Is there a better way?
> ___
> 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] Can an SQL script be built from within sqlite?

[petern]

>Are you the developer / maintainer of the SQLite shell?

THE developer is D Richard Hipp.  He is the decisionmaker and principal
developer of the SQLite releases originated from sqlite.org.  For all
practical purposes, that is the official release.

On the other hand, because of the open license, anybody can make changes
their copy of the SQLite which best traverse the critical paths of their
own project(s).  There are also extension interfaces which enable writing
native target code that is forward compatible with later releases of
SQLite.  Wrapping your own native code in those extension interfaces is the
best way to implement special features for a project/product.

Speaking of critical paths, it seems that my question uncovered some
critical paths you weren't aware of.  To assume your application will run
everywhere without a supporting technology (or your own code) which tests
such a guarantee is a mistake.   SQLite shell makes no such guarantee.

My advice would be to figure out all of your project's critical paths
before plunging into development - except for the narrowest possible
development required to figure out a critical path.

Peter




On Mon, Jan 15, 2018 at 2:08 AM, Shane Dev <devshan...@gmail.com> wrote:

> Hi Peter,
>
> Ideally, I would like to execute a series of "test cases" from within the
> SQLite shell without dependency on an external scripting language. These
> would not normally be executed by the application end user. Initially, I
> planned to implement this with triggers but I see now that the results of
> executing a given statement from within a trigger can be different from
> executing the same statement via the SQLite shell (which I assume is
> calling sqlite3_step or sqlite3_exec)
>
> For conditional logic, case expressions are currently sufficient for my
> needs. So far, I have not needed to execute a script periodically but it
> might useful in the future.
>
> Are you the developer / maintainer of the SQLite shell?
>
> On 15 January 2018 at 01:30, petern <peter.nichvolo...@gmail.com> wrote:
>
> > Shane.  That's very interesting considering the effort to make the one
> > thing happen exactly once without external software dependency.
> > Does the capability to write specially named local files but not have a
> > periodic loop nor network capability somehow get your application off the
> > ground?
> > Based on your problem statement, the user would have to initiate your
> > script and know when/if it is required to be run...
> >
> > I had in mind adding periodic and conditional dot commands to SQLite
> shell
> > - to simulate continuous operation of application code.  If you've
> figured
> > out a way around needing such things to make a useful standalone SQLite
> > application, I would be very interested to understand how that works.
> >
> > Peter
> >
> >
> >
> >
> >
> > On Sun, Jan 14, 2018 at 2:33 PM, Shane Dev <devshan...@gmail.com> wrote:
> >
> > > Hi Simon,
> > >
> > > I have found a way achieve this purely in the SQLite shell. The trick
> is
> > to
> > > make all rows in tcout1 SQL statements and then execute them.
> > >
> > > sqlite> CREATE TABLE tcout1(sql text);
> > > sqlite> CREATE TABLE tcout2(sql text);
> > > sqlite> insert into tcout1(sql) select "insert into tcout2(sql) select
> > > '.headers off';";
> > > sqlite> insert into tcout1(sql) select "insert into tcout2(sql) select
> > > '.once tc'||strftime('%s','now');";
> > > sqlite> insert into tcout1(sql) select "insert into tcout2(sql) select
> > > 'select * from tc;';";
> > > sqlite> .once tcout1.sql
> > > sqlite> select * from tcout1;
> > > sqlite> .read tcout1.sql
> > > sqlite> select * from tcout2;
> > > .headers off
> > > .once tc1515968593
> > > select * from tc;
> > >
> > >
> > > On 13 January 2018 at 19:57, Simon Slavin <slav...@bigfraud.org>
> wrote:
> > >
> > > > On 13 Jan 2018, at 6:48pm, Shane Dev <devshan...@gmail.com> wrote:
> > > >
> > > > > Is there a way to execute the contents of certain rows (the second
> > row
> > > in
> > > > > this example) and replace it with its own result to create second
> > > table /
> > > > > view which could interpreted by the sqlite shell?
> > > >
> > > > Not inside the SQLite shell.
> > > >
> > > > Looks like you need to learn programming.  Or at least how to script
> > your
> > > > OS shell.  W

Re: [sqlite] Defect: single row table cross join causes infinite loop

[petern]

That's interesting because a cross join can always be optimized for lazy
evaluation as rows are generated.  There are no join constraints.

As it stands, without lazy cross join optimization, the only general
solution here is to 'pick' individual params from using subqueries:

WITH params(n) AS (
   VALUES (5)
  ),
  coinflip(flip,side) AS (
SELECT 1, random()>0
UNION ALL
SELECT flip+1, random()>0 FROM coinflip
  )
SELECT flip,side FROM coinflip WHERE flip%(SELECT n FROM params)=0 LIMIT 3;
'flip','side'
5,1
10,0
15,1



On Mon, Jan 15, 2018 at 1:10 AM, Clemens Ladisch <clem...@ladisch.de> wrote:

> petern wrote:
> > there is an infinite loop when params table column "n" is used.
>
> >  WITH params(n) AS (
> >VALUES (5)
> >  ),
> >  coinflip(flip,side) AS (
> >SELECT 1, random()>0
> >UNION ALL
> >SELECT flip+1, random()>0 FROM coinflip
> >  )
> >  SELECT flip,side FROM coinflip,params WHERE flip%n=0 LIMIT 3;
>
> > So, what's going on here?  [params] is a constant one row table!
>
> But "coinflip" is infinitely large.  And the EXPLAIN output shows that
> the database tries to compute the entire table before doing the join.
>
> The only reliable way to prevent the infinite loop is to put a LIMIT (or
> a WHERE on a counter) inside the recursive CTE.
>
>
> Regards,
> Clemens
> ___
> 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] sqlite_column_table_name() and table alias name

[petern]

Jake.  Maybe somebody else can see the patch.  There's a login screen at
the linked page.
Others are using Node.js with the current version of SQLite.  Are you sure
that patch is needed?
If you can briefly explain what's not working I'm sure somebody else will
reply.
Peter


On Sun, Jan 14, 2018 at 9:17 PM, Jake Chen  wrote:

> Hi,
>
> My apologies if it's impolite to reply to such an ancient thread.
>
> I've encountered the same problem while implementing an object-relational
> mapping library for Node.js. Is there any chance that OP's patch gets
> merged into master?
>
> If this email weren't able to get related to the original post, here's the
> link http://mailinglists.sqlite.org/cgi-bin/mailman/private/
> sqlite-users/2014-November/056379.html  org/cgi-bin/mailman/private/sqlite-users/2014-November/056379.html>
>
> Jake Chen
> ___
> 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] possible bug: separator string and quote mode

[petern]

Pamela.  Shell .mode quote uses hardwired separator, presumably to rule out
errors when the mode is requested to generate SQL compatible strings.

It seems to me the enhancement you're requesting could be made backward
compatible by using  p->colSeparator instead of the hardwired ',' and then
setting the  p->colSeparator to the backward compatible comma in the .mode
quote meta-command and then allowing the user to subsequently override with
the .separator command.

Maybe there is a different reason for the hardwiring or reason why it can't
be changed.  That would be Richard's decision.

Peter
--
/*from shell.c*/
case MODE_Quote: {
  if( azArg==0 ) break;
  if( p->cnt==0 && p->showHeader ){
for(i=0; i0 ) raw_printf(p->out, ",");
  output_quoted_string(p->out, azCol[i]);
}
raw_printf(p->out,"\n");
  }
  p->cnt++;
  for(i=0; i0 ) raw_printf(p->out, ",");
if( (azArg[i]==0) || (aiType && aiType[i]==SQLITE_NULL) ){
  utf8_printf(p->out,"NULL");
}else if( aiType && aiType[i]==SQLITE_TEXT ){
  output_quoted_string(p->out, azArg[i]);
}else if( aiType && aiType[i]==SQLITE_INTEGER ){
  utf8_printf(p->out,"%s", azArg[i]);
}else if( aiType && aiType[i]==SQLITE_FLOAT ){
  char z[50];
  double r = sqlite3_column_double(p->pStmt, i);
  sqlite3_snprintf(50,z,"%!.20g", r);
  raw_printf(p->out, "%s", z);
}else if( aiType && aiType[i]==SQLITE_BLOB && p->pStmt ){
  const void *pBlob = sqlite3_column_blob(p->pStmt, i);
  int nBlob = sqlite3_column_bytes(p->pStmt, i);
  output_hex_blob(p->out, pBlob, nBlob);
}else if( isNumber(azArg[i], 0) ){
  utf8_printf(p->out,"%s", azArg[i]);
}else{
  output_quoted_string(p->out, azArg[i]);
}
  }
  raw_printf(p->out,"\n");
  break;
}


On Sun, Jan 14, 2018 at 9:29 AM, p dev  wrote:

> I have been using SQLite for a small data-processing application.
> I am hoping to get quote mode output with a field separator that is not a
> comma.Many thanks,
> Pamela--
> Problem
>
> Quote mode output ignores separator string
> Background
> https://www.sqlite.org/cli.html5. Changing Output Formats
> ...
> In "quote" mode, the output is formatted as SQL literals. Strings are
> enclosed in single-quotes and internal single-quotes are escaped by
> doubling. Blobs are displayed in hexadecimal blob literal notation (Ex:
> x'abcd'). Numbers are displayed as ASCII text and NULL values are shown as
> "NULL". All columns are separated from each other by a comma (or whatever
> alternative character is selected using ".separator").
> ...
>
> Demonstration
>
> SQLite version 3.21.0 2017-10-24 18:55:49
> Enter ".help" for usage hints.
> sqlite> create table tbl1(one varchar(10), two smallint);
> sqlite> insert into tbl1 values('hello!',10);
> sqlite> insert into tbl1 values('goodbye', 20);
> sqlite> insert into tbl1 values(null, 30);
> sqlite> select * from tbl1;
> hello!|10
> goodbye|20
> |30
>
> sqlite> .separator #
> sqlite> select * from tbl1;
> hello!#10
> goodbye#20
> #30
>
> sqlite> .mode quote
> sqlite> select * from tbl1;
> 'hello!',10
> 'goodbye',20
> NULL,30
>
>
> Expected Output
>
> sqlite> select * from tbl1;'hello!'#10
> 'goodbye'#20
> NULL#30
>
> sqlite> .show
> echo: off
>  eqp: off
>  explain: auto
>  headers: on
> mode: quote
>nullvalue: ""
>   output: stdout
> colseparator: "#"
> rowseparator: "\n"
>stats: off
>width:
> filename: data.db3
>
> ___
> 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] Can an SQL script be built from within sqlite?

[petern]

Shane.  That's very interesting considering the effort to make the one
thing happen exactly once without external software dependency.
Does the capability to write specially named local files but not have a
periodic loop nor network capability somehow get your application off the
ground?
Based on your problem statement, the user would have to initiate your
script and know when/if it is required to be run...

I had in mind adding periodic and conditional dot commands to SQLite shell
- to simulate continuous operation of application code.  If you've figured
out a way around needing such things to make a useful standalone SQLite
application, I would be very interested to understand how that works.

Peter





On Sun, Jan 14, 2018 at 2:33 PM, Shane Dev  wrote:

> Hi Simon,
>
> I have found a way achieve this purely in the SQLite shell. The trick is to
> make all rows in tcout1 SQL statements and then execute them.
>
> sqlite> CREATE TABLE tcout1(sql text);
> sqlite> CREATE TABLE tcout2(sql text);
> sqlite> insert into tcout1(sql) select "insert into tcout2(sql) select
> '.headers off';";
> sqlite> insert into tcout1(sql) select "insert into tcout2(sql) select
> '.once tc'||strftime('%s','now');";
> sqlite> insert into tcout1(sql) select "insert into tcout2(sql) select
> 'select * from tc;';";
> sqlite> .once tcout1.sql
> sqlite> select * from tcout1;
> sqlite> .read tcout1.sql
> sqlite> select * from tcout2;
> .headers off
> .once tc1515968593
> select * from tc;
>
>
> On 13 January 2018 at 19:57, Simon Slavin  wrote:
>
> > On 13 Jan 2018, at 6:48pm, Shane Dev  wrote:
> >
> > > Is there a way to execute the contents of certain rows (the second row
> in
> > > this example) and replace it with its own result to create second
> table /
> > > view which could interpreted by the sqlite shell?
> >
> > Not inside the SQLite shell.
> >
> > Looks like you need to learn programming.  Or at least how to script your
> > OS shell.  Which OS are you using ?  And if it’s Linux/Unix, which shell
> > are you using ?
> >
> > 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Defect: single row table cross join causes infinite loop

[petern]

Consider the hypothetical CTE concerned with displaying the n'th coin flip
of a random series as follows.  [This is a simplified proxy for any
computation where an earlier computed table supplies the parameters for a
later computed table.]

sqlite> .v
SQLite 3.22.0 2018-01-12 23:38:10
dec3ea4e4e6c4b1761ddc883a29eaa50dcd663ce6199667cc0ff82f7849d4f2a


--This way works as expected: note how the final select is not joining the
params table.
sqlite>  WITH params(n) AS (VALUES (5))
 ,coinflip(flip,side) AS (SELECT 1,random()>0 UNION ALL SELECT
flip+1,random()>0 FROM coinflip)
SELECT flip,side FROM coinflip WHERE flip%5=0 LIMIT 3;
flip,side
5,0
10,1
15,0

--However, there is an infinite loop when params table column "n" is used.
 WITH params(n) AS (VALUES (5))
 ,coinflip(flip,side) AS (SELECT 1,random()>0 UNION ALL SELECT
flip+1,random()>0 FROM coinflip)
 SELECT flip,side FROM coinflip,params WHERE flip%n=0 LIMIT 3;

--Also infinite loop.
--Simply cross join params table without using any columns from it.

 WITH params(n) AS (VALUES (5))
 ,coinflip(flip,side) AS (SELECT 1,random()>0 UNION ALL SELECT
flip+1,random()>0 FROM coinflip)
 SELECT flip,side FROM coinflip,params WHERE flip%5=0 LIMIT 3;

--So, what's going on here?  [params] is a constant one row table!  How can
it be adding any rows to the output set let alone an infinite number?


Peter
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_value_pointer() metadata is also stripped by trivial cross join. Defect:

[petern]

sqlite> .v
SQLite 3.22.0 2018-01-12 23:38:10
dec3ea4e4e6c4b1761ddc883a29eaa50dcd663ce6199667cc0ff82f7849d4f2a

sqlite> WITH pointer_tab AS (SELECT pointer()pointer) SELECT
ispointer(pointer) FROM pointer_tab;
ispointer(pointer)
1

sqlite> WITH pointer_tab AS (SELECT pointer()pointer), other_tab(i) AS
(VALUES (1)) SELECT ispointer(pointer),i FROM pointer_tab,other_tab;
ispointer(pointer),i
0,1

--Expected 1,1.  sqlite3_value_pointer is constant.
--There is no sort here.  What happened to the pointer metadata?

listing of pointer.c testing extension:

#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1
static void free(void*pointer) {if (pointer) sqlite3_free(pointer);}
/*return a test pointer to one byte.*/
static void pointer(sqlite3_context *context, int argc, sqlite3_value
**argv) {
  sqlite3_result_pointer(context, sqlite3_malloc(1), "pointer", free);
}
/*return 1 if arg0 is sqlite3 pointer. return 0 otherwise.*/
static void ispointer(sqlite3_context *context, int argc, sqlite3_value
**argv) {
  sqlite3_result_int(context,0!=sqlite3_value_pointer(argv[0],"pointer"));
}
int sqlite3_pointer_init(sqlite3 *db, char **pzErrMsg, const
sqlite3_api_routines *pApi) {
  SQLITE_EXTENSION_INIT2(pApi);
  int rc = sqlite3_create_function(db, "pointer", 0, SQLITE_UTF8, 0,
pointer, 0, 0);
  if (SQLITE_OK == rc) rc = sqlite3_create_function(db, "ispointer", 1,
SQLITE_UTF8, 0, ispointer, 0, 0);
  return rc;
}

Peter
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.22.0 coming soon

[petern]

Richard. As you are probably already aware, I forgot about the encoding
column.  An encoding (UTF8,UTF16,...) column would also needed for the
PRAGMA function_list report to be the key for functions differing only by
the encoding flag:

https://sqlite.org/c3ref/create_function.html

One final issue.  Using the key columns discussed so far, functions
overriding builtin ones would appear in the PRAGMA report but subsequent
builtin overrides and non-builtin overrides can only be reported as the
most recent one.  Perhaps it would be better to have a module name column
instead of the existing builtin boolean column where the reserved module
name, call it 'sqlite', designates builtin entries.

Is {name, module, argcount, aggegate, encoding} a sufficient key to the
function description when special case builtins have the suggested
simulated entry(s)?

Peter








On Sat, Jan 13, 2018 at 12:15 PM, petern <peter.nichvolo...@gmail.com>
wrote:

> Single builtin functions that otherwise would have required two or more
> separate create_function calls should have two or more corresponding
> simulated entries for plain and aggregate flavors distinguishable by column
> values.max(A) aggregate and max(A,B,...) function could look something
> like this:
>
> name,builtin,aggregate,argcount
> max,1,1,1
> max,1,0,-1
>
> The arg_count column would simply be the nArg supplied (or as simulated)
> to sqlite3_create_function(..,int nArg,..). That is -1 for any number and 0
> to 127 for specific quantity. In case of coalesce(), argcount would have to
> be -1 but the description should say something about the minimum number of
> arguments - case in point about the need for descriptions.
>
> Are there other possibilities which wouldn't have a key?
>
> Peter
>
>
> On Fri, Jan 12, 2018 at 1:09 PM, Richard Hipp <d...@sqlite.org> wrote:
>
>> On 1/12/18, petern <peter.nichvolo...@gmail.com> wrote:
>> > Is adding arg_count and
>> > is_aggregate columns to PRAGMA function_list() on the roadmap?
>>
>> How would that work with functions like coalesce() and max() that take
>> an arbitrary number of arguments, or like max() that is an aggregate
>> with one argument and a scalar with two or more arguments?
>>
>> --
>> 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
>>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.22.0 coming soon

[petern]

Single builtin functions that otherwise would have required two or more
separate create_function calls should have two or more corresponding
simulated entries for plain and aggregate flavors distinguishable by column
values.max(A) aggregate and max(A,B,...) function could look something
like this:

name,builtin,aggregate,argcount
max,1,1,1
max,1,0,-1

The arg_count column would simply be the nArg supplied (or as simulated) to
sqlite3_create_function(..,int nArg,..). That is -1 for any number and 0 to
127 for specific quantity. In case of coalesce(), argcount would have to be
-1 but the description should say something about the minimum number of
arguments - case in point about the need for descriptions.

Are there other possibilities which wouldn't have a key?

Peter


On Fri, Jan 12, 2018 at 1:09 PM, Richard Hipp <d...@sqlite.org> wrote:

> On 1/12/18, petern <peter.nichvolo...@gmail.com> wrote:
> > Is adding arg_count and
> > is_aggregate columns to PRAGMA function_list() on the roadmap?
>
> How would that work with functions like coalesce() and max() that take
> an arbitrary number of arguments, or like max() that is an aggregate
> with one argument and a scalar with two or more arguments?
>
> --
> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Defect: trivial cross join strips BLOB subtype

[petern]

sqlite> .v
SQLite 3.22.0 2018-01-12 23:38:10
dec3ea4e4e6c4b1761ddc883a29eaa50dcd663ce6199667cc0ff82f7849d4f2a

sqlite> WITH blob_tab AS (SELECT blob()blob) SELECT subtype(blob) FROM
blob_tab;
subtype(blob)
1

sqlite> WITH blob_tab AS (SELECT blob()blob), other_tab(i) AS (values (1))
SELECT subtype(blob),i FROM blob_tab,other_tab;
subtype(blob),i
0,1

--expected output is 1,1.  Subtype is constant 1.  What happened to it?

Source of test extension subtype.c:

#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1
static void free(void*pBLOB) {if (pBLOB) sqlite3_free(pBLOB);}

/*return a one byte test blob with constant subtype=1*/
static void blob(sqlite3_context *context, int argc, sqlite3_value **argv) {
  sqlite3_result_blob(context, sqlite3_malloc(1), 1, free);
  sqlite3_result_subtype(context, 1);
}

 /*return the subtype of arg0*/
static void subtype(sqlite3_context *context, int argc, sqlite3_value
**argv) {
  sqlite3_result_int(context,sqlite3_value_subtype(argv[0]));
}

int sqlite3_subtype_init(sqlite3 *db, char **pzErrMsg, const
sqlite3_api_routines *pApi) {
  SQLITE_EXTENSION_INIT2(pApi);
  int rc = sqlite3_create_function(db, "blob", 0, SQLITE_UTF8, 0, blob, 0,
0);
  if (SQLITE_OK == rc) rc = sqlite3_create_function(db, "subtype", 1,
SQLITE_UTF8, 0, subtype, 0, 0);
  return rc;
}

Peter
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.22.0 coming soon

[petern]

This is premature of course.  The columns of the current PRAGMA
function_list() report do not form a practical key for functions.
Additional columns arg_count and is_aggregate would be needed  join PRAGMA
function_list() with a function description table.

Suggestion: As an example of both testing the new zipfile facility and
introspection, SQLite core built-in descriptions could be published as a
zip file which joins properly with a newly sufficient PRAGMA
function_list().

So, now the more precisely relevant question is:   Is adding arg_count and
is_aggregate columns to PRAGMA function_list() on the roadmap?

With that change alone, at least extension implementors would have a way to
publish PRAGMA interactive function argument descriptions.

Richard?



On Fri, Jan 12, 2018 at 8:47 AM, petern <peter.nichvolo...@gmail.com> wrote:

> Ryan.  The core and sqlite3_create_function...() needn't be burdened at
> all except to store some very basic description strings as a compile time
> option.
>
> PRAGMA function_list() cold gather descriptions on the fly by:
>
> 1. Spinning through the list of registered %_function_description(F,N)
> functions for each row, or
>
> 2. Spinning through a list of library module exports, say
> sqlite3__function_description(F,N).
>
> Modules implementing either interface could do so trivially with if
> statements or with a binary search of hand sorted description array.
>
> This enhancement is feasible, easy to implement, and would help
> tremendously to see exactly how to call any function from the command line.
>
> My question mainly was to find out if the idea was considered and if it is
> on the roadmap along with the SQLITE_INTROSPECTION_PRAGMAS change.
>
> Peter
>
>
> On Thu, Jan 11, 2018 at 2:31 PM, R Smith <ryansmit...@gmail.com> wrote:
>
>>
>> On 2018/01/11 8:11 PM, petern wrote:
>>
>>> With SQLITE_INTROSPECTION_PRAGMAS turned on, is a function description on
>>> the roadmap?
>>> It would be very helpful to expose a short description of function
>>> arguments.
>>>
>>> Implementation suggestion: a new trailing argument "description" on
>>> sqlite3_create_function() or sqlite3_create_function_v2()
>>> and corresponding description column in the PRAGMA function_list report.
>>>
>>
>> Well, I like the idea.
>>
>> For one, it would allow an easy to read updated description for those of
>> us who maintain SQLite management tools and connectors... but accessing the
>> web is more or less equally simple, though a direct api would be nice too.
>> Maybe this would better serve add-on libraries and UDFs since those
>> function descriptions are not typically available on the web, and certainly
>> not on the standard sqlite site.
>>
>> The only con I'm seeing is the extra disk/memory footprint for what is
>> essentially comments and can (especially for the standard functions) be
>> found easily at: https://sqlite.org/lang_corefunc.html
>>
>> Perhaps there is another benefit I'm not yet seeing that would better
>> merit paying said resource cost - if so, I'm eager to hear it.
>>
>> Cheers,
>> Ryan
>>
>> ___
>> 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] SQLite 3.22.0 coming soon

[petern]

Ryan.  The core and sqlite3_create_function...() needn't be burdened at all
except to store some very basic description strings as a compile time
option.

PRAGMA function_list() cold gather descriptions on the fly by:

1. Spinning through the list of registered %_function_description(F,N)
functions for each row, or

2. Spinning through a list of library module exports, say
sqlite3__function_description(F,N).

Modules implementing either interface could do so trivially with if
statements or with a binary search of hand sorted description array.

This enhancement is feasible, easy to implement, and would help
tremendously to see exactly how to call any function from the command line.

My question mainly was to find out if the idea was considered and if it is
on the roadmap along with the SQLITE_INTROSPECTION_PRAGMAS change.

Peter


On Thu, Jan 11, 2018 at 2:31 PM, R Smith <ryansmit...@gmail.com> wrote:

>
> On 2018/01/11 8:11 PM, petern wrote:
>
>> With SQLITE_INTROSPECTION_PRAGMAS turned on, is a function description on
>> the roadmap?
>> It would be very helpful to expose a short description of function
>> arguments.
>>
>> Implementation suggestion: a new trailing argument "description" on
>> sqlite3_create_function() or sqlite3_create_function_v2()
>> and corresponding description column in the PRAGMA function_list report.
>>
>
> Well, I like the idea.
>
> For one, it would allow an easy to read updated description for those of
> us who maintain SQLite management tools and connectors... but accessing the
> web is more or less equally simple, though a direct api would be nice too.
> Maybe this would better serve add-on libraries and UDFs since those
> function descriptions are not typically available on the web, and certainly
> not on the standard sqlite site.
>
> The only con I'm seeing is the extra disk/memory footprint for what is
> essentially comments and can (especially for the standard functions) be
> found easily at: https://sqlite.org/lang_corefunc.html
>
> Perhaps there is another benefit I'm not yet seeing that would better
> merit paying said resource cost - if so, I'm eager to hear it.
>
> Cheers,
> Ryan
>
> ___
> 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] SQLite 3.22.0 coming soon

[petern]

Or, more practically, please consider a virtual style API where PRAGMA
function_list() gathers function description strings from all registered 2
argument functions named like %_function_description(F,N) where F is the
function name and N is the argument count.

A good example implementation might be a function called
sqlite3_function_description(F,N) which returns short argument descriptions
of all builtin functions - possibly derived from the build process.

Peter






On Thu, Jan 11, 2018 at 10:11 AM, petern <peter.nichvolo...@gmail.com>
wrote:

> With SQLITE_INTROSPECTION_PRAGMAS turned on, is a function description on
> the roadmap?
> It would be very helpful to expose a short description of function
> arguments.
>
> Implementation suggestion: a new trailing argument "description" on
> sqlite3_create_function() or sqlite3_create_function_v2()
> and corresponding description column in the PRAGMA function_list report.
>
>
>
> On Tue, Jan 9, 2018 at 5:06 PM, Richard Hipp <d...@sqlite.org> wrote:
>
>> All of the minor issues mentioned by prior emails in this thread
>> should now be fixed.  Thanks to everybody for proof-reading and
>> testing!
>>
>> Fresh source code is now available on trunk
>> (https://sqlite.org/src/info/trunk) and from the "Prerelease Snapshot"
>> link on the https://sqlite.org/download.html page if you would like to
>> continue testing.
>>
>> We are not yet at "pencils down".  More enhancements may yet go into
>> this release before we start our final release testing.  Nevertheless,
>> the community testing you have conducted so far has found several real
>> issues, and is greatly appreciated.  Please continue the good work!
>> --
>> 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
>>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.22.0 coming soon

[petern]

With SQLITE_INTROSPECTION_PRAGMAS turned on, is a function description on
the roadmap?
It would be very helpful to expose a short description of function
arguments.

Implementation suggestion: a new trailing argument "description" on
sqlite3_create_function() or sqlite3_create_function_v2()
and corresponding description column in the PRAGMA function_list report.



On Tue, Jan 9, 2018 at 5:06 PM, Richard Hipp  wrote:

> All of the minor issues mentioned by prior emails in this thread
> should now be fixed.  Thanks to everybody for proof-reading and
> testing!
>
> Fresh source code is now available on trunk
> (https://sqlite.org/src/info/trunk) and from the "Prerelease Snapshot"
> link on the https://sqlite.org/download.html page if you would like to
> continue testing.
>
> We are not yet at "pencils down".  More enhancements may yet go into
> this release before we start our final release testing.  Nevertheless,
> the community testing you have conducted so far has found several real
> issues, and is greatly appreciated.  Please continue the good work!
> --
> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it possible to conditionally insert a record?

[petern]

Functions and aggregates have to return a scalar value or NULL.  Please
recall functions as a basic concept from early high school:
https://en.wikipedia.org/wiki/Function

The query below will never return a NULL max(value) row from the
empty_table table:

WITH empty_table(value) AS (SELECT 1 WHERE 0) SELECT * FROM (SELECT
max(value)max_value FROM empty_table) WHERE max_value NOT NULL;

Also, INSERTing zero rows is conditioned by having no rows in the source
SELECT like the following pattern:

INSERT INTO ...  SELECT ... FROM ... WHERE  

Shane.  I encourage you experiment on your own in the shell of SQLite to
improve your intuition about SQL.  Everything isn't a special case to be
learned by rote.   There are are a few general patterns that, once
mastered, do explain what to expect most of the time.
















On Wed, Jan 10, 2018 at 1:20 PM, Shane Dev  wrote:

> Hi Ryan,
>
> Nice! I have never used IGNORE before.
>
> Would you agree the documentation is wrong for the case of SELECT max(X)
> FROM [an empty table or subquery]?
>
> max(X)
>
> The max() aggregate function returns the maximum value of all values in the
> group. The maximum value is the value that would be returned last in an
> ORDER BY on the same column. Aggregate max() returns NULL if and only if
> there are no non-NULL values in the group.
>
> https://www.sqlite.org/lang_aggfunc.html
>
>
> On 10 January 2018 at 21:44, R Smith  wrote:
>
> > Perhaps like this:
> >
> >   -- SQLite version 3.20.1  [ Release: 2017-08-24 ]  on SQLitespeed
> > version 2.0.2.4.
> >   -- Script Items: 4  Parameter Count: 0
> >   -- 
> > 
> >
> > create table source_table(value);
> >
> > create table max_value(max_value NOT NULL);
> >
> > insert OR IGNORE into max_value select max(value) from source_table;
> >
> > select * from max_value;
> >
> >
> >   --   Script Stats: Total Script Execution Time: 0d 00h 00m and
> > 00.031s
> >
> >
> >
> > On 2018/01/10 6:48 PM, Shane Dev wrote:
> >
> >> Hello,
> >>
> >> sqlite> create table source_table(value);
> >> sqlite> create table max_value(max_value);
> >> sqlite> insert into max_value select max(value) from source_table;
> >> sqlite> select * from table_max_value;
> >> max_value
> >>
> >> sqlite>
> >>
> >>
> >> How can the maximum value of column source_table.value be inserted into
> >> max_value only if there are records in source_table? (If source_table is
> >> empty, nothing should be inserted into max_value, not even a NULL)
> >> ___
> >> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to add a running number column to a view which generates a limited series of randomly chosen integers?

[petern]

Your inner CTE will have to examine every generated row and count only
matches toward "running_num".  You'll also need another column like
"last_running_num" with a referencing CASE statement in the "running_num"
column to condition emitting, for example, a non-null "running_num"...

Or you can simply and more efficiently use a local variable extension like
auxint.c on your existing VIEW (without the LIMIT) like so:

sqlite> .load auxint
sqlite> SELECT auxint('id',1)running_num,num FROM vrand limit 10;
running_num,num
1,1
2,2
3,3
4,4
5,5
6,9
7,11
8,14
9,15
10,16

The ~20 lines of code for auxint.c  are here:

https://www.mail-archive.com/sqlite-users@mailinglists.sqlit
e.org/msg107018.html




On Sun, Jan 7, 2018 at 10:37 PM, Shane Dev  wrote:

> Hello,
>
> The view VRAND below generates a series of 3 randomly chosen integers -
>
> CREATE VIEW vrand as with r(num, rand) as (
> select 1, cast(round(abs(random())/9223372036854775808) as int)
> union all
> select num+1, cast(round(abs(random())/9223372036854775808) as int) from
> r)
> select num from r where rand=1 limit 3;
>
> sqlite> select * from vrand;
> num
> 1
> 2
> 4
>
> sqlite> select count(*) from vrand;
> count(*)
> 3
>
> Now I would like to add a "running number" column with a result set like -
>
> running_num, num
> 1, 1
> 2, 2
> 3, 4
>
> However, the follow statement seems to trigger an infinite loop -
>
> select (select count(*) from vrand where num <= v.num), num from vrand as
> v;
>
> How can a running number column be added to this view?
> ___
> 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] GROUP_CONCAT with DISTINCT bug

[petern]

Scott.   I almost forgot about this example.  Consider group_concat's
orthogonal function group_replace.

I adapted this from SQLite replace and group_concat.  The author disclaims
all rights to the following code:
---

struct StrRepl {
  const unsigned char* zStr;
  u32 nStr;
  u8 replError; /* STRREPL_NOMEM or STRREPL_TOOBIG */
};
typedef struct StrRepl StrRepl;
#define STRREPL_NOMEM   1
#define STRREPL_TOOBIG  2

/*
 ** Aggregate group_replace(A,B,C) result string is derived from A by
replacing
 ** every exact match occurrence of B with C.  A is presumed constant over
the group
 ** while B and C may vary at each step.  Collating sequences are not used.
 */
static void groupReplaceStep(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
  ) {

  const unsigned char *zStr; /* The input string A */
  const unsigned char *zPattern; /* The pattern string B */
  const unsigned char *zRepl; /* The replacement string C */
  unsigned char *zOut; /* The output */
  int nStr; /* Size of zStr */
  int nPattern; /* Size of zPattern */
  int nRepl; /* Size of zRep */
  i64 nOut; /* Maximum size of zOut */
  int loopLimit; /* Last zStr[] that might match zPattern[] */
  int i, j; /* Loop counters */

  assert(argc == 3);
  UNUSED_PARAMETER(argc);
  zStr = sqlite3_value_text(argv[0]);
  if (zStr == 0) return;
  nStr = sqlite3_value_bytes(argv[0]);
  assert(zStr == sqlite3_value_text(argv[0])); /* No encoding change */
  zPattern = sqlite3_value_text(argv[1]);
  if (zPattern == 0) {
assert(sqlite3_value_type(argv[1]) == SQLITE_NULL
  || sqlite3_context_db_handle(context)->mallocFailed);

return;
  }
  if (zPattern[0] == 0) {
assert(sqlite3_value_type(argv[1]) != SQLITE_NULL);
sqlite3_result_value(context, argv[0]);
return;
  }
  nPattern = sqlite3_value_bytes(argv[1]);
  assert(zPattern == sqlite3_value_text(argv[1])); /* No encoding change */
  zRepl = sqlite3_value_text(argv[2]);
  if (zRepl == 0) return;
  nRepl = sqlite3_value_bytes(argv[2]);
  assert(zRepl == sqlite3_value_text(argv[2]));

  StrRepl* pStrRepl = (StrRepl*) sqlite3_aggregate_context(context, sizeof
(StrRepl));
  if (pStrRepl) {
if (pStrRepl->replError) return;
if (!pStrRepl->zStr) {
  if (sqlite3_value_type(argv[0]) == SQLITE_NULL
|| sqlite3_value_type(argv[1]) == SQLITE_NULL) return;
  pStrRepl->nStr = sqlite3_value_bytes(argv[0]);
  pStrRepl->zStr = contextMalloc(context,pStrRepl->nStr);

memcpy((void*)pStrRepl->zStr,sqlite3_value_text(argv[0]),pStrRepl->nStr);
/* No encoding change */
}
zPattern = sqlite3_value_text(argv[1]);
nPattern = sqlite3_value_bytes(argv[1]);
assert(zPattern == sqlite3_value_text(argv[1])); /* No encoding change
*/
if (0 == pStrRepl->nStr || 0 == nPattern) return;
zRepl = sqlite3_value_text(argv[2]);
nRepl = sqlite3_value_bytes(argv[2]);
if (0 == zRepl) return;
nOut = pStrRepl->nStr + 1;
assert(nOut < SQLITE_MAX_LENGTH);
zOut = contextMalloc(context, (i64) nOut);
if (0 == zOut) return;
loopLimit = pStrRepl->nStr - nPattern;
for (i = j = 0; i <= loopLimit; i++) {
  if (pStrRepl->zStr[i] != zPattern[0] || memcmp(&(pStrRepl->zStr)[i],
zPattern, nPattern)) {
zOut[j++] = pStrRepl->zStr[i];
  } else {
u8 *zOld;
sqlite3 *db = sqlite3_context_db_handle(context);
nOut += nRepl - nPattern;
testcase(nOut - 1 == db->aLimit[SQLITE_LIMIT_LENGTH]);
testcase(nOut - 2 == db->aLimit[SQLITE_LIMIT_LENGTH]);
if (nOut - 1 > db->aLimit[SQLITE_LIMIT_LENGTH]) {
  pStrRepl->replError = STRREPL_TOOBIG;
  sqlite3_free(zOut);
  return;
}
zOld = zOut;
zOut = sqlite3_realloc64(zOut, (int) nOut);
if (zOut == 0) {
  pStrRepl->replError = STRREPL_NOMEM;
  sqlite3_free(zOld);
  return;
}
memcpy([j], zRepl, nRepl);
j += nRepl;
i += nPattern - 1;
  }
}
assert(j + pStrRepl->nStr - i + 1 == nOut);
memcpy([j], >zStr[i], pStrRepl->nStr - i);
j += pStrRepl->nStr - i;
assert(j <= nOut);
zOut[j] = 0;
void* pFree = (void*)pStrRepl->zStr;
pStrRepl->zStr = zOut;
pStrRepl->nStr = nOut;
sqlite3_free(pFree);
  }
}

static void groupReplaceFinalize(sqlite3_context *context) {
  const char **pzVal;
  StrRepl* pStrRepl = sqlite3_aggregate_context(context, 0);
  if (pStrRepl) {
if (pStrRepl->replError == STRREPL_TOOBIG) {
  sqlite3_result_error_toobig(context);
} else if (pStrRepl->replError == STRREPL_NOMEM) {
  sqlite3_result_error_nomem(context);
} else {
  sqlite3_result_text(context, pStrRepl->zStr, -1, sqlite3_free);
}
  }
}


On Tue, Jan 2, 2018 at 4:57 PM, Scott Robison <sc...@casaderobison.com>
wrote:

> On Tue, Jan 2, 2018 at 5:46 PM, petern <peter.nichvolo...@gmail.com>
>

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

[petern]

Hi Scott.

>Are there other aggregate functions that take multiple arguments?

Absolutely.  I've got a few in my code which deserialize table rows into
runtime objects.  Fortunately, the DISTINCT filter makes no sense in that
use case, so I didn't bump into this issue myself.

If you're looking for a genuine hypothetical DISTINCT filter aggregate
case, consider an aggregate which computes statistics about matrices.  In
some mode, such a aggregate could legitimately be invoked with a DISTINCT
filter.   Obviously the nested query workaround fixes that case too.

Given the nested workaround is always possible, short circuit optimizing
the SELECT syntax makes sense but it would be nice to know if that's the
only rationale.

There is also the sensible expectation of group_concat() to have rows
supplied in controlled order, by nested SELECT if needed.  The expectation
of a controlling nested SELECT is definitely already there.

Peter

On Tue, Jan 2, 2018 at 4:12 PM, Scott Robison <sc...@casaderobison.com>
wrote:

> On Tue, Jan 2, 2018 at 4:15 PM, petern <peter.nichvolo...@gmail.com>
> wrote:
> > Hi Tony.  Good. Yes, simpler test case is always better when posting
> > possible bugs.
> >
> > Unfortunately, as Cezary points out, this error is by design (from
> > select.c):
> >
> >if( pFunc->iDistinct>=0 ){
> >   Expr *pE = pFunc->pExpr;
> >   assert( !ExprHasProperty(pE, EP_xIsSelect) );
> >   if( pE->x.pList==0 || pE->x.pList->nExpr!=1 ){
> > sqlite3ErrorMsg(pParse, "DISTINCT aggregates must have exactly
> one "
> >"argument");
> > pFunc->iDistinct = -1;
> >   }else{
> >
> > It would be interesting to understand the harm avoided by disallowing
> > DISTINCT scope of all the aggregate parameters.   Probably slower, but
> what
> > else?  Usually, there is a comment in the source but not for this one.
>
> I'm not the guy who wrote it or decided how it should work, but it
> seems to me that "group_concat(distinct x,y)" would not work
> intuitively if y is a variable, though (somewhat surprisingly to me)
> it works:
>
> sqlite> create table t(x,y);
> sqlite> insert into t values (1,','),(1,'+'),(2,'.'),(2,'-');
> sqlite> select group_concat(x,y) from t;
> 1+1.2-2
> sqlite> select group_concat(distinct x,y) from t;
> Error: DISTINCT aggregates must have exactly one argument
> sqlite> select group_concat(x,y) from (select distinct x, y from t);
> 1+1.2-2
>
> Are there other aggregate functions that take multiple arguments? I
> can't find any examples online of aggregates that take more than one,
> which seems like the normal way something like this would be done.
> ___
> 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] GROUP_CONCAT with DISTINCT bug

[petern]

Hi Tony.  Good. Yes, simpler test case is always better when posting
possible bugs.

Unfortunately, as Cezary points out, this error is by design (from
select.c):

   if( pFunc->iDistinct>=0 ){
  Expr *pE = pFunc->pExpr;
  assert( !ExprHasProperty(pE, EP_xIsSelect) );
  if( pE->x.pList==0 || pE->x.pList->nExpr!=1 ){
sqlite3ErrorMsg(pParse, "DISTINCT aggregates must have exactly one "
   "argument");
pFunc->iDistinct = -1;
  }else{

It would be interesting to understand the harm avoided by disallowing
DISTINCT scope of all the aggregate parameters.   Probably slower, but what
else?  Usually, there is a comment in the source but not for this one.

Peter


On Tue, Jan 2, 2018 at 2:54 PM, Tony Papadimitriou <to...@acm.org> wrote:

> Even simpler, then...
> select group_concat(distinct 1,',');
>
> -Original Message- From: petern
> Simpler one line test case also parses incorrectly:
>
> WITH t(c) AS (VALUES (1)) SELECT group_concat(DISTINCT c) FROM t;
> "group_concat(DISTINCT c)"
> 1
>
> WITH t(c) AS (VALUES (1)) SELECT group_concat(DISTINCT c,',') FROM t;
> Error: DISTINCT aggregates must have exactly one argument
>
>
> On Tue, Jan 2, 2018 at 12:36 PM, Tony Papadimitriou <to...@acm.org> wrote:
>
> create table t(s);
>> insert into t values ('A'),('A'),('B');
>>
>> select group_concat(s,', ') from t group by null;   -- OK
>> select group_concat(distinct s) from t group by null;   -- OK
>> select group_concat(distinct s,', ') from t group by null;  -- ERROR
>>
>> -- The moment the optional delimiter is given along with DISTINCT you get
>> this error:
>> -- Error: near line 6: DISTINCT aggregates must have exactly one argument
>>
>> -- Thank you.
>> ___
>> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

[petern]

Simpler one line test case also parses incorrectly:

WITH t(c) AS (VALUES (1)) SELECT group_concat(DISTINCT c) FROM t;
"group_concat(DISTINCT c)"
1

WITH t(c) AS (VALUES (1)) SELECT group_concat(DISTINCT c,',') FROM t;
Error: DISTINCT aggregates must have exactly one argument


On Tue, Jan 2, 2018 at 12:36 PM, Tony Papadimitriou  wrote:

> create table t(s);
> insert into t values ('A'),('A'),('B');
>
> select group_concat(s,', ') from t group by null;   -- OK
> select group_concat(distinct s) from t group by null;   -- OK
> select group_concat(distinct s,', ') from t group by null;  -- ERROR
>
> -- The moment the optional delimiter is given along with DISTINCT you get
> this error:
> -- Error: near line 6: DISTINCT aggregates must have exactly one argument
>
> -- Thank you.
> ___
> 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] Capturing groups for regexp.c Check-in [3d6fba62] ?

[petern]

Indeed, but JavaScript and awk also have first class loop accessible
variables to make up for the limitations in their respective regex parsers.

About linear time. Are you saying it is slower than linear time to compile
a group captured regex or that it is impossible to efficiently reuse the
compiled object in the body of a complementary UDF in the same statement?

All sorts of ad hoc parsing functions are possible and I use quite a few in
my code.  However, as I pointed out in the carefully worked test case
linked below, there is presently no reliable way to share that parsed state
among functions with different names, in different columns, or of column
valued objects using the thread safe auxdata API.  Those improvements in
the auxdata API alone would be valuable without the regexp capture
capability.

https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg107041.html

BTW, it is not only my possibly eccentric boutique code that is running
into this problem:

https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg107045.html



On Mon, Jan 1, 2018 at 10:44 AM, Richard Hipp <d...@sqlite.org> wrote:

> On 1/1/18, petern <peter.nichvolo...@gmail.com> wrote:
> > Richard.  Please consider adding capturing groups during your upgrade of
> > the regexp.c matching capability.
>
> I did consider that.  It seems hard to do in linear time.  I also
> notice that neither JavaScript nor AWK support that capability.
>
> --
> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Capturing groups for regexp.c Check-in [3d6fba62] ?

[petern]

Richard.  Please consider adding capturing groups during your upgrade of
the regexp.c matching capability.

In addition to the adding a powerful new capability to all SQLite
expressions, it would be very instructive to see how your code obtains the
cached object for a pair of captured group accessors such as
regexp_group_count(R) and regexp_group(R,I) where R is the common regexp
and I is the index of the captured group text to be retrieved.

Peter
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Efficient query to count number of leaves in a DAG.

[petern]

Shane.  I sent you a query to work with the crippled schema and index you
proposed for TABLE edges.
Clemens then explicitly suggested you correct the schema to have use of
automatic covering index.

>CREATE TABLE edges(parent not null references nodes, child not null
>references nodes, primary key(parent, child));

Try your leaf counter again - after making the schema changes Clemens
suggested.

Peter


On Mon, Jan 1, 2018 at 8:13 AM, Shane Dev  wrote:

> Hi,
>
> I want to the count the number of leaves (descendants without children) for
> each node in a DAG
>
> DAG definition -
>
> CREATE TABLE nodes(id integer primary key, description text);
> CREATE TABLE edges(parent not null references nodes, child not null
> references nodes, primary key(parent, child));
>
> My query -
>
> CREATE VIEW v_count_leaves as with recursive r(id, top) as (
> select id, id from nodes
> union all
> select t.id, top from nodes as t, edges as e, r where e.parent=r.id and
> t.id
> =e.child)
> select top, count(*) from r where top<>id and id not in (select parent from
> edges where parent=id) group by top;
>
> It seems to work but is complex to understand and debug despite my aim to
> keep it simple as possible, but more importantly - it is very slow when
> there are more than a few thousand nodes and edges.
>
> It there a more efficient (and ideally simpler) way?
> ___
> 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] Can select * from table where not exists (subquery) be optimized?

[petern]

This query will use the index you proposed.

SELECT * FROM nodes NATURAL JOIN (SELECT parent AS id FROM edges WHERE
parent NOT IN (SELECT child FROM edges));

Peter

On Sun, Dec 31, 2017 at 6:14 PM, Shane Dev  wrote:

> Hello,
>
> I have a directed acyclic graph defined as follows -
>
> sqlite> .sch
> CREATE TABLE nodes(id integer primary key, description text);
> CREATE TABLE edges(parent not null references nodes, child not null
> references nodes, primary key(parent, child));
>
> Now I want to find the "roots" of the graph - i.e nodes which are not
> children of other nodes -
>
> select * from nodes where not exists (select * from edges where child=
> nodes.id);
>
> This works but is very slow when there are a million nodes and edges.
>
> Looking at the query plan -
>
> sqlite> explain query plan select * from nodes where not exists (select *
> from edges where child=nodes.id);
> selectidorder   fromdetail
> 0   0   0   SCAN TABLE nodes
> 0   0   0   EXECUTE CORRELATED SCALAR SUBQUERY 1
> 1   0   0   SCAN TABLE edges
>
> I thought an index on edges(child) might help
>
> sqlite> CREATE INDEX iedges on edges(child);
>
> but it didn't -
>
> sqlite> explain query plan select * from nodes where not exists (select *
> from edges where child=nodes.id);
> selectidorder   fromdetail
> 0   0   0   SCAN TABLE nodes
> 0   0   0   EXECUTE CORRELATED SCALAR SUBQUERY 1
> 1   0   0   SCAN TABLE edges
>
> Is there any way to speed it up?
> ___
> 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] Dan. Latest zipfile.c checkin feedback

[petern]

I tried the new write feature.  Not sure if intended but read back row
order is somewhat unintuitive.  Is there an easy way to create a functional
archive read back order without introducing sortable name prefix artifacts
or an index file entry?

CREATE VIRTUAL TABLE newzip USING zipfile('new.zip');
INSERT INTO newzip(name,data) VALUES ('row1','row1 data'),('row2','row2
data');

SELECT * FROM newzip;
name,mode,mtime,sz,data,method
row2,0,4039286400,0,"row2 data",0
row1,0,4039286400,0,"row1 data",0

A sensible default for mtime could also be helpful.   Also, for compiling,
it couldn't hurt to add a modest comment about how to link to the right
zlib library on the major platforms.

Since the library is now linked, are you planning on decompressing data
automatically(or by module parameter flag) in a near release?  It doesn't
decompress data automatically from my tests.

Aside from crashing when sensible arguments aren't provided for name or
data,  the write upgrade and inclusion of compression seems very usable.

Creating the table suggests a related question.  Forgive my ignorance if it
was debated before.  Why can't the normal table name syntax of
INSERT/UPDATE support eponymous vtable module arguments?   Using this
example for instance:

INSERT INTO newzip('new.zip')(name,data) VALUES ...

Or something more efficiently parsed like square brackets?

INSERT INTO newzip['new.zip'](name,data) VALUES ...


Peter
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Dan: zipfile.c FYI data column quirk

[petern]

Hi Dan. $ xxd row2.txt
000: 7465 7874 206f 6620 6669 6c65 2072 6f77  text of file row
010: 322e 7478 740a   2.txt.

0a.  Normally dependable Gedit, used for such tinkering, is putting in that
linefeed.  From my regular IDE I also see it.
Sorry for the possible false alarm.  Good news is the new extension
compiles easy and works well.
Thanks for everything.
Peter







On Wed, Dec 27, 2017 at 12:40 PM, Dan Kennedy <danielk1...@gmail.com> wrote:

> On 12/28/2017 03:20 AM, petern wrote:
>
>> sqlite> load zipfile.so
>>
>> sqlite> SELECT * FROM zipfile('rows.zip');
>> name,mode,mtime,sz,data,method
>> row1.txt,33204,1514396814,22,"text of file row1.txt
>> ",0
>> row2.txt,33204,1514396416,22,"text of file row2.txt
>> ",0
>>
>> --Extra newline is introduced for some reason.
>> --Added explicit newline to end of row1.txt, then got this:
>>
>> sqlite> SELECT * FROM zipfile('rows.zip');
>> name,mode,mtime,sz,data,method
>> row1.txt,33204,1514397988,23,"text of file row1.txt
>>
>> ",0
>> row2.txt,33204,1514396416,22,"text of file row2.txt
>> ",0
>>
>> #testfile
>> $ zip -0 rows.zip row1.txt row2.txt
>>
>> $ zipinfo rows.zip
>> Archive:  rows.zip
>> Zip file size: 355 bytes, number of entries: 2
>> -rw-rw-r--  3.0 unx   23 bx stor 17-Dec-27 11:55 row1.txt
>> -rw-rw-r--  3.0 unx   22 bx stor 17-Dec-27 09:40 row2.txt
>> 2 files, 45 bytes uncompressed, 45 bytes compressed:  0.0%
>>
>
> What does this command return?
>
>   xxd row2.txt
>
> Thanks,
> Dan.
>
>
> ___
> 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] Dan: zipfile.c FYI data column quirk

[petern]

sqlite> load zipfile.so

sqlite> SELECT * FROM zipfile('rows.zip');
name,mode,mtime,sz,data,method
row1.txt,33204,1514396814,22,"text of file row1.txt
",0
row2.txt,33204,1514396416,22,"text of file row2.txt
",0

--Extra newline is introduced for some reason.
--Added explicit newline to end of row1.txt, then got this:

sqlite> SELECT * FROM zipfile('rows.zip');
name,mode,mtime,sz,data,method
row1.txt,33204,1514397988,23,"text of file row1.txt

",0
row2.txt,33204,1514396416,22,"text of file row2.txt
",0

#testfile
$ zip -0 rows.zip row1.txt row2.txt

$ zipinfo rows.zip
Archive:  rows.zip
Zip file size: 355 bytes, number of entries: 2
-rw-rw-r--  3.0 unx   23 bx stor 17-Dec-27 11:55 row1.txt
-rw-rw-r--  3.0 unx   22 bx stor 17-Dec-27 09:40 row2.txt
2 files, 45 bytes uncompressed, 45 bytes compressed:  0.0%

Perhaps newline is a quirk of zip itself.  String "text of file row1.txt"
is 21 characters.

Peter
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ubuntu error with 3210000

[petern]

Are your linking switches the same in both release and debug?  In
particular, -ldl (dynamic libraries) ?

On Mon, Dec 25, 2017 at 5:31 AM, raanan barzel  wrote:

> With sqlite 3.21. embedded in my application, building on Windows (10,
> using VS 2017 Community) is error-free.
>
> However, building the same application on Ubuntu 16.04 in Release mode is
> error-free, while Debug mode produces errors:
>
> *** /usr/bin/ld: GCCDebug/sqlite3.o: undefined reference to symbol
> 'dlclose@@GLIBC_2.2.5'
>
> *** //lib/x86_64-linux-gnu/libdl.so.2: error adding symbols: DSO missing
> from command line
>
> Not being deeply involved with Linux, I have no idea what needs to be
> done. Any advice ?
>
> Thanks
>
>
> ---
> L'absence de virus dans ce courrier électronique a été vérifiée par le
> logiciel antivirus Avast.
> https://www.avast.com/antivirus
>
> ___
> 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] Changed behaviour or bug using field alias in 3.21.0

[petern]

Radovan.  Thank you for sticking to your guns.  Your appeal to expected
behavior under other DB engines was also a very good post.  I see the usual
suspects of the echo chamber uselessly piled on against you to clog the
forum nevertheless.  That happens too frequently.

BTW, here is a simpler test suite which pinpoints the problem:

SELECT d FROM (SELECT c AS d FROM (SELECT 1 AS c));
d
1

CREATE TABLE test AS SELECT d FROM (SELECT c AS d FROM (SELECT 1 AS c));
SELECT * FROM test;
c
1

CREATE TABLE test AS SELECT c AS d FROM (SELECT 1 AS c);
SELECT * FROM test;
d
1

And still one more that illustrates the contradictory and unstable
behavior. Use '*' instead of 'd' and it works as expected.

CREATE TABLE test AS SELECT * FROM (SELECT c AS d FROM (SELECT 1 AS c));
SELECT * FROM test;
d
1

Obviously the most immediate well formed alias 'd' should be carried
through irrespective of nesting.

FYI, there was a checkin just now with some changes to track intermediate
aliases:

http://www.sqlite.org/src/info/5efd854fe2147033

Peter


On Fri, Dec 22, 2017 at 11:34 AM, Radovan Antloga 
wrote:

> Thank you Klaus!
>
> Klaus Maas je 22.12.2017 ob 20:30 napisal:
>
>> Radovan is correct.
>> Executing the same command sequence in version 3.11.0 and 3.21.0 results
>> in different column names for table test2.
>> (Xubuntu 16.04 with sqlite3.11.0 and sqlite3.21.0)
>> I marked the results with '<='
>> Klaus
>>
>>
>> SQLite version 3.11.0 2016-02-15 17:29:24
>> Enter ".help" for usage hints.
>> Connected to a transient in-memory database.
>> Use ".open FILENAME" to reopen on a persistent database.
>> sqlite> .header on
>> sqlite>
>> sqlite> create table test(a int, b int);
>> sqlite> insert into test values (1,1);
>> sqlite>
>> sqlite> select d from (select c as d from (select a as c from test));
>> d
>> 1
>> sqlite>
>> sqlite> create table test2 as
>>...> select d from (select c as d from (select a as c from test));
>> sqlite> SELECT * FROM test2;
>> d  <=
>> 1
>> sqlite>
>>
>>
>>
>> SQLite version 3.21.0 2017-10-24 18:55:49
>> Enter ".help" for usage hints.
>> Connected to a transient in-memory database.
>> Use ".open FILENAME" to reopen on a persistent database.
>> sqlite> .header on
>> sqlite> create table test(a int, b int);
>> sqlite> insert into test values (1,1);
>> sqlite>
>> sqlite> select d from (select c as d from (select a as c from test));
>> d
>> 1
>> sqlite> create table test2 as
>>...> select d from (select c as d from (select a as c from test));
>> sqlite> SELECT * FROM test2;
>> a   <=
>> 1
>> sqlite>
>>
>> email signature Klaus Maas
>> 
>> On 2017-12-22 20:11, Radovan Antloga wrote:
>>
>>> Just try this sql-s:
>>>
>>> create table test(a int, b int);
>>> insert into test values (1,1);
>>>
>>> select d from (select c as d from (select a as c from test));
>>>
>>> will return name d.
>>>
>>> create table test2 as
>>> select d from (select c as d from (select a as c from test));
>>>
>>> will create table test2 with column name a.
>>>
>>> with PostgreSQL I get table test2 with name d.
>>>
>>> Thank you very much for your time!
>>>
>>>
>>> Richard Hipp je 22.12.2017 ob 20:06 napisal:
>>>
 On 12/22/17, Radovan Antloga  wrote:

> My point is you do not have to change anything regards
> to how select works. SELECT statement is working just
> great or OK or fine.
>
> CREATE TABLE AS
> SELECT 
>
> does not give same name as SELECT does.
>
> SELECT give name d
>
> CREATE TABLE AS SELECT
> create table with name a instead of d.
>
 Can you provide a simple test case for this behavior?


>>> ___
>>> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Odd question

[petern]

Re: Nelson "odd". This will make the desired (?) side effect happen:

.load eval.so
SELECT coalesce(eval('INSERT INTO table1 VALUES(a, b, c)'), 1) AS value;

If INSERT references columns from an outer scope then use printf() inside
the eval().
From a program, use https://sqlite.org/c3ref/load_extension.html instead of
".load".

The source of eval.c is in the SQLite source tree or
https://www.sqlite.org/src/artifact/f971962e92ebb8b0

That one is eval(X,Y) where Y is optional column separator.  If you need
the improved eval(X,Y,Z) where Z is an optional line separator, let me know
and I'll post that upgrade.

Peter


On Sat, Dec 16, 2017 at 1:04 PM, Keith Medcalf  wrote:

>
> Your method of achieving a solution to whatever problem you are trying to
> solve will not work.
>
> Perhaps you can state the problem you are trying to solve without making
> an assumption as to how to solve that problem, someone may be able to solve
> the problem with a workable solution rather than an unworkable solution.
>
> ---
> 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 Nelson, Erik - 2
> >Sent: Saturday, 16 December, 2017 12:53
> >To: SQLite mailing list
> >Subject: [sqlite] Odd question
> >
> >For unfortunate reasons, I need a query that does an insert and also
> >returns at least one row... for example, something along the lines of
> >
> >Select 1 as value from (insert into table1 values(a, b, c))
> >
> >Or
> >
> >Select coalesce((insert into table1 values(a, b, c)), 1) as value
> >
> >I've tried a number of options but haven't been able to get anything
> >to work.  Is it possible?
> >
> >
> >-
> >-
> >This message, and any attachments, is for the intended recipient(s)
> >only, may contain information that is privileged, confidential and/or
> >proprietary and subject to important terms and conditions available
> >at http://www.bankofamerica.com/emaildisclaimer.   If you are not the
> >intended recipient, please delete this message.
> >___
> >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] API, sqlite3_value_bytes and sqlite3_value_blob...

[petern]

Dave. The documentation contains many such catch-all statements which do
not reflect a full decision tree.  The usual cover story will either be (I
paraphrase) : 1. "that's an implementation detail" or 2. "it might change
later, so the documentation can only make a short blanket statement".

It is far more likely that spelling and grammatical errors you report of
the documentation will be corrected.

The current decision tree of the particular catch-all documentation comment
you found is in vdbemem.c at the comment and function body listing below.
Ultimately there is only the source code.  Getting used to reading it for
yourself will probably save you a lot of time.

int sqlite3ValueBytes(sqlite3_value *pVal, u8 enc){
  Mem *p = (Mem*)pVal;
  assert( (p->flags & MEM_Null)==0 || (p->flags & (MEM_Str|MEM_Blob))==0 );
  if( (p->flags & MEM_Str)!=0 && pVal->enc==enc ){
return p->n;
  }
  if( (p->flags & MEM_Blob)!=0 ){
if( p->flags & MEM_Zero ){
  return p->n + p->u.nZero;
}else{
  return p->n;
}
  }
  if( p->flags & MEM_Null ) return 0;
  return valueBytes(pVal, enc);
}

Peter

On Wed, Dec 13, 2017 at 8:38 AM, dave  wrote:

> I have a question regarding the API documention at
> http://sqlite.org/c3ref/value_blob.html, which states:
> "... the pointer returned from sqlite3_value_blob(), .. can be invalidated
> by a subsequent call to sqlite3_value_bytes(), ..."
> Is that statement still true?  I ask because I notice that the source of
> many of the extensions in 'sqlite/ext' seem to violate that advice.
>
> I first noticed this when I was recently working on fileio.c (e.g. line 73
> vs 77), but grepping through the source I find many other cases where the
> pointer is retrieved via  *_blob() or *.text() BEFORE invoking
> sqlite3_value_bytes().  E.g these source and line numbers:
> fts2_tokenizer.c:71, 72
> fts3_expr.c:1248, 1249
> fts3_tokenizer.c:78, 79
> fts3_tokenize_vtab.c:347, 348
> fts3_write.c:5290, 5291
> fts5_index.c:6270, 6271
> fts5_storage.c:735, 736
> fts5_tcl.c:547
> fts5_test_tok.c:375, 376
> fts5_vocab.c:607, 608; 612, 613; 616, 617
> (I stopped grepping at this point; this list is not comprehensive).
>
> Anyway, just wondered if the api documentation's advice is maybe
> out-of-date
> with current reality.  Thoughts/comments?
>
> Cheers!
> -dave
> ___
> 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] extracting domain names from website addresses efficiently

[petern]

Klaus.  The CTE manual with good examples is at
https://www.sqlite.org/lang_with.html
IgorT posted some good stuff about your problem using CTE.

FYI. TRIGGER is also recursive.  Could be more efficient if you have to
store them anyway:

CREATE TABLE domain(d TEXT);
CREATE TRIGGER domain_after_ins AFTER INSERT ON domain WHEN instr(NEW.d,'.')
  BEGIN INSERT INTO domain VALUES(substr(NEW.d,instr(NEW.d,'.')+1)); END;

PRAGMA recursive_triggers=1;
INSERT INTO domain VALUES ('a.b.c.d');
INSERT INTO domain VALUES('e.f.g.1.2');
SELECT * FROM domain;
d
--
a.b.c.d
b.c.d
c.d
d
e.f.g.1.2
f.g.1.2
g.1.2
1.2
2

Peter







On Mon, Dec 11, 2017 at 9:22 AM, Klaus Maas  wrote:

> Thank you, Igor, for your solution.
> I expected that I would be pointed to a recursive approach.
> Your solution is quite elegant in my opinion.
> My problem, however, is that I have not yet understood recursive CTE's.
> Could you give me some pointers for good resources to learn about them?
>
> Well, your code example is helping a lot in understanding them, although I
> am still a long way off producing something similar myself without
> assistance.
>
> There is 1 item in the last where condition of which I do not understand
> the purpose:
> (instr(subdomain, '.') = 0 and subdomain = long)
> This means that anything in the form of 'ftp://test/' would output the
> string between the two delimiters (:// and /), in this case 'test'.
> But that is not a domain name in the format domain.tld.
> (I am working under the assumption that table links is cleaned up contains
> valid links only with the protocol and :// prepended.)
> Or am I missing something?.
>
> Klaus
>
> 
> On 2017-12-11 14:59, Igor Tandetnik wrote:
>
>> Something like this (could likely be simplified further, but this should
>> give the idea):
>>
>> with recursive subdomains as (
>>   SELECT substr(link, instr(link, '://')+3, instr(substr(link,
>> instr(link, '://')+3), '/')-1) AS long,
>>  substr(link, instr(link, '://')+3, instr(substr(link,
>> instr(link, '://')+3), '/')-1) as subdomain
>>   FROM links
>> union all
>>   select long, substr(subdomain, instr(subdomain, '.') + 1)
>>   from subdomains
>>   where instr(subdomain, '.') > 0)
>> select * from subdomains
>> where (instr(subdomain, '.') = 0 and subdomain = long) OR
>>   (instr(subdomain, '.') > 0 and instr(substr(subdomain,
>> instr(subdomain, '.') + 1), '.')=0);
>>
>> The main point is to recursively build a table of all suffixes, then
>> select just the suffixes you want.
>>
>
> ___
> 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] Retrieving constraint name

[petern]

Igor/Cezary,

It is remarkable how 'struct Parse' already contains the constraint name as
Cezary pointed out.
->  Token constraintName;/* Name of the constraint currently being parsed */
But is not included in the 'struct FKey' linked list node that is reeled in
to produce columns in the PRAGMA report.
For the official release, presumably, test cases would have to be added in
addition to simply hooking it up as suggested.

In the meantime, parsing wouldn't be difficult even with primitive built-in
SQL string functions.  Consider how the constraint name must occur within
the comma delimited part of the well formed CREATE TABLE statement.  When
obtained from sqlite_master, the statement is guaranteed to be well
formed.  Simply examine each comma delimited candidate part.  If present,
the first word between keyword CONSTRAINT and keyword REFERENCES is the
constraint name.

Peter









On Sun, Dec 10, 2017 at 7:29 PM, Igor Korot  wrote:

> Hi,
>
> On Sun, Dec 10, 2017 at 8:30 PM, Cezary H. Noweta 
> wrote:
> > Hello,
> >
> > On 2017-12-11 01:04, Igor Korot wrote:
> >
> >> On Sun, Dec 10, 2017 at 5:01 PM, Cezary H. Noweta 
> >> wrote:
> >
> >
> >>> On 2017-12-10 07:21, Igor Korot wrote:
> >
> >
>  The CREATE TABLE statement supports the following syntax:
> 
>  CREATE TABLE( , CONSTRAINT  FOREIGN
>  KEY() REFERENCES (ref_column_list>);
> >
> >
>  [...] If not - does this mean that the only way to get the name is to
>  parse the
>  sql
>  from sqlite_master? Or there is a better way?
> >
> >
> >>> The answer is ``not''. Constraint names are ignored and disappearing
> >>> without
> >>> a trace except for ``CHECK'' constraint (the name is used to build an
> >>> error
> >>> message). Unparsed ``sql'' column of ``sqlite_master'' is the sole
> place
> >>> which contains an indirect info about ``FOREIGN KEY'' constraint's
> name.
> >
> >
> >> Thank you for confirming.
> >
> > You are welcome. BTW, SQLite parses SQL every time it creates a table
> > (by a SQL command or after an opening of BTree file) -- I believe there
> > is no better way. You do not need to parse SQL on your own (it is hard,
> > if not impossible, to establish a link between a name and a particular
> > constraint). All you need is to append ``char *'' field to ``struct
> > FKey'' and to inject a function ``build.c:sqlite3CreateForeignKey()'':
> > ``pParse->constraintName'' will contain the constraint's name (note
> > that the name is not dequoted -- you will have to dequote it; look at
> > ``build.c:sqlite3AddCheckConstraint()'' to know how assigning of a
> > constraint's name is done). This will allow you to build your own map of
> > ``FOREIGN KEY'' names. For example, if you want to expand ``PRAGMA
> > foreign_key_list'', go to ``pragma.c:sqlite3Pragma():case
> > PragTyp_FOREIGN_KEY_LIST:'' and append new FKey's field.
>
> Thank you, but I need to keep the official SQLite code.
>
>
> >
> >
> > -- best regards
> >
> > Cezary H. Noweta
> > ___
> > 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] Any chance of exposing INTEGER PRIMARY KEY metadata? Needed by many tools

[petern]

Hey Peter.  Good on you.  Lobbying for sensible fixes to the public API
does pay off sometimes.

There's a new branch in the timeline.  [Watch for a merge here:
https://www.sqlite.org/src/timeline?n=50 ]

https://www.sqlite.org/src/info/2494132a2b1221a4

  **   PRAGMA table_ipk()  **  ** If  has an INTEGER
PRIMARY KEY column that is an alias for  ** the ROWID, then return the
name of that column.  If  does not  ** have a ROWID alias, or
if it does not have a ROWID, or if  is  ** a view or virtual
table or if it does not exist, then return no rows.

The lesson here is that one may safely ignore the sometimes authoritative
sounding status quo mafia who occupy this mailing list but have no source
check-in credentials whatsoever.

On Mon, Nov 27, 2017 at 4:38 PM, Peter Halasz 
wrote:

> Disclaimer: I am not a developer on linq2db. Nor am I a developer on "DB
> Browser for SQLite", nor am I a developer of any of the other tools which
> help developers create SQLite databases and integrate them with their
> projects through UIs and APIs built on SQLite and its metadata.
>
> However, it's quite clear to me, as only a user of just a few of these
> tools and libraries, that they would be improved greatly if they had a
> direct way of querying which field in a SQLite table was acting as an alias
> of ROW ID.
>
> linq2db, for example, has no simple way of definitely telling whether a
> field is a true INTEGER PRIMARY KEY, so its developers have left it as an
> exercise for its library users to debug errors which come up as a result,
> and to manually "correct column definitions". Something which can result in
> much time wasted unnecessarily tracking down the bugs, finding special API
> calls to work around them, and a less than favorable view of SQLite for its
> documentation of these anomalies.
>
> linq2db could, in theory, fix this issue. But it would take a bunch of
> error-prone work to re-create SQLite's ROW ID rules, so they have chosen
> not to. They have simply chosen to ignore the weird behavior that comes
> form not differentiating an INTEGER PRIMARY KEY from an INT PRIMARY KEY. I
> don't blame them for having other priorities. After all, linq2db integrates
> with 12 other database engines. And this isn't about linq2db, it's about
> every tool that comes across the need for this metadata.
>
> Many other tools also have bugs or poor UX because they cannot access a
> simple bit of metadata. This could be solved quite easily if SQLite simply
> exposed this data.
>
> I was honestly stunned when I discovered this basic information, something
> which completely changes the behavior of a field, is not accessible to
> users.
>
> Please can SQLite developers make the ROW ID status of a field visible in
> future versions?
>
> It could be done either directly through a PRAGMA statement, or slightly
> less directly by exposing "isRowId()" or "collseq()" style methods as
> posted by peter.nichvolodov in the previous thread.
>
> I really hope there is not resistance this idea. It's really amazing to me
> that such a mature, battle-tested database engine does not fully expose its
> metadata and field behavior.
>
> Cheers
>
> Peter H.
> ___
> 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] How to use sqlite3_table_column_metadata?

[petern]

Hi Bart.  Questions like that can be answered by looking at the body of the
function in your copy of the source code.  In this case, the call will
accept null column name and then later set default values for fields like
so:

if( pCol ){
zDataType = sqlite3ColumnType(pCol,0);
zCollSeq = pCol->zColl;
notnull = pCol->notNull!=0;
primarykey  = (pCol->colFlags & COLFLAG_PRIMKEY)!=0;
autoinc = pTab->iPKey==iCol && (pTab->tabFlags & TF_Autoincrement)!=0;
  }else{
zDataType = "INTEGER";
primarykey = 1;
  }

That else block is originating your phantom values without specifying a
column name but I don't think there is any cosmic significance.  Earlier in
the same function body there are also some comments:

/* Find the column for which info is requested */
  if( zColumnName==0 ){
/* Query for existance of table only */
  }else{

Be thankful for comments!

Peter

On Mon, Nov 27, 2017 at 5:32 PM, Bart Smissaert <bart.smissa...@gmail.com>
wrote:

> One strange thing I noticed with this:
>
> If I pass a valid connection  and  valid table name but null for all other
> input parameters I get:
>
> return value:  0
> data type: INTEGER
> collation:   BINARY
> not null: 0
> part of primary key:  1
> auto-increment:0
>
> How does that work?
>
> RBS
>
> On Mon, Nov 27, 2017 at 10:13 AM, Bart Smissaert <bart.smissa...@gmail.com
> >
> wrote:
>
> > > My advise to the VB guy would be to load the extension instead of
> trying
> > to
> > get VB to marshal pointers from the C API.
> >
> > ?? I do load the extension although it is compiled std_call, so VB6 can
> > talk to it.
> >
> > RBS
> >
> >
> > On Mon, Nov 27, 2017 at 12:59 AM, petern <peter.nichvolo...@gmail.com>
> > wrote:
> >
> >> I pasted that SQL into a SQLite shell and tried it out.
> >>
> >> First off,  Affinity (aka pragma table_info.type) column is case
> collated,
> >> so the LIKE operator should be used:
> >>
> >> select O.*
> >>   from SysColumns as O
> >>  where ObjectType == 'table'
> >>and IsPrimaryKey == 1
> >>and Affinity LIKE 'INTEGER'
> >>and not exists (select 1
> >>  from SysColumns as I
> >> where I.ObjectType == O.ObjectType
> >>   and I.ObjectName == O.ObjectName
> >>   and I.IsPrimaryKey > 1);
> >>
> >> [Side question. Do you find the '==' operator more readable/informative
> >> than the plain '=' SQL convention?]
> >>
> >> The main problem is still AUTOINCREMENT. You may not use AI, but a tool
> >> that reads others' schemas has to deal with that possibility.  As well,
> >> the
> >> system table sqlite_sequence will have no rows about an AI column when
> >> that
> >> AI table has no rows.  Therefore, the only general way, without calling
> >> the
> >> internal parser as I did in the extension, is to partly parse the SQL of
> >> the CREATE TABLE statement.
> >>
> >> Partial parsing isn't too hard I suppose.  There are a couple facts
> about
> >> the well formed CREATE statement that help.
> >>
> >> 1. The keywords PRIMARY KEY must be two words.
> >> 2. The keyword "AUTOINCREMENT" must come after "KEY" and before the next
> >> comma or closing bracket.
> >>
> >> From those facts it should be feasible to deduce the AUTOINCREMENT
> status
> >> by use of the instr() and substr() functions.
> >>
> >> Or, one could simply use the native -std=c90 style SQLite extension I
> >> posted for the other Peter...
> >>
> >> My advise to the VB guy would be to load the extension instead of trying
> >> to
> >> get VB to marshal pointers from the C API.  Though, I suppose that isn't
> >> the worst idea from recent posts.  Reading all the rows of a table in
> >> Python to get the count() has to be the best one.  In fact, that one is
> a
> >> classic job interview question.
> >>
> >>
> >>
> >>
> >>
> >>
> >> On Sun, Nov 26, 2017 at 1:41 PM, Keith Medcalf <kmedc...@dessus.com>
> >> wrote:
> >>
> >> > Not an answer to what is wrong with your call to get the metadata,
> >> > however, the information is all available from SQL.
> >> >
> >> > Data Catalog Views:
> >> >
> >> &g

Re: [sqlite] sqlite3_get_auxdata() defect

[petern]

Clemens,

Indeed.  It doesn't work as a basis for reliable performance improvement of
user code.  Basically, that API is practically useless except for the
narrow in-house use case for which it was created.  And yes, I already make
heavy use of the natural aggregate cache functionality but for the
limitation of only producing only one final output value.  A statement
level function cache obviously has more general use.

So, as I pointed out to Ben, the other poster who also discovered the
limitations of sqlite3_get_auxdata(), it would be trivial to get the
desired statement level cache speedup if only there were a per-connection
version of the API, say sqlite3_set_data(sqlite3*,void*) and void*
sqlite3_get_data(sqlite3*).

A connection level data area is definitely missing from the API.  To
implement one's own thread aware global connection to cache map wrapper
seems a lot to ask when it could so easily be added to the SQLite API.
Such an SQLite API implementation has a huge advantage in already having an
internal hash map implementation and total awareness of current operational
threading mode of SQLite.

That being said, there is still a documentation bug that is wasting
people's time.  A face value reading of the documentation at
https://www.sqlite.org/c3ref/get_auxdata.html gives the impression that
caching is solely dependent on the index and value of the function argument
when in fact there are 3 more very important and unstated limitations.
Documenting those limitations would only add one or two sentences but would
save people a lot of time.

Peter





On Mon, Nov 27, 2017 at 1:27 PM, Clemens Ladisch <clem...@ladisch.de> wrote:

> petern wrote:
> > Evidently the sqlite3_get_auxdata() API is very buggy.
>
> The documentation says that
> | under some circumstances the associated metadata may be preserved.
>
> You have found circumstances where the metadata is not preserved.
>
> This is not a bug.
>
> I can see that you are unhappy, but that is just because you are trying
> to make it do something that it was never intended to do.
>
> If you want a function with mutable state over multiple invocations,
> do not use sqlite3_set_auxdata() but an aggregation function, or store
> the data elsewhere.
>
>
> Regards,
> Clemens
> ___
> 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] sqlite3_get_auxdata() defect

[petern]

Clemens.

Here it that example again, but with the argument factored out so there is
no question about value or memory location of the cache argument target.
Remarkably, caching doesn't work at all when one does this!

sqlite> WITH arg(x) AS (SELECT 'x'), t(id) AS (VALUES(1),(2)) SELECT
id,auxint(x,id),auxint(x,1),auxint(x,null),auxint(x) FROM arg,t;
id  auxint(x,id)  auxint(x,1)  auxint(x,null)  auxint(x)
--    ---  --  --
1   1 1
2   2 1

Compare with the original example using literal value for the cached
argument:

sqlite> WITH t(id) AS (VALUES(1),(2)) SELECT
id,auxint('x',id),auxint('x',1),auxint('x',null),auxint('x') FROM t;
id  auxint('x',id)  auxint('x',1)  auxint('x',null)  auxint('x')
--  --  -    ---
1   1   1
2   3   2

I think the poster Ben, from last week, bumped into the above bug and was
seeking advice about why his function argument cache was being flushed for
every row.

Evidently the sqlite3_get_auxdata() API is very buggy.

Clemens, I'm a bit surprised you can't see or don't want to see the defects
here.  Very curious to read your reply.

For completeness, here is the testing extension auxint.c once again:
--
/*
Further to https://sqlite.org/c3ref/get_auxdata.html
UDF auxint(X,I) - stores I plus the function argument X's current metadata
INT and returns the new value.
UDF auxint(X) - returns the current value of the function meta-data INT
associated with X.
*/
#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1
static void reclaim(void*p) {if (p) sqlite3_free(p);}
static void auxint(sqlite3_context *context, int argc, sqlite3_value
**argv) {
  int* pInt = sqlite3_get_auxdata(context, 0);
  if (argc>1 && sqlite3_value_type(argv[1]) != SQLITE_NULL){
if (!pInt) {pInt = sqlite3_malloc(sizeof(int)); *pInt=0;
sqlite3_set_auxdata(context, 0, pInt, reclaim);}
*pInt += sqlite3_value_int(argv[1]);
  }
  if (pInt) sqlite3_result_int(context,*pInt);
}
#ifdef _WIN32
__declspec(dllexport)
#endif
int sqlite3_auxint_init(sqlite3 *db, char **pzErrMsg, const
sqlite3_api_routines *pApi) {
  SQLITE_EXTENSION_INIT2(pApi);
  int rc = sqlite3_create_function(db, "auxint", 1, SQLITE_UTF8, 0, auxint,
0, 0);
  if (SQLITE_OK == rc) sqlite3_create_function(db, "auxint", 2,
SQLITE_UTF8, 0, auxint, 0, 0);
  return rc;
}
--

On Mon, Nov 27, 2017 at 1:15 AM, Clemens Ladisch <clem...@ladisch.de> wrote:

> petern wrote:
> > So, at the very least, the documentation at
> > https://sqlite.org/c3ref/get_auxdata.html is woefully incomplete
> because:
> >
> > 1. The identical function in a different column of the same SELECT has a
> > separate meta-data cache.
>
> This is an implementation detail of the current version.
>
> > 2. Functions with identical names and different arg count, yet sharing
> > common identical meta data argument at the specified argument index, use
> a
> > separate meta-data cache.
>
> This is an implementation detail of the current version.
>
> > Ideally, these problems should be fixed instead of simply being
> documented
> > away.  And, if they are fixed, SELECT columns can have an efficient,
> > thread-safe, and powerful new expressiveness for the idioms of running
> > sums, running products, running concatenations, synthetic id columns, and
> > so on.
>
> The auxdata is intended as a cache, so it must depend only on the
> argument value, not on any other state.
>
> If you want to do something different, store the data elsewhere.  And
> there is not much of a guarantee when or how often user-defined functions
> are called, so even with your desired changes to the auxdata mechanism,
> you would not get the desired result.
>
>
> Regards,
> Clemens
> ___
> 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] sqlite3_get_auxdata() defect

[petern]

>The auxdata is intended as a cache, so it must depend only on the
>argument value, not on any other state.

I'm not following.  Where is the argument different in my example.  The
zeroth argument is always 'x'.  Are you saying the argument has to be the
same memory location as well as the same value?

Take another look at the example.



On Mon, Nov 27, 2017 at 1:15 AM, Clemens Ladisch <clem...@ladisch.de> wrote:

> petern wrote:
> > So, at the very least, the documentation at
> > https://sqlite.org/c3ref/get_auxdata.html is woefully incomplete
> because:
> >
> > 1. The identical function in a different column of the same SELECT has a
> > separate meta-data cache.
>
> This is an implementation detail of the current version.
>
> > 2. Functions with identical names and different arg count, yet sharing
> > common identical meta data argument at the specified argument index, use
> a
> > separate meta-data cache.
>
> This is an implementation detail of the current version.
>
> > Ideally, these problems should be fixed instead of simply being
> documented
> > away.  And, if they are fixed, SELECT columns can have an efficient,
> > thread-safe, and powerful new expressiveness for the idioms of running
> > sums, running products, running concatenations, synthetic id columns, and
> > so on.
>
> The auxdata is intended as a cache, so it must depend only on the
> argument value, not on any other state.
>
> If you want to do something different, store the data elsewhere.  And
> there is not much of a guarantee when or how often user-defined functions
> are called, so even with your desired changes to the auxdata mechanism,
> you would not get the desired result.
>
>
> Regards,
> Clemens
> ___
> 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] sqlite3_get_auxdata() defect

[petern]

Consider the simplest possible pair of argument metadata test functions as
follows.
--
/*
Further to https://sqlite.org/c3ref/get_auxdata.html
UDF auxint(X,I) - stores I plus the function argument X's current metadata
INT and returns the new value.
UDF auxint(X) - returns the current value of the function meta-data INT
associated with X.
*/
#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1
static void reclaim(void*p) {if (p) sqlite3_free(p);}
static void auxint(sqlite3_context *context, int argc, sqlite3_value
**argv) {
  int* pInt = sqlite3_get_auxdata(context, 0);
  if (argc>1 && sqlite3_value_type(argv[1]) != SQLITE_NULL){
if (!pInt) {pInt = sqlite3_malloc(sizeof(int)); *pInt=0;
sqlite3_set_auxdata(context, 0, pInt, reclaim);}
*pInt += sqlite3_value_int(argv[1]);
  }
  if (pInt) sqlite3_result_int(context,*pInt);
}
#ifdef _WIN32
__declspec(dllexport)
#endif
int sqlite3_auxint_init(sqlite3 *db, char **pzErrMsg, const
sqlite3_api_routines *pApi) {
  SQLITE_EXTENSION_INIT2(pApi);
  int rc = sqlite3_create_function(db, "auxint", 1, SQLITE_UTF8, 0, auxint,
0, 0);
  if (SQLITE_OK == rc) sqlite3_create_function(db, "auxint", 2,
SQLITE_UTF8, 0, auxint, 0, 0);
  return rc;
}
--
Below is a simple query of two rows which both set and get the INT
meta-data for arg0 named 'x' in various ways:

sqlite> .load auxint.so
sqlite> WITH t(id) AS (VALUES(1),(2)) SELECT
id,auxint('x',id),auxint('x',1),auxint('x',null),auxint('x') FROM t;
id  auxint('x',id)  auxint('x',1)  auxint('x',null)  auxint('x')
--  --  -    ---
1   1   1
2   3   2

Expected output:

id  auxint('x',id)  auxint('x',1)  auxint('x',null)  auxint('x')
--  --  -    ---
1   1   2   2   2
2   4   5   5   5

So, at the very least, the documentation at
https://sqlite.org/c3ref/get_auxdata.html is woefully incomplete because:

1. The identical function in a different column of the same SELECT has a
separate meta-data cache.

2. Functions with identical names and different arg count, yet sharing
common identical meta data argument at the specified argument index, use a
separate meta-data cache.

Ideally, these problems should be fixed instead of simply being documented
away.  And, if they are fixed, SELECT columns can have an efficient,
thread-safe, and powerful new expressiveness for the idioms of running
sums, running products, running concatenations, synthetic id columns, and
so on.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users