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

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:

Re: [sqlite] exit status of command shell

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

Re: [sqlite] Access to sqlite3_api_routines outside of a loadable extension context

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:

Re: [sqlite] exit status of command shell

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

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

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

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

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

Re: [sqlite] Before Insert/Update Trigger

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

Re: [sqlite] Before Insert/Update Trigger

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

Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

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

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

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

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

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

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

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

Re: [sqlite] Function design question

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

Re: [sqlite] How to use WITH CLAUSE in a UPDATE statement?

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 ...

[sqlite] Bug (or feature?) Trailing comment becomes part of column name

sqlite> .mode column sqlite> SELECT sqlite_source_id()--sql_comment ...> ; sqlite_source_id()--sql_comment 2018-01-22 18:45:57 0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2alt1 sqlite> SELECT

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

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

Re: [sqlite] Improper error message

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

Re: [sqlite] printf() problem padding multi-byte UTF-8 code points

: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 des

Re: [sqlite] printf() problem padding multi-byte UTF-8 code points

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

Re: [sqlite] printf() problem padding multi-byte UTF-8 code points

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,

[sqlite] Segmentation fault in 3.22 eval.c under PRAGMA empty_result_callbacks=1

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

Re: [sqlite] Static sqlite3 library for Linux

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

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

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().

Re: [sqlite] sqlite3_set_last_insert_rowid

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

Re: [sqlite] regression since 3.20.0

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

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

Re: [sqlite] UTF8 and NUL

lar 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.nichvo

Re: [sqlite] UTF8 and NUL

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

Re: [sqlite] UTF8 and NUL

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.

Re: [sqlite] Atoi64 bug(s)

/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. &g

Re: [sqlite] Atoi64 bug(s)

) 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:5

Re: [sqlite] Atoi64 bug(s)

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); --

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

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

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

(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>

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

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...@gmai

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

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

Re: [sqlite] Bug in unique index

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

Re: [sqlite] unexpected row value error

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

Re: [sqlite] unexpected row value error

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),

Re: [sqlite] .DUMP displays floats differently from SELECT

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

Re: [sqlite] .DUMP displays floats differently from SELECT

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

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

://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:

Re: [sqlite] Feature request for the Shell Tool: .mode json

>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

Re: [sqlite] Feature request for the Shell Tool: .mode json

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

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

ed 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 doe

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

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

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

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), (ran

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

, 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 fli

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

. 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; &

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

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"

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

nce 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: > > >

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

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

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

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

Re: [sqlite] IN clause

, 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 >

Re: [sqlite] IN clause

>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

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

tegies 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 hin

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

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/1

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

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 pr

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

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

Re: [sqlite] Speed issue of SELECT in my application

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

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

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 >

Re: [sqlite] Can an SQL script be built from within sqlite?

c) > > 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,

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

<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 A

Re: [sqlite] sqlite_column_table_name() and table alias name

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

Re: [sqlite] possible bug: separator string and quote mode

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

Re: [sqlite] Can an SQL script be built from within sqlite?

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

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

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

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

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)

Re: [sqlite] SQLite 3.22.0 coming soon

: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 &

Re: [sqlite] SQLite 3.22.0 coming soon

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 <pe

[sqlite] Defect: trivial cross join strips BLOB subtype

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

Re: [sqlite] SQLite 3.22.0 coming soon

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

Re: [sqlite] SQLite 3.22.0 coming soon

mit...@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. >> >>

Re: [sqlite] SQLite 3.22.0 coming soon

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_PRAGM

Re: [sqlite] SQLite 3.22.0 coming soon

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

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

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

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

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

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

-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> > wrote: > > Hi Scott. > > > >>Are there other aggregate functions th

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

ted 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 whe

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

NCT 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,','); >

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

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

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

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.

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

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

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

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

Re: [sqlite] Can select * from table where not exists (subquery) be optimized?

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

[sqlite] Dan. Latest zipfile.c checkin feedback

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

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

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

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

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

Re: [sqlite] Ubuntu error with 3210000

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

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

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

Re: [sqlite] Odd question

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

Re: [sqlite] API, sqlite3_value_bytes and sqlite3_value_blob...

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

Re: [sqlite] extracting domain names from website addresses efficiently

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

Re: [sqlite] Retrieving constraint name

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

Re: [sqlite] Any chance of exposing INTEGER PRIMARY KEY metadata? Needed by many tools

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

Re: [sqlite] How to use sqlite3_table_column_metadata?

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_

Re: [sqlite] sqlite3_get_auxdata() defect

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 say

Re: [sqlite] sqlite3_get_auxdata() defect

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:

Re: [sqlite] sqlite3_get_auxdata() defect

e 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: >

[sqlite] sqlite3_get_auxdata() defect

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

  1   2   >