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

Hi David, Nice work! your query is far quicker than mine- even without the reverseEdges index. I think you are right about the problem of potentially double counting leaves. There weren't any multi-parent nodes in my test data so I didn't notice this mistake. Could you please explain why your

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

Hello, On 2018-01-03 01:44, Cezary H. Noweta wrote: MySQL has a separator specified by a distinct clause. I'm sorry -- I meant ``distinct'' == ``separate/different'' (a clause named ``SEPARATOR''). Not to be confused with ``DISTINCT'' clause in SQLite's ``group_concat(DISTINCT...)''. --

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

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

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

On Tue, Jan 2, 2018 at 5:46 PM, petern wrote: > 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

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

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

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

Hello, On 2018-01-03 00:02, Tony Papadimitriou wrote: MySQL does not seem to have a problem with it. MySQL has a separator specified by a distinct clause. In SQLite it is specified by a second expression, which, in a canonical and intuitive point of view, is a constant string. However it

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

On Tue, Jan 2, 2018 at 4:15 PM, petern 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

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

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(

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

MySQL does not seem to have a problem with it. -Original Message- From: Scott Robison On Tue, Jan 2, 2018 at 1: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; --

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

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

Re: [sqlite] Database Variable

> On Jan 2, 2018, at 1:49 PM, Gregory Moore wrote: > > I was poking around in FMDB’s source code on github. FMDB is a widely used > Objective-C wrapper around SQLite. I was looking to see what they do out of > curiosity. FMDB declares and uses the database variable

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

Hello, On 2018-01-02 22:39, Scott Robison wrote: On Tue, Jan 2, 2018 at 1: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

Re: [sqlite] Database Variable

Gregory Moore wrote: > void *_database; > > int openCode = sqlite3_open(databasePathC, (sqlite3**)&_database); > > is there some advantage to declaring the database variable as a void > pointer and casting it as sqlite3 verses just declaring it as sqlite3? Not really. A plain void pointer does

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

[sqlite] Database Variable

Hi I sent this message previously, but it ended being a reply in another thread which was not my intention. So let’s try one more time... I have an Objective-C iPhone application developed using Xcode. I have a singleton object which serves as the database controller. I use the SQLite C APIs

Re: [sqlite] C++ compiler

Hi, On Tue, Jan 2, 2018 at 3:34 PM, wrote: > Hi: > > Not only it can, but that is probably the use in the 99.00% of C++ > applications that uses SQLite. That number should probably be 99.999(9)%... ;-) Thank you. > > The only caveat is that you can get some warnings

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

On Tue, Jan 2, 2018 at 1: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

Re: [sqlite] C++ compiler

Hi: Not only it can, but that is probably the use in the 99.00% of C++ applications that uses SQLite. The only caveat is that you can get some warnings depending on the compiler you use. Cheers -- Adolfo J. Millan > > Mensaje original > De: eli > Para:

[sqlite] GROUP_CONCAT with DISTINCT bug

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

[sqlite] Database Variable

Hi I have an Objective-C iPhone application developed using Xcode. I have a singleton object which serves as the database controller. I use the SQLite C APIs to interact with the database. It works really well but I’m curious about something. In my app, I declare my database instance variable

Re: [sqlite] C++ compiler

Ditto. Large C++ project - no problem using SQLite. Are you trying to compile sqlite.c as a C++ file? That won't work obviously, you have to compile as C and link it in. The sqlite3.h header however can be pulled into any C++ file. - Deon -Original Message- From: sqlite-users

Re: [sqlite] C++ compiler

On 30 Dec 2017, at 11:35am, eli wrote: > It would be awesome if SQLite could compile as a part of bigger C++ project. It can. It should work fine. This is the main way SQLite is intended to be used. Download the "amalgamation" source code (one .h and one .c file) and

Re: [sqlite] C++ compiler

Hi, On Sat, Dec 30, 2017 at 5:35 AM, eli wrote: > Hello, > > It would be awesome if SQLite could compile as a part of bigger C++ project. > Right now there is a bunch of pointer casting errors, that can be fixed in > a matter of hour IMHO. Which OS/compiler are you trying?

Re: [sqlite] C++ compiler

Eli Sent: Saturday, December 30, 2017 6:36 AM >It would be awesome if SQLite could compile as a part of bigger C++ project. >Right now there is a bunch of pointer casting errors, that can be fixed in >a matter of hour IMHO. I don't have any trouble using it as part of a larger C++ project.

Re: [sqlite] C++ compiler

On Sat, Dec 30, 2017 at 5:35 AM, eli wrote: > Hello, > > It would be awesome if SQLite could compile as a part of bigger C++ > project. > Right now there is a bunch of pointer casting errors, that can be fixed in > a matter of hour IMHO. > ​I'm not a very knowledgeable C++

[sqlite] C++ compiler

Hello, It would be awesome if SQLite could compile as a part of bigger C++ project. Right now there is a bunch of pointer casting errors, that can be fixed in a matter of hour IMHO. Cheers, ___ sqlite-users mailing list

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

I think you need a union there instead of a union all. Otherwise you're double (or more) counting leaves where there is more than 1 path to get to the leaf. I don't have a large dataset to test it on, but how about something like: create table nodes ( id integer primary key, description

[sqlite] Dynamically load all the sqlite functions

Hi all, I need to dynamically load all the functions inside a sqlite shared library. What I am trying to do is to execute the same code just swapping a pointer from libsqlite1 and libsqlite2 (two different version of the sqlite library). Normally I should dlopen the library, load all functions

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

Yes, Lifepillar's way is the more orthodox approach, however I always preferred the path-based one because: 1) One seldom runs queries only based on the descendants map; there usually is an "AND -some other conditions-" involved; thus the ability to have one covering index of the condition comes

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

On 02/01/2018 06:54, Dinu wrote: If a different perspective may be helpful to you: If moving overhead to writes is an option (ie you dont have many or time critical writes), then the tree descendants problem can be sped up to stellar speeds by using a path column. In a more relational spirit,