Re: [sqlite] curious discovery about geopoly module

[Vladimir Vissoultchev]

You can try some other sqlite wrapper for VBA, for instance vbRichClient ships 
with 3.24 -- http://www.vbrichclient.com/#/en/Downloads.htm

Unfortunately as it's primary target is VB6 the stdcall port of sqlite is 
compiled to x86 binary only.

cheers,


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Graham Hardman
Sent: Tuesday, October 30, 2018 11:49 AM
To: SQLite mailing list 
Subject: [sqlite] curious discovery about geopoly module

Hi everybody, 

regarding the geopoly module in version 3.25.2 

a couple of weeks ago I wrote about the fact that the 'pragma compile_options' 
command failed to display when I was trying to check that my special build of 
the sqlite dll library and the command line shell was working corrrectly. 
Richard Hipp's reply was to announce that he had forgotten to fully broadcast 
the new module to the rest of the code. 

Fair enough i thought,  since i was still able to test out my ideas using the 
shell interface and in my SQLiteExpert program on my windows
10 pc. 

Since my previous message I have been doing some tests in 64-bit vba by 
adapting code released on github by Govert: SQLiteForExcel [1] 

It has been performing well except that, to my great surprise I have been 
unable to get it to create a virtual table using geopoly. 

Tonight I have documented this in a module that runs some general tests that 
all succeed (creating normal tables, inserting records, and querying the 
results. A simple Rtree virtual table  was also part of the testing). The final 
part of my testing was to try to create a geopoly vitual table. The prepared 
statement succeeded, but the step process failed - returning 1. The extended 
error code was also 1, and the error message was "no such module: geopoly" 

I guess that makes sense in one way, but it begs the question of why the shell 
and my version of SQLiteExpert find a way to understand what is required. In 
the meantime it seems I shall have to suspend my vba coding until the next 
release where hopefully the above issue will be fixed. I can provide my code 
and the library if wished. 

The last few lines from my debug printing to the vba intermediate window is 
pasted below: 

--begin test with a simple geopoly virtual table opening an in memory 
database SQLite3Open returned 0 open the rtree virtual table sqlcmd is: 'create 
virtual table newtab using geopoly(a,b,c)'
SQLite3PrepareV2 returned 0
SQLite3Step failed returning 1
Extended error code is: 1
Extended error message is: no such module: geopoly SQLite3Finalize returned 1

forced to abandon testing since geopoly table could not be
created-

assertion documented 

Regards, 

Graham Hardman. 

 

Links:
--
[1] https://github.com/govert/SQLiteForExcel
___
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] [EXTERNAL] unexpected error with "GROUP BY 0"

[Vladimir Vissoultchev]

> By the way, this feature is documented for ORDER BY, but I don't see it for 
> GROUP BY.

It's not standard for GROUP BY e.g. SQL Server does not support it (ORDER BY 
col indexes are fine there too)

At least sqlite does not support the abomination GROUP BY 1 DESC the way MySQL 
does.

cheers,


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Mark Brand
Sent: Wednesday, May 30, 2018 5:22 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"

Thanks for the clarification.


> You have constant integers, output column identifiers and "any other 
> expression" as terms for GROUP BY.

Just to make sure I'm not missing something subtle: I understand the "constant 
integer" is what gets interpreted as a result column number. What is an "output 
column identifier" then? Isn't it already covered by the broader category "any 
other expression"?

It's still a pretty astonishing language feature(!?) that an integer 
numeric-literal, which in every other column-like context represents its 
integer value, gets interpreted after GROUP BY or ORDER BY as a result column 
number. Fortunately, SQLite isn't to blame for designing this.

By the way, this feature is documented for ORDER BY, but I don't see it for 
GROUP BY.

Mark

On 30/05/18 13:28, Hick Gunter wrote:
> You have constant integers, output column identifiers and "any other 
> expression" as terms for GROUP BY. If the expression evalutes to a constant 
> value, you will have only one output row.
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users 
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von 
> Mark Brand
> Gesendet: Mittwoch, 30. Mai 2018 12:11
> An: sqlite-users@mailinglists.sqlite.org
> Betreff: Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"
>
> Thanks. I had forgotten that GROUP BY considers a literal integer in this 
> context to be a column number, a feature I don't use.
>
> These, on the other hand, work as I would have expected:
>
> sqlite> select 0 group by cast (0 as int);
> 0
> sqlite> select 0 group by (select 0);
> 0
>
> Mark
>
>
> On 30/05/18 12:00, Hick Gunter wrote:
>> Yes. If the expression is a constant integer K, then it is considered an 
>> alias for the K-th column of the result set. Columns are ordered from left 
>> to right starting with 1.
>>
>> There is no 0-th column, so GROUP BY 0 is "out of range", just the same as 
>> "SELECT 0 GROUP BY 31" would be.
>>
>> -Ursprüngliche Nachricht-
>> Von: sqlite-users
>> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von 
>> Mark Brand
>> Gesendet: Mittwoch, 30. Mai 2018 11:32
>> An: SQLite mailing list 
>> Betreff: [EXTERNAL] [sqlite] unexpected error with "GROUP BY 0"
>>
>> Hi,
>>
>> Is there a good reason for this error:
>>
>> sqlite> SELECT  0 GROUP BY 0;
>> Error: 1st GROUP BY term out of range - should be between 1 and 1
>> sqlite> SELECT 0 GROUP BY 1;
>> 0
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>> ___
>>Gunter Hick | Software Engineer | Scientific Games International 
>> GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR:
>> 0430013 | (O) +43 1 80100 - 0
>>
>> May be privileged. May be confidential. Please delete if not the addressee.
>> ___
>> 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
>
>
> ___
>   Gunter Hick | Software Engineer | Scientific Games International 
> GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 
> 0430013 | (O) +43 1 80100 - 0
>
> May be privileged. May be confidential. Please delete if not the addressee.
> ___
> 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] RIGHT JOIN! still not supported?

[Vladimir Vissoultchev]

You don't need derived tables, just use brackets for explicitly order the 
execution of JOIN operators like this:

SELECT  P.PersonName
, Pt.PetName
, Pa.AccessoryName
FROMPersons P
LEFT JOIN   (   Pets Pt
JOINPetAccessories Pa
ON  Pt.PetName = Pa.PetName)
ON  P.PersonName = Pt.PersonName;

JOIN ordering is handy especially if you have to LEFT JOIN more tables to 
Persons, then using RIGHT JOIN obfuscates the code unnecessary. IMO this 
ordering pattern comes up often in reporting queries.

And this reminds me of "How much is 2 + 2 * 2?" puzzle on calculator vs excel 
:-))

cheers,



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Chris Locke
Sent: Wednesday, March 22, 2017 11:22 AM
To: SQLite mailing list 
Subject: Re: [sqlite] RIGHT JOIN! still not supported?

An interesting discussion of it on StackOverflow...
http://stackoverflow.com/questions/689963/does-anyone-use-right-outer-joins

To give one example where a RIGHT JOIN may be useful.

Suppose that there are three tables for People, Pets, and Pet Accessories.
People may optionally have pets and these pets may optionally have accessories.

If the requirement is to get a result listing all people irrespective of 
whether or not they own a pet and information about any pets they own that also 
have accessories.


All in all probably easiest to use a RIGHT JOIN

SELECT P.PersonName,
   Pt.PetName,
   Pa.AccessoryName
FROM   Pets Pt
   JOIN PetAccessories Pa
 ON Pt.PetName = Pa.PetName
   RIGHT JOIN Persons P
 ON P.PersonName = Pt.PersonName;

Though if determined to avoid this another option would be to introduce a 
derived table that can be left joined to.

On Wed, Mar 22, 2017 at 7:53 AM, Eric Grange  wrote:

> For the sake of curiosity, is anyone (as in any human) using RIGHT JOIN?
>
> Personally I never had a need for a RIGHT JOIN, not because of 
> theoretical or design considerations, but it just never came into my 
> flow of thought when writing SQL...
>
> I guess some automated SQL query generators could use it though, 
> because they do not have a "flow of thought".
>
> On Tue, Mar 21, 2017 at 9:50 PM, Darren Duncan 
> 
> wrote:
>
> > What benefit does a RIGHT JOIN give over a LEFT JOIN?  What queries 
> > are more natural to write using the first rather than the second?
> >
> > While I can understand arguments based on simple mirror parity, eg 
> > we
> have
> > < so we should have > too, lots of other operations don't have 
> > mirror syntax either.
> >
> > -- Darren Duncan
> >
> > On 2017-03-21 8:42 AM, Daniel Kamil Kozar wrote:
> >
> >> Seeing how SQLite was created in 2000, it seems like nobody really 
> >> needed this feature for the last 17 years enough in order to 
> >> actually implement it.
> >>
> >> Last I heard, patches are welcome on this mailing list. Don't keep 
> >> us waiting.
> >>
> >> Kind regards,
> >> Daniel
> >>
> >> On 20 March 2017 at 21:09, PICCORO McKAY Lenz 
> >> 
> >> wrote:
> >>
> >>> i got this
> >>>
> >>> Query Error: RIGHT and FULL OUTER JOINs are not currently 
> >>> supported Unable to execute statement
> >>>
> >>> still today in 21 ts century?
> >>>
> >>> Lenz McKAY Gerardo (PICCORO)
> >>> http://qgqlochekone.blogspot.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
>
___
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] UPSERT

[Vladimir Vissoultchev]

https://www.sqlite.org/lang_replace.html

Time for new compatibility keyword UPSERT?

cheers,


-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Benoit
Mortgat
Sent: Wednesday, January 20, 2016 12:00 PM
To: SQLite mailing list 
Subject: Re: [sqlite] UPSERT

Hello,

It already has, it is called INSERT OR REPLACE
https://www.sqlite.org/lang_insert.html



2016-01-20 2:40 GMT+01:00 :

> Will SQLite ever get an UPSERT command similar to other DBMS? Thanks 
> for any info.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



--
Benoit Mortgat
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is it possible to have query planner use FTS Index for ORDER BY

[Vladimir Vissoultchev]

Actually, doubt it's possible to use `content1_index` for ordering in your
case once `content` rows are filtered by the IN operator. Most probably the
index is just ignored here.

Do you get any measurable performance improvement with vs w/o it?

cheers,


-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of David
Waters
Sent: Thursday, August 20, 2015 8:58 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Is it possible to have query planner use FTS Index for
ORDER BY

Thanks Dan and Vladimir.  A combination of the two approaches is required.
I'll detail it here for future reference:

Using the sub query against the FTS table as suggested:

SELECT * FROM main
WHERE rowid IN (SELECT rowid FROM main WHERE main MATCH 'data') ORDER BY
udate

Still causes the USE TEMP B-TREE FOR ORDER BY.  As Dan pointed out, the FTS
"index" isn't a traditional SQL Index.  However, by using the suggested
content table to populate the FTS table, I was able to get the desired
result, with just a few more steps (and disk space) required:

CREATE TABLE IF NOT EXISTS content1 (id INTEGER PRIMARY KEY, udate, msg)
CREATE VIRTUAL TABLE IF NOT EXISTS main USING
fts4(content="content1",id,udate,msg)
CREATE TRIGGER IF NOT EXISTS content_trig1 AFTER INSERT ON content1 BEGIN
INSERT INTO main(docid,udate,msg) VALUES(new.rowid, new.udate,new.msg); END
CREATE INDEX IF NOT EXISTS content1_index on content1 (udate)

To query with ORDER BY, I had to also add the "INDEXED BY" to get the Query
Planner to use the Index, and not a temp B-tree.

SELECT * FROM content1 INDEXED BY content1_index WHERE rowid IN (SELECT
rowid FROM main WHERE main MATCH 'data to match') ORDER BY odate"

And I now have fast (and sorted) FTS queries again, with no temp B-tree!

Thanks.

Dave



On Thu, Aug 20, 2015 at 11:23 AM, Vladimir Vissoultchev 
wrote:

> Try to rewrite the query like this
>
> SELECT * FROM main
> WHERE rowid IN (SELECT rowid FROM main WHERE main MATCH 'data') ORDER 
> BY udate
>
> cheers,
> 
>
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org
> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of 
> David Waters
> Sent: Thursday, August 20, 2015 4:47 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Is it possible to have query planner use FTS 
> Index for ORDER BY
>
> Sorry.  I should have specified that that 'udate' is one of the 
> indexed columns of the FTS4 table main.
>
> The goal is to do a FTS query and be able to ensure the results are 
> ordered the same each time (via the ORDER BY).  It seemed at first to 
> me that the FTS index contains what is needed for the ORDER BY, but 
> that is likely incorrect.
>
> Thanks.
>
> DW
>
> On Thursday, August 20, 2015, Dan Kennedy  wrote:
>
> > On 08/20/2015 12:38 PM, David Waters wrote:
> >
> >> I have a large FTS4 table (around 200 million rows and growing).  A 
> >> simple query (SELECT * FROM main WHERE main MATCH 'data') returns 
> >> in less than a second.  However, if an ORDER BY is added (SELECT * 
> >> FROM main WHERE main MATCH 'data' ORDER BY udate) it will never 
> >> return (after 20 mins, I canceled the query).
> >>
> >> EXPLAIN QUERY PLAN on the ORDER BY statement shows 'USE TEMP B-TREE 
> >> FOR ORDER BY'.  Shouldn't it attempt to use the available FTS Index 
> >> for ORDER BY?
> >>
> >
> > I don't see how it could. The FTS index is not a list of rows sorted 
> > by udate.
> >
> > If not, Is there another method (or work around) to get the data 
> > back
> >> in order?
> >>
> >
> > You could use an external content FTS index. Then put a regular 
> > index on the udate column of your external content table and query 
> > it directly for non-fulltext queries.
> >
> >   https://www.sqlite.org/fts3.html#section_6_2_2
> >
> > Dan.
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
> --
> Dave
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



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

[sqlite] Is it possible to have query planner use FTS Index for ORDER BY

[Vladimir Vissoultchev]

Try to rewrite the query like this

SELECT * FROM main
WHERE rowid IN (SELECT rowid FROM main WHERE main MATCH 'data') 
ORDER BY udate

cheers,


-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of David
Waters
Sent: Thursday, August 20, 2015 4:47 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Is it possible to have query planner use FTS Index for
ORDER BY

Sorry.  I should have specified that that 'udate' is one of the indexed
columns of the FTS4 table main.

The goal is to do a FTS query and be able to ensure the results are ordered
the same each time (via the ORDER BY).  It seemed at first to me that the
FTS index contains what is needed for the ORDER BY, but that is likely
incorrect.

Thanks.

DW

On Thursday, August 20, 2015, Dan Kennedy  wrote:

> On 08/20/2015 12:38 PM, David Waters wrote:
>
>> I have a large FTS4 table (around 200 million rows and growing).  A 
>> simple query (SELECT * FROM main WHERE main MATCH 'data') returns in 
>> less than a second.  However, if an ORDER BY is added (SELECT * FROM 
>> main WHERE main MATCH 'data' ORDER BY udate) it will never return 
>> (after 20 mins, I canceled the query).
>>
>> EXPLAIN QUERY PLAN on the ORDER BY statement shows 'USE TEMP B-TREE 
>> FOR ORDER BY'.  Shouldn't it attempt to use the available FTS Index 
>> for ORDER BY?
>>
>
> I don't see how it could. The FTS index is not a list of rows sorted 
> by udate.
>
> If not, Is there another method (or work around) to get the data back
>> in order?
>>
>
> You could use an external content FTS index. Then put a regular index 
> on the udate column of your external content table and query it 
> directly for non-fulltext queries.
>
>   https://www.sqlite.org/fts3.html#section_6_2_2
>
> Dan.
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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