Re: [sqlite] [EXTERNAL] virtual tables, xBestIndex, pIdxInfo->aConstraint[].op, and 'NOT'...

[dave]

I am using 3.20.1

Yes, I noticed LIKE etc.  It looked like it changed from a bitfield to an
enum at some point.  So, I guess I am one versionpoint shy of having NE and
IS.
OK, I'll check out at least 3.21 -- I was hesitant to upgrade just now
because there were some shell.c issues I had with those embedded extensions
relative to the implementation in 3.20, but this may compell me to do so.
(I use shell.c in a special debug build of my product).

Thanks!
-dave

> -Original Message-
> From: sqlite-users 
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
> Behalf Of Hick Gunter
> Sent: Monday, September 24, 2018 1:57 AM
> To: 'SQLite mailing list'
> Subject: Re: [sqlite] [EXTERNAL] virtual tables, xBestIndex, 
> pIdxInfo->aConstraint[].op, and 'NOT'...
> 
> 
> Which version are you using? The set of constraint constants 
> was extended in versions 3.10 (LIKE, GLOB, REGEXP), 3.21 (NE, 
> IS*) and most recently 3.25 (FUNCTION)
> 
> -Ursprüngliche Nachricht-
> Von: sqlite-users 
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
> Auftrag von dave
> Gesendet: Sonntag, 23. September 2018 23:26
> An: 'SQLite mailing list' 
> Betreff: [EXTERNAL] [sqlite] virtual tables, xBestIndex, 
> pIdxInfo->aConstraint[].op, and 'NOT'...
> 
> Folks,
> 
> I cannot seem to find a means of filtering on negated 
> operators, e.g. <>, not null, not like, etc., in the 
> xBestIndex() method for virtual vables.  As best as I can 
> tell, I cannot, unless there is something I am missing, hence 
> this inquiry.
> 
> In a few virtual tables I have implemented, I have handled 
> the SQLITE_INDEX_CONSTRAINT_EQ, and the 
> SQLITE_INDEX_CONSTRAINT_LIKE (for
> example) in the xBestIndex and xFilter.  These code paths are 
> taken for queries of the form:
> 
> select * from myvtab where mycol = 'xxx';
> select * from myvtab where mycol like 'xxx';
> 
> but /not/ for queries of the form:
> 
> select * from myvtab where mycol <> 'xxx';
> select * from myvtab where mycol not like 'xxx';
> 
> I can work around these things for now with caveats in 
> documentation, but it does sometimes cause confusion to users.
> 
> For example, in one case I have extended the syntax of LIKE . 
>  That extension of syntax is invoked for a positive LIKE 
> constraint, but is bypassed for a negated one.  I can work 
> around that with an extension function, but I won't get the 
> hints at record enumeration time that could reduce the 
> dataset from the underlying source.
> 
> In other cases, I have some 'required' columns, which must be 
> present in a EQ constraints (usually they wind up being 
> parameters to a function call that generates the underlying 
> data).  I emit an error when such constraints are missing, 
> but it can be confusing to users when:
> 
> select * from myvtab where mycol <> 'xxx';
> 
> indicates that "you must have a constraint on 'mycol'"
> 
> Lastly, some behavioural inconsistencies occur between these forms:
> 
> select * from myvtab where mycol = null;
> select * from myvtab where mycol is null;
> 
> Since the first comes in as a constraint to xBestIndex, 
> whereas the second does not.
> 
> Anyway, as I said, I can work around this for now, but I 
> thought I would ask
> if:
> 
> 1)  is it true:  xBestIndex doesn't get to see negated 
> predicates, or is it just somewhere that I have not found?
> 2)  if it's not possible, would it be worthwhile to consider 
> extending the operator set in some way to present the 
> negative clauses at some release in the future?
> 
> Thanks for any info!
> 
> -dave
> 
> ___
> 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] Compression for ft5

[Domingo Alvarez Duarte]

Hello !

After looking at how compression is implemented in fts3 and wanting the 
same for fts5 I managed to get a working implementation that I'm sharing 
here with the same license as sqlite3 in hope it can be useful to others 
and maybe be added to sqlite3.


Cheers !


Here is on implementation of optional compression and min_word_size for 
columns in fts5:


===

create virtual table if not exists docs_fts using fts5(
    doc_fname unindexed, doc_data compressed,
    compress=compress, uncompress=uncompress,
    tokenize = 'unicode61 min_word_size=3'
);

===

https://gist.github.com/mingodad/7fdec8eebdde70ee388db60855760c72


And here is an implementation of optional compression for columns in fts3/4:

===

create virtual table if not exists docs_fts using fts4(
    doc_fname, doc_data,
    tokenize = 'unicode61',
    notindexed=doc_fname, notcompressed=doc_fname,
    compress=compress, uncompress=uncompress
);

===

https://gist.github.com/mingodad/2f05cd1280d58f93f89133b2a2011a4d

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

Re: [sqlite] sqlite3_get_table() failed with error "database is locked"

[Clemens Ladisch]

ldl wrote:
> Multiple applications called sqlite3_get_table() to read data in a same db 
> file at the same time, got error "database is locked"

Somebody has a write lock.

> Why read-only access is locked?

Because one access is not read only.

> How to resolve the issue?

Find out who locked it.
Do you know which processess could access the DB file?


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

Re: [sqlite] sqlite 3.25.1 windows function. So it should be?

[Clemens Ladisch]

Djelf wrote:
> SQLSTATE[42803]: Grouping error: 7 ERROR:  column "t.v3" must appear in the 
> GROUP BY clause or be used in an aggregate function
>
> It seems to me that sqlite should issue a similar message.

This is allowed for compatibility with MySQL.
And there is a case with min()/max() where this is actually useful:



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

Re: [sqlite] sqlite 3.25.1 windows function. So it should be?

[Djelf]

I understood what's the matter. The query was incorrect.

I tested this on posgresql.

SQLSTATE[42803]: Grouping error: 7 ERROR:  column "t.v3" must appear in the
GROUP BY clause or be used in an aggregate function

It seems to me that sqlite should issue a similar message.

Correct query is

SELECT 
v1,v2,sum(sum(v3+v4)) OVER (PARTITION BY v2 ORDER BY v1) as val 
FROM t 
GROUP BY v1,v2; 

v1  v2  val
1   1   10001
2   1   10011
3   1   1





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

Re: [sqlite] sqlite 3.25.1 windows function. So it should be?

[Clemens Ladisch]

Djelf wrote:
> INSERT INTO t (v1,v2,v3,v4) VALUES
> (1,1,1,0),(1,1,0,1),(2,1,0,10),(3,1,0,100),(3,1,0,1000);
>
> SELECT
>   v1,v2,sum(v3+v4) OVER (PARTITION BY v2 ORDER BY v1) as val
> FROM t
> GROUP BY v1,v2;
>
> v1v2  val
> 1 1   1
> 2 1   10010
> 3 1   10110
>
> Yes, I know that v1 is duplicated, but whether the result should disappear
> 10011?

Looks correct.  The Postgres manual explains it this way:
> If the query contains any window functions, these functions are
> evaluated after any grouping, aggregation, and HAVING filtering is
> performed. That is, if the query uses any aggregates, GROUP BY, or
> HAVING, then the rows seen by the window functions are the group rows
> instead of the original table rows from FROM/WHERE.


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

Re: [sqlite] Bug report: Window functions in VIEWs broken in 3.25.1

[Clemens Ladisch]

Bjoern Hoehrmann wrote:
>   Using the sqlite-tools-linux-x86-3250100 Linux binaries I find that
> Window functions in VIEWS behave differently from PostgreSQL 9.6 and
> from what I expect.
>
>   DROP TABLE IF EXISTS example;
>   CREATE TABLE example(t INT, total INT);
>   INSERT INTO example VALUES(0,2);
>   INSERT INTO example VALUES(5,1);
>   INSERT INTO example VALUES(10,1);
>
>   DROP VIEW IF EXISTS view_example;
>   CREATE VIEW view_example AS
>   SELECT
> NTILE(256) OVER (ORDER BY total) - 1 AS nt
>   FROM
> example
>   ;
>
>   SELECT * FROM view_example;
>
> In SQLite 3.25.1 I get 0, 0, 0

The EXPLAIN output shows that the optimizer ended up generating
a program for "SELECT 1 - 1 FROM example".

> while PostgreSQL 9.6 gives 0, 1, 2.

And the same query outside a view gives the correct ouput.


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