Re: [sqlite] Limit on number of columns in SQLite table

[Digital Dog]

On Wed, Nov 6, 2019 at 7:22 PM Jens Alfke  wrote:

>
>
> > On Nov 6, 2019, at 9:25 AM, Digital Dog  wrote:
> >
> > If there are millions or billions of rows
> > in the data set I consider it big data and the only reasonable format for
> > storing it is a column store format.
>
> There are many types of stores for "big data". My employer, Couchbase, has
> customers who would disagree with you; they use our document database to
> store millions or billions of rows.
>

I was talking about this specific data set with very well defined columns.
However I'm not sure if Parquet will be able to store this amount of
columns, or query them efficiently.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit on number of columns in SQLite table

[Jens Alfke]

> On Nov 6, 2019, at 9:25 AM, Digital Dog  wrote:
> 
> If there are millions or billions of rows
> in the data set I consider it big data and the only reasonable format for
> storing it is a column store format.

There are many types of stores for "big data". My employer, Couchbase, has 
customers who would disagree with you; they use our document database to store 
millions or billions of rows.

It depends on the data type. For highly structured data with many sparse 
columns you may be right. For less structured data a key-value or document 
store is better. I'm sure there are other types of big-data storage I'm unaware 
of.

In the original poster's case, it didn't seem like the data set really had 
zillions of columns, since [IIRC] they didn't need to be queried separately. 
You could put something like that in a key-value store with each value an 
encoded C array, for example.

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

Re: [sqlite] Limit on number of columns in SQLite table

[Digital Dog]

Hi!

I'm late to the party, but want to contribute. I did not read all messages
in the thread, but in those I've read did not contain a question about
number of ROWS. We know how many columns you desire, but how many rows are
there?

No matter how I like SQLite, I would not store this kind of data in it,
neither short term nor long term. If there are millions or billions of rows
in the data set I consider it big data and the only reasonable format for
storing it is a column store format. It gives up to 1:100 compression
(depending on the data of course) and incredible querying speeds (if it
doesn't have to read some columns it just doesn't do that). If it can skip
row groups altogether based on metadata, it will. None of row storage
engines will give you comparable efficiency. And especially because you do
not need transactional semantic, you can consider faster engines, tailored
to this use case (without WAL, locking or MVCC). The ones I can think of
now are: Apache Parquet, Cassandra and MonetDB, not to mention excellent
implementation and support in Microsoft SQL Server. You have to do your own
research on this subject.


On Thu, Oct 17, 2019 at 7:38 PM Mitar  wrote:

> ""Hi!
>
> On Thu, Oct 17, 2019 at 5:38 PM Jens Alfke  wrote:
> > Why should SQLite make changes, which would introduce performance
> problems if used, just to save your particular application the trouble of
> concatenating some vectors into single columns, when it uses SQLite for an
> edge use-case that’s pretty far removed from its main purpose?
>
> Then maybe section "File archive and/or data container" in
> "Appropriate Uses For SQLite" should explain that this is not the
> purpose of SQLite anymore. Because "SQLite is a good solution for any
> situation that requires bundling diverse content into a self-contained
> and self-describing package for shipment across a network." seem to
> work only when "diverse" is a table with less 2000 columns. Somehow
> describing a table with key/value columns can hardly be called
> self-describing.
>
> I am on purpose ironic, because I am not sure if talking about "main
> purpose" is really a constructive conversation here if there is a list
> of many "non-main" but still suggested use cases for SQLite. Not to
> mention the "Data analysis" use case, where again, if I am used to do
> analysis on datasets with many columns now would have to change the
> algorithms how I do my analysis to adapt to limited number of columns.
> It does not seem that putting vectors into single columns would really
> enable many "Data analysis" options inside SQLite. I am even surprised
> that it says "Many bioinformatics researchers use SQLite in this way."
> With limit on 2000 columns this is a very strange claim. I would love
> to see a reference here and see how they do that. I might learn
> something new.
>
>
> Mitar
>
> --
> http://mitar.tnode.com/
> https://twitter.com/mitar_m
> ___
> 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] Limit on number of columns in SQLite table

[Mitar]

""Hi!

On Thu, Oct 17, 2019 at 5:38 PM Jens Alfke  wrote:
> Why should SQLite make changes, which would introduce performance problems if 
> used, just to save your particular application the trouble of concatenating 
> some vectors into single columns, when it uses SQLite for an edge use-case 
> that’s pretty far removed from its main purpose?

Then maybe section "File archive and/or data container" in
"Appropriate Uses For SQLite" should explain that this is not the
purpose of SQLite anymore. Because "SQLite is a good solution for any
situation that requires bundling diverse content into a self-contained
and self-describing package for shipment across a network." seem to
work only when "diverse" is a table with less 2000 columns. Somehow
describing a table with key/value columns can hardly be called
self-describing.

I am on purpose ironic, because I am not sure if talking about "main
purpose" is really a constructive conversation here if there is a list
of many "non-main" but still suggested use cases for SQLite. Not to
mention the "Data analysis" use case, where again, if I am used to do
analysis on datasets with many columns now would have to change the
algorithms how I do my analysis to adapt to limited number of columns.
It does not seem that putting vectors into single columns would really
enable many "Data analysis" options inside SQLite. I am even surprised
that it says "Many bioinformatics researchers use SQLite in this way."
With limit on 2000 columns this is a very strange claim. I would love
to see a reference here and see how they do that. I might learn
something new.


Mitar

-- 
http://mitar.tnode.com/
https://twitter.com/mitar_m
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit on number of columns in SQLite table

[Jens Alfke]

—Jens 

> On Oct 17, 2019, at 1:56 AM, Mitar  wrote:
> 
> So why not increase the limit to 2 billion

Why should SQLite make changes, which would introduce performance problems if 
used, just to save your particular application the trouble of concatenating 
some vectors into single columns, when it uses SQLite for an edge use-case 
that’s pretty far removed from its main purpose?

It may seem like a harmless change to you, but I’m sure it would result in 
increased support work. Someone will report extremely slow query performance, 
and after several back-and-forth emails will mention that, oh yeah, this query 
does access the five millionth column in a table...

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

Re: [sqlite] Limit on number of columns in SQLite table

[Mitar]

Hi!

Oh, or we could just split CSV into separate lines, and then just
store one line per SQLite row, into one column. Then we do not have to
use JSON or something.

That would work for CSV files. For other types of inputs we might be
able to find some other similar approach.

So generally the main use case would be sub-sampling rows.


Mitar

On Thu, Oct 17, 2019 at 4:11 PM Donald Griggs  wrote:
>
> So if character-separated values (CSV-ish) were originally your preferred
> import format, would using that format for the blob's work for you?
>
> E.g., Suppose you need to index the first two fields only, and so can use a
> blob column for the bulk of the record.  If the records were supplied as:
>  MyFirstField~MySecondField~thousands|of|data|items|...
> and you imported these records into your 3-column table, defining tilde (~)
> as the separator, you could retain a simple format.
>
> If ever needed, you can easily export the table with the indexed columns
> treated like the bulk of the data
> from the sqlite3 command line utility:
> .mode list
> .separator |
> SELECT * FROM MyTable;
>
>MyFirstField|MySecondField|thousands|of|data|items|...
>
>
>
>
>
>
>
> On Thu, Oct 17, 2019 at 9:10 AM Mitar  wrote:
>
> > Hi!
> >
> > On Thu, Oct 17, 2019 at 3:04 PM Eric Grange  wrote:
> > > my suggestion would be to store them as JSON in a blob, and use the JSON
> > > functions of SQLite to extract the data
> >
> > JSON has some crazy limitations like by standard it does not support
> > full floating point spec, so NaN and infinity cannot be represented
> > there. So JSON is really no a great format when you want to preserve
> > as much of the input as possible (like, integers, floats, text, and
> > binary). SQLite seems to be spot on in this regard.
> >
> > But yes, if there would be some other standard to SQLite and supported
> > format to embed, that approach would be useful. Like composite value
> > types.
> >
> >
> > Mitar
> >
> > --
> > http://mitar.tnode.com/
> > https://twitter.com/mitar_m
> > ___
> > 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



-- 
http://mitar.tnode.com/
https://twitter.com/mitar_m
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit on number of columns in SQLite table

[Donald Griggs]

So if character-separated values (CSV-ish) were originally your preferred
import format, would using that format for the blob's work for you?

E.g., Suppose you need to index the first two fields only, and so can use a
blob column for the bulk of the record.  If the records were supplied as:
 MyFirstField~MySecondField~thousands|of|data|items|...
and you imported these records into your 3-column table, defining tilde (~)
as the separator, you could retain a simple format.

If ever needed, you can easily export the table with the indexed columns
treated like the bulk of the data
from the sqlite3 command line utility:
.mode list
.separator |
SELECT * FROM MyTable;

   MyFirstField|MySecondField|thousands|of|data|items|...







On Thu, Oct 17, 2019 at 9:10 AM Mitar  wrote:

> Hi!
>
> On Thu, Oct 17, 2019 at 3:04 PM Eric Grange  wrote:
> > my suggestion would be to store them as JSON in a blob, and use the JSON
> > functions of SQLite to extract the data
>
> JSON has some crazy limitations like by standard it does not support
> full floating point spec, so NaN and infinity cannot be represented
> there. So JSON is really no a great format when you want to preserve
> as much of the input as possible (like, integers, floats, text, and
> binary). SQLite seems to be spot on in this regard.
>
> But yes, if there would be some other standard to SQLite and supported
> format to embed, that approach would be useful. Like composite value
> types.
>
>
> Mitar
>
> --
> http://mitar.tnode.com/
> https://twitter.com/mitar_m
> ___
> 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] Limit on number of columns in SQLite table

[Mitar]

Hi!

On Thu, Oct 17, 2019 at 3:04 PM Eric Grange  wrote:
> my suggestion would be to store them as JSON in a blob, and use the JSON
> functions of SQLite to extract the data

JSON has some crazy limitations like by standard it does not support
full floating point spec, so NaN and infinity cannot be represented
there. So JSON is really no a great format when you want to preserve
as much of the input as possible (like, integers, floats, text, and
binary). SQLite seems to be spot on in this regard.

But yes, if there would be some other standard to SQLite and supported
format to embed, that approach would be useful. Like composite value
types.


Mitar

-- 
http://mitar.tnode.com/
https://twitter.com/mitar_m
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit on number of columns in SQLite table

[Eric Grange]

> I wrote earlier that for us use case where we are reading whole rows is
the most common one.
> [...]
> we are looking for ways to store this in a stable format which will be
supported for next 50 years, without modifying to original data too much.

If you do not need access to individual columns too much, but want to still
be able to operate on them when needed,
my suggestion would be to store them as JSON in a blob, and use the JSON
functions of SQLite to extract the data
when needed (https://www.sqlite.org/json1.html)

This would mean you have both the stability of a text format for long-term
storage (JSON) while still having
the ability to run queries, index and manipulate data with SQL.

I am using such an approach successfully, and when some fields in the JSON
are flagged for indexing or direct access
(f.i. for summaries / descriptions), I just extract them to a relational
table which can then be indexed and used directly
as usual (you can use triggers to automate that extraction).
You may also be able to use indexes on expression if you only want indexing
(https://www.sqlite.org/expridx.html)

Eric

Le jeu. 17 oct. 2019 à 14:50, Mitar  a écrit :

> Hi!
>
> This is getting a bit off topic.
>
> On Thu, Oct 17, 2019 at 12:07 PM Simon Slavin 
> wrote:
> > 1) Almost no piece of software can handle a grid 2 billion cells wide.
> Excel maxes out at 16,384 columns.  Matlab can store and retrieve a cell of
> data directly from a file, but it has a max array size of 1.  R maxes
> out at 2147483647, which is more than 2 billion.  But R has to hold all the
> data from a matrix in memory at once and it can't assign enough memory to
> one object to hold that many cells.
>
> Of course, 2 billion is a lot. But 100k is something many ML libraries
> support. Pandas, ndarray, R. Not something to magical about that.
>
> > 2) Object names are not data.  They're descriptions in your favourite
> human language.  They're not meant to have weird sequences of characters in.
>
> Not sure what this relates to.
>
> > 3) Lots of CSV import filters ignore a column header row, or can only
> create fieldnames with certain limits (max length, no punctuation
> characters, etc.).  So you should expect to lose fieldnames if you try to
> import your data into some new piece of software.
>
> Does SQLite have limitations on what can be a column name? If not,
> then I would not worry what some CSV importers do. We would use a good
> one to convert to SQLLite.
>
> > (4) SQLite stores all the data for a row is together, in a sequence.  If
> you ask for the data in the 3756th column of a row, SQLite has to read and
> parse the data for the first 3755 columns of that row, just to read a
> single value from storage.  As you can imagine, this is slow and involves a
> lot of I/O.  And while it happens the row up to that point must all be held
> in memory.  Consequently, nobody who uses SQLite for its intended purpose
> actually does this.  I dread to think how slow random access over 2 billion
> columns would be in SQLite.
>
> I wrote earlier that for us use case where we are reading whole rows
> is the most common one.
>
> > Your gene expressions are data.  They are not the names of table
> entities.  They should be stored in a table as other posts suggested.
>
> Maybe. But often this data is represented as a row of expressions with
> columns for each gene. Because this is what is being distributed, we
> are looking for ways to store this in a stable format which will be
> supported for next 50 years, without modifying to original data too
> much. I do hear suggestions to do such transformation, but that is
> less ideal for our use case.
>
>
> Mitar
>
> --
> http://mitar.tnode.com/
> https://twitter.com/mitar_m
> ___
> 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] Limit on number of columns in SQLite table

[Mitar]

Hi!

This is getting a bit off topic.

On Thu, Oct 17, 2019 at 12:07 PM Simon Slavin  wrote:
> 1) Almost no piece of software can handle a grid 2 billion cells wide.  Excel 
> maxes out at 16,384 columns.  Matlab can store and retrieve a cell of data 
> directly from a file, but it has a max array size of 1.  R maxes out at 
> 2147483647, which is more than 2 billion.  But R has to hold all the data 
> from a matrix in memory at once and it can't assign enough memory to one 
> object to hold that many cells.

Of course, 2 billion is a lot. But 100k is something many ML libraries
support. Pandas, ndarray, R. Not something to magical about that.

> 2) Object names are not data.  They're descriptions in your favourite human 
> language.  They're not meant to have weird sequences of characters in.

Not sure what this relates to.

> 3) Lots of CSV import filters ignore a column header row, or can only create 
> fieldnames with certain limits (max length, no punctuation characters, etc.). 
>  So you should expect to lose fieldnames if you try to import your data into 
> some new piece of software.

Does SQLite have limitations on what can be a column name? If not,
then I would not worry what some CSV importers do. We would use a good
one to convert to SQLLite.

> (4) SQLite stores all the data for a row is together, in a sequence.  If you 
> ask for the data in the 3756th column of a row, SQLite has to read and parse 
> the data for the first 3755 columns of that row, just to read a single value 
> from storage.  As you can imagine, this is slow and involves a lot of I/O.  
> And while it happens the row up to that point must all be held in memory.  
> Consequently, nobody who uses SQLite for its intended purpose actually does 
> this.  I dread to think how slow random access over 2 billion columns would 
> be in SQLite.

I wrote earlier that for us use case where we are reading whole rows
is the most common one.

> Your gene expressions are data.  They are not the names of table entities.  
> They should be stored in a table as other posts suggested.

Maybe. But often this data is represented as a row of expressions with
columns for each gene. Because this is what is being distributed, we
are looking for ways to store this in a stable format which will be
supported for next 50 years, without modifying to original data too
much. I do hear suggestions to do such transformation, but that is
less ideal for our use case.


Mitar

-- 
http://mitar.tnode.com/
https://twitter.com/mitar_m
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit on number of columns in SQLite table

[Simon Slavin]

On 17 Oct 2019, at 9:56am, Mitar  wrote:

> I can understand how supporting a large number of columns might be
> inappropriate when you want to run complicated SQL queries on data,
> but to just store data and then extract all rows to do some data
> processing, Or as the most complicated query it would be to extract
> just a subsample of rows. But not really do to any JOIN queries or
> something like that. it looks like except for artificial limit in
> SQLite, because it is not useful for general case, there is no other
> reason why it could not be supported.
> 
> So why not increase the limit to 2 billion

1) Almost no piece of software can handle a grid 2 billion cells wide.  Excel 
maxes out at 16,384 columns.  Matlab can store and retrieve a cell of data 
directly from a file, but it has a max array size of 1.  R maxes out at 
2147483647, which is more than 2 billion.  But R has to hold all the data from 
a matrix in memory at once and it can't assign enough memory to one object to 
hold that many cells.

2) Object names are not data.  They're descriptions in your favourite human 
language.  They're not meant to have weird sequences of characters in.

3) Lots of CSV import filters ignore a column header row, or can only create 
fieldnames with certain limits (max length, no punctuation characters, etc.).  
So you should expect to lose fieldnames if you try to import your data into 
some new piece of software.

And here't the dealbreaker:

(4) SQLite stores all the data for a row is together, in a sequence.  If you 
ask for the data in the 3756th column of a row, SQLite has to read and parse 
the data for the first 3755 columns of that row, just to read a single value 
from storage.  As you can imagine, this is slow and involves a lot of I/O.  And 
while it happens the row up to that point must all be held in memory.  
Consequently, nobody who uses SQLite for its intended purpose actually does 
this.  I dread to think how slow random access over 2 billion columns would be 
in SQLite.

So rereading the text from our message I quoted at the top.  No.  Nobody would 
ever use SQLite to store a table 2 billion columns wide even if they didn't 
intend to do anything complicated to it.  Because it would take a ridiculous 
amount of time and memory just to insert data in to a table that wide.

Your gene expressions are data.  They are not the names of table entities.  
They should be stored in a table as other posts suggested.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit on number of columns in SQLite table

[Mitar]

Hi!

I can see how this is a reasonable limit when SQLite is used for
querying power it provides. In our case we are really focusing on it
as a standard long-term storage format. So in the "Appropriate Uses
For SQLite" document [1] you have a section called "File archive
and/or data container" and this is why we started considering SQLite
as a dataset archive format. We would not like to store files
directly, but contents of those files (like contents of CSV). But try
to not modify them more than necessary. So we got interested
especially in the "SQLite is a good solution for any situation that
requires bundling diverse content into a self-contained and
self-describing package for shipment across a network." statement. So
I can understand how supporting a large number of columns might be
inappropriate when you want to run complicated SQL queries on data,
but to just store data and then extract all rows to do some data
processing, Or as the most complicated query it would be to extract
just a subsample of rows. But not really do to any JOIN queries or
something like that. it looks like except for artificial limit in
SQLite, because it is not useful for general case, there is no other
reason why it could not be supported.

So why not increase the limit to 2 billion, and have it at runtime by
default limited to 2000. And then using PRAGMA one could increase this
if needed to 2 billion? PRAGMA already can decrease the limit, so we
can keep the existing 2000 limit, but to support it without having to
recompile, people could increase it all the way to 2 billion. Is there
any significant performance downside to this?

[1] https://www.sqlite.org/whentouse.html


Mitar

On Wed, Oct 16, 2019 at 8:21 PM Richard Hipp  wrote:
>
> SQLite could, in theory, be enhanced (with just a few minor tweaks) to
> support up to 2 billion columns.  But having a relation with a large
> number of columns seems like a very bad idea stylistically.  That's
> not how relational databases are intended to be used.  Normally when a
> table acquires more than a couple dozen columns, that is a good
> indication that you need normalize and/or refactor your schema. Schema
> designers almost unanimously follow that design principle.  And so
> SQLite is optimized for the overwhelmingly common case of a small
> number of columns per table.
>
> Hence, adding the ability to have a table with a huge number of
> columns is not something that I am interested in supporting in SQLite
> at this time.
>
> --
> D. Richard Hipp
> d...@sqlite.org



-- 
http://mitar.tnode.com/
https://twitter.com/mitar_m
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit on number of columns in SQLite table

[Jens Alfke]

> On Oct 16, 2019, at 6:08 AM, Mitar  wrote:
> 
> Quite
> some of datasets we are dealing with have 100k or so columns.

There was a thread about this a few months ago. You Should not store every 
number of a huge vector in a separate column. You don’t need to individually 
query on every individual item, do you? Just store the vector as one column — 
JSON works well, or a blob containing a C array.

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

Re: [sqlite] Limit on number of columns in SQLite table

[Richard Hipp]

SQLite could, in theory, be enhanced (with just a few minor tweaks) to
support up to 2 billion columns.  But having a relation with a large
number of columns seems like a very bad idea stylistically.  That's
not how relational databases are intended to be used.  Normally when a
table acquires more than a couple dozen columns, that is a good
indication that you need normalize and/or refactor your schema. Schema
designers almost unanimously follow that design principle.  And so
SQLite is optimized for the overwhelmingly common case of a small
number of columns per table.

Hence, adding the ability to have a table with a huge number of
columns is not something that I am interested in supporting in SQLite
at this time.

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

Re: [sqlite] Limit on number of columns in SQLite table

[Mitar]

Hi!

On Wed, Oct 16, 2019 at 3:29 PM Richard Hipp  wrote:
> Are you trying to store a big matrix with approx 100k columns?  A
> better way to do that in a relational database (*any* relational
> database, not just SQLite) is to store one entry per matrix elements:

Sure, this is useful for sparse matrix. But in our case we would like
to preserve as much as possible of the original dataset structure. So
those types of transformations then require also users to restructure
data back. For example, Pandas DataFrame can be easily read out of the
SQLite, but if it is converted in a way you described, then user has
to make one more step to move things around.

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_sql_table.html

I would hope that if this is something which is generally a good idea,
that databases would internally do such mapping, while exposing
regular structure back to the user.


Mitar

-- 
http://mitar.tnode.com/
https://twitter.com/mitar_m
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit on number of columns in SQLite table

[Richard Hipp]

On 10/16/19, Mitar  wrote:
> Hi!
>
> We are considering using SQLite as a ML dataset archival format for
> datasets in OpenML (https://www.openml.org/). When investigating it,
> we noticed that it has a very low limit on number of columns. Quite
> some of datasets we are dealing with have 100k or so columns. Are
> there any fundamental reasons why this limit is so low (even if we
> extend it during compiling, it can be at most 32k columns), while
> others are comfortably large? Any plans to extend this limit in the
> future?

Are you trying to store a big matrix with approx 100k columns?  A
better way to do that in a relational database (*any* relational
database, not just SQLite) is to store one entry per matrix elements:

CREATE TABLE matrix(row_number INT, column_number INT, value ANY);

Only three columns are required in such a relation, regardless of the
number of columns in the matrix.

If performance and storage efficiency are a high priority and if the
number of rows and columns are limited to 2 billion each, then you can
combine the row and column number into a single integer key:

cell_number := row_number*2147483648 + column_number;

Then make your table just this:

CREATE TABLE matrix(cell_number INTEGER PRIMARY KEY, value ANY);

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

Re: [sqlite] LIMIT

[Thomas Kurz]

> Don't want to spoil any news, it is viewable by the public anyway, but there 
> are clearly experimental, interesting (and significant) work ongoing by the 
> SQLite developers around SQL windowing functions.  A quick look to the 
> exp-window-functions branch clearly shows that.

They are part of the current draft release:
http://www.sqlite.org/draft/releaselog/3_25_0.html

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

Re: [sqlite] LIMIT

[Richard Rousselot]

Just wanted to support the idea of adding windowing functions to SQLite as
it is the biggest hurdle for me to get others to use it.  If size is an
issue, I would love to have a build option to enable it.

My $0.02

On Tue, Jun 26, 2018 at 8:27 AM Gert Van Assche  wrote:

> Thanks Olivier, very good to know.
>
> gert
>
> Op di 26 jun. 2018 om 09:06 schreef Olivier Mascia :
>
> > > Le 26 juin 2018 à 07:56, Gert Van Assche  a écrit :
> > >
> > > If I would like to use these ranking techniques in SQLite, can I do
> this
> > > with an extension? Has nobody created a Windowing.dll yet?
> > > Just asking. My short term need has been solved, but now that I know
> > this,
> > > I'm sure I'd like to use this technique later. But I like to stick to
> > > SQLite.
> >
> > Don't want to spoil any news, it is viewable by the public anyway, but
> > there are clearly experimental, interesting (and significant) work
> ongoing
> > by the SQLite developers around SQL windowing functions.  A quick look to
> > the exp-window-functions branch clearly shows that.
> >
> > https://www.sqlite.org/src/timeline?n=100=exp-window-functions
> >
> > So it _may_ be possible that you see these features in a later version of
> > SQLite, or not.  We'll see.  I certainly wish the best for this
> experience
> > which looks good and nicely ongoing.
> >
> > From experience as a user of SQLite, not all development of features
> > through branches get merged to the trunk.  Sometimes they stay available
> as
> > a branch for you to choose to use, sometimes they end up in the main
> SQLite
> > product (trunk branch).  Sometimes they can stay significant time aside
> > before one day being merged.  I have no specific expectations, and you
> > shouldn't have too, I'm just monitoring areas of development that are
> > interesting to my eyes and programming needs.  Mainly the
> > 'server-process-edition' branch, the 'begin-concurrent-pnu' branch and
> this
> > 'exp-window-functions' branch.
> >
> > :)
> >
> > --
> > Best Regards, Meilleures salutations, Met vriendelijke groeten,
> > Olivier Mascia
> >
> >
> > ___
> > 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] LIMIT

[Gert Van Assche]

Thanks Olivier, very good to know.

gert

Op di 26 jun. 2018 om 09:06 schreef Olivier Mascia :

> > Le 26 juin 2018 à 07:56, Gert Van Assche  a écrit :
> >
> > If I would like to use these ranking techniques in SQLite, can I do this
> > with an extension? Has nobody created a Windowing.dll yet?
> > Just asking. My short term need has been solved, but now that I know
> this,
> > I'm sure I'd like to use this technique later. But I like to stick to
> > SQLite.
>
> Don't want to spoil any news, it is viewable by the public anyway, but
> there are clearly experimental, interesting (and significant) work ongoing
> by the SQLite developers around SQL windowing functions.  A quick look to
> the exp-window-functions branch clearly shows that.
>
> https://www.sqlite.org/src/timeline?n=100=exp-window-functions
>
> So it _may_ be possible that you see these features in a later version of
> SQLite, or not.  We'll see.  I certainly wish the best for this experience
> which looks good and nicely ongoing.
>
> From experience as a user of SQLite, not all development of features
> through branches get merged to the trunk.  Sometimes they stay available as
> a branch for you to choose to use, sometimes they end up in the main SQLite
> product (trunk branch).  Sometimes they can stay significant time aside
> before one day being merged.  I have no specific expectations, and you
> shouldn't have too, I'm just monitoring areas of development that are
> interesting to my eyes and programming needs.  Mainly the
> 'server-process-edition' branch, the 'begin-concurrent-pnu' branch and this
> 'exp-window-functions' branch.
>
> :)
>
> --
> Best Regards, Meilleures salutations, Met vriendelijke groeten,
> Olivier Mascia
>
>
> ___
> 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] LIMIT

[Olivier Mascia]

> Le 26 juin 2018 à 07:56, Gert Van Assche  a écrit :
> 
> If I would like to use these ranking techniques in SQLite, can I do this
> with an extension? Has nobody created a Windowing.dll yet?
> Just asking. My short term need has been solved, but now that I know this,
> I'm sure I'd like to use this technique later. But I like to stick to
> SQLite.

Don't want to spoil any news, it is viewable by the public anyway, but there 
are clearly experimental, interesting (and significant) work ongoing by the 
SQLite developers around SQL windowing functions.  A quick look to the 
exp-window-functions branch clearly shows that.

https://www.sqlite.org/src/timeline?n=100=exp-window-functions

So it _may_ be possible that you see these features in a later version of 
SQLite, or not.  We'll see.  I certainly wish the best for this experience 
which looks good and nicely ongoing.

From experience as a user of SQLite, not all development of features through 
branches get merged to the trunk.  Sometimes they stay available as a branch 
for you to choose to use, sometimes they end up in the main SQLite product 
(trunk branch).  Sometimes they can stay significant time aside before one day 
being merged.  I have no specific expectations, and you shouldn't have too, I'm 
just monitoring areas of development that are interesting to my eyes and 
programming needs.  Mainly the 'server-process-edition' branch, the 
'begin-concurrent-pnu' branch and this 'exp-window-functions' branch.

:)

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia


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

Re: [sqlite] LIMIT

[Gert Van Assche]

Very interesting,  Ryan. Thanks for sharing.

If I would like to use these ranking techniques in SQLite, can I do this
with an extension? Has nobody created a Windowing.dll yet?
Just asking. My short term need has been solved, but now that I know this,
I'm sure I'd like to use this technique later. But I like to stick to
SQLite.

Gert

Op ma 25 jun. 2018 om 01:00 schreef R Smith :

>
> On 2018/06/24 9:30 PM, Gert Van Assche wrote:
> > whaw... I could never come up with solutions like this. Will this work
> with
> > SQLite?
> > Where can I read more about this?
>
> I've only shown that query since Dan asked about it - it's not an SQLite
> query.
>
> To answer/comment on your questions:
>
> 1. Sure you can come up with it if you have read about it and understand
> what it is intended for - There is nothing you can't know, only things
> you don't know yet. :)
>
> 2. This will not work in SQLite currently - it's part of a group of
> functions called "Windowing" functions built into the bigger DB engines
> which do not care about their "Liteness".
> "Windowing functions" is basically a set of functions that treats the
> current ROW and its immediate preceding or acceding rows, or set of
> grouped rows within a bigger query (grouping by whatever common aspect
> you choose) as a unit and can return localized aggregates over them.
> The ranking/row position functions prove especially handy when wanting
> queries to easily produce row orders, ranks, etc. - but I promise you,
> they all are very possible in SQLite without Windowing functions, just
> not quite as obvious (as this thread has shown). More difficult to do
> are things like Percentile or Running-Total, though those too can be
> accomplished with a self-join or correlated sub-query in SQLite.
> The example in the original mail is from MSSQL, but similar queries work
> in Postgres, Oracle, etc.
>
> 3. You can read more by Googling "RDBMS Windowing functions" - or just
> click here: http://google.com/?q=RDBMS+windowing+functions
>
>
> Quick intro to Windowing in SQL (Apologies to all, the following is NOT
> for SQLite [yet]):
>
> Say I have a list of students in three classrooms and their test scores,
> and I would like to show the listing ranked by their scores, per class.
>
>
> Sorting makes part of the problem easy:
>
> SELECT class, student, score
>FROM students
>   ORDER BY class, student, score DESC
> ;  -- This works in SQLite
>
> This might produce a list like:
>
> class, student, score
> A5, Ann, 94
> A5, Able,88
> A5, Adrian,  67
> B5, James,   92
> B5, Jenny,   88
> B5, John,87
> B5, Joan,74
> C5, Sloan,   98
> C5, Sean,79
>
> Now a common thing is to want to put a rank integer number in front of
> every classmate so it is easy to see position, like this (spaced for
> legibility only):
>
> rank, class, student, score
> 1, A5, Ann, 94
> 2, A5, Able,88
> 3, A5, Adrian,  67
>
> 1, B5, James,   92
> 2, B5, Jenny,   88
> 3, B5, John,87
> 4, B5, Joan,74
>
> 1, C5, Sloan,   98
> 2, C5, Sean,79
>
> And for that the query will have to produce the whole list (no overall
> grouping) but then group these students by "class" to figure out the
> rank within every class "window" of the  larger query.
>
> You could say in English: We need to show the RANK *over* every window
> (aka "set of records") that is *partitioned by* the "class" field, where
> the rank within each "class" is determined by the value of the "score"
> field in a *descending order*.  That's easy enough to understand (I hope!).
>
> All that remains is to translate that to SQL. Looking at the English
> phrase above and the SQL below should quickly clarify how it all fits
> together:
>
> SELECT RANK() OVER (PARTITION BY class ORDER BY score DESC) as rank,
> class, student, score
>FROM students
>   ORDER BY class, student, score DESC
> ;  -- This example is MSSQL format
>
>
> If you don't care about the rank, and simply wish to number the rows in
> some order (student name for example), you could opt for something like:
>
> SELECT ROW_NUMBER() OVER (PARTITION BY class ORDER BY student) as
> student_no, class, student, score
>FROM students
>   ORDER BY class, student
> ;  -- MSSQL again
>
> which will output this:
>
> student_no, class, student, score
> 1, A5, Able,88
> 2, A5, Adrian,  67
> 3, A5, Ann, 94
> 1, B5, James,   92
> 2, B5, Jenny,   88
> 3, B5, Joan,74
> 4, B5, John,87
> 1, C5, Sean,79
> 2, C5, Sloan,   98
>
> Of course the aggregate doesn't need to be a rank or row number, it can
> simply be a count() or Avg() or any aggregate function you fancy.
>
> Lastly, if I now want to only see the top 2 ranked students of every
> class (which is like your original question in this thread), I can
> simply have the RANK query above as an inner query and in the outer
> query add "... WHERE rank <= 2; "[1].
>
> rank, class, student, score
> 1, A5, Ann, 94
> 2, A5, Able,88
> 1, B5, James,   92
> 2, B5, Jenny,   88
> 1, 

Re: [sqlite] LIMIT

[R Smith]

On 2018/06/24 9:30 PM, Gert Van Assche wrote:

whaw... I could never come up with solutions like this. Will this work with
SQLite?
Where can I read more about this?


I've only shown that query since Dan asked about it - it's not an SQLite 
query.


To answer/comment on your questions:

1. Sure you can come up with it if you have read about it and understand 
what it is intended for - There is nothing you can't know, only things 
you don't know yet. :)


2. This will not work in SQLite currently - it's part of a group of 
functions called "Windowing" functions built into the bigger DB engines 
which do not care about their "Liteness".
"Windowing functions" is basically a set of functions that treats the 
current ROW and its immediate preceding or acceding rows, or set of 
grouped rows within a bigger query (grouping by whatever common aspect 
you choose) as a unit and can return localized aggregates over them.
The ranking/row position functions prove especially handy when wanting 
queries to easily produce row orders, ranks, etc. - but I promise you, 
they all are very possible in SQLite without Windowing functions, just 
not quite as obvious (as this thread has shown). More difficult to do 
are things like Percentile or Running-Total, though those too can be 
accomplished with a self-join or correlated sub-query in SQLite.
The example in the original mail is from MSSQL, but similar queries work 
in Postgres, Oracle, etc.


3. You can read more by Googling "RDBMS Windowing functions" - or just 
click here: http://google.com/?q=RDBMS+windowing+functions



Quick intro to Windowing in SQL (Apologies to all, the following is NOT 
for SQLite [yet]):


Say I have a list of students in three classrooms and their test scores, 
and I would like to show the listing ranked by their scores, per class.



Sorting makes part of the problem easy:

SELECT class, student, score
  FROM students
 ORDER BY class, student, score DESC
;  -- This works in SQLite

This might produce a list like:

class, student, score
A5, Ann,     94
A5, Able,    88
A5, Adrian,  67
B5, James,   92
B5, Jenny,   88
B5, John,    87
B5, Joan,    74
C5, Sloan,   98
C5, Sean,    79

Now a common thing is to want to put a rank integer number in front of 
every classmate so it is easy to see position, like this (spaced for 
legibility only):


rank, class, student, score
1, A5, Ann,     94
2, A5, Able,    88
3, A5, Adrian,  67

1, B5, James,   92
2, B5, Jenny,   88
3, B5, John,    87
4, B5, Joan,    74

1, C5, Sloan,   98
2, C5, Sean,    79

And for that the query will have to produce the whole list (no overall 
grouping) but then group these students by "class" to figure out the 
rank within every class "window" of the  larger query.


You could say in English: We need to show the RANK *over* every window 
(aka "set of records") that is *partitioned by* the "class" field, where 
the rank within each "class" is determined by the value of the "score" 
field in a *descending order*.  That's easy enough to understand (I hope!).


All that remains is to translate that to SQL. Looking at the English 
phrase above and the SQL below should quickly clarify how it all fits 
together:


SELECT RANK() OVER (PARTITION BY class ORDER BY score DESC) as rank, 
class, student, score

  FROM students
 ORDER BY class, student, score DESC
;  -- This example is MSSQL format


If you don't care about the rank, and simply wish to number the rows in 
some order (student name for example), you could opt for something like:


SELECT ROW_NUMBER() OVER (PARTITION BY class ORDER BY student) as 
student_no, class, student, score

  FROM students
 ORDER BY class, student
;  -- MSSQL again

which will output this:

student_no, class, student, score
1, A5, Able,    88
2, A5, Adrian,  67
3, A5, Ann,     94
1, B5, James,   92
2, B5, Jenny,   88
3, B5, Joan,    74
4, B5, John,    87
1, C5, Sean,    79
2, C5, Sloan,   98

Of course the aggregate doesn't need to be a rank or row number, it can 
simply be a count() or Avg() or any aggregate function you fancy.


Lastly, if I now want to only see the top 2 ranked students of every 
class (which is like your original question in this thread), I can 
simply have the RANK query above as an inner query and in the outer 
query add "... WHERE rank <= 2; "[1].


rank, class, student, score
1, A5, Ann,     94
2, A5, Able,    88
1, B5, James,   92
2, B5, Jenny,   88
1, C5, Sloan,   98
2, C5, Sean,    79


Now you know. :)
Cheers,
Ryan


[1] - Rank is only useful as a value if the order-by is of a unique 
field (or combination of fields). Consider if the top 5 students had 
scores like 95, 88, 88, 88 and 75. The RANK() function over them would 
produce ranks 1, 2, 2, 2, 5 - because technically the 3 middle scores 
are all the same and so all ranked equally 2nd, while the 5th score is 
3rd highest and will be ranked either 5th or 3rd based on whether you 
use RANK() [5th] or DENSE_RANK() [3rd] functions.


To change this to a solid numbered list, it 

Re: [sqlite] LIMIT

[Gert Van Assche]

whaw... I could never come up with solutions like this. Will this work with
SQLite?
Where can I read more about this?

Op zo 24 jun. 2018 om 20:29 schreef R Smith :

>
>
> On 2018/06/22 11:05 PM, Dan Kennedy wrote:
> > On 06/23/2018 03:52 AM, R Smith wrote:
> >>
> >> On 2018/06/22 10:04 PM, Gert Van Assche wrote:
> >>>   All,
> >>>
> >>> I'm sure it must be possible, I just don't find how.
> >>> I have a table T with 2 fields (F1 and F2). The F1 are unique and
> >>> the F2
> >>> are not unique.
> >>> I would like to get only 10 F1 fields for each unique F2.
> >>
> >> This is not normally done, and windowing functions in other RDBMSes
> >> makes for an easier way, but it can still be done in SQLite with some
> >> creative grouping of a self-joined query.
> >> In this example, I limited it to 3 F1 items per unique F2 for
> >> brevity, but you can easily change the "< 4" to "< 11" or "<= 10"
> >> according to preference.
> >
> > What would the window-function query be?
>
> Mainly one can avoid the self-join with a windowing function, like this:
>
> SELECT F2, F1 FROM (SELECT ROW_COUNT() OVER (PARTITION BY F2 ORDER BY
> F1) AS C, F2, F1) AS XWHERE X.C <= 10
>
> or
>
> SELECT F2, F1
>FROM (
>   SELECT F2, F1, DENSE_RANK() OVER (PARTITION BY F2 ORDER BY F1
> DESC) AS R
>) AS X
> WHERE R <= 10
>
>
>
> ___
> 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] LIMIT

[R Smith]

On 2018/06/22 11:05 PM, Dan Kennedy wrote:

On 06/23/2018 03:52 AM, R Smith wrote:


On 2018/06/22 10:04 PM, Gert Van Assche wrote:

  All,

I'm sure it must be possible, I just don't find how.
I have a table T with 2 fields (F1 and F2). The F1 are unique and 
the F2

are not unique.
I would like to get only 10 F1 fields for each unique F2.


This is not normally done, and windowing functions in other RDBMSes 
makes for an easier way, but it can still be done in SQLite with some 
creative grouping of a self-joined query.
In this example, I limited it to 3 F1 items per unique F2 for 
brevity, but you can easily change the "< 4" to "< 11" or "<= 10" 
according to preference.


What would the window-function query be?


Mainly one can avoid the self-join with a windowing function, like this:

SELECT F2, F1 FROM (SELECT ROW_COUNT() OVER (PARTITION BY F2 ORDER BY 
F1) AS C, F2, F1) AS XWHERE X.C <= 10


or

SELECT F2, F1
  FROM (
 SELECT F2, F1, DENSE_RANK() OVER (PARTITION BY F2 ORDER BY F1 
DESC) AS R

  ) AS X
WHERE R <= 10



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

Re: [sqlite] LIMIT

[Barry]

I checked with a fake dataset: the GROUP BY solution is quicker with no
index on F2 (the query planner can understand that query well enough to
create an automatic covering index). However, with an index on F2, the
rowid solution is considerably faster for a dataset with 2600 random
distinct F2 and one million total rows in T.

The test script is at the end of the email. On my computer, with the rowid
correlation, the query returned in 14 seconds*. With the GROUP BY solution,
the query took 50 seconds.

It was interesting to see that both queries ran quickly spitting out values
until SQLite had returned all possible values, then stalled for a long time
as the DB engine processed all rows that could never return anything.

Cheers,

 - Barry

[*I suspected that most of the time with the rowid query was taken in
writing to the console, so I SELECT'd COUNT(*) instead of * and it returned
in 6 seconds, and I suspect still did most of the work (the query plan was
the same). I tried the same thing on the GROUP BY query, but the GROUP BY
and COUNT don't play so well together and it still spat out a bunch of
numbers to the console.]

CREATE TABLE T(F1 INTEGER PRIMARY KEY, F2 TEXT NOT NULL);

CREATE TABLE Names(id INTEGER PRIMARY KEY, Name TEXT NOT NULL);

INSERT INTO Names(Name) VALUES
('Alex'),('Brett'),('Cindy'),('Dennis'),('Echo'),('Frank'),('Garry'),('Herbert'),('Indigo'),('Jack'),('Karl'),('Lima'),('Melon'),('Nunes'),('Oprah'),('Peter'),('Quincy'),('Robert'),('Sarah'),('Tangerine'),('Unicorn'),('Violet'),('Wilfred'),('Violet'),('Wesley'),('Xavier'),('Yeltzin'),('Zbrudov');

WITH num(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM num WHERE i < 100)
INSERT INTO Names(Name)
SELECT Name || i FROM Names, Num;

WITH num(i, rnd) AS (SELECT 1, RANDOM()%1300 + 1300 UNION ALL SELECT i + 1,
RANDOM()%1300 + 1300 FROM num WHERE i < 100)
INSERT INTO T(F2)
SELECT (SELECT Name FROM Names WHERE id = rnd) FROM num;

CREATE INDEX idx_F2 ON T(F2);

On Sun, 24 Jun 2018 at 07:00, Gert Van Assche  wrote:

> Ryan, my dataset isn't that big. 11K records.
> Your solution is noticeable faster 996 ms vs 13126 ms.
> Interesting!
>
> gert
>
> Op za 23 jun. 2018 om 18:09 schreef R Smith :
>
> >
> > On 2018/06/23 2:47 PM, Gert Van Assche wrote:
> > > Barry, that's even easier indeed. And it works perfect!
> > > Thanks for sharing this.
> >
> > I very much like the method which Barry suggested for the simplicity,
> > but have avoided it since I thought it would be significantly slower on
> > a large data set, however, SQLite is known for some pretty neat
> > optimizations (especially where the row_id is concerned) and I'm now
> > interested to know if indeed such optimizations feature in this case. I
> > have a few such implemented queries that might benefit from changing
> over.
> >
> > May I ask, if you do have a rather large dataset, and perhaps don't mind
> > the time, would you care to compare the two methods and let us know if
> > there is any discernible difference in speed? Also one small important
> > item - how many F1 items are there typically per unique F2 item in
> > total? Does it grow over time?
> >
> > Also, you do not need hashes, for either of the two queries - the
> > queries should work regardless of integer or Unicode text used, perhaps
> > the comparison might need a specific collation? Surrounding both
> > references with a TRIM() function might also help. Essentially, if F1 of
> > record 3 in Table T is '*&^%@#*&^@#' then   "... FROM T AS A, T AS B
> > WHERE A.F1 = B.F1" must resolve correctly. If it doesn't, it's a bug.
> > The <, <=, >, >= might all produce some Unicode weirdness upon
> > inadequate collations.
> >
> >
> > If time doesn't allow, then don't spend effort on this, it's simply a
> > curiosity. :)
> >
> > Thanks!
> > 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIMIT

[Gert Van Assche]

Ryan, my dataset isn't that big. 11K records.
Your solution is noticeable faster 996 ms vs 13126 ms.
Interesting!

gert

Op za 23 jun. 2018 om 18:09 schreef R Smith :

>
> On 2018/06/23 2:47 PM, Gert Van Assche wrote:
> > Barry, that's even easier indeed. And it works perfect!
> > Thanks for sharing this.
>
> I very much like the method which Barry suggested for the simplicity,
> but have avoided it since I thought it would be significantly slower on
> a large data set, however, SQLite is known for some pretty neat
> optimizations (especially where the row_id is concerned) and I'm now
> interested to know if indeed such optimizations feature in this case. I
> have a few such implemented queries that might benefit from changing over.
>
> May I ask, if you do have a rather large dataset, and perhaps don't mind
> the time, would you care to compare the two methods and let us know if
> there is any discernible difference in speed? Also one small important
> item - how many F1 items are there typically per unique F2 item in
> total? Does it grow over time?
>
> Also, you do not need hashes, for either of the two queries - the
> queries should work regardless of integer or Unicode text used, perhaps
> the comparison might need a specific collation? Surrounding both
> references with a TRIM() function might also help. Essentially, if F1 of
> record 3 in Table T is '*&^%@#*&^@#' then   "... FROM T AS A, T AS B
> WHERE A.F1 = B.F1" must resolve correctly. If it doesn't, it's a bug.
> The <, <=, >, >= might all produce some Unicode weirdness upon
> inadequate collations.
>
>
> If time doesn't allow, then don't spend effort on this, it's simply a
> curiosity. :)
>
> Thanks!
> 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] LIMIT

[Simon Slavin]

On 23 Jun 2018, at 5:08pm, R Smith  wrote:

> May I ask, if you do have a rather large dataset, and perhaps don't mind the 
> time, would you care to compare the two methods and let us know if there is 
> any discernible difference in speed?

Answers will be different depending on type of main storage, size of cache 
available, and how much of the computer's processors are devoted to SQLite.  I 
used to have an overnight run (not using SQLite) on an early dual-processor 
machine which took three times longer to work if a tiny clock was showing on 
the display.  The clock hogged one of the processors.

It would be an interesting comparison, but it's valid only for the setup it's 
run on.

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

Re: [sqlite] LIMIT

[R Smith]

On 2018/06/23 2:47 PM, Gert Van Assche wrote:

Barry, that's even easier indeed. And it works perfect!
Thanks for sharing this.


I very much like the method which Barry suggested for the simplicity, 
but have avoided it since I thought it would be significantly slower on 
a large data set, however, SQLite is known for some pretty neat 
optimizations (especially where the row_id is concerned) and I'm now 
interested to know if indeed such optimizations feature in this case. I 
have a few such implemented queries that might benefit from changing over.


May I ask, if you do have a rather large dataset, and perhaps don't mind 
the time, would you care to compare the two methods and let us know if 
there is any discernible difference in speed? Also one small important 
item - how many F1 items are there typically per unique F2 item in 
total? Does it grow over time?


Also, you do not need hashes, for either of the two queries - the 
queries should work regardless of integer or Unicode text used, perhaps 
the comparison might need a specific collation? Surrounding both 
references with a TRIM() function might also help. Essentially, if F1 of 
record 3 in Table T is '*&^%@#*&^@#' then   "... FROM T AS A, T AS B 
WHERE A.F1 = B.F1" must resolve correctly. If it doesn't, it's a bug.  
The <, <=, >, >= might all produce some Unicode weirdness upon 
inadequate collations.



If time doesn't allow, then don't spend effort on this, it's simply a 
curiosity. :)


Thanks!
Ryan


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

Re: [sqlite] LIMIT

[Gert Van Assche]

Barry, that's even easier indeed. And it works perfect!
Thanks for sharing this.

gert

Op za 23 jun. 2018 om 14:32 schreef Barry Smith :

> Ryan's way works well. Here is a second method which expresses it in a
> different way:
>
> SELECT * FROM T t1 where rowid IN (SELECT rowid FROM T t2 WHERE t1.F2 =
> t2.F2 ORDER BY rowid LIMIT 10)
>
> If you have WITHOUT ROWID tables you'd have to replace rowid with your
> primary key.
>
> (The query may still work without the ORDER BY, I didn't test it, but even
> if it does a future query optimiser might break that because without the
> order by the results of the inner select are free to include a different 10
> rowids for every value in the outer query)
>
> > On 23 Jun 2018, at 9:50 pm, Gert Van Assche  wrote:
> >
> > Hi Ryan, thanks for this. This is working if the F1 field is a numeric
> > value. With text (especially Asian & Arabic characters) this does not
> seem
> > to work.
> > So I created an MD5 hash from the text fields and it works great! Thank
> you
> > so much.
> >
> > gert
> >
> > Op vr 22 jun. 2018 om 22:52 schreef R Smith :
> >
> >>
> >>> On 2018/06/22 10:04 PM, Gert Van Assche wrote:
> >>>  All,
> >>>
> >>> I'm sure it must be possible, I just don't find how.
> >>> I have a table T with 2 fields (F1 and F2). The F1 are unique and the
> F2
> >>> are not unique.
> >>> I would like to get only 10 F1 fields for each unique F2.
> >>
> >> This is not normally done, and windowing functions in other RDBMSes
> >> makes for an easier way, but it can still be done in SQLite with some
> >> creative grouping of a self-joined query.
> >> In this example, I limited it to 3 F1 items per unique F2 for brevity,
> >> but you can easily change the "< 4" to "< 11" or "<= 10" according to
> >> preference.
> >>
> >>   -- SQLite version 3.20.1  [ Release: 2017-08-24 ]  on SQLitespeed
> >> version 2.0.2.4.
> >>   --
> >>
> >>
> 
> >>
> >> CREATE TABLE T(F1 INTEGER PRIMARY KEY, F2 TEXT);
> >>
> >> INSERT INTO T(F1,F2) VALUES
> >>  (1,  'John')
> >> ,(2,  'John')
> >> ,(3,  'Jason')
> >> ,(4,  'John')
> >> ,(5,  'Jason')
> >> ,(6,  'John')
> >> ,(7,  'John')
> >> ,(8,  'Jason')
> >> ,(9,  'Jason')
> >> ,(10,  'Joan')
> >> ,(11,  'Joan')
> >> ,(12,  'Joan')
> >> ,(13,  'Jimmy')
> >> ;
> >>
> >> SELECT A.F2, B.F1
> >>   FROM T AS A
> >>   JOIN T AS B ON B.F2 = A.F2 AND B.F1 >= A.F1
> >>  GROUP BY A.F2, B.F1
> >>  HAVING COUNT(*) < 4
> >> ;
> >>
> >>   -- F2|  F1
> >>   -- - | ---
> >>   -- Jason |  3
> >>   -- Jason |  5
> >>   -- Jason |  8
> >>   -- Jimmy |  13
> >>   -- Joan  |  10
> >>   -- Joan  |  11
> >>   -- Joan  |  12
> >>   -- John  |  1
> >>   -- John  |  2
> >>   -- John  |  4
> >>
> >>
> >>
> >> -- Another option to note, in case the 10 limit is not important and
> >> simply aimed
> >> -- at saving space, is to use group concatenation, like so:
> >>
> >> SELECT F2, group_concat(F1)AS F1
> >>   FROM T
> >>  GROUP BY F2
> >> ;
> >>
> >>   --   |
> >>   -- F2|F1
> >>   -- - | -
> >>   -- Jason |  3,5,8,9
> >>   -- Jimmy | 13
> >>   -- Joan  |  10,11,12
> >>   -- John  | 1,2,4,6,7
> >>
> >>
> >>   --
> >>
> >>
> 
> >>
> >> 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
> ___
> 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] LIMIT

[Barry Smith]

Ryan's way works well. Here is a second method which expresses it in a 
different way:

SELECT * FROM T t1 where rowid IN (SELECT rowid FROM T t2 WHERE t1.F2 = t2.F2 
ORDER BY rowid LIMIT 10)

If you have WITHOUT ROWID tables you'd have to replace rowid with your primary 
key.

(The query may still work without the ORDER BY, I didn't test it, but even if 
it does a future query optimiser might break that because without the order by 
the results of the inner select are free to include a different 10 rowids for 
every value in the outer query)

> On 23 Jun 2018, at 9:50 pm, Gert Van Assche  wrote:
> 
> Hi Ryan, thanks for this. This is working if the F1 field is a numeric
> value. With text (especially Asian & Arabic characters) this does not seem
> to work.
> So I created an MD5 hash from the text fields and it works great! Thank you
> so much.
> 
> gert
> 
> Op vr 22 jun. 2018 om 22:52 schreef R Smith :
> 
>> 
>>> On 2018/06/22 10:04 PM, Gert Van Assche wrote:
>>>  All,
>>> 
>>> I'm sure it must be possible, I just don't find how.
>>> I have a table T with 2 fields (F1 and F2). The F1 are unique and the F2
>>> are not unique.
>>> I would like to get only 10 F1 fields for each unique F2.
>> 
>> This is not normally done, and windowing functions in other RDBMSes
>> makes for an easier way, but it can still be done in SQLite with some
>> creative grouping of a self-joined query.
>> In this example, I limited it to 3 F1 items per unique F2 for brevity,
>> but you can easily change the "< 4" to "< 11" or "<= 10" according to
>> preference.
>> 
>>   -- SQLite version 3.20.1  [ Release: 2017-08-24 ]  on SQLitespeed
>> version 2.0.2.4.
>>   --
>> 
>> 
>> 
>> CREATE TABLE T(F1 INTEGER PRIMARY KEY, F2 TEXT);
>> 
>> INSERT INTO T(F1,F2) VALUES
>>  (1,  'John')
>> ,(2,  'John')
>> ,(3,  'Jason')
>> ,(4,  'John')
>> ,(5,  'Jason')
>> ,(6,  'John')
>> ,(7,  'John')
>> ,(8,  'Jason')
>> ,(9,  'Jason')
>> ,(10,  'Joan')
>> ,(11,  'Joan')
>> ,(12,  'Joan')
>> ,(13,  'Jimmy')
>> ;
>> 
>> SELECT A.F2, B.F1
>>   FROM T AS A
>>   JOIN T AS B ON B.F2 = A.F2 AND B.F1 >= A.F1
>>  GROUP BY A.F2, B.F1
>>  HAVING COUNT(*) < 4
>> ;
>> 
>>   -- F2|  F1
>>   -- - | ---
>>   -- Jason |  3
>>   -- Jason |  5
>>   -- Jason |  8
>>   -- Jimmy |  13
>>   -- Joan  |  10
>>   -- Joan  |  11
>>   -- Joan  |  12
>>   -- John  |  1
>>   -- John  |  2
>>   -- John  |  4
>> 
>> 
>> 
>> -- Another option to note, in case the 10 limit is not important and
>> simply aimed
>> -- at saving space, is to use group concatenation, like so:
>> 
>> SELECT F2, group_concat(F1)AS F1
>>   FROM T
>>  GROUP BY F2
>> ;
>> 
>>   --   |
>>   -- F2|F1
>>   -- - | -
>>   -- Jason |  3,5,8,9
>>   -- Jimmy | 13
>>   -- Joan  |  10,11,12
>>   -- John  | 1,2,4,6,7
>> 
>> 
>>   --
>> 
>> 
>> 
>> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIMIT

[Gert Van Assche]

Hi Ryan, thanks for this. This is working if the F1 field is a numeric
value. With text (especially Asian & Arabic characters) this does not seem
to work.
So I created an MD5 hash from the text fields and it works great! Thank you
so much.

gert

Op vr 22 jun. 2018 om 22:52 schreef R Smith :

>
> On 2018/06/22 10:04 PM, Gert Van Assche wrote:
> >   All,
> >
> > I'm sure it must be possible, I just don't find how.
> > I have a table T with 2 fields (F1 and F2). The F1 are unique and the F2
> > are not unique.
> > I would like to get only 10 F1 fields for each unique F2.
>
> This is not normally done, and windowing functions in other RDBMSes
> makes for an easier way, but it can still be done in SQLite with some
> creative grouping of a self-joined query.
> In this example, I limited it to 3 F1 items per unique F2 for brevity,
> but you can easily change the "< 4" to "< 11" or "<= 10" according to
> preference.
>
>-- SQLite version 3.20.1  [ Release: 2017-08-24 ]  on SQLitespeed
> version 2.0.2.4.
>--
>
> 
>
> CREATE TABLE T(F1 INTEGER PRIMARY KEY, F2 TEXT);
>
> INSERT INTO T(F1,F2) VALUES
>   (1,  'John')
> ,(2,  'John')
> ,(3,  'Jason')
> ,(4,  'John')
> ,(5,  'Jason')
> ,(6,  'John')
> ,(7,  'John')
> ,(8,  'Jason')
> ,(9,  'Jason')
> ,(10,  'Joan')
> ,(11,  'Joan')
> ,(12,  'Joan')
> ,(13,  'Jimmy')
> ;
>
> SELECT A.F2, B.F1
>FROM T AS A
>JOIN T AS B ON B.F2 = A.F2 AND B.F1 >= A.F1
>   GROUP BY A.F2, B.F1
>   HAVING COUNT(*) < 4
> ;
>
>-- F2|  F1
>-- - | ---
>-- Jason |  3
>-- Jason |  5
>-- Jason |  8
>-- Jimmy |  13
>-- Joan  |  10
>-- Joan  |  11
>-- Joan  |  12
>-- John  |  1
>-- John  |  2
>-- John  |  4
>
>
>
> -- Another option to note, in case the 10 limit is not important and
> simply aimed
> -- at saving space, is to use group concatenation, like so:
>
> SELECT F2, group_concat(F1)AS F1
>FROM T
>   GROUP BY F2
> ;
>
>--   |
>-- F2|F1
>-- - | -
>-- Jason |  3,5,8,9
>-- Jimmy | 13
>-- Joan  |  10,11,12
>-- John  | 1,2,4,6,7
>
>
>--
>
> 
>
> 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] LIMIT

[sub sk79]

> What would the window-function query be?


Note: For anyone stumbling upon this thread, below code is not supported in
SQLite natively.

SELECT F1, F2

FROM (

SELECT F2, F1, Rank()

  OVER (PARTITION BY F2

ORDER BY F1 ) AS Rank

FROM T

)  WHERE Rank <= 10

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

Re: [sqlite] LIMIT

[Dan Kennedy]

On 06/23/2018 03:52 AM, R Smith wrote:


On 2018/06/22 10:04 PM, Gert Van Assche wrote:

  All,

I'm sure it must be possible, I just don't find how.
I have a table T with 2 fields (F1 and F2). The F1 are unique and the F2
are not unique.
I would like to get only 10 F1 fields for each unique F2.


This is not normally done, and windowing functions in other RDBMSes 
makes for an easier way, but it can still be done in SQLite with some 
creative grouping of a self-joined query.
In this example, I limited it to 3 F1 items per unique F2 for brevity, 
but you can easily change the "< 4" to "< 11" or "<= 10" according to 
preference.


What would the window-function query be?

Dan.





  -- SQLite version 3.20.1  [ Release: 2017-08-24 ]  on SQLitespeed 
version 2.0.2.4.
  -- 



CREATE TABLE T(F1 INTEGER PRIMARY KEY, F2 TEXT);

INSERT INTO T(F1,F2) VALUES
 (1,  'John')
,(2,  'John')
,(3,  'Jason')
,(4,  'John')
,(5,  'Jason')
,(6,  'John')
,(7,  'John')
,(8,  'Jason')
,(9,  'Jason')
,(10,  'Joan')
,(11,  'Joan')
,(12,  'Joan')
,(13,  'Jimmy')
;

SELECT A.F2, B.F1
  FROM T AS A
  JOIN T AS B ON B.F2 = A.F2 AND B.F1 >= A.F1
 GROUP BY A.F2, B.F1
 HAVING COUNT(*) < 4
;

  -- F2|  F1
  -- - | ---
  -- Jason |  3
  -- Jason |  5
  -- Jason |  8
  -- Jimmy |  13
  -- Joan  |  10
  -- Joan  |  11
  -- Joan  |  12
  -- John  |  1
  -- John  |  2
  -- John  |  4



-- Another option to note, in case the 10 limit is not important and 
simply aimed

-- at saving space, is to use group concatenation, like so:

SELECT F2, group_concat(F1)AS F1
  FROM T
 GROUP BY F2
;

  --   |
  -- F2|F1
  -- - | -
  -- Jason |  3,5,8,9
  -- Jimmy | 13
  -- Joan  |  10,11,12
  -- John  | 1,2,4,6,7


  -- 



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

[R Smith]

On 2018/06/22 10:04 PM, Gert Van Assche wrote:

  All,

I'm sure it must be possible, I just don't find how.
I have a table T with 2 fields (F1 and F2). The F1 are unique and the F2
are not unique.
I would like to get only 10 F1 fields for each unique F2.


This is not normally done, and windowing functions in other RDBMSes 
makes for an easier way, but it can still be done in SQLite with some 
creative grouping of a self-joined query.
In this example, I limited it to 3 F1 items per unique F2 for brevity, 
but you can easily change the "< 4" to "< 11" or "<= 10" according to 
preference.


  -- SQLite version 3.20.1  [ Release: 2017-08-24 ]  on SQLitespeed 
version 2.0.2.4.
  -- 



CREATE TABLE T(F1 INTEGER PRIMARY KEY, F2 TEXT);

INSERT INTO T(F1,F2) VALUES
 (1,  'John')
,(2,  'John')
,(3,  'Jason')
,(4,  'John')
,(5,  'Jason')
,(6,  'John')
,(7,  'John')
,(8,  'Jason')
,(9,  'Jason')
,(10,  'Joan')
,(11,  'Joan')
,(12,  'Joan')
,(13,  'Jimmy')
;

SELECT A.F2, B.F1
  FROM T AS A
  JOIN T AS B ON B.F2 = A.F2 AND B.F1 >= A.F1
 GROUP BY A.F2, B.F1
 HAVING COUNT(*) < 4
;

  -- F2    |  F1
  -- - | ---
  -- Jason |  3
  -- Jason |  5
  -- Jason |  8
  -- Jimmy |  13
  -- Joan  |  10
  -- Joan  |  11
  -- Joan  |  12
  -- John  |  1
  -- John  |  2
  -- John  |  4



-- Another option to note, in case the 10 limit is not important and 
simply aimed

-- at saving space, is to use group concatenation, like so:

SELECT F2, group_concat(F1)AS F1
  FROM T
 GROUP BY F2
;

  --   |
  -- F2    |    F1
  -- - | -
  -- Jason |  3,5,8,9
  -- Jimmy | 13
  -- Joan  |  10,11,12
  -- John  | 1,2,4,6,7


  -- 



Cheers!
Ryan


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

Re: [sqlite] LIMIT with OFFSET is not working in android platform

[Sathish Kumar]

Hi Richard,
 Today, i verified with android NDK 17 and It is working fine without
disabling compiler optimization.

Thanks,
Sathish

On Mon, May 21, 2018 at 9:41 PM, Sathish Kumar 
wrote:

> Thanks, I will do that.
>
> Thanks,
> Sathish
>
>
> On Mon 21 May, 2018, 9:34 PM Richard Hipp,  wrote:
>
>> On 5/21/18, Sathish Kumar  wrote:
>> > Hi Richard,
>> >  Yes, it works if i disable the compiler optimization. Below is
>> what i
>> > used in my Android.mk file. Will it create any side effects if we
>> disable
>> > compiler optimization ?
>> >
>> > LOCAL_CFLAGS += -O0
>> >
>> > LOCAL_CPPFLAGS += -O0
>>
>> Looks like you have found a bug in clang.  I recommend updating to the
>> latest version of the compiler that you can get your hands on, and see
>> if that doesn't fix the problem.
>>
>> Running SQLite with compiler optimizations off will make it slower.
>> --
>> 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

Re: [sqlite] LIMIT with OFFSET is not working in android platform

[Dominique Pellé]

On Mon, May 21, 2018 at 6:04 PM Richard Hipp  wrote:

> > LOCAL_CFLAGS += -O0
> >
> > LOCAL_CPPFLAGS += -O0

> Looks like you have found a bug in clang.  I recommend updating to the
> latest version of the compiler that you can get your hands on, and see
> if that doesn't fix the problem.

> Running SQLite with compiler optimizations off will make it slower.

Either it is a bug in clang, or SQLite has an undefined behavior,
which could cause differences in debug and release mode.

Can you try to build with -fsanitize=undefined and see if it
reports anything?

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

Re: [sqlite] LIMIT with OFFSET is not working in android platform

[Sathish Kumar]

Thanks, I will do that.

Thanks,
Sathish


On Mon 21 May, 2018, 9:34 PM Richard Hipp,  wrote:

> On 5/21/18, Sathish Kumar  wrote:
> > Hi Richard,
> >  Yes, it works if i disable the compiler optimization. Below is what
> i
> > used in my Android.mk file. Will it create any side effects if we disable
> > compiler optimization ?
> >
> > LOCAL_CFLAGS += -O0
> >
> > LOCAL_CPPFLAGS += -O0
>
> Looks like you have found a bug in clang.  I recommend updating to the
> latest version of the compiler that you can get your hands on, and see
> if that doesn't fix the problem.
>
> Running SQLite with compiler optimizations off will make it slower.
> --
> 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

Re: [sqlite] LIMIT with OFFSET is not working in android platform

[Richard Hipp]

On 5/21/18, Sathish Kumar  wrote:
> Hi Richard,
>  Yes, it works if i disable the compiler optimization. Below is what i
> used in my Android.mk file. Will it create any side effects if we disable
> compiler optimization ?
>
> LOCAL_CFLAGS += -O0
>
> LOCAL_CPPFLAGS += -O0

Looks like you have found a bug in clang.  I recommend updating to the
latest version of the compiler that you can get your hands on, and see
if that doesn't fix the problem.

Running SQLite with compiler optimizations off will make it slower.
-- 
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

Re: [sqlite] LIMIT with OFFSET is not working in android platform

[Sathish Kumar]

Hi Richard,
 Yes, it works if i disable the compiler optimization. Below is what i
used in my Android.mk file. Will it create any side effects if we disable
compiler optimization ?

LOCAL_CFLAGS += -O0

LOCAL_CPPFLAGS += -O0


Thanks,
Sathish

On Fri, May 18, 2018 at 7:28 PM, Sathish Kumar 
wrote:

> Ok, I will try.
>
> On Fri 18 May, 2018, 7:15 PM Richard Hipp,  wrote:
>
>> On 5/18/18, Sathish Kumar  wrote:
>> > Hi Richard,
>> > Further update on this issue.
>> >
>> > I am observing this issue only with the library build using
>> ndk-build
>> > via terminal and the library built via android studio gradle works
>> fine. Is
>> > there any difference in compiling sqlite using gradle and via ndk-build
>> via
>> > terminal ?
>>
>> Try building with compiler optimizations disabled (-O0) and see if
>> that makes a difference.
>>
>> --
>> 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

Re: [sqlite] LIMIT with OFFSET is not working in android platform

[Sathish Kumar]

Ok, I will try.

On Fri 18 May, 2018, 7:15 PM Richard Hipp,  wrote:

> On 5/18/18, Sathish Kumar  wrote:
> > Hi Richard,
> > Further update on this issue.
> >
> > I am observing this issue only with the library build using ndk-build
> > via terminal and the library built via android studio gradle works fine.
> Is
> > there any difference in compiling sqlite using gradle and via ndk-build
> via
> > terminal ?
>
> Try building with compiler optimizations disabled (-O0) and see if
> that makes a difference.
>
> --
> 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

Re: [sqlite] LIMIT with OFFSET is not working in android platform

[Richard Hipp]

On 5/18/18, Sathish Kumar  wrote:
> Hi Richard,
> Further update on this issue.
>
> I am observing this issue only with the library build using ndk-build
> via terminal and the library built via android studio gradle works fine. Is
> there any difference in compiling sqlite using gradle and via ndk-build via
> terminal ?

Try building with compiler optimizations disabled (-O0) and see if
that makes a difference.

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

Re: [sqlite] LIMIT with OFFSET is not working in android platform

[Sathish Kumar]

Hi Richard,
Further update on this issue.

I am observing this issue only with the library build using ndk-build
via terminal and the library built via android studio gradle works fine. Is
there any difference in compiling sqlite using gradle and via ndk-build via
terminal ?


Thanks,
Sathish

On Thu, May 17, 2018 at 8:07 AM, Sathish Kumar 
wrote:

> Hi Richard,
>   Is there any issue with this compile option ?
>
>
> Thanks,
> Sathish
>
>
> On Wed 16 May, 2018, 5:30 PM Sathish Kumar, 
> wrote:
>
>> sqlite> SELECT sqlite_source_id();
>>
>> 2017-06-08 14:26:16 0ee482a1e0eae22e08edc8978c9733
>> a96603d4509645f348ebf55b579e89636b
>>
>>
>> sqlite> PRAGMA compile_options;
>>
>> COMPILER=clang-5.0.300080
>>
>> DEFAULT_SYNCHRONOUS=2
>>
>> DEFAULT_WAL_SYNCHRONOUS=2
>>
>> ENABLE_FTS3
>>
>> ENABLE_FTS5
>>
>> ENABLE_JSON1
>>
>> ENABLE_RTREE
>>
>> SYSTEM_MALLOC
>>
>> TEMP_STORE=3
>>
>> THREADSAFE=1
>>
>> sqlite>
>>
>>
>> On Wed, May 16, 2018 at 5:29 PM, Richard Hipp  wrote:
>>
>>> On 5/16/18, Sathish Kumar  wrote:
>>> > Samsung S9 device with Android 8.0
>>>
>>> What output do you see from the following commands:
>>>
>>>   SELECT sqlite_source_id();
>>>   PRAGMA compile_options;
>>>
>>> --
>>> 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

Re: [sqlite] LIMIT with OFFSET is not working in android platform

[Sathish Kumar]

Hi Richard,
  Is there any issue with this compile option ?


Thanks,
Sathish


On Wed 16 May, 2018, 5:30 PM Sathish Kumar,  wrote:

> sqlite> SELECT sqlite_source_id();
>
> 2017-06-08 14:26:16
> 0ee482a1e0eae22e08edc8978c9733a96603d4509645f348ebf55b579e89636b
>
>
> sqlite> PRAGMA compile_options;
>
> COMPILER=clang-5.0.300080
>
> DEFAULT_SYNCHRONOUS=2
>
> DEFAULT_WAL_SYNCHRONOUS=2
>
> ENABLE_FTS3
>
> ENABLE_FTS5
>
> ENABLE_JSON1
>
> ENABLE_RTREE
>
> SYSTEM_MALLOC
>
> TEMP_STORE=3
>
> THREADSAFE=1
>
> sqlite>
>
>
> On Wed, May 16, 2018 at 5:29 PM, Richard Hipp  wrote:
>
>> On 5/16/18, Sathish Kumar  wrote:
>> > Samsung S9 device with Android 8.0
>>
>> What output do you see from the following commands:
>>
>>   SELECT sqlite_source_id();
>>   PRAGMA compile_options;
>>
>> --
>> 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

Re: [sqlite] LIMIT with OFFSET is not working in android platform

[Sathish Kumar]

sqlite> SELECT sqlite_source_id();

2017-06-08 14:26:16
0ee482a1e0eae22e08edc8978c9733a96603d4509645f348ebf55b579e89636b


sqlite> PRAGMA compile_options;

COMPILER=clang-5.0.300080

DEFAULT_SYNCHRONOUS=2

DEFAULT_WAL_SYNCHRONOUS=2

ENABLE_FTS3

ENABLE_FTS5

ENABLE_JSON1

ENABLE_RTREE

SYSTEM_MALLOC

TEMP_STORE=3

THREADSAFE=1

sqlite>


On Wed, May 16, 2018 at 5:29 PM, Richard Hipp  wrote:

> On 5/16/18, Sathish Kumar  wrote:
> > Samsung S9 device with Android 8.0
>
> What output do you see from the following commands:
>
>   SELECT sqlite_source_id();
>   PRAGMA compile_options;
>
> --
> 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

Re: [sqlite] LIMIT with OFFSET is not working in android platform

[Dan Kennedy]

On 05/16/2018 06:20 PM, Sathish Kumar wrote:

Hi,
  Sorry, Please find the full sequence below, it is taken from S9 device
running with Android 8.0.


This is a bit strange. The VM code looks correct. Try running a [make 
clean] to ensure you're getting a clean build.


If that doesn't work, please try building with SQLITE_DEBUG defined and 
post the results of running the script below. It's working on Android 7 
here.


Dan.



create table test (id integer primary key autoincrement, name text);

insert into test (name) values('a');
insert into test (name) values('a');
insert into test (name) values('a');
insert into test (name) values('a');
insert into test (name) values('a');
insert into test (name) values('a');
insert into test (name) values('a');

.print "--- select count(*) from test;"
select count(*) from test;
.print "--- select * from test;"
select * from test;
.print "--- select * from test limit 10 offset 2;"
select * from test limit 10 offset 2;
.print "--- select * from test limit 10 offset 5;"
select * from test limit 10 offset 5;
.print "--- select * from test limit 2 offset 3;"
select * from test limit 2 offset 3;
.print "--- EXPLAIN SELECT * FROM test LIMIT 2 OFFSET 5;"
EXPLAIN SELECT * FROM test LIMIT 2 OFFSET 5;
.print "--- SELECT * FROM test LIMIT 2 OFFSET 5;"
SELECT * FROM test LIMIT 2 OFFSET 5;
.print "--- with vdbe_trace"
PRAGMA vdbe_trace = 1;
SELECT * FROM test LIMIT 2 OFFSET 5;






sqlite> create table test (id integer primary key autoincrement, name
text);

sqlite> insert into test (name) values('a');
sqlite> insert into test (name) values('a');
sqlite> insert into test (name) values('a');
sqlite> insert into test (name) values('a');
sqlite> insert into test (name) values('a');
sqlite> insert into test (name) values('a');
sqlite> insert into test (name) values('a');
sqlite> select count(*) from test;
7
sqlite> select * from test limit 10 offset 2;
2|a
3|a
4|a
5|a
6|a
7|a
sqlite> select * from test limit 10 offset 5;
2|a
3|a
4|a
5|a
6|a
7|a
sqlite> select * from test limit 2 offset 3;
2|a
3|a
sqlite> select * from test limit 2 offset 5;
2|a
3|a
sqlite> select * from test limit 2,5;
2|a
3|a
4|a
5|a
6|a
sqlite> select * from test limit 5,2;
2|a
3|a
sqlite> select * from test limit 5,3;
2|a
3|a
4|a
sqlite> select * from test;
1|a
2|a
3|a
4|a
5|a
6|a
7|a
sqlite> EXPLAIN SELECT * FROM test LIMIT 2 OFFSET 5;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 14000
1 Integer2 1 000
2 Integer5 2 000
3 MustBeInt  2 0 000
4 OffsetLimit1 3 200
5 OpenRead   0 383   0 2  00
6 Rewind 0 13000
7   IfPos  2 12100
8   Rowid  0 4 000
9   Column 0 1 500
10  ResultRow  4 2 000
11  DecrJumpZero   1 13000
12Next   0 7 001
13Halt   0 0 000
14Transaction0 0 440  01
15Goto   0 1 000

sqlite> .schema test
CREATE TABLE test (id integer primary key autoincrement, name text);

sqlite> PRAGMA table_info(test);
0|id|integer|0||1
1|name|text|0||0

Thanks,
Sathish

On Wed, May 16, 2018 at 4:19 PM, Richard Hipp  wrote:


On 5/16/18, Sathish Kumar  wrote:

I just skipped those statements in mail, but i inserted 7 rows.

Please send *exactly* the sequence of commands that you think are
producing an incorrect answer.  Please leave nothing to chance, or to
interpretation.

--
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] LIMIT with OFFSET is not working in android platform

[Richard Hipp]

On 5/16/18, Sathish Kumar  wrote:
> Samsung S9 device with Android 8.0

What output do you see from the following commands:

  SELECT sqlite_source_id();
  PRAGMA compile_options;

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

Re: [sqlite] LIMIT with OFFSET is not working in android platform

[Sathish Kumar]

Samsung S9 device with Android 8.0

On Wed, May 16, 2018 at 4:50 PM, Sathish Kumar 
wrote:

> Hi,
>  Sorry, Please find the full sequence below, it is taken from S9 device
> running with Android 8.0.
>
> sqlite> create table test (id integer primary key autoincrement, name
> text);
>
> sqlite> insert into test (name) values('a');
> sqlite> insert into test (name) values('a');
> sqlite> insert into test (name) values('a');
> sqlite> insert into test (name) values('a');
> sqlite> insert into test (name) values('a');
> sqlite> insert into test (name) values('a');
> sqlite> insert into test (name) values('a');
> sqlite> select count(*) from test;
> 7
> sqlite> select * from test limit 10 offset 2;
> 2|a
> 3|a
> 4|a
> 5|a
> 6|a
> 7|a
> sqlite> select * from test limit 10 offset 5;
> 2|a
> 3|a
> 4|a
> 5|a
> 6|a
> 7|a
> sqlite> select * from test limit 2 offset 3;
> 2|a
> 3|a
> sqlite> select * from test limit 2 offset 5;
> 2|a
> 3|a
> sqlite> select * from test limit 2,5;
> 2|a
> 3|a
> 4|a
> 5|a
> 6|a
> sqlite> select * from test limit 5,2;
> 2|a
> 3|a
> sqlite> select * from test limit 5,3;
> 2|a
> 3|a
> 4|a
> sqlite> select * from test;
> 1|a
> 2|a
> 3|a
> 4|a
> 5|a
> 6|a
> 7|a
> sqlite> EXPLAIN SELECT * FROM test LIMIT 2 OFFSET 5;
> addr  opcode p1p2p3p4 p5  comment
>   -        -  --  -
> 0 Init   0 14000
> 1 Integer2 1 000
> 2 Integer5 2 000
> 3 MustBeInt  2 0 000
> 4 OffsetLimit1 3 200
> 5 OpenRead   0 383   0 2  00
> 6 Rewind 0 13000
> 7   IfPos  2 12100
> 8   Rowid  0 4 000
> 9   Column 0 1 500
> 10  ResultRow  4 2 000
> 11  DecrJumpZero   1 13000
> 12Next   0 7 001
> 13Halt   0 0 000
> 14Transaction0 0 440  01
> 15Goto   0 1 000
>
> sqlite> .schema test
> CREATE TABLE test (id integer primary key autoincrement, name text);
>
> sqlite> PRAGMA table_info(test);
> 0|id|integer|0||1
> 1|name|text|0||0
>
> Thanks,
> Sathish
>
> On Wed, May 16, 2018 at 4:19 PM, Richard Hipp  wrote:
>
>> On 5/16/18, Sathish Kumar  wrote:
>> > I just skipped those statements in mail, but i inserted 7 rows.
>>
>> Please send *exactly* the sequence of commands that you think are
>> producing an incorrect answer.  Please leave nothing to chance, or to
>> interpretation.
>>
>> --
>> 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

Re: [sqlite] LIMIT with OFFSET is not working in android platform

[Sathish Kumar]

Hi,
 Sorry, Please find the full sequence below, it is taken from S9 device
running with Android 8.0.

sqlite> create table test (id integer primary key autoincrement, name
text);

sqlite> insert into test (name) values('a');
sqlite> insert into test (name) values('a');
sqlite> insert into test (name) values('a');
sqlite> insert into test (name) values('a');
sqlite> insert into test (name) values('a');
sqlite> insert into test (name) values('a');
sqlite> insert into test (name) values('a');
sqlite> select count(*) from test;
7
sqlite> select * from test limit 10 offset 2;
2|a
3|a
4|a
5|a
6|a
7|a
sqlite> select * from test limit 10 offset 5;
2|a
3|a
4|a
5|a
6|a
7|a
sqlite> select * from test limit 2 offset 3;
2|a
3|a
sqlite> select * from test limit 2 offset 5;
2|a
3|a
sqlite> select * from test limit 2,5;
2|a
3|a
4|a
5|a
6|a
sqlite> select * from test limit 5,2;
2|a
3|a
sqlite> select * from test limit 5,3;
2|a
3|a
4|a
sqlite> select * from test;
1|a
2|a
3|a
4|a
5|a
6|a
7|a
sqlite> EXPLAIN SELECT * FROM test LIMIT 2 OFFSET 5;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 14000
1 Integer2 1 000
2 Integer5 2 000
3 MustBeInt  2 0 000
4 OffsetLimit1 3 200
5 OpenRead   0 383   0 2  00
6 Rewind 0 13000
7   IfPos  2 12100
8   Rowid  0 4 000
9   Column 0 1 500
10  ResultRow  4 2 000
11  DecrJumpZero   1 13000
12Next   0 7 001
13Halt   0 0 000
14Transaction0 0 440  01
15Goto   0 1 000

sqlite> .schema test
CREATE TABLE test (id integer primary key autoincrement, name text);

sqlite> PRAGMA table_info(test);
0|id|integer|0||1
1|name|text|0||0

Thanks,
Sathish

On Wed, May 16, 2018 at 4:19 PM, Richard Hipp  wrote:

> On 5/16/18, Sathish Kumar  wrote:
> > I just skipped those statements in mail, but i inserted 7 rows.
>
> Please send *exactly* the sequence of commands that you think are
> producing an incorrect answer.  Please leave nothing to chance, or to
> interpretation.
>
> --
> 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

Re: [sqlite] LIMIT with OFFSET is not working in android platform

[Richard Hipp]

On 5/16/18, Sathish Kumar  wrote:
> I just skipped those statements in mail, but i inserted 7 rows.

Please send *exactly* the sequence of commands that you think are
producing an incorrect answer.  Please leave nothing to chance, or to
interpretation.

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

Re: [sqlite] LIMIT with OFFSET is not working in android platform

[Sathish Kumar]

I just skipped those statements in mail, but i inserted 7 rows.

On Wed 16 May, 2018, 4:06 PM Richard Hipp,  wrote:

> On 5/16/18, Sathish Kumar  wrote:
> > Hi,
> > Please find the below example.
> >
> > sqlite> create table test (id integer primary key autoincrement, name
> > text);
> > sqlite> insert into test (name) values('a');
> > sqlite> insert into test (name) values('a');
> > sqlite> select count(*) from test;
> > 7
>
> How is it that you have 7 rows in the table after only doing 2 INSERTs?
>
> --
> 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

Re: [sqlite] LIMIT with OFFSET is not working in android platform

[Sathish Kumar]

Hi,
Please find the below example.

sqlite> create table test (id integer primary key autoincrement, name text);
sqlite> insert into test (name) values('a');
sqlite> insert into test (name) values('a');
sqlite> select count(*) from test;
7
sqlite> select * from test limit 10 offset 2;
2|a
3|a
4|a
5|a
6|a
7|a
sqlite> select * from test limit 10 offset 5;
2|a
3|a
4|a
5|a
6|a
7|a
sqlite> select * from test limit 2 offset 5;
2|a
3|a
sqlite> select * from test limit 5,3;
2|a
3|a
4|a

sqlite> EXPLAIN SELECT * FROM test LIMIT 2 OFFSET 5;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 14000
1 Integer2 1 000
2 Integer5 2 000
3 MustBeInt  2 0 000
4 OffsetLimit1 3 200
5 OpenRead   0 383   0 2  00
6 Rewind 0 13000
7   IfPos  2 12100
8   Rowid  0 4 000
9   Column 0 1 500
10  ResultRow  4 2 000
11  DecrJumpZero   1 13000
12Next   0 7 001
13Halt   0 0 000
14Transaction0 0 440  01
15Goto   0 1 000
sqlite>

sqlite> .schema test
CREATE TABLE test (id integer primary key autoincrement, name text);

sqlite> PRAGMA table_info(test);
0|id|integer|0||1
1|name|text|0||0
sqlite>

Thanks,
Sathish

On Wed, May 16, 2018 at 3:43 PM, Richard Hipp  wrote:

> On 5/16/18, Sathish Kumar  wrote:
> > Hi All,
> >  OFFSET clause is not working as expected in android platform.
> >
> > [ ISSUE ] : eg. select * from test limit 2 offset 5;
> >
> > [ Expected Result ] : This query should return two rows starting from
> index
> > 6.
> >
> > [ Actual Result ] : It always returns rows starting from index 2, even
> > though it had enough rows in the table. (Please see the below example, it
> > has all the informations).
>
> Unable to reproduce the problem.  The example text came through garbled.
>
> --
> 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

Re: [sqlite] LIMIT with OFFSET is not working in android platform

[Richard Hipp]

On 5/16/18, Sathish Kumar  wrote:
> Hi,
> Please find the below example.
>
> sqlite> create table test (id integer primary key autoincrement, name
> text);
> sqlite> insert into test (name) values('a');
> sqlite> insert into test (name) values('a');
> sqlite> select count(*) from test;
> 7

How is it that you have 7 rows in the table after only doing 2 INSERTs?

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

Re: [sqlite] LIMIT with OFFSET is not working in android platform

[Richard Hipp]

On 5/16/18, Sathish Kumar  wrote:
> Hi All,
>  OFFSET clause is not working as expected in android platform.
>
> [ ISSUE ] : eg. select * from test limit 2 offset 5;
>
> [ Expected Result ] : This query should return two rows starting from index
> 6.
>
> [ Actual Result ] : It always returns rows starting from index 2, even
> though it had enough rows in the table. (Please see the below example, it
> has all the informations).

Unable to reproduce the problem.  The example text came through garbled.

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

Re: [sqlite] LIMIT versus sqlite3_step LIMIT times

[Cezary H. Noweta]

Hello,

On 2018-02-16 13:00, Dominique Devienne wrote:

While you're technically right, I think of SQL as a declarative language,
and as such I'd say giving this information to SQLite is a best practice
IMHO.
Unlikely in this case, but perhaps one day SQLite might be able to optimize
"something" based on it. And in general, not all queries are that simple.
Giving the query planner all possible information should be recommended. My
$0.02. --DD


Indeed, you are absolutely right -- in addition, I have made a silent 
assumption that OP calls SQLite from C code using a simple construct 
like ``for ( i = 0; nofrows > i; ++i ) ...step...'' -- there are 
scenarios where SQLite VDBE's loop can be faster.


Ad. query planner -- I have mentioned that a cost of a full external 
sorting for WHERE condition can be reduced by a LIMIT clause -- this 
fact should be obviously considered.


-- best regards

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

Re: [sqlite] LIMIT versus sqlite3_step LIMIT times

[x]

I suppose ‘select * from (original select with limit clause) limit :lim’ gets 
round it.

From: Dominique Devienne<mailto:ddevie...@gmail.com>
Sent: 16 February 2018 12:21
To: SQLite mailing list<mailto:sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] LIMIT versus sqlite3_step LIMIT times

On Fri, Feb 16, 2018 at 1:11 PM, x <tam118...@hotmail.com> wrote:

> Thanks for the replies. For my purpose it was about avoiding the
> possibility of having to apply a limit to a query that might already have a
> limit clause.


Good point. I tried, and indeed that's an issue. I really really which
SQLite expose more meta-data about prepared statements :). --DD

C:\Users\ddevienne>sqlite3
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>
sqlite> create table t (c);
sqlite> insert into t values (1), (2), ('three');
sqlite> select * from t;
1
2
three
sqlite> select * from t limit 1;
1
sqlite> select * from t limit 1 limit 2;
Error: near "limit": syntax error
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] LIMIT versus sqlite3_step LIMIT times

[Dominique Devienne]

On Fri, Feb 16, 2018 at 1:11 PM, x  wrote:

> Thanks for the replies. For my purpose it was about avoiding the
> possibility of having to apply a limit to a query that might already have a
> limit clause.


Good point. I tried, and indeed that's an issue. I really really which
SQLite expose more meta-data about prepared statements :). --DD

C:\Users\ddevienne>sqlite3
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>
sqlite> create table t (c);
sqlite> insert into t values (1), (2), ('three');
sqlite> select * from t;
1
2
three
sqlite> select * from t limit 1;
1
sqlite> select * from t limit 1 limit 2;
Error: near "limit": syntax error
sqlite>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIMIT versus sqlite3_step LIMIT times

[x]

Thanks for the replies. For my purpose it was about avoiding the possibility of 
having to apply a limit to a query that might already have a limit clause.

From: Dominique Devienne<mailto:ddevie...@gmail.com>
Sent: 16 February 2018 12:00
To: SQLite mailing list<mailto:sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] LIMIT versus sqlite3_step LIMIT times

On Fri, Feb 16, 2018 at 12:33 PM, Cezary H. Noweta <c...@poczta.onet.pl>
wrote:

> On 2018-02-16 11:18, x wrote:
>
>> If a query is sorted on an index is there any advantage to including
>> LIMIT in the stmt as opposed to omitting it and stepping through the result
>> set LIMIT times?
>>
>
> No -- LIMIT appends an additional opcode to check the number of rows and
> introduces an effort related to an additional parsing. In general, LIMIT
> can reduce a cost of a full external sorting, however this does not apply
> to your query.
>

While you're technically right, I think of SQL as a declarative language,
and as such I'd say giving this information to SQLite is a best practice
IMHO.
Unlikely in this case, but perhaps one day SQLite might be able to optimize
"something" based on it. And in general, not all queries are that simple.
Giving the query planner all possible information should be recommended. My
$0.02. --DD
___
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] LIMIT versus sqlite3_step LIMIT times

[Dominique Devienne]

On Fri, Feb 16, 2018 at 12:33 PM, Cezary H. Noweta 
wrote:

> On 2018-02-16 11:18, x wrote:
>
>> If a query is sorted on an index is there any advantage to including
>> LIMIT in the stmt as opposed to omitting it and stepping through the result
>> set LIMIT times?
>>
>
> No -- LIMIT appends an additional opcode to check the number of rows and
> introduces an effort related to an additional parsing. In general, LIMIT
> can reduce a cost of a full external sorting, however this does not apply
> to your query.
>

While you're technically right, I think of SQL as a declarative language,
and as such I'd say giving this information to SQLite is a best practice
IMHO.
Unlikely in this case, but perhaps one day SQLite might be able to optimize
"something" based on it. And in general, not all queries are that simple.
Giving the query planner all possible information should be recommended. My
$0.02. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIMIT versus sqlite3_step LIMIT times

[Cezary H. Noweta]

Hello,

On 2018-02-16 11:18, x wrote:

If a query is sorted on an index is there any advantage to including LIMIT in 
the stmt as opposed to omitting it and stepping through the result set LIMIT 
times?


No -- LIMIT appends an additional opcode to check the number of rows and 
introduces an effort related to an additional parsing. In general, LIMIT 
can reduce a cost of a full external sorting, however this does not 
apply to your query.


BTW, in file ``src/select.c'' line 2377:

Expr *pLimit;/* Saved values of p->nLimit  */

should be

Expr *pLimit;/* Saved values of p->pLimit  */

-- best regards

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

Re: [sqlite] LIMIT doesn't return expected rows

[Tobias Ellinghaus]

Am Mittwoch, 12. Oktober 2016, 12:11:11 CEST schrieb Richard Hipp:
> On 10/11/16, Keith Medcalf  wrote:
> > #define SQLITE_ENABLE_EXPLAIN_COMMENTS 1
> > 
> > makes it work properly.  neither NDEBUG nor SQLITE_DEBUG explicitly
> > defined.
> > 
> > Over to Richard ...
> 
> Should now be fixed on trunk and in the latest Prerelease Snapshot at
> https://sqlite.org/download.html

Thank you very much for the quick help. Much appreciated. :-)

Tobias


signature.asc
Description: This is a digitally signed message part.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIMIT doesn't return expected rows

[David Empson]

Works for me building the sqlite3 command line tool from the prerelease 
snapshot, on both Mac and Windows.

SQLite version 3.15.0 2016-10-12 15:15:30
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .read test.sql
select sqlite_source_id();
2016-10-12 15:15:30 61f0526978af667781c57bcc87510e4524efd0d8
create table i (id integer primary key autoincrement, flags integer);
insert into i values (1,1),(2,1),(3,1),(4,1),(5,5),(6,6),(7,4);
create table m (id integer);
insert into m values (1),(2),(3),(4),(5),(6),(7);
SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC;
6
5
7
1
2
3
4
SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC limit 3;
6
5
7

> On 13/10/2016, at 5:11 AM, Richard Hipp  wrote:
> 
> On 10/11/16, Keith Medcalf  wrote:
>> 
>> #define SQLITE_ENABLE_EXPLAIN_COMMENTS 1
>> 
>> makes it work properly.  neither NDEBUG nor SQLITE_DEBUG explicitly defined.
>> 
>> Over to Richard ...
> 
> Should now be fixed on trunk and in the latest Prerelease Snapshot at
> https://sqlite.org/download.html
> 
> -- 
> 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] LIMIT doesn't return expected rows

[Richard Hipp]

On 10/11/16, Keith Medcalf  wrote:
>
> #define SQLITE_ENABLE_EXPLAIN_COMMENTS 1
>
> makes it work properly.  neither NDEBUG nor SQLITE_DEBUG explicitly defined.
>
> Over to Richard ...

Should now be fixed on trunk and in the latest Prerelease Snapshot at
https://sqlite.org/download.html

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

Re: [sqlite] LIMIT doesn't return expected rows

[Keith Medcalf]

#define SQLITE_ENABLE_EXPLAIN_COMMENTS 1

makes it work properly.  neither NDEBUG nor SQLITE_DEBUG explicitly defined.

Over to Richard ...

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Keith Medcalf
> Sent: Tuesday, 11 October, 2016 19:22
> To: SQLite mailing list
> Subject: Re: [sqlite] LIMIT doesn't return expected rows
> 
> 
> By default, I do not see the query being flattened.  Flattening it
> manually produces the same right results independant of something config.h
> does...
> 
> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC limit
> 3;
> 1
> 2
> 3
> sqlite> select i.id from i, m where i.id=m.id order by flags desc limit 3;
> select i.id from i, m where i.id=m.id order by flags desc limit 3;
> 6
> 5
> 7
> 
> > Well I can reproduce the wrong answer.  If I compile the amalgamation
> > code:
> >
> >   with no config.h I get the same result as you,
> >   but with the following config.h, the result is correct
> >
> >  (this is with the code from the head of trunk):
> >
> > #ifndef _CONFIG_H
> > #define _CONFIG_H
> >
> > // Values of WINVER and _WIN32_WINNT for various minimum levels of Win32
> > Compatability
> > //
> > // WIN100x0A00  WIN6 0x0600  W2K  0x0500 NT4
> > 0x0400
> > //  VISTA0x0600  WXP  0x0501 W95
> > 0x0400
> > //  W2K8 0x0600  W2K3 0x0502 NT4E
> > 0x0401
> > //  WIN7 0x0601  W98
> > 0x0410
> > //  WIN8 0x0602  WME
> > 0x0490
> > //  WIN810x0603
> >
> > #ifndef _WIN32_WINNT
> > #define _WIN32_WINNT 0x0600
> > #endif
> > #ifndef WINVER
> > #define WINVER _WIN32_WINNT
> > #endif
> >
> > // General Platform Compilation Support Options
> >
> > #define HAVE_MALLOC_USABLE_SIZE 1
> > #define HAVE_USLEEP 1
> >
> > // *** SQLITE GENERAL CONFIGURATION OPTIONS ***
> >
> > // #define SQLITE_DEFAULT_AUTOMATIC_INDEX  1   //
> default:
> > 1
> > // #define SQLITE_DEFAULT_AUTOVACUUM   0   //
> default:
> > 0
> > #define SQLITE_DEFAULT_CACHE_SIZE   65536   // 256 MB
> > // #define SQLITE_DEFAULT_FILE_FORMAT  4   //
> default:
> > 4
> > // #define SQLITE_DEFAULT_FILE_PERMISSIONS 0644//
> default:
> > 0644
> > #define SQLITE_DEFAULT_FOREIGN_KEYS 1   // default:
> 0
> > // #define SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT   4194304 //
> default:
> > -1
> > // #define SQLITE_DEFAULT_LOCKING_MODE 0   //
> default:
> > 0
> > // #define SQLITE_DEFAULT_MEMSTATUS1   //
> default:
> > 1
> > #define SQLITE_DEFAULT_PAGE_SIZE4096// default:
> > 4096 max: 65536
> > // #define SQLITE_DEFAULT_SYNCHRONOUS  2   //
> default:
> > 2
> > // #define SQLITE_DEFAULT_WAL_SYNCHRONOUS  2   //
> default:
> > same as default synchronous
> > // #define SQLITE_DEFAULT_WORKER_THREADS   4   //
> default:
> > 0
> > #define SQLITE_DEFAULT_WAL_AUTOCHECKPOINT   256 // default:
> > 1000 pages
> > #define SQLITE_DEFAULT_PROXYDIR_PERMISSIONS 0755// default:
> > 0755
> > #define SQLITE_DEFAULT_RECURSIVE_TRIGGERS   1   // default:
> 0
> > #define SQLITE_DEFAULT_TEMP_CACHE_SIZE  65536   // default:
> > 500 pages
> > #define SQLITE_DEFAULT_SHARED_CACHE 0   // default:
> 0
> > #define SQLITE_DEFAULT_MMAP_SIZE0   // default:
> 0
> > // #define SQLITE_LIKE_DOESNT_MATCH_BLOBS  1   //
> default:
> > undefined
> > // #define SQLITE_SORTER_PMASZ 64  //
> default:
> > 250
> > // #define SQLITE_EXTRA_DURABLE1   // Extra
> > DirSync's default not defined
> >
> >
> > // *** SQLITE FEATURE CONFIGURATION OPTIONS ***
> >
> > //#define SQLITE_ALLOW_COVERING_INDEX_SCAN 1
> > #define SQLITE_ENABLE_8_3_NAMES 1
> > // #define SQLITE_ENABLE_ATOMIC_WRITE 1
> > #define SQLITE_ENABLE_API_ARMOR 1   // Enable
> API
> > Armour
> > #define SQLITE_ENABLE_COLUMN_METADATA 1
> > #define SQLI

Re: [sqlite] LIMIT doesn't return expected rows

[Keith Medcalf]

 // Use
> Default System Heap (default if no other specified)
> // #define SQLITE_MALLOC_SOFT_LIMIT 1024
> // #define SQLITE_POWERSAFE_OVERWRITE 0
> // #define SQLITE_4_BYTE_ALIGNED_MALLOC 1
> // #define SQLITE_USE_ALLOCA 1 // Use
> AllocA to Allocate Parse object os Stack
> #define SQLITE_USE_QUADMATH 1   // Use 128-bit
> Floats if available
> 
> 
> // *** SQLITE OMIT FEATURES ***
> 
> // #define SQLITE_OMIT_ALTERTABLE
> // #define SQLITE_OMIT_ANALYZE
> // #define SQLITE_OMIT_ATTACH
> // #define SQLITE_OMIT_AUTHORIZATION
> // #define SQLITE_OMIT_AUTOINCREMENT
> // #define SQLITE_OMIT_AUTOINIT
> // #define SQLITE_OMIT_AUTOMATIC_INDEX
> // #define SQLITE_OMIT_AUTORESET
> // #define SQLITE_OMIT_AUTOVACUUM
> // #define SQLITE_OMIT_BETWEEN_OPTIMIZATION
> // #define SQLITE_OMIT_BLOB_LITERAL
> // #define SQLITE_OMIT_BTREECOUNT
> // #define SQLITE_OMIT_BUILTIN_TEST
> // #define SQLITE_OMIT_CAST
> // #define SQLITE_OMIT_CHECK
> // #define SQLITE_OMIT_COMPILEOPTION_DIAGS
> // #define SQLITE_OMIT_COMPLETE
> // #define SQLITE_OMIT_COMPOUND_SELECT
> // #define SQLITE_OMIT_DATETIME_FUNCS
> // #define SQLITE_OMIT_DECLTYPE
> // #define SQLITE_OMIT_DEPRECATED
> // #define SQLITE_OMIT_DISKIO
> // #define SQLITE_OMIT_EXPLAIN
> // #define SQLITE_OMIT_FLAG_PRAGMAS
> // #define SQLITE_OMIT_FLOATING_POINT
> // #define SQLITE_OMIT_FOREIGN_KEY
> // #define SQLITE_OMIT_GET_TABLE
> // #define SQLITE_OMIT_INCRBLOB
> // #define SQLITE_OMIT_INTEGRITY_CHECK
> // #define SQLITE_OMIT_LIKE_OPTIMIZATION
> // #define SQLITE_OMIT_LOAD_EXTENSION
> // #define SQLITE_OMIT_LOCALTIME
> // #define SQLITE_OMIT_LOOKASIDE
> // #define SQLITE_OMIT_MEMORYDB
> // #define SQLITE_OMIT_MERGE_SORT
> // #define SQLITE_OMIT_OR_OPTIMIZATION
> // #define SQLITE_OMIT_PAGER_PRAGMAS
> // #define SQLITE_OMIT_PRAGMA
> // #define SQLITE_OMIT_PROGRESS_CALLBACK
> // #define SQLITE_OMIT_QUICKBALANCE
> // #define SQLITE_OMIT_REINDEX
> // #define SQLITE_OMIT_SCHEMA_PRAGMAS
> // #define SQLITE_OMIT_SCHEMA_VERSION_PRAGMAS
> // #define SQLITE_OMIT_SHARED_CACHE
> // #define SQLITE_OMIT_SHUTDOWN_DIRECTORIES
> // #define SQLITE_OMIT_SUBQUERY
> // #define SQLITE_OMIT_TCL_VARIABLE
> // #define SQLITE_OMIT_TEMPDB
> // #define SQLITE_OMIT_TRACE
> // #define SQLITE_OMIT_TRIGGER
> // #define SQLITE_OMIT_TRUNCATE_OPTIMIZATION
> // #define SQLITE_OMIT_UTF16
> // #define SQLITE_OMIT_VACUUM
> // #define SQLITE_OMIT_VIEW
> // #define SQLITE_OMIT_VIRTUALTABLE
> // #define SQLITE_OMIT_WAL
> // #define SQLITE_OMIT_WSD
> // #define SQLITE_OMIT_XFER_OPT
> 
> // *** SQLITE DEBUGGING FEATURES ***
> 
> // #define SQLITE_DEBUG 1
> // #define SQLITE_ENABLE_EXPENSIVE_ASSERT 1
> // #define SQLITE_ENABLE_OVERSIZE_CELL_CHECK 1
> // #define SQLITE_ENABLE_SELECTTRACE 1 // Enable
> Select Trace (.selecttrace 0x100) needs SQLITE_DEBUG
> // #define SQLITE_ENABLE_SQLLOG 1  // Enable
> SQLITE_CONFIG_SQLLOG (see documentation)
> // #define SQLITE_ENABLE_STMT_SCANSTATUS 1 // Enable
> Collection of Statement Scan Status
> // #define SQLITE_ENABLE_WHERETRACE 1
> // #define SQLITE_IOTRACE 1
> // #define SQLITE_MEMDEBUG 1
> // #define SQLITE_REVERSE_UNORDERED_SELECTS 1
> // #define SQLITE_USE_FCNTL_TRACE 1// Enable
> extra vfslog fcntrl trace
> // #define SQLITE_YYTRACKMAXSTACKDEPTH 1
> 
> #if defined(_WIN32) && defined(__GNUC__)
> #define UNICODE_STRING_MAX_BYTES ((WORD) 65534)
> #define UNICODE_STRING_MAX_CHARS (32767)
> #define HAVE_FDATASYNC 1
> #define HAVE_GMTIME_R 1
> #define HAVE_LOCALTIME_S 1
> #define HAVE_ISNAN 1
> #define HAVE_MALLOC_USABLE_SIZE 1
> #define HAVE_USLEEP 1
> #define HAVE_UTIME 1
> #endif
> 
> #if defined(_MSC_VER)
> #define HAVE_FDATASYNC 1
> #define HAVE_GMTIME_R 1
> #define HAVE_LOCALTIME_S 1
> #define HAVE_MALLOC_USABLE_SIZE 1
> #define HAVE_USLEEP 1
> #define HAVE_UTIME 1
> #endif
> 
> #if defined(__GNUC__) && defined(SQLITE_USE_QUADMATH)
> #define LONGDOUBLE_TYPE __float128
> #endif
> #endif
> 
> 
> > -Original Message-
> > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > On Behalf Of David Empson
> > Sent: Tuesday, 11 October, 2016 18:41
> > To: SQLite mailing list
> > Subject: Re: [sqlite] LIMIT doesn't return expected rows
> >
> > Following up: same for the Mac distribution of 3.14.2 command line tool.
> > Using the pre-release snapshot of 3.15.0 from the main download page to
> > build the sqlite3 3.15.0 on the Mac, I get the same answer: 1,2,3.
> >
> > SQLit

Re: [sqlite] LIMIT doesn't return expected rows

[Keith Medcalf]

// #define SQLITE_OMIT_INTEGRITY_CHECK
// #define SQLITE_OMIT_LIKE_OPTIMIZATION
// #define SQLITE_OMIT_LOAD_EXTENSION
// #define SQLITE_OMIT_LOCALTIME
// #define SQLITE_OMIT_LOOKASIDE
// #define SQLITE_OMIT_MEMORYDB
// #define SQLITE_OMIT_MERGE_SORT
// #define SQLITE_OMIT_OR_OPTIMIZATION
// #define SQLITE_OMIT_PAGER_PRAGMAS
// #define SQLITE_OMIT_PRAGMA
// #define SQLITE_OMIT_PROGRESS_CALLBACK
// #define SQLITE_OMIT_QUICKBALANCE
// #define SQLITE_OMIT_REINDEX
// #define SQLITE_OMIT_SCHEMA_PRAGMAS
// #define SQLITE_OMIT_SCHEMA_VERSION_PRAGMAS
// #define SQLITE_OMIT_SHARED_CACHE
// #define SQLITE_OMIT_SHUTDOWN_DIRECTORIES
// #define SQLITE_OMIT_SUBQUERY
// #define SQLITE_OMIT_TCL_VARIABLE
// #define SQLITE_OMIT_TEMPDB
// #define SQLITE_OMIT_TRACE
// #define SQLITE_OMIT_TRIGGER
// #define SQLITE_OMIT_TRUNCATE_OPTIMIZATION
// #define SQLITE_OMIT_UTF16
// #define SQLITE_OMIT_VACUUM
// #define SQLITE_OMIT_VIEW
// #define SQLITE_OMIT_VIRTUALTABLE
// #define SQLITE_OMIT_WAL
// #define SQLITE_OMIT_WSD
// #define SQLITE_OMIT_XFER_OPT

// *** SQLITE DEBUGGING FEATURES ***

// #define SQLITE_DEBUG 1
// #define SQLITE_ENABLE_EXPENSIVE_ASSERT 1
// #define SQLITE_ENABLE_OVERSIZE_CELL_CHECK 1
// #define SQLITE_ENABLE_SELECTTRACE 1 // Enable Select 
Trace (.selecttrace 0x100) needs SQLITE_DEBUG
// #define SQLITE_ENABLE_SQLLOG 1  // Enable 
SQLITE_CONFIG_SQLLOG (see documentation)
// #define SQLITE_ENABLE_STMT_SCANSTATUS 1 // Enable 
Collection of Statement Scan Status
// #define SQLITE_ENABLE_WHERETRACE 1
// #define SQLITE_IOTRACE 1
// #define SQLITE_MEMDEBUG 1
// #define SQLITE_REVERSE_UNORDERED_SELECTS 1
// #define SQLITE_USE_FCNTL_TRACE 1// Enable extra 
vfslog fcntrl trace
// #define SQLITE_YYTRACKMAXSTACKDEPTH 1

#if defined(_WIN32) && defined(__GNUC__)
#define UNICODE_STRING_MAX_BYTES ((WORD) 65534)
#define UNICODE_STRING_MAX_CHARS (32767)
#define HAVE_FDATASYNC 1
#define HAVE_GMTIME_R 1
#define HAVE_LOCALTIME_S 1
#define HAVE_ISNAN 1
#define HAVE_MALLOC_USABLE_SIZE 1
#define HAVE_USLEEP 1
#define HAVE_UTIME 1
#endif

#if defined(_MSC_VER)
#define HAVE_FDATASYNC 1
#define HAVE_GMTIME_R 1
#define HAVE_LOCALTIME_S 1
#define HAVE_MALLOC_USABLE_SIZE 1
#define HAVE_USLEEP 1
#define HAVE_UTIME 1
#endif

#if defined(__GNUC__) && defined(SQLITE_USE_QUADMATH)
#define LONGDOUBLE_TYPE __float128
#endif
#endif


> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of David Empson
> Sent: Tuesday, 11 October, 2016 18:41
> To: SQLite mailing list
> Subject: Re: [sqlite] LIMIT doesn't return expected rows
> 
> Following up: same for the Mac distribution of 3.14.2 command line tool.
> Using the pre-release snapshot of 3.15.0 from the main download page to
> build the sqlite3 3.15.0 on the Mac, I get the same answer: 1,2,3.
> 
> SQLite version 3.15.0 2016-10-10 14:34:00
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> .read test.sql
> select sqlite_source_id();
> 2016-10-10 14:34:00 aebe429e52ffef026cb0803fb164339d61bd2e88
> create table i (id integer primary key autoincrement, flags integer);
> insert into i values (1,1),(2,1),(3,1),(4,1),(5,5),(6,6),(7,4);
> create table m (id integer);
> insert into m values (1),(2),(3),(4),(5),(6),(7);
> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC;
> 6
> 5
> 7
> 1
> 2
> 3
> 4
> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC limit
> 3;
> 1
> 2
> 3
> 
> 
> > On 12/10/2016, at 1:23 PM, David Empson <demp...@emptech.co.nz> wrote:
> >
> > Keith - using your example, I get the same result as Tobias: the second
> select produces 1,2,3. This is with the sqlite3.exe Windows command line
> tool for SQLite 3.14.2 downloaded from sqlite.org. Same sqlite_source_id()
> too. I’m not set up to build SQLite from source, so can’t easily test
> 3.15.0, but If I do the same with a copy of 3.8.11.1 I have handy I get
> the correct result: 6, 5, 7.
> >
> > SQLite version 3.14.2 2016-09-12 18:50:49
> > Enter ".help" for usage hints.
> > Connected to a transient in-memory database.
> > Use ".open FILENAME" to reopen on a persistent database.
> > sqlite> .read test.sql
> > select sqlite_source_id();
> > 2016-09-12 18:50:49 29dbef4b8585f753861a36d6dd102ca634197bd6
> > create table i (id integer primary key autoincrement, flags integer);
> > insert into i values (1,1),(2,1),(3,1),(4,1),(5,5),(6,6),(7,4);
> > create table m (id integer);
> > insert into m values (1),(2),(3),(4),(5),(6),(7);
> > SELECT id FROM i WHERE id IN (SELECT id FROM m)

Re: [sqlite] LIMIT doesn't return expected rows

[David Empson]

Following up: same for the Mac distribution of 3.14.2 command line tool. Using 
the pre-release snapshot of 3.15.0 from the main download page to build the 
sqlite3 3.15.0 on the Mac, I get the same answer: 1,2,3.

SQLite version 3.15.0 2016-10-10 14:34:00
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .read test.sql
select sqlite_source_id();
2016-10-10 14:34:00 aebe429e52ffef026cb0803fb164339d61bd2e88
create table i (id integer primary key autoincrement, flags integer);
insert into i values (1,1),(2,1),(3,1),(4,1),(5,5),(6,6),(7,4);
create table m (id integer);
insert into m values (1),(2),(3),(4),(5),(6),(7);
SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC;
6
5
7
1
2
3
4
SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC limit 3;
1
2
3


> On 12/10/2016, at 1:23 PM, David Empson <demp...@emptech.co.nz> wrote:
> 
> Keith - using your example, I get the same result as Tobias: the second 
> select produces 1,2,3. This is with the sqlite3.exe Windows command line tool 
> for SQLite 3.14.2 downloaded from sqlite.org. Same sqlite_source_id() too. 
> I’m not set up to build SQLite from source, so can’t easily test 3.15.0, but 
> If I do the same with a copy of 3.8.11.1 I have handy I get the correct 
> result: 6, 5, 7.
> 
> SQLite version 3.14.2 2016-09-12 18:50:49
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> .read test.sql
> select sqlite_source_id();
> 2016-09-12 18:50:49 29dbef4b8585f753861a36d6dd102ca634197bd6
> create table i (id integer primary key autoincrement, flags integer);
> insert into i values (1,1),(2,1),(3,1),(4,1),(5,5),(6,6),(7,4);
> create table m (id integer);
> insert into m values (1),(2),(3),(4),(5),(6),(7);
> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC;
> 6
> 5
> 7
> 1
> 2
> 3
> 4
> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC limit 3;
> 1
> 2
> 3
> 
>> On 12/10/2016, at 12:59 PM, Keith Medcalf <kmedc...@dessus.com> wrote:
>> 
>> SQLite version 3.14.2 2016-09-12 18:50:49
>> Enter ".help" for usage hints.
>> Connected to a transient in-memory database.
>> Use ".open FILENAME" to reopen on a persistent database.
>> sqlite> .read \\test.sql
>> select sqlite_source_id();
>> 2016-09-12 18:50:49 29dbef4b8585f753861a36d6dd102ca634197bd6
>> create table i (id integer primary key autoincrement, flags integer);
>> insert into i values (1,1),(2,1),(3,1),(4,1),(5,5),(6,6),(7,4);
>> create table m (id integer);
>> insert into m values (1),(2),(3),(4),(5),(6),(7);
>> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC;
>> 6
>> 5
>> 7
>> 1
>> 2
>> 3
>> 4
>> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC limit 3;
>> 6
>> 5
>> 7
>> 
>> I just compiled 3.14.2 from my source repository and it works correctly.  
>> 
>> Can you check the result of "select sqlite_source_id();", which should be:
>> 
>> 2016-09-12 18:50:49 29dbef4b8585f753861a36d6dd102ca634197bd6
>> 
>> https://www.sqlite.org/src/info/29dbef4b8585f753
>> 
>>> -Original Message-
>>> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
>>> On Behalf Of Tobias Ellinghaus
>>> Sent: Tuesday, 11 October, 2016 11:41
>>> To: sqlite-users@mailinglists.sqlite.org
>>> Subject: Re: [sqlite] LIMIT doesn't return expected rows
>>> 
>>> Am Dienstag, 11. Oktober 2016, 06:50:01 CEST schrieb Keith Medcalf:
>>>> This was fixed September 7.  The fix appears in 3.14.2 and also on the
>>>> current 3.15.0.
>>> 
>>> Does that mean that 3.14.2 is supposed to give the "6, 5, 7" result in the
>>> last query? I am asking as that's the version I am using (installed from
>>> Debian/sid) and I get "1, 2, 3" here.
>>> 
>>>> https://www.sqlite.org/releaselog/3_14_2.html
>>>> 
>>>> The ORDER BY LIMIT optimization is not valid unless the inner-most IN
>>>> operator loop is actually used by the query plan. Ticket
>>>> https://sqlite.org/src/info/0c4df46116e90f92
>>>> 
>>>> 
>>>> SQLite version 3.15.0 2016-10-10 14:48:36
>>>> Enter ".help" for usage hints.
>>>> Connected to a transient in-memory database.
>>>> Use ".open FILENAME" 

Re: [sqlite] LIMIT doesn't return expected rows

[David Empson]

Keith - using your example, I get the same result as Tobias: the second select 
produces 1,2,3. This is with the sqlite3.exe Windows command line tool for 
SQLite 3.14.2 downloaded from sqlite.org. Same sqlite_source_id() too. I’m not 
set up to build SQLite from source, so can’t easily test 3.15.0, but If I do 
the same with a copy of 3.8.11.1 I have handy I get the correct result: 6, 5, 7.

SQLite version 3.14.2 2016-09-12 18:50:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .read test.sql
select sqlite_source_id();
2016-09-12 18:50:49 29dbef4b8585f753861a36d6dd102ca634197bd6
create table i (id integer primary key autoincrement, flags integer);
insert into i values (1,1),(2,1),(3,1),(4,1),(5,5),(6,6),(7,4);
create table m (id integer);
insert into m values (1),(2),(3),(4),(5),(6),(7);
SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC;
6
5
7
1
2
3
4
SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC limit 3;
1
2
3

> On 12/10/2016, at 12:59 PM, Keith Medcalf <kmedc...@dessus.com> wrote:
> 
> SQLite version 3.14.2 2016-09-12 18:50:49
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> .read \\test.sql
> select sqlite_source_id();
> 2016-09-12 18:50:49 29dbef4b8585f753861a36d6dd102ca634197bd6
> create table i (id integer primary key autoincrement, flags integer);
> insert into i values (1,1),(2,1),(3,1),(4,1),(5,5),(6,6),(7,4);
> create table m (id integer);
> insert into m values (1),(2),(3),(4),(5),(6),(7);
> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC;
> 6
> 5
> 7
> 1
> 2
> 3
> 4
> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC limit 3;
> 6
> 5
> 7
> 
> I just compiled 3.14.2 from my source repository and it works correctly.  
> 
> Can you check the result of "select sqlite_source_id();", which should be:
> 
> 2016-09-12 18:50:49 29dbef4b8585f753861a36d6dd102ca634197bd6
> 
> https://www.sqlite.org/src/info/29dbef4b8585f753
> 
>> -Original Message-
>> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
>> On Behalf Of Tobias Ellinghaus
>> Sent: Tuesday, 11 October, 2016 11:41
>> To: sqlite-users@mailinglists.sqlite.org
>> Subject: Re: [sqlite] LIMIT doesn't return expected rows
>> 
>> Am Dienstag, 11. Oktober 2016, 06:50:01 CEST schrieb Keith Medcalf:
>>> This was fixed September 7.  The fix appears in 3.14.2 and also on the
>>> current 3.15.0.
>> 
>> Does that mean that 3.14.2 is supposed to give the "6, 5, 7" result in the
>> last query? I am asking as that's the version I am using (installed from
>> Debian/sid) and I get "1, 2, 3" here.
>> 
>>> https://www.sqlite.org/releaselog/3_14_2.html
>>> 
>>> The ORDER BY LIMIT optimization is not valid unless the inner-most IN
>>> operator loop is actually used by the query plan. Ticket
>>> https://sqlite.org/src/info/0c4df46116e90f92
>>> 
>>> 
>>> SQLite version 3.15.0 2016-10-10 14:48:36
>>> Enter ".help" for usage hints.
>>> Connected to a transient in-memory database.
>>> Use ".open FILENAME" to reopen on a persistent database.
>>> sqlite> create table i (id integer primary key, flags integer);
>>> sqlite> insert into i values (1,1),(2,1),(3,1),(4,1),(5,5),(6,6),(7,4);
>>> sqlite> create table m (id integer);
>>> sqlite> insert into m values (1),(2),(3),(4),(5),(6),(7);
>>> sqlite> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags
>> DESC;
>>> 6
>>> 5
>>> 7
>>> 1
>>> 2
>>> 3
>>> 4
>>> sqlite> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags
>> DESC
>>> limit 3; 6
>>> 5
>>> 7
>> 
>> Tobias
>> 
>> [...]
> 
> 
> 
> ___
> 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] LIMIT doesn't return expected rows

[Keith Medcalf]

SQLite version 3.14.2 2016-09-12 18:50:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .read \\test.sql
select sqlite_source_id();
2016-09-12 18:50:49 29dbef4b8585f753861a36d6dd102ca634197bd6
create table i (id integer primary key autoincrement, flags integer);
insert into i values (1,1),(2,1),(3,1),(4,1),(5,5),(6,6),(7,4);
create table m (id integer);
insert into m values (1),(2),(3),(4),(5),(6),(7);
SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC;
6
5
7
1
2
3
4
SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC limit 3;
6
5
7

I just compiled 3.14.2 from my source repository and it works correctly.  

Can you check the result of "select sqlite_source_id();", which should be:

2016-09-12 18:50:49 29dbef4b8585f753861a36d6dd102ca634197bd6

https://www.sqlite.org/src/info/29dbef4b8585f753

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Tobias Ellinghaus
> Sent: Tuesday, 11 October, 2016 11:41
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] LIMIT doesn't return expected rows
> 
> Am Dienstag, 11. Oktober 2016, 06:50:01 CEST schrieb Keith Medcalf:
> > This was fixed September 7.  The fix appears in 3.14.2 and also on the
> > current 3.15.0.
> 
> Does that mean that 3.14.2 is supposed to give the "6, 5, 7" result in the
> last query? I am asking as that's the version I am using (installed from
> Debian/sid) and I get "1, 2, 3" here.
> 
> > https://www.sqlite.org/releaselog/3_14_2.html
> >
> > The ORDER BY LIMIT optimization is not valid unless the inner-most IN
> > operator loop is actually used by the query plan. Ticket
> > https://sqlite.org/src/info/0c4df46116e90f92
> >
> >
> > SQLite version 3.15.0 2016-10-10 14:48:36
> > Enter ".help" for usage hints.
> > Connected to a transient in-memory database.
> > Use ".open FILENAME" to reopen on a persistent database.
> > sqlite> create table i (id integer primary key, flags integer);
> > sqlite> insert into i values (1,1),(2,1),(3,1),(4,1),(5,5),(6,6),(7,4);
> > sqlite> create table m (id integer);
> > sqlite> insert into m values (1),(2),(3),(4),(5),(6),(7);
> > sqlite> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags
> DESC;
> > 6
> > 5
> > 7
> > 1
> > 2
> > 3
> > 4
> > sqlite> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags
> DESC
> > limit 3; 6
> > 5
> > 7
> 
> Tobias
> 
> [...]



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

Re: [sqlite] LIMIT doesn't return expected rows

[Dan Kennedy]

On 10/11/2016 07:57 PM, Simon Slavin wrote:

On 11 Oct 2016, at 1:44pm, Eric Minbiole  wrote:


Your problem is that although SQL accepts the clauses written in the order
you wrote them in, the LIMIT clause is processed before the ORDER BY clause.

Is that really true? I had always thought that the ORDER BY was processed
first, though I admit I don't see anything authoritative either way.

I didn't know.  Which is why I wrote about what was happening rather than what 
a specification said should happen.  The order in which these clauses are 
listed in the SQLite documentation suggests that ORDER BY should be performed 
/before/ LIMIT.


This is correct. In an SQL SELECT, the sorting is done before the limiting.

Dan.

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

Re: [sqlite] LIMIT doesn't return expected rows

[Tobias Ellinghaus]

Am Dienstag, 11. Oktober 2016, 06:50:01 CEST schrieb Keith Medcalf:
> This was fixed September 7.  The fix appears in 3.14.2 and also on the
> current 3.15.0.

Does that mean that 3.14.2 is supposed to give the "6, 5, 7" result in the 
last query? I am asking as that's the version I am using (installed from 
Debian/sid) and I get "1, 2, 3" here.

> https://www.sqlite.org/releaselog/3_14_2.html
> 
> The ORDER BY LIMIT optimization is not valid unless the inner-most IN
> operator loop is actually used by the query plan. Ticket
> https://sqlite.org/src/info/0c4df46116e90f92
> 
> 
> SQLite version 3.15.0 2016-10-10 14:48:36
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> create table i (id integer primary key, flags integer);
> sqlite> insert into i values (1,1),(2,1),(3,1),(4,1),(5,5),(6,6),(7,4);
> sqlite> create table m (id integer);
> sqlite> insert into m values (1),(2),(3),(4),(5),(6),(7);
> sqlite> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC;
> 6
> 5
> 7
> 1
> 2
> 3
> 4
> sqlite> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC
> limit 3; 6
> 5
> 7

Tobias

[...]

signature.asc
Description: This is a digitally signed message part.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIMIT doesn't return expected rows

[Simon Slavin]

On 11 Oct 2016, at 1:44pm, Eric Minbiole  wrote:

>> Your problem is that although SQL accepts the clauses written in the order
>> you wrote them in, the LIMIT clause is processed before the ORDER BY clause.
> 
> Is that really true? I had always thought that the ORDER BY was processed
> first, though I admit I don't see anything authoritative either way.

I didn't know.  Which is why I wrote about what was happening rather than what 
a specification said should happen.  The order in which these clauses are 
listed in the SQLite documentation suggests that ORDER BY should be performed 
/before/ LIMIT.

I did find this:



"If you combine LIMIT row_count with ORDER BY, MySQL ends the sorting as soon 
as it has found the first row_count rows of the sorted result"

Note the word 'sorted'.  This agrees with what I wrote above, in that it 
suggest that in MySQL ORDER BY must be processed first.

I'm happy to see that Keith says that the behaviour noted by Tobias was a bug 
and has been fixed.  I also note that a test for this combination of clauses 
appears in the test suite, so the bug shouldn't happen again.

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

Re: [sqlite] LIMIT doesn't return expected rows

[Keith Medcalf]

This was fixed September 7.  The fix appears in 3.14.2 and also on the current 
3.15.0.

https://www.sqlite.org/releaselog/3_14_2.html

The ORDER BY LIMIT optimization is not valid unless the inner-most IN operator 
loop is actually used by the query plan. Ticket 
https://sqlite.org/src/info/0c4df46116e90f92


SQLite version 3.15.0 2016-10-10 14:48:36
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table i (id integer primary key, flags integer);
sqlite> insert into i values (1,1),(2,1),(3,1),(4,1),(5,5),(6,6),(7,4);
sqlite> create table m (id integer);
sqlite> insert into m values (1),(2),(3),(4),(5),(6),(7);
sqlite> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC;
6
5
7
1
2
3
4
sqlite> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC 
limit 3;
6
5
7

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Tobias Ellinghaus
> Sent: Tuesday, 11 October, 2016 04:53
> To: sqlite-users@mailinglists.sqlite.org
> Subject: [sqlite] LIMIT doesn't return expected rows
> 
> Hello,
> 
> first let me mention that I am new to this list so apologies if my
> question
> came up before. I couldn't find anything though, and in #sqlite on
> Freenode I
> was pointed here, so here I am.
> 
> I am working on some code that creates a (potentially big) SQL query on
> the
> fly. My problem is, that I need to get a certain subset of the result the
> query
> gives. However, that somehow doesn't work with sqlite 3.14.2. In 3.8.5 it
> still worked according to someone on IRC testing it.
> 
> Example database:
> 
> sqlite> .dump i
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE i (id integer primary key autoincrement, flags integer);
> INSERT INTO "i" VALUES(1,1);
> INSERT INTO "i" VALUES(2,1);
> INSERT INTO "i" VALUES(3,1);
> INSERT INTO "i" VALUES(4,1);
> INSERT INTO "i" VALUES(5,5);
> INSERT INTO "i" VALUES(6,6);
> INSERT INTO "i" VALUES(7,4);
> COMMIT;
> sqlite> .dump m
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE m (id integer);
> INSERT INTO "m" VALUES(1);
> INSERT INTO "m" VALUES(2);
> INSERT INTO "m" VALUES(3);
> INSERT INTO "m" VALUES(4);
> INSERT INTO "m" VALUES(5);
> INSERT INTO "m" VALUES(6);
> INSERT INTO "m" VALUES(7);
> COMMIT;
> 
> Simplified example query – in reality there would be a couple of those
> inner
> SELECT which makes it hard to use JOIN.
> 
> sqlite> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags
> DESC;
> 6
> 5
> 7
> 1
> 2
> 3
> 4
> 
> Now I only want the first three values, 6, 5 and 7. However:
> 
> sqlite> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags
> DESC
> LIMIT 0, 3;
> 1
> 2
> 3
> 
> It almost seems to limit the inner SELECT instead of the outer one.
> 
> Is that a bug in recent versions of sqlite? Or was that a bug before and I
> am
> doing something wrong?
> 
> Thanks for any insight and maybe a hint how to get what I want
> Tobias



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

Re: [sqlite] LIMIT doesn't return expected rows

[Eric Minbiole]

> Your problem is that although SQL accepts the clauses written in the order
> you wrote them in, the LIMIT clause is processed before the ORDER BY clause.
>
>
Is that really true? I had always thought that the ORDER BY was processed
first, though I admit I don't see anything authoritative either way.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIMIT doesn't return expected rows

[Simon Slavin]

On 11 Oct 2016, at 11:52am, Tobias Ellinghaus  wrote:

> Now I only want the first three values, 6, 5 and 7. However:
> 
> sqlite> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC 
> LIMIT 0, 3;
> 1
> 2
> 3

Your problem is that although SQL accepts the clauses written in the order you 
wrote them in, the LIMIT clause is processed before the ORDER BY clause.

sqlite> SELECT id FROM i WHERE id IN (SELECT id FROM m) LIMIT 0, 3;
1
2
3
sqlite> 

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

Re: [sqlite] Limit to 5 records per Group / Top N results per group

[Igor Tandetnik]

On 6/1/2016 5:36 PM, Russell, Rory wrote:

In SQLIte, I have 2 tables, one is for a list of Projects (about 50
projects) and the other is a list of key dates for each project. Each
project has about 20 key dates.

How can run a query that will only return the top/first 5 dates for each
project in one query.


Something along these lines, perhaps:

select ProjectName, KeyDate
from Projects p, KeyDates using (ProjectID)
where KeyDate in (
  select d2.KeyDate from KeyDates d2
  where d2.ProjectId = p.ProjectId
  order by d2.KeyDate limit 5);

--
Igor Tandetnik

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

Re: [sqlite] Limit of attached databases

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 04/06/13 08:14, Eleytherios Stamatogiannakis wrote:
> We have tried with both views and VTs but SQLite does not create
> automatic indexes on them at all. So right now, to be able to have
> automatic indexes from SQLite's side we materialize all Virtual Tables
> into plain tables:

There is no reason that the virtual table implementation can't create
automatic indexes.  In response to the xBestIndex calls, the indices don't
actually have to exist.  You just need to ensure the estimated cost is
relatively useful.  If SQLite then decides to use the index you can then
create it on the fly.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlGuQQMACgkQmOOfHg372QRxxwCfV1QvfO6Fsky0x3krTLe08+Nm
EN4AniM6kkOHgcTm/mtREY3iD4QAF9o+
=KOXn
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit of attached databases

[Eleytherios Stamatogiannakis]

On 04/06/13 17:37, Simon Slavin wrote:


On 4 Jun 2013, at 3:09pm, Eleytherios Stamatogiannakis  wrote:


Is there any way to go beyond the SQLITE_MAX_ATTACHED limit for *read only* 
attached DBs?


See section 11 of



It's a 64-bit value, and two bits are already taken up.


Yes i have seen it in SQLite's code. I considered changing it to a 
bitfield, but the problem is that this long int is used in various other 
places in SQLite's internals for transaction' metadata.




You can attach databases, copy data from them to the main database, then detach 
those and attach some others.  Or you can create a hierarchy of shards (each of 
62 shards can point to up to 62 others).  Or you can rewrite your code so it 
never uses more than 62 shards no matter how many nodes are available.


Attaching and detaching is only useful when materializing the shards 
into a single table:


create table T
attach T1
insert into T select * from T1.T
detach T1
attach T2
insert into T select * from T2.T
...

How would a hierarchy of shards work? You cannot attach a DB onto 
another attached DB for the hierarchy idea to work.


Also, only using 62 shards at a time can be very constrained/slow in our 
use case (see below).



None of them good solutions, I'm afraid.


Yes :-(.


Also is there anyway for SQLite to create an automatic index on a view (or 
Virtual Table), without having to first materialize the view (or VT)?


I believe that SQLite needs the data to be in one place (i.e. at least a 
virtual table) for the indexing routine to work.


We have tried with both views and VTs but SQLite does not create 
automatic indexes on them at all. So right now, to be able to have 
automatic indexes from SQLite's side we materialize all Virtual Tables 
into plain tables:


create temp table T as select * from UnionAllVT1;
create temp table G as select * from UnionAllVT2;
...

which doubles our I/O to process a single sharded table.

 - 1 full read + 1 full write of all data to materialize the UnionAllVT 
into a plain table.
 - 1 full read + 1 full write of the data in the materialized table to 
create the automatic index.


It would be very nice if the automatic index could be created directly 
from the UnionAllVT, but we haven't found a way to do it.



If you're willing to put a bit of SQLite-only effort in, you could implement 
your own virtual table implementation that consulted data on each of your 
nodes.  This would be quite highly customised for your own application's 
requirements but it would mean you didn't have to do any attaching or detaching 
at all.  Your SQLite API calls could address your data as if it was all in one 
database file but SQLite would understand how data is partitioned between nodes 
and automatically gather it from all the necessary nodes.


We already have done this (creating VTs is very easy in madIS [*]). We 
have the UnionALL virtual table that scans over all the DB shards.


Above UnionALL VT only supports scans, and it is only used to 
materialize the shards into a regular table. It would be very costly 
having per shard indexes, because each Filter on the UnionALL VT would 
need to be passed to all of the shards. A single automatic index, works 
best.



Another way to do it would be to implement your own VFS which would distribute 
over the nodes not at the row level but as if they were all one huge storage 
medium (i.e. like a RAID).


Each shard that we use is already a self contained SQLite DB. We would 
need to change our whole approach to convert to a disk page based 
sharding approach.


Thanks for your ideas.

l.

[*] 
https://code.google.com/p/madis/source/browse/src/functions/vtable/unionalldb.py


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

Re: [sqlite] Limit of attached databases

[Michael Black]

Oops...make that an unsigned int.

Change this declaration
#if SQLITE_MAX_ATTACHED>30
  typedef __uint128_t yDbMask;

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Michael Black
Sent: Tuesday, June 04, 2013 9:51 AM
To: est...@gmail.com; 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Limit of attached databases

Gcc does have a __int128_t and __uint128_t available if you're on 64-bit and
have a current enough gcc (I'm using 4.4.4 and this works on Linux and
Windows)
Looks like a fairly easy change in the code.
Unless somebody already knows that this won't work?

main()
{
__uint128_t i128;
printf("i128=%d\n",sizeof(i128));
}
i128=16

Change this to 126
#ifndef SQLITE_MAX_ATTACHED
# define SQLITE_MAX_ATTACHED 126
#endif

Change this declaration
#if SQLITE_MAX_ATTACHED>30
  typedef __int128_t yDbMask;

Change this to 126 instead of 62
#if SQLITE_MAX_ATTACHED<0 || SQLITE_MAX_ATTACHED>62
# error SQLITE_MAX_ATTACHED must be between 0 and 62
#endif

And see if it works OK for you.  I have no way to test this but it does
compile for me.

I don't see why it wouldn't work.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Eleytherios
Stamatogiannakis
Sent: Tuesday, June 04, 2013 9:09 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Limit of attached databases

Hi,

During our work on a distributed processing system (which uses SQLite 
shards), we have hit the SQLITE_MAX_ATTACHED limit of attached DBs.

The way we use SQLite for distributed processing [*], is the following:
  - Each table is sharded into multiple SQLite DBs on different nodes of 
the cluster.
  - To process a query, we run on each shard a query which produces 
multiple sharded SQLite result DBs.
  - We redistribute in the cluster the result DBs, and the next set of 
cluster nodes, attaches all the input shard SQLite DBs, and it creates a 
temp view that unions all the input DB shards into a single view.
  - It then executes a query on the views that produces new result DB shards
  - and so on

We recently got access to a cluster of 64 nodes and it is very easy now 
to hit the SQLITE_MAX_ATTACHED limit (1 DB shard gets produced per node).

So the question that i have is:

Is there any way to go beyond the SQLITE_MAX_ATTACHED limit for *read 
only* attached DBs?

Also is there anyway for SQLite to create an automatic index on a view 
(or Virtual Table), without having to first materialize the view (or VT)?

Thanks in advance.

Lefteris Stamatogiannakis.

[*] The same processing ideas are used in hadapt:

http://hadapt.com/

which uses Postgres for the DB shards.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

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

Re: [sqlite] Limit of attached databases

[Michael Black]

Gcc does have a __int128_t and __uint128_t available if you're on 64-bit and
have a current enough gcc (I'm using 4.4.4 and this works on Linux and
Windows)
Looks like a fairly easy change in the code.
Unless somebody already knows that this won't work?

main()
{
__uint128_t i128;
printf("i128=%d\n",sizeof(i128));
}
i128=16

Change this to 126
#ifndef SQLITE_MAX_ATTACHED
# define SQLITE_MAX_ATTACHED 126
#endif

Change this declaration
#if SQLITE_MAX_ATTACHED>30
  typedef __int128_t yDbMask;

Change this to 126 instead of 62
#if SQLITE_MAX_ATTACHED<0 || SQLITE_MAX_ATTACHED>62
# error SQLITE_MAX_ATTACHED must be between 0 and 62
#endif

And see if it works OK for you.  I have no way to test this but it does
compile for me.

I don't see why it wouldn't work.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Eleytherios
Stamatogiannakis
Sent: Tuesday, June 04, 2013 9:09 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Limit of attached databases

Hi,

During our work on a distributed processing system (which uses SQLite 
shards), we have hit the SQLITE_MAX_ATTACHED limit of attached DBs.

The way we use SQLite for distributed processing [*], is the following:
  - Each table is sharded into multiple SQLite DBs on different nodes of 
the cluster.
  - To process a query, we run on each shard a query which produces 
multiple sharded SQLite result DBs.
  - We redistribute in the cluster the result DBs, and the next set of 
cluster nodes, attaches all the input shard SQLite DBs, and it creates a 
temp view that unions all the input DB shards into a single view.
  - It then executes a query on the views that produces new result DB shards
  - and so on

We recently got access to a cluster of 64 nodes and it is very easy now 
to hit the SQLITE_MAX_ATTACHED limit (1 DB shard gets produced per node).

So the question that i have is:

Is there any way to go beyond the SQLITE_MAX_ATTACHED limit for *read 
only* attached DBs?

Also is there anyway for SQLite to create an automatic index on a view 
(or Virtual Table), without having to first materialize the view (or VT)?

Thanks in advance.

Lefteris Stamatogiannakis.

[*] The same processing ideas are used in hadapt:

http://hadapt.com/

which uses Postgres for the DB shards.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Limit of attached databases

[Simon Slavin]

On 4 Jun 2013, at 3:09pm, Eleytherios Stamatogiannakis  wrote:

> Is there any way to go beyond the SQLITE_MAX_ATTACHED limit for *read only* 
> attached DBs?

See section 11 of



It's a 64-bit value, and two bits are already taken up.

You can attach databases, copy data from them to the main database, then detach 
those and attach some others.  Or you can create a hierarchy of shards (each of 
62 shards can point to up to 62 others).  Or you can rewrite your code so it 
never uses more than 62 shards no matter how many nodes are available.

None of them good solutions, I'm afraid.

> Also is there anyway for SQLite to create an automatic index on a view (or 
> Virtual Table), without having to first materialize the view (or VT)?

I believe that SQLite needs the data to be in one place (i.e. at least a 
virtual table) for the indexing routine to work.

If you're willing to put a bit of SQLite-only effort in, you could implement 
your own virtual table implementation that consulted data on each of your 
nodes.  This would be quite highly customised for your own application's 
requirements but it would mean you didn't have to do any attaching or detaching 
at all.  Your SQLite API calls could address your data as if it was all in one 
database file but SQLite would understand how data is partitioned between nodes 
and automatically gather it from all the necessary nodes.



Another way to do it would be to implement your own VFS which would distribute 
over the nodes not at the row level but as if they were all one huge storage 
medium (i.e. like a RAID).



I don't know which, if either, to recommend.  This kind of programming is 
beyond me, but someone into C and with a good understanding of your farm should 
be able to do it.

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

Re: [sqlite] Limit on the Compound Select Statements

[Don V Nielsen]

Would it make more sense to put the values into a text file and import the
text file?  It separates the data from the application, and simplifies
making future changes to the list.

On Thu, Feb 23, 2012 at 1:52 PM, Abhinav Upadhyay <
er.abhinav.upadh...@gmail.com> wrote:

> On Thu, Feb 23, 2012 at 6:50 PM, Simon Slavin 
> wrote:
> >
> > On 23 Feb 2012, at 1:16pm, Abhinav Upadhyay <
> er.abhinav.upadh...@gmail.com> wrote:
> >
> >> I do not remember the
> >> exact error message but it close to this. As per the documentation on
> >> the compound select statements
> >> (http://www.sqlite.org/lang_select.html) on Sqlite website, there is
> >> no mention of an explicit limit. I would like to know the exact limit
> >> on this, so that I could my code to work within this limit
> >
> > 
> >
> > especially item 3, but also others.
> >
> > However, I question the advantage of doing one long INSERT rather than
> doing many inside a transaction.  Are you binding parameters ?
> >
>
> It was already inside a bigger transaction, I was trying out something
> naive and turns out it is not worth it. Thanks for the pointer  :)
>
> --
> Abhinav
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit on the Compound Select Statements

[Abhinav Upadhyay]

On Thu, Feb 23, 2012 at 6:50 PM, Simon Slavin  wrote:
>
> On 23 Feb 2012, at 1:16pm, Abhinav Upadhyay  
> wrote:
>
>> I do not remember the
>> exact error message but it close to this. As per the documentation on
>> the compound select statements
>> (http://www.sqlite.org/lang_select.html) on Sqlite website, there is
>> no mention of an explicit limit. I would like to know the exact limit
>> on this, so that I could my code to work within this limit
>
> 
>
> especially item 3, but also others.
>
> However, I question the advantage of doing one long INSERT rather than doing 
> many inside a transaction.  Are you binding parameters ?
>

It was already inside a bigger transaction, I was trying out something
naive and turns out it is not worth it. Thanks for the pointer  :)

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

Re: [sqlite] Limit on the Compound Select Statements

[Richard Hipp]

On Thu, Feb 23, 2012 at 8:25 AM, Petite Abeille wrote:

>
> On Feb 23, 2012, at 2:16 PM, Abhinav Upadhyay wrote:
>
> > . I was wondering if I could insert them using a
> > single INSERT query
>
> Ah, also, there is not much benefit in using a compound insert.
>
> You could as well simply insert all your values in one transaction and be
> done.
>
> On the other hand, the forthcoming 3.7.11 release seems to support
> multi-valued insert statements.
>
> http://www.sqlite.org/draft/releaselog/3_7_11.html
>

The new multi-valued insert is merely syntactic suger for the compound
insert.  There is no performance advantage one way or the other.


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



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit on the Compound Select Statements

[Petite Abeille]

On Feb 23, 2012, at 2:16 PM, Abhinav Upadhyay wrote:

> . I was wondering if I could insert them using a
> single INSERT query

Ah, also, there is not much benefit in using a compound insert.

You could as well simply insert all your values in one transaction and be done.

On the other hand, the forthcoming 3.7.11 release seems to support multi-valued 
insert statements.

http://www.sqlite.org/draft/releaselog/3_7_11.html

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

Re: [sqlite] Limit on the Compound Select Statements

[Petite Abeille]

On Feb 23, 2012, at 2:16 PM, Abhinav Upadhyay wrote:

> I would like to know the exact limit
> on this, so that I could my code to work within this limit :)


See Maximum Number Of Terms In A Compound SELECT Statement:

http://www.sqlite.org/limits.html

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

Re: [sqlite] Limit on the Compound Select Statements

[Simon Slavin]

On 23 Feb 2012, at 1:16pm, Abhinav Upadhyay  
wrote:

> I do not remember the
> exact error message but it close to this. As per the documentation on
> the compound select statements
> (http://www.sqlite.org/lang_select.html) on Sqlite website, there is
> no mention of an explicit limit. I would like to know the exact limit
> on this, so that I could my code to work within this limit



especially item 3, but also others.

However, I question the advantage of doing one long INSERT rather than doing 
many inside a transaction.  Are you binding parameters ?

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

Re: [sqlite] Limit COUNT

[Frank Missel]

> boun...@sqlite.org] On Behalf Of Bart Smissaert
> 
> That seems to be the answer and after some quick testing it looks it makes
it
> more efficient as well!
> 
> On Tue, Oct 18, 2011 at 1:36 PM, Kit  wrote:
> > 2011/10/16 Fabian :
> >> How can you limit a count-query? I tried:
> >> SELECT COUNT(*) FROM table LIMIT 5000
> >
> > SELECT min(COUNT(*),5000) FROM table;
> > --

My first impression was that the count(*) inside the min() would access all
the records anyway (perhaps not all the columns though) and thus in fact
still access more than 5000 records, even if it was hidden by the min
function.

To test this I took a database containing tables with millions of records
and executed the following statements:

1. select count(*) from table;

2. select min(count(*), 5000) from table;

3. select count(*) from (select null from table limit 5000);

Now to really test properly the computer would have to be restarted before
each test in order to avoid buffering of the disks, etc.
However, I could not be bothered in this case and just used three separate
tables with identical structure but a different number of records (between 4
and 5 million). As expected with the difference in execution times between
the three statements it did not matter that much.

1. Around 150 seconds

2. Around 14 seconds

3. Around 50 milliseconds !!!

Thus it seems that although variation two perhaps avoids fully reading the
records, it still have to touch them (or perhaps the primary key) which is
better than a normal full select count(*) on the table. Perhaps somebody
know the inner workings better and can explain the difference of factor 10
between variation 1 and 2.

To actually limit the count statement only variation 3 actually works (which
has been suggested earlier in the thread) and is of course several orders of
magnitude faster.

Somebody had also suggested: 
select count(*) from (select 1 from table limit 5000);

Notice the inner select of 1 instead of null. 
As predicted this is slightly slower at around 60 milliseconds. 

So selecting null is always better in sub queries when only the count or
existence of records are needed and not actual values from any of the
columns.


/Frank Missel

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

Re: [sqlite] Limit COUNT

[Bart Smissaert]

That seems to be the answer and after some quick testing it looks it
makes it more efficient as well!

RBS


On Tue, Oct 18, 2011 at 1:36 PM, Kit  wrote:
> 2011/10/16 Fabian :
>> How can you limit a count-query? I tried:
>> SELECT COUNT(*) FROM table LIMIT 5000
>
> SELECT min(COUNT(*),5000) FROM table;
> --
> Kit
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit COUNT

[Kit]

2011/10/16 Fabian :
> How can you limit a count-query? I tried:
> SELECT COUNT(*) FROM table LIMIT 5000

SELECT min(COUNT(*),5000) FROM table;
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit COUNT

[Frank Missel]

> boun...@sqlite.org] On Behalf Of Fabian
> Sent: 17 October 2011 15:34
> >
> No, I only want to have a capped total available.
> 
> If I would go with Simons solution, I have to read the rows for the first
> 100 pages (or whatever the cap is) into a temporary table, just to show
the
> first page. I don't need a cache for all those other pages, so that seems
a lot
> of overhead. I only want to know if there are 100 or less pages (without
> copying data around).
> 
> Maybe COUNT() is also creating a temporary table behind the scenes, then
> the performance of Simons solutions would be comparable with what I have
> now, and I would have the advantage that I can re-use that table to show
> subsequent pages without reading from disk.
> 
> But I always assumed COUNT() was faster than copying between tables,
> maybe I should just benchmark it.

I had the idea that you just retrieved the first 100 records and not the
first 100 pages.
Could the user not just see the first 100 records and perhaps an indicator
if there were more or not. He could then get 100 records at a time browsing
through them or if he so wished get a record count (you would then use the
count function on all records).

If you need to know up front whether there are more than 5000 records or not
I suggest you use the suggestion from Petite Abeille:

select count( * )
from   (
 select 1   or even just "select null" which
will not fetch anything.
 fromtable
 limit 5000
   )

You could do that and also just select e.g. 50 actual rows and display these
to the user.


/Frank


 

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

Re: [sqlite] Limit COUNT

[Kit]

2011/10/16 Petite Abeille :
> On Oct 16, 2011, at 10:39 PM, Kit wrote:
>>> select count(*) from (select 1 from table limit 5000)
>> SELECT count(1) FROM (SELECT 1 FROM table LIMIT 5000);
>
> you realize that count( * )  has a very specific meaning, right?
> "The count(*) function (with no arguments) returns the total number of rows 
> in the group."
> http://www.sqlite.org/lang_aggfunc.html
> If this is what you mean, then stick to it :)

I originally thought that the symbol "*" means "all columns". I tried
to "EXPLAIN", now I see it differently.
Thank you.
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit COUNT

[reseok]

What about this:


SELECT
 CASE count(*) WHEN 5000 THEN 'More than 5000' ELSE 'Less than 5000' END
FROM (SELECT ID FROM table ORDER BY whatever LIMIT 5000 OFFSET 25000)



Fabian schrieb:
> 2011/10/16 Frank Missel 
> 
>> What do you want to attain with the count?
>>
>>
> I want to allow users to paginate through a result set. The pages are
> retreived through LIMIT/OFFSET, but to calculate the total number of pages,
> I have execute a separate COUNT() query (without LIMIT) once.
> 
> Because I'm basicly executing the same query twice just to get a total
> count, I'm trying to optimize this. Restricting the maximum number of pages
> to 10 should improve performance, if there was some way to put make COUNT()
> respect the LIMIT specified.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 

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

Re: [sqlite] Limit COUNT

[Fabian]

2011/10/16 Frank Missel 

>
> But it sounds a bit like Fabian both wants to have the total number of
> records available and at the same time limit the count.
>
>
No, I only want to have a capped total available.

If I would go with Simons solution, I have to read the rows for the first
100 pages (or whatever the cap is) into a temporary table, just to show the
first page. I don't need a cache for all those other pages, so that seems a
lot of overhead. I only want to know if there are 100 or less pages (without
copying data around).

Maybe COUNT() is also creating a temporary table behind the scenes, then the
performance of Simons solutions would be comparable with what I have now,
and I would have the advantage that I can re-use that table to show
subsequent pages without reading from disk.

But I always assumed COUNT() was faster than copying between tables, maybe I
should just benchmark it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit COUNT

[Petite Abeille]

On Oct 16, 2011, at 10:39 PM, Kit wrote:

>> select count(*) from (select 1 from table limit 5000)
> 
> SELECT count(1) FROM (SELECT 1 FROM table LIMIT 5000);

you realize that count( * )  has a very specific meaning, right?

"The count(*) function (with no arguments) returns the total number of rows in 
the group."

http://www.sqlite.org/lang_aggfunc.html

If this is what you mean, then stick to it :)

http://stackoverflow.com/questions/2710621/count-vs-count1-vs-countpk-which-is-better/2710703#2710703

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

Re: [sqlite] Limit COUNT

[Kit]

> select count(*) from (select 1 from table limit 5000)

SELECT count(1) FROM (SELECT 1 FROM table LIMIT 5000);
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit COUNT

[Petite Abeille]

On Oct 16, 2011, at 1:09 PM, Fabian wrote:

> How can you limit a count-query? I tried:
> 
> SELECT COUNT(*) FROM table LIMIT 5000
> 
> But it ignores the LIMIT clause. 

No it doesn't, it works as advertised. You are falling into the same trap as 
you did just a couple of threads ago. You need to get a grip on that LIMIT 
construct :)

The fine manual is there to help, please see "ORDER BY and LIMIT/OFFSET 
Clauses":

http://www.sqlite.org/lang_select.html

In the meantime:

select count( * )
from   (
 select 1
 fromtable
 limit 5000
   )


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

Re: [sqlite] Limit COUNT

[Frank Missel]

> boun...@sqlite.org] On Behalf Of Simon Slavin
> Sent: 16 October 2011 21:53
> 
> Perhaps he could read the rows LIMIT 100 into a buffer (after all, he's
going
> to need them eventually for when he displays them), then count how many
> rows he got.

Yeah, I would go that way also.

But it sounds a bit like Fabian both wants to have the total number of
records available and at the same time limit the count.
It is just not possible to do both at the same time as far as I can see.

Thus, you are left with following options as far as I can see:

1. As Slavin mentions: Read a certain number of records, e.g. 101 and change
your code so that the user can ask for e.g. 100 additional records at a
time. Possibly you could have an extra function that the user could activate
to have a total count if he so wishes. This would then read all records and
you would not have to worry about performance as all records (unless there
are very many) will be in the memory cache and subsequent request for these
records should be fast.

2. If there are many concurrent users and the query requests from these are
quite predictable, e.g. straight reads of all records in certain tables, you
could perhaps  have a local background job regularly count the number of
records for the relevant tables/queries and store these count results  in a
special status table. Your client program could then read the number of
records from the special status table as well as the first 101 records from
the actual data carrying table. The user could then get the first 100
records, and continue with batches of 100 as needed.

If these options does not seem to fit, I think that you have to explain a
bit more about how your solution is,  what you expect and how you access the
database.


/Frank


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

Re: [sqlite] Limit COUNT

[Roger Andersson]

 On 10/16/11 14:21, Fabian wrote:

I want to allow users to paginate through a result set. The pages are
retreived through LIMIT/OFFSET, but to calculate the total number of pages,
I have execute a separate COUNT() query (without LIMIT) once.

Because I'm basicly executing the same query twice just to get a total
count, I'm trying to optimize this. Restricting the maximum number of pages
to 10 should improve performance, if there was some way to put make COUNT()
respect the LIMIT specified.
___

I can't tell if this is more efficient but it's one query
select *,(select count(*) from table) as total_record_count from table 
limit 100;

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

Re: [sqlite] Limit COUNT

[Simon Slavin]

On 16 Oct 2011, at 2:50pm, Bart Smissaert wrote:

> He is trying to make it more efficient, so stop counting if count > X.
> So setting the count after having counted the whole lot won't help.

Then he can't use count() because SQLite's implementation of it is not 
efficient for that.

Perhaps he could read the rows LIMIT 100 into a buffer (after all, he's going 
to need them eventually for when he displays them), then count how many rows he 
got.

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

Re: [sqlite] Limit COUNT

[Bart Smissaert]

He is trying to make it more efficient, so stop counting if count > X.
So setting the count after having counted the whole lot won't help.

RBS


On Sun, Oct 16, 2011 at 2:46 PM, Simon Slavin  wrote:
>
> On 16 Oct 2011, at 1:21pm, Fabian wrote:
>
>> 2011/10/16 Frank Missel 
>>
>>> What do you want to attain with the count?
>>
>> I want to allow users to paginate through a result set. The pages are
>> retreived through LIMIT/OFFSET, but to calculate the total number of pages,
>> I have execute a separate COUNT() query (without LIMIT) once.
>
> You're using a programming language.  So do this:
>
> numberOfRows = SELECT count(*) FROM myTable
> if (numberOfRows > 100) then numberOfRows = 100
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users