Re: [sqlite] Query Planner GROUP BY and HAVING clauses optimization ?

Thank you Keith for the detail explanation. I misunderstood the 2 replies were opposite but this is not the case. Thank you again Jean-bapstiste ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/ma

Re: [sqlite] Query Planner GROUP BY and HAVING clauses optimization ?

On Wednesday, 15 January, 2020 02:06, Jean-Baptiste Gardette wrote: > Just to be sure, is it unsafe to write a non agregate SELECT with GROUP > BY and HAVING clauses (without sub-SELECT) for the sole prupose > explained before (even if the approache is discutable) ? Presently, yes it is. >I

Re: [sqlite] Query Planner GROUP BY and HAVING clauses optimization ?

Just to be sure, is it unsafe to write a non agregate SELECT with GROUP BY and HAVING clauses (without sub-SELECT) for the sole prupose explained before (even if the approache is discutable) ? I understand 2 different answers here : - "No, this kind of query can't be rewritten by the optimizer

Re: [sqlite] Query Planner GROUP BY and HAVING clauses optimization ?

On 1/14/20, Keith Medcalf wrote: > > I seem to recall something about "expensive" conditions that will be forced > to be run on only as few surviving candidate rows as possible, but my > recollection is vague (they say the memory is the second thing to go -- > strange I can't remember the first).

Re: [sqlite] Query Planner GROUP BY and HAVING clauses optimization ?

On Tuesday, 14 January, 2020 09:23, Simon Slavin wrote: >Would it be possible to phrase your SELECT as a SELECT with a sub-SELECT >? Have the sub-SELECT figure out which rows you want in which order, >then use a SELECT to apply your UDF to them ? It is guaranteed that the >sub-SELECT is proces

Re: [sqlite] Query Planner GROUP BY and HAVING clauses optimization ?

On 14 Jan 2020, at 4:14pm, Jean-Baptiste Gardette wrote: > The reason i asked this is that i have a query in wich one condition > filtering the recordset involves > an UDF and this UDF needs to be processed after all table filters have been > applied You cannot guarantee this. And even if you

Re: [sqlite] Query Planner GROUP BY and HAVING clauses optimization ?

Thank you Dominic and Keith for your replies The reason i asked this is that i have a query in wich one condition filtering the recordset involves an UDF and this UDF needs to be processed after all table filters have been applied Illustration : additionnal table : CREATE TABLE t2 ( a TEXT

Re: [sqlite] Query Planner GROUP BY and HAVING clauses optimization ?

On Tuesday, 14 January, 2020 06:58, Jean-Baptiste Gardette wrote: >Consider the following exemple : >CREATE TABLE t1 ( >a TEXT PRIMARY KEY, >b INTEGER); >SELECT * >FROM t1 >GROUP BY a >HAVING b > 1; >Will the GROUP BY clause be supressed and HAVING clause be rewritten in >WHERE clause by the

Re: [sqlite] Query Planner GROUP BY and HAVING clauses optimization ?

On Tue, Jan 14, 2020 at 2:57 PM Jean-Baptiste Gardette wrote: > SELECT * FROM t1 GROUP BY a HAVING b > 1; > > Will the GROUP BY clause be supressed and HAVING clause be rewritten in WHERE > clause by the optimizer ? My question would be why you wouldn't write it as a WHERE clause in the first pl

[sqlite] Query Planner GROUP BY and HAVING clauses optimization ?

Hi, Consider the following exemple : CREATE TABLE t1 ( a TEXT PRIMARY KEY, b INTEGER); SELECT * FROM t1 GROUP BY a HAVING b > 1; Will the GROUP BY clause be supressed and HAVING clause be rewritten in WHERE clause by the optimizer ? Jean-Baptiste _

Re: [sqlite] Query regarding Sqlite 3.30.1

On 12/19/19, Uthra Ganesan wrote: > Hi > > We are currently using SQLite 3.28.0. Can you please lt us know when can we > expect 3.30.1 in Maven central repository. > Please ask whomever controls the "Maven central repository". They will make that determination, not us. -- D. Richard Hipp d...@

[sqlite] Query regarding Sqlite 3.30.1

Hi We are currently using SQLite 3.28.0. Can you please lt us know when can we expect 3.30.1 in Maven central repository. Thanks Uthra ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/lis

Re: [sqlite] Query freezing on latest SQLite 3.30.1 build

s On Behalf Of Clovis Ribeiro,MyABCM Sent: Thursday, December 5, 2019 2:04 PM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Query freezing on latest SQLite 3.30.1 build Folks, The following query, when executed against the attached database using SQLite 3.30.1 (running on Windows OS)

[sqlite] Query freezing on latest SQLite 3.30.1 build

Folks, The following query, when executed against the attached database using SQLite 3.30.1 (running on Windows OS) will take forever to execute. If we remove all columns from both tables that are not actually used in the query, it is executed in milliseconds. SELECT COUNT(*) FROM (SELECT

Re: [sqlite] Query for Many to Many

On 6 Sep 2019, at 7:36am, Rowan Worth wrote: > I was surprised when this behaved differently in other SQL engines. eg. in > SQLite you can write: > > SELECT col1, col2 FROM table1, table2 USING But please don't, for the reason you gave. Not only is it ambiguous but different SQL engines inte

Re: [sqlite] Query for Many to Many

On Tue, 3 Sep 2019 at 22:17, Keith Medcalf wrote: > And the "," in the list of tables may be replaced by the word JOIN. It is > merely an alternate spelling. > I was surprised when this behaved differently in other SQL engines. eg. in SQLite you can write: SELECT col1, col2 FROM table1, table2

Re: [sqlite] Query planner regression with FTS4: slower path is chosen

On 2/9/62 16:57, Paul wrote: I has been a while without response, so I just bumping this message. 19 July 2019, 14:21:27, by "Paul" : I have a test case when the regression can be observed in queries that use JOINs with FTS4 tables, somewhere in between 3.22.0 and 3.23.0. For some reason the

Re: [sqlite] Query for Many to Many

" comments here> >> > USING needs parenthesis around the column list: ...using >> > (author_id)...using (book_isbn)... >> > -Original Message- >> > From: sqlite-users sqlite-users-boun...@mailinglists.sqlite.org On >> > Behalf Of Dominique Devienne &g

Re: [sqlite] Query for Many to Many

author_id)...using (book_isbn)... > > -Original Message- > > From: sqlite-users sqlite-users-boun...@mailinglists.sqlite.org On > > Behalf Of Dominique Devienne > > Sent: Tuesday, August 27, 2019 10:08 AM > > To: SQLite mailing list sqlite-users@mailinglists.sqlite

Re: [sqlite] Query for Many to Many

; > > USING needs parenthesis around the column list: ...using > (author_id)...using (book_isbn)... > > > -Original Message- > From: sqlite-users On > Behalf Of Dominique Devienne > Sent: Tuesday, August 27, 2019 10:08 AM > To: SQLite mailing list > Subject

Re: [sqlite] Query planner regression with FTS4: slower path is chosen

I has been a while without response, so I just bumping this message. 19 July 2019, 14:21:27, by "Paul" : > I have a test case when the regression can be observed in queries that > use JOINs with FTS4 tables, somewhere in between 3.22.0 and 3.23.0. > For some reason the planner decides to search

Re: [sqlite] Query for Many to Many

It does support natural joins. USING needs parenthesis around the column list: ...using (author_id)...using (book_isbn)... -Original Message- From: sqlite-users On Behalf Of Dominique Devienne Sent: Tuesday, August 27, 2019 10:08 AM To: SQLite mailing list Subject: Re: [sqlite

Re: [sqlite] Query for Many to Many

On Tue, Aug 27, 2019 at 4:00 PM Dominique Devienne wrote: > select author.*, books.* > from author_books > join author on author.author_id = author_books.author_id > join books on books.book_isbn = author_books.book_isbn > Which can also be written: select author.*, books.* from aut

Re: [sqlite] Query for Many to Many

On Tue, Aug 27, 2019 at 3:38 PM dboland9 wrote: > I need some help writing some queries for a MTM relationship. The example > tables are: > > author table books table author_books table > author_id PKbook_isbn PKa_b_id PK > author_fnamebook_title

Re: [sqlite] Query for Many to Many

: Tuesday, August 27, 2019 9:38 AM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Query for Many to Many All, I need some help writing some queries for a MTM relationship.  The example tables are: author table books table author_books table author_id PK

[sqlite] Query for Many to Many

All, I need some help writing some queries for a MTM relationship.  The example tables are: author table books table author_books table author_id PKbook_isbn PKa_b_id PK author_fnamebook_title author_id FK author_lnamebook_pub_

Re: [sqlite] Query Builder Access

On 20 Jul 2019, at 7:33pm, Revere Perkins wrote: > Is the SQL Query Builder only available in the licensed version? Sorry. SQLite is always completely free, and there's no Query Builder included as part of SQLite. You must be asking about a third-party product. And there are lots of SQL Qu

[sqlite] Query Builder Access

Hi, Is the SQL Query Builder only available in the licensed version? If it is available in the free version, how does one access it? The documentation says something like 'go to the Query Builder page' , but I don't find mention of it under any menu or via an icon. Thanks, Revere __

[sqlite] Query planner regression with FTS4: slower path is chosen

I have a test case when the regression can be observed in queries that use JOINs with FTS4 tables, somewhere in between 3.22.0 and 3.23.0. For some reason the planner decides to search non-FTS table first then scan the whole FTS table. Version 3.22.0 is the last unaffected, while issue is still pr

Re: [sqlite] Query planner: Covering index not chosen over primary key

Thank you for the suggestion! The actual schema & query are a good deal more complicated, and I'm not looking for general optimization help with them right now. Jen Pollock On Fri, May 03, 2019 at 10:11:04PM +0100, Simon Slavin wrote: > On 3 May 2019, at 9:34pm, Jen Pollock wrote: > > > SELEC

Re: [sqlite] Query planner: Covering index not chosen over primary key

On 3 May 2019, at 9:34pm, Jen Pollock wrote: > SELECT filename > FROM images >JOIN embedded_files ON images.file_id == embedded_files.id > WHERE type == 'png'; Try this: CREATE INDEX images (type, file_id); ANALYZE; ___ sqlite-users mailing lis

Re: [sqlite] Query planner: Covering index not chosen over primary key

On 5/3/19, Jen Pollock wrote: > I assume the problem here is that the primary key is usually a weird > thing to index. I can definitely work around this, but I thought it > might be worth reporting as something that could perhaps be improved in > the query planner. Thank you. I have your request

[sqlite] Query planner: Covering index not chosen over primary key

I have a database with a schema roughly like this: CREATE TABLE embedded_files( id INTEGER PRIMARY KEY, filename TEXT, data BLOB ); CREATE TABLE images( id INTEGER PRIMARY KEY, file_id INTEGER, type TEXT, FOREIGN KEY(file_id) REFERENCES embedded_files(id) ); Th

Re: [sqlite] Query Regression IN and Virtual Tables - followup

prüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Dan Kennedy Gesendet: Freitag, 29. März 2019 14:30 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] Re: [sqlite] Query Regression IN and Virtual Tables On 29/3/62 14:32, Hick Gun

[sqlite] Query Regression IN and Virtual Tables - followup

- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Dan Kennedy Gesendet: Freitag, 29. März 2019 14:30 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] Re: [sqlite] Query Regression IN and Virtual Tables On 29/3/62 14:32, Hick Gunter wrote: > When upgrading f

Re: [sqlite] Query Regression IN and Virtual Tables

On 29/3/62 14:32, Hick Gunter wrote: When upgrading from 3.7.14.1 to 3.24 I noticed the following problem Given a virtual table like CREATE VIRTUAL TABLE vt ( key1 INTEGER, key2 INTEGER, key3 INTEGER, attr1 INTEGER,...); whose xBestIndex function simulates (in unsupported syntax) CREATE VIR

[sqlite] Query Regression IN and Virtual Tables

When upgrading from 3.7.14.1 to 3.24 I noticed the following problem Given a virtual table like CREATE VIRTUAL TABLE vt ( key1 INTEGER, key2 INTEGER, key3 INTEGER, attr1 INTEGER,...); whose xBestIndex function simulates (in unsupported syntax) CREATE VIRTUAL INDEX vt_key ON vt (key1, key2, key

Re: [sqlite] Query planner: Scanning subqueries vs using automatic covering index

Thanks to all who have replied, very informative! :) This is just a database for own personal use so it's not a big deal in any way, mainly trying to get a better understanding of how Sqlite works here. I'll note that the sql queries are not static inside my application but they are generated dyn

Re: [sqlite] Query planner: Scanning subqueries vs using automatic covering index

On Sunday, 17 March, 2019 11:19, niklas wrote: >I agree that correlated subqueries in general seem more natural and >are probably also less likely to have the performance pessimizations >noticed with joins. >But I might also want to use the column, or in case of a correlated >subquery, the colu

Re: [sqlite] Query planner: Scanning subqueries vs using automatic covering index

On 17 Mar 2019, at 5:19pm, niklas wrote: > Is this a recent change in Sqlite or have I misunderstood something? The > Sqlite documentation still does not seem to say that column aliases can be > used in the WHERE clause at least. You are correct in two things. Column aliases cannot be relied

Re: [sqlite] Query planner: Scanning subqueries vs using automatic covering index

I agree that correlated subqueries in general seem more natural and are probably also less likely to have the performance pessimizations noticed with joins. But I might also want to use the column, or in case of a correlated subquery, the column alias, in the WHERE clause and previously that has n

Re: [sqlite] Query planner: Scanning subqueries vs using automatic covering index

On 15 Mar 2019, at 7:02pm, niklas wrote: > The data used for sqlite_stat1 in create.txt is taken from the real data, > it's copied from the sql-dump generated just after running ANALYZE. Okay. I should have guessed that. Sorry for doubting you. You seem to have figured out a work-around for

Re: [sqlite] Query planner: Scanning subqueries vs using automatic covering index

The data used for sqlite_stat1 in create.txt is taken from the real data, it's copied from the sql-dump generated just after running ANALYZE. I only wanted to include the minimum amount of data the demonstrate the issue so I omitted all other tables, views and data. As I understand it sqlite only

Re: [sqlite] Query planner: Scanning subqueries vs using automatic covering index

Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf >Sent: Friday, 15 March, 2019 14:44 >To: SQLite mailing list >Subject: Re: [sqlite] Query planner: Scanning subqueries vs using >automatic covering index >

Re: [sqlite] Query planner: Scanning subqueries vs using automatic covering index

volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of niklas >Sent: Friday, 15 March, 2019 01:36 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] Query planner: Scanning subqueries vs using >au

Re: [sqlite] Query planner: Scanning subqueries vs using automatic covering index

On 15 Mar 2019, at 7:35am, niklas wrote: > Dropping analyze (sqlite_stat1) information from the database made > the problem go away, i.e. made sqlite use index instead of scanning. Instead of dropping ANALYZE information, did you try updating it instead, by running ANALYZE again ? Ah ... >

[sqlite] Query planner: Scanning subqueries vs using automatic covering index

I recently noticed some very slow queries for my sqlite book database. Turns out that it happened due to the query planner decided to scan sub-queries instead of using an automatic covering index to search them. The database contains about 3000 entries and with two subqueries it took a noticab

Re: [sqlite] How to do I get an 'AND' condition to work in an SQLite query?

On 31 Jan 2019, at 6:21pm, Scott wrote: > Figured it out! I had set the column Deleted to "CHAR" but all the fields > without 'X' were null. If I replaced null with a valid character it worked. > Thanks for your time. Ah, JOINing on NULL. Well done. For future reference, SQLite doesn't have a

Re: [sqlite] How to do I get an 'AND' condition to work in an SQLite query?

Figured it out! I had set the column Deleted to "CHAR" but all the fields without 'X' were null. If I replaced null with a valid character it worked. Thanks for your time. Scott ValleryEcclesiastes 4:9-10 On Thursday, January 31, 2019, 12:46:34 PM EST, Scott wrote: I can return result

Re: [sqlite] How to do I get an 'AND' condition to work in an SQLite query?

On 31 Jan 2019, at 5:46pm, Scott wrote: > I can return results successfully from the t.Topic and n.Deleted columns > separately, but when I try to use AND I receive no results. There was an optimization bug that looked like your example in some previous version of SQLite. Are you running an u

[sqlite] How to do I get an 'AND' condition to work in an SQLite query?

I can return results successfully from the t.Topic and n.Deleted columns separately, but when I try to use AND I receive no results. I'm not sure what I may be doing wrong. This is my first exhaustive work with a database project, so I've had to learn some syntax along the way, but has me stumpe

Re: [sqlite] Query Planning Knowledge

Andy Bennett wrote: >> foreign key constraints > > my experience with other engines taught me that it makes experimenting at the > monitor harder. Then don't use them. :) But do you actually want 'wrong' data? > Are there any efficiency benefits or is it just there to enforce data > integrity?

Re: [sqlite] Query Planning Knowledge

Hi, I could use the inner join for the "entrys" join and the "items" join but not the "entry-items" join because each entry can have more than one item. WITH a(id, name) AS (VALUES (1, 'A')), b(id, name) AS (VALUES (1, 'B1'), (1, 'B2')) SELECT * FROM a INNER JOIN b USING (id); 1|

Re: [sqlite] Query Planning Knowledge

Andy Bennett wrote: > I could use the inner join for the "entrys" join and the "items" join > but not the "entry-items" join because each entry can have more than > one item. WITH a(id, name) AS (VALUES (1, 'A')), b(id, name) AS (VALUES (1, 'B1'), (1, 'B2')) SELECT * FROM a INNER JOIN b

Re: [sqlite] Query Planning Knowledge

Hi David, Thanks for your thoughtful reply. Can't go into as much detail as you. But a couple comments. "primary key unique" is redundant, and will actually create a redundant unique index. Are you refering to the CREATE TABLE clauses for the "items" and "registers" tables? I appear to

Re: [sqlite] Query Planning Knowledge

January, 2019 06:09 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] Query Planning Knowledge > >Hi, > >I'm having some problems understanding what the query planner is >doing and >how to convince it to work in the way I want. > >Sorry that this is such

Re: [sqlite] Query Planning Knowledge

hat way"... or it may not. Worth a try though. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Andy Bennett Sent: Tuesday, January 22, 2019 8:09 AM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Query Planning Kno

[sqlite] Query Planning Knowledge

Hi, I'm having some problems understanding what the query planner is doing and how to convince it to work in the way I want. Sorry that this is such a long and involved eMail. I've tried to describe my problem and show the steps I have taken in debugging it. I have made some effort to presen

[sqlite] Query regression with virtual tables

On upgrading from 3.7.14 to 3.24 (at last) I found a very detrimental change in the query implementation for a certain type of query: SELECT <...> FROM WHERE a IN () AND b IN () AND c = AND timestamp between AND ORDER BY timestamp DESC LIMIT ,; In 3.7.14 the xBestIndex function was called

Re: [sqlite] Query performance slower in 3.25

On Sat, Sep 29, 2018 at 5:33 PM Richard Hipp wrote: > On 9/29/18, Stephen F. Booth wrote: > > A query that ran fine under SQLite 3.24 is substantially slower in 3.25: > > Thanks for the data sent off-list > > Your work-around is to add a plus sign "+" before the "a.id" in the > GROUP BY clau

Re: [sqlite] Query performance slower in 3.25

On 9/29/18, Stephen F. Booth wrote: > A query that ran fine under SQLite 3.24 is substantially slower in 3.25: Thanks for the data sent off-list Your work-around is to add a plus sign "+" before the "a.id" in the GROUP BY clause. (And, BTW, shouldn't that really be an ORDER BY clause instea

Re: [sqlite] Query performance slower in 3.25

Could you please provide us with the database schema, or perhaps even a short script that demonstrates your problem, so that we can try to debug it? On 9/29/18, Stephen F. Booth wrote: > A query that ran fine under SQLite 3.24 is substantially slower in 3.25: > > SQLite version 3.24.0 2018-06-04

[sqlite] Query performance slower in 3.25

A query that ran fine under SQLite 3.24 is substantially slower in 3.25: SQLite version 3.24.0 2018-06-04 19:24:41 > .timer on > select a.id from a join c on a.id = case when c.b_a_name is not null then c.b_a_id else c.a_id end where a.id in (select a_fts.rowid from a_fts where a_fts match 'r*' or

[sqlite] Query planner improvements in case of AUTOMATIC INDEX

Hi List, When investigating performance of one of our queries I found an interesting situation that might be an opportunity for performance improvement. Tested with Sqlite version 3.15.2 (November 2016). Consider the following table and query CREATE TABLE Node (     Id  INTEGER PRIM

Re: [sqlite] Query on TEMP view.

.org] On Behalf Of Hegde, Deepakakumar >(D.) >Sent: Monday, 27 August, 2018 23:47 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] Query on TEMP view. > >Hi All, > > >I am facing a problem where in defined function registered to sqlite >is called multiple time

[sqlite] Query on TEMP view.

Hi All, I am facing a problem where in defined function registered to sqlite is called multiple time withput considering the filter. Ex: Table and entry: ID NAME PPID 1a.mp3 2 2b.mp3 3 Query: SELECT COUNT(TEMP.ID) FROM (SELECT ID,NAME,PPID FROM AUDIO WHERE PPID=2) AS TEMP

Re: [sqlite] Query optimisation

-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: 24 August 2018 15:43 To: SQLite mailing list Subject: Re: [sqlite] Query optimisation On 8/24/18, David Raymond wrote: > Running just "explain some query" will give you the virtual machine program > that it plans on using. You can then sca

Re: [sqlite] Query optimisation

On 8/24/18, David Raymond wrote: > Running just "explain some query" will give you the virtual machine program > that it plans on using. You can then scan through that to see what it's > doing. Note that the descriptions on the below page for those op codes are > sometimes really confusing and it

Re: [sqlite] Query optimisation

Goto 0 1 000 sqlite> -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of David Wellman Sent: Friday, August 24, 2018 7:47 AM To: SQLite Users Subject: [sqlite] Query optimisation HI all, I wo

[sqlite] Query optimisation

HI all, I would like to use the following example as a learning exercise for myself to check my understanding of part of sqlite processing. I have the following query which functionally works fine, and to be upfront about it the volume of data is so small that performance is not an issue.

Re: [sqlite] SQLite query planner doesn't seem to know when a partial index is COVERING

June 15, 2018 1:09 PM To: SQLite mailing list Subject: [sqlite] SQLite query planner doesn't seem to know when a partial index is COVERING Looks like a missed optimization opportunity here. Well, more than that - there doesn't appear to be a way to get SQLITE to automatically use a parti

[sqlite] SQLite query planner doesn't seem to know when a partial index is COVERING

Looks like a missed optimization opportunity here. Well, more than that - there doesn't appear to be a way to get SQLITE to automatically use a partial index if a similar non-partial index exists. E.g. create table Foo(a,b,c,d,e); create index Foo_inx on Foo(a,b,c); create index Foo_partial_inx

Re: [sqlite] Query on SQLite - VxWorks

On 9 Jun 2018, at 6:36pm, Guna Sekar wrote: > Is SQLite supports outfile query and dumps all data into specified file > format ? I don't understand your question, but you might want to use the command-line shell program to dump a database to a text file -- either as SQL commands or a CSV fil

[sqlite] Query on SQLite - VxWorks

Hi Team, Is SQLite supports outfile query and dumps all data into specified file format ? I have tried to extract data from table using outfile query but query failed status returned. If supports , Can you please share the syntax or example that will really help lot to me. Awaiting for your

Re: [sqlite] query planning - covering indices

On 5/15/18, Peter Johnson wrote: > > My understanding is that covering indices are more efficient, as the table > itself does not need to be scanned when all the required columns exist in > the covering index? That is often the case, but there are exceptions. > > Is it correct to say that exampl

[sqlite] query planning - covering indices

I noticed that the query planner favours the primary index when a covering index exists which can satisfy the same query. My understanding is that covering indices are more efficient, as the table itself does not need to be scanned when all the required columns exist in the covering index? Is it

Re: [sqlite] Sqlite query to get the offset of an entry in the list

SELECT COUNT(*) FROM TABLE WHERE NAME <= (SELECT NAME FROM TABLE WHERE ID = 3 ORDER BY NAME ASC) Balaji Ramanathan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite query to get the offset of an entry in the list.

Hegde, Deepakakumar (D.) Sent: Friday, April 27, 2018 3:51:27 PM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Sqlite query to get the offset of an entry in the list. Hi All, We have a requirement where in offset of the primary key ID is needed as per the sorted list. Table: ID NA

Re: [sqlite] Sqlite query to get the offset of an entry in the list.

: SQLite mailing list >Subject: Re: [sqlite] Sqlite query to get the offset of an entry in >the list. > >SELECT COUNT(*)+1 FROM TABLE WHERE NAME < (SELECT NAME FROM TABLE >WHERE ID = 3) > > > >(I think) > > > > >From: sqlite

Re: [sqlite] Sqlite query to get the offset of an entry in the list.

ers [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Simon Davies >Sent: Friday, 27 April, 2018 09:35 >To: SQLite mailing list >Subject: Re: [sqlite] Sqlite query to get the offset of an entry in >the list. > >On 27 April 2018 at 15:51, Hegde, Deepakakumar (D.) > wrote:

Re: [sqlite] Sqlite query to get the offset of an entry in the list.

anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Hegde, Deepakakumar >(D.) >Sent: Friday, 27 April, 2018 08:51 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] Sqlite q

Re: [sqlite] Sqlite query to get the offset of an entry in the list.

SELECT COUNT(*)+1 FROM TABLE WHERE NAME < (SELECT NAME FROM TABLE WHERE ID = 3) (I think) From: sqlite-users on behalf of Hegde, Deepakakumar (D.) Sent: Friday, April 27, 2018 3:51:27 PM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Sql

Re: [sqlite] Sqlite query to get the offset of an entry in the list.

On 27 April 2018 at 15:51, Hegde, Deepakakumar (D.) wrote: > Hi All, > > We have a requirement where in offset of the primary key ID is needed as per > the sorted list. . . . > 1 AAA > 3 BBB > 5 CCC > 4 WWW > 2 ZZZ > > So position of ID 3 as per the sorted list of the NAME is 2. sqlite

[sqlite] Sqlite query to get the offset of an entry in the list.

Hi All, We have a requirement where in offset of the primary key ID is needed as per the sorted list. Table: ID NAME 1 AAA 2 ZZZ 3 BBB 4 WWW 5 CCC Now need to get the offset of the ID 3 in the sorted list of the NAME. SELECT * FROM TABLE ORDER BY NAME ASC

Re: [sqlite] Query regarding CVE-2018-8740

I think sqlite-autoconf-3230100 should be OK. http://www.sqlite.org/2018/sqlite-autoconf-3230100.tar.gz Always check download page for the newest version http://www.sqlite.org/download.html 2018-04-27 1:11 GMT+02:00, salil GK: > Hello > >We are using sqlite-autoconf-322 in our product. R

[sqlite] Query regarding CVE-2018-8740

Hello We are using sqlite-autoconf-322 in our product. Recently there was a CVE released for sqlite - CVE-2018-8740 - for which patch is available in https://www.sqlite.org/cgi/src/vdiff?from=1774f1c3baf0bc3d&to=d75e67654aa9620b. But this patch is for sqlite code I suppose. The patch menti

Re: [sqlite] Query speed Regression: 3.15 much faster than 3.22 (Was: How to optimise a somewhat-recursive query? )

On 3/18/18, Jonathan Moules wrote: > > I can provide a small replication database if desired. Please do. Send it as an attachment directly to me. Also please send the exact text of the query that is running slowly. -- D. Richard Hipp d...@sqlite.org __

[sqlite] Query speed Regression: 3.15 much faster than 3.22 (Was: How to optimise a somewhat-recursive query? )

Hi List, So, I thought I'd solved my little problem, but upon testing it in my application it subjectively didn't seem any faster. I upgraded the SQLite in my IDE to 3.22, and it is confirming my suspicions. It turns out that when I run the below in 3.22, it takes about 0.150s. But in 3.15 it

Re: [sqlite] Query optimizer and recursive common table expressions

Hello, On 2018-01-04 01:53, R Smith wrote: Not to mention that if you wait several years, depending on your processor/compiler, the integer 64 value might wrap around and x<=3 might become true once more, producing rows again  :) Unfortunately, it will be stuck when int becomes double (at

Re: [sqlite] Query optimizer and recursive common table expressions

On 2018/01/04 12:36 AM, Richard Hipp wrote: On 1/3/18, Shane Dev wrote: sqlite> with recursive cnt(x) as (select 1 union all select x+1 from cnt) select * from cnt where x <= 3; [no sqlite> prompt, CPU utilization 25%] I assume sqlite is recursively adding rows to the queue without considering

Re: [sqlite] Query optimizer and recursive common table expressions

On 1/3/18, Shane Dev wrote: > > sqlite> with recursive cnt(x) as (select 1 union all select x+1 from cnt) > select * from cnt where x <= 3; > [no sqlite> prompt, CPU utilization 25%] > > I assume sqlite is recursively adding rows to the queue without considering > that the subsequent SELECT only n

Re: [sqlite] Query optimizer and recursive common table expressions

I have just spotted a couple of typos in my email below. The first two common table expressions should have been as follows - with recursive cnt(x) as (select 1 union all select x+1 from cnt limit 3) select * from cnt; with recursive cnt(x) as (select 1 union all select x+1 from cnt) select * from

[sqlite] Query optimizer and recursive common table expressions

Hi, This simple recursive common table expression returns all integers from 1 to 3 as expected - sqlite> with recursive cnt(x) as (select 1 union all select x+1 from cnt limit 3) select * from cnt where x; x 1 2 3 sqlite> If the LIMIT constraint is moved from the compound SELECT to the subsequen

Re: [sqlite] Query on bug fix for ticket 7ffd1ca1d2ad4ec

Hi Simon, Thank you for your suggestion. Will check it out. Regards, Mohan -- View this message in context: http://sqlite.1065341.n5.nabble.com/Query-on-bug-fix-for-ticket-7ffd1ca1d2ad4ec-tp96175p96608.html Sent from the SQLite mailing list archive at Nabble.com. _

Re: [sqlite] Query on bug fix for ticket 7ffd1ca1d2ad4ec

On 10 Jul 2017, at 9:17am, mohan_gn wrote: > Since our product is already in field with older > version of SQLite, we want to know this bug's impact. We use simple queries > like 'select * from abc where col1=123 and col2=456'. We don't use any > JOINs, VIEWs, nested queries. The tables we use

Re: [sqlite] Query on bug fix for ticket 7ffd1ca1d2ad4ec

Thank you for the reply. I know it has been fixed. Since our product is already in field with older version of SQLite, we want to know this bug's impact. We use simple queries like 'select * from abc where col1=123 and col2=456'. We don't use any JOINs, VIEWs, nested queries. The tables we use do

Re: [sqlite] Query on bug fix for ticket 7ffd1ca1d2ad4ec

On 10 Jul 2017, at 7:15am, mohan_gn wrote: > Could anyone please answer above query? Do you mean this bug ? If so, the bug was fixed in January. Please download the latest version of SQLite and see whether you can reproduce the bug. Simon. ___

Re: [sqlite] Query on bug fix for ticket 7ffd1ca1d2ad4ec

Hi All, Could anyone please answer above query? Thank you very much, Mohan -- View this message in context: http://sqlite.1065341.n5.nabble.com/Query-on-bug-fix-for-ticket-7ffd1ca1d2ad4ec-tp96175p96575.html Sent from the SQLite mailing list archive at Nabble.com.

[sqlite] Query on bug fix for ticket 7ffd1ca1d2ad4ec

Hi All, We are using SQLite 3.7.16.1 in our product which has been released to market sometime back. We have been analysing releases of SQLite from 3.7.16 to 3.19.2 to see if we need to upgrade the SQLite for the software that is already deployed in the field. For this purpose we use release

Re: [sqlite] Query plan gone haywire lays waste to my library's performance

On Friday, 28 April, 2017 15:55, Simon Slavin wrote: > > The only difference is the explicit JOIN statement. I was under the > > impression that using this, vs. the way I wrote it, is a matter of taste > > that doesn’t affect the execution of the query. > SQLite computes two-table searches usin

  1   2   3   4   5   6   7   8   9   10   >