Re: [sqlite] Bug: WINDOW clause within a CTE

On 15/9/62 11:57, Jake Thaw wrote: The following examples demonstrate a possible bug when using a WINDOW clause within a CTE: SQLite version 3.30.0 2019-09-14 16:44:51 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to re

[sqlite] Bug: WINDOW clause within a CTE

The following examples demonstrate a possible bug when using a WINDOW clause within a CTE: SQLite version 3.30.0 2019-09-14 16:44:51 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sq

Re: [sqlite] specify CHECK clause to date text field

On 14/8/19 8:46 PM, Adrian Ho wrote: > On 14/8/19 8:33 PM, Clemens Ladisch wrote: >> CREATE TABLE t ( >> date date CHECK (date = date(date, '+0 days')) >> ); > Sadly, this isn't sufficient for guarding against malformed dates like > '2019-02-00' and '2019-02-1' that the OP listed, because the

Re: [sqlite] specify CHECK clause to date text field

SQLite mailing list >Subject: Re: [sqlite] specify CHECK clause to date text field > >> It seems the date function does not check that the date is valid, >> only the format. > >I've run into the same issue. Don't remember if it has been raised on >the list, but I have a

Re: [sqlite] specify CHECK clause to date text field

On Wed Aug 14, 2019 at 09:08:26PM +0800, Adrian Ho wrote: > On 14/8/19 8:47 PM, no...@null.net wrote: > > > > CREATE TABLE table_a( > > dt TEXT -- NOT NULL if you like > > CONSTRAINT valid_date CHECK(dt IS date(dt,'localtime')) > > ); > > Sorry, that 'localtime' qualifier

Re: [sqlite] specify CHECK clause to date text field

On 14/8/19 8:47 PM, no...@null.net wrote: > > CREATE TABLE table_a( > dt TEXT -- NOT NULL if you like > CONSTRAINT valid_date CHECK(dt IS date(dt,'localtime')) > ); Sorry, that 'localtime' qualifier is a non-starter; that will throw a "non-deterministic function in index

Re: [sqlite] specify CHECK clause to date text field

On 14/8/19 8:47 PM, Richard Hipp wrote: > On 8/14/19, Adrian Ho wrote: >> Here's a Dirty Little Secret: All the SQLite date functions are centered >> around strftime(), which is not implemented in a strictly correct sense >> in *every* Unix-like platform I've seen. > Not true. > > SQLite

Re: [sqlite] specify CHECK clause to date text field

On 8/14/19, Adrian Ho wrote: > Here's a Dirty Little Secret: All the SQLite date functions are centered > around strftime(), which is not implemented in a strictly correct sense > in *every* Unix-like platform I've seen. Not true. SQLite implements its own date and time computations, based on

Re: [sqlite] specify CHECK clause to date text field

> It seems the date function does not check that the date is valid, > only the format. I've run into the same issue. Don't remember if it has been raised on the list, but I have a vague memory that it fell into the WONTFIX category :-( > Consequently, I would appreciate any advice on the

Re: [sqlite] specify CHECK clause to date text field

On 14/8/19 8:33 PM, Clemens Ladisch wrote: > CREATE TABLE t ( > date date CHECK (date = date(date, '+0 days')) > ); Sadly, this isn't sufficient for guarding against malformed dates like '2019-02-00' and '2019-02-1' that the OP listed, because the CHECK expression in those cases resolves to

Re: [sqlite] specify CHECK clause to date text field

On 12/8/19 10:59 PM, Martin wrote: > sqlite> .version > SQLite 3.29.0 2019-07-10 17:32:03 > fc82b73eaac8b36950e527f12c4b5dc1e147e6f4ad2217ae43ad82882a88bfa6 > zlib version 1.2.11 > clang-10.0.1 > sqlite> select date('2019-02-00'); -- null > > sqlite> select date('2019-02-01'); -- ok >

Re: [sqlite] specify CHECK clause to date text field

Martin wrote: > sqlite> select date('2019-02-29'); -- not a leap year > 2019-02-29 > I would appreciate any advice on the preferred way to specify a > CREATE TABLE .. CHECK clause > to guard inserting a -mm-dd date into a text field. sqlite> select date('2019-02-29', '+0 days');

[sqlite] specify CHECK clause to date text field

Hi, Using the precompiled CLI for MacOS (10.13.6) I stumbled upon the following to do with the date() function: sqlite> .version SQLite 3.29.0 2019-07-10 17:32:03 fc82b73eaac8b36950e527f12c4b5dc1e147e6f4ad2217ae43ad82882a88bfa6 zlib version 1.2.11 clang-10.0.1 sqlite> select

Re: [sqlite] Problem with clause "where X in (...)"

Click on the link at the bottom of this email... From: sqlite-users on behalf of Don Walsh Sent: Wednesday, February 13, 2019 03:09 PM To: SQLite mailing list Subject: Re: [sqlite] Problem with clause "where X in (...)" Get me of this list On W

Re: [sqlite] Problem with clause "where X in (...)"

boun...@mailinglists.sqlite.org] >> On Behalf Of Mohd Radzi Ibrahim >> Sent: Wednesday, February 13, 2019 8:35 AM >> To: sqlite-users@mailinglists.sqlite.org >> Subject: [sqlite] Problem with clause "where X in (...)" >> >> Hi, >> I thought

Re: [sqlite] Problem with clause "where X in (...)"

without any error code, why do you say it's broken? > > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Mohd Radzi Ibrahim > Sent: Wednesday, February 13, 2019 8:35 AM > To: sqlite-users@mailinglist

Re: [sqlite] Problem with clause "where X in (...)"

users-boun...@mailinglists.sqlite.org] On Behalf Of Mohd Radzi Ibrahim Sent: Wednesday, February 13, 2019 8:35 AM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Problem with clause "where X in (...)" Hi, I thought that I hit a bug with SQLite 3.28.0 and 3.27.1 when my in-li

Re: [sqlite] Problem with clause "where X in (...)"

Your message is incomplete. Do you have a test case that we can see? On 2/13/19, Mohd Radzi Ibrahim wrote: > Hi, > I thought that I hit a bug with SQLite 3.28.0 and 3.27.1 when my in-list > contains too many items the code breaks here: > > The sqlite3_step just exit without returning any error

[sqlite] Problem with clause "where X in (...)"

Hi, I thought that I hit a bug with SQLite 3.28.0 and 3.27.1 when my in-list contains too many items the code breaks here: The sqlite3_step just exit without returning any error code. best regards, Radzi ___ sqlite-users mailing list

Re: [sqlite] Using with clause in update statement

x wrote: > If I replace the following statement > > UPDATE SomeTable SET SomeCol = Col1 * (LongWindedFormala) + Col2 * > (LongWindedFormala) + ... > > with > > WITH CTE(X) AS (SELECT LongWindedFormula) > UPDATE SomeTable SET SomeCol = Col1 * (SELECT X FROM CTE) + Col2 * (SELECT X > FROM

[sqlite] Using with clause in update statement

If I replace the following statement UPDATE SomeTable SET SomeCol = Col1 * (LongWindedFormala) + Col2 * (LongWindedFormala) + ... with WITH CTE(X) AS (SELECT LongWindedFormula) UPDATE SomeTable SET SomeCol = Col1 * (SELECT X FROM CTE) + Col2 * (SELECT X FROM CTE) + . Is ‘(SELECT X

[sqlite] AsyncTableQuery "Where" clause can't handle "OR" (||).

Sorry got confused with Sqlite sites, created this ticket at system.data.sqlite.org yesterday. Regards, Winter Ticket UUID: 4ca56c780c92f6e308abf1ad5bb76be2a3e29a68 Title: AsyncTableQuery "Where" clause can't handle "OR" (||). Status: Open Type: Code_Defect Severity: Important Priority:

Re: [sqlite] SQLite where clause tree

> On Nov 10, 2014, at 9:31 AM, Richard Hipp wrote: > > On Mon, Nov 10, 2014 at 12:28 PM, Ward Willats > wrote: > >> >>> On Nov 10, 2014, at 3:11 AM, Richard Hipp wrote: >>> >>> If you recompile the SQLite command-line shell

Re: [sqlite] SQLite where clause tree

On Mon, Nov 10, 2014 at 12:28 PM, Ward Willats wrote: > > > On Nov 10, 2014, at 3:11 AM, Richard Hipp wrote: > > > > If you recompile the SQLite command-line shell (sqlite3.exe) using the > > -DSQLITE_ENABLE_SELECTTRACE option, then you can enter: > > >

Re: [sqlite] SQLite where clause tree

> On Nov 10, 2014, at 3:11 AM, Richard Hipp wrote: > > If you recompile the SQLite command-line shell (sqlite3.exe) using the > -DSQLITE_ENABLE_SELECTTRACE option, then you can enter: > If I do that, gcc -D SQLITE_ENABLE_SELECTTRACE -D HAVE_READLINE -l readline -o

Re: [sqlite] SQLite where clause tree

On 11/10/2014 4:40 AM, Prakash Premkumar wrote: = /\ salary+ / \ 3 3 / * /

Re: [sqlite] SQLite where clause tree

On Mon, Nov 10, 2014 at 4:40 AM, Prakash Premkumar <prakash.p...@gmail.com> wrote: > The where clause in sqlite is encoded as a tree > > Let's say I have select statement like : > > SELECT * from employee where salary = 3+5*4+3; > > The tree which takes

Re: [sqlite] SQLite where clause tree

On 10 Nov 2014, at 9:40am, Prakash Premkumar wrote: > If I am constructing this tree for where clause by myself, should i take > the operator precedence in to account while constructing it or will sqlite > take care of precedence,given any tree (i.e constructing it with

Re: [sqlite] SQLite where clause tree

h.p...@gmail.com] > Gesendet: Montag, 10. November 2014 10:40 > An: General Discussion of SQLite Database > Betreff: [sqlite] SQLite where clause tree > > The where clause in sqlite is encoded as a tree > > Let's say I have select statement like : > > SELECT * from e

Re: [sqlite] SQLite where clause tree

2014 10:40 An: General Discussion of SQLite Database Betreff: [sqlite] SQLite where clause tree The where clause in sqlite is encoded as a tree Let's say I have select statement like : SELECT * from employee where salary = 3+5*4+3; The tree which takes care of operator precedence

[sqlite] SQLite where clause tree

The where clause in sqlite is encoded as a tree Let's say I have select statement like : SELECT * from employee where salary = 3+5*4+3; The tree which takes care of operator precedence is : = /\ salary

Re: [sqlite] The IN (... ) clause

"Richard Hipp" wrote... On Sun, Sep 14, 2014 at 4:31 PM, Simon Slavin wrote: On 14 Sep 2014, at 8:58pm, jose isaias cabrera wrote: > Yeah, that is what I am using now. I was trying to get the speed that supposedly is in the IN clause. :-)

Re: [sqlite] The IN (... ) clause

On Sun, Sep 14, 2014 at 4:31 PM, Simon Slavin wrote: > > On 14 Sep 2014, at 8:58pm, jose isaias cabrera > wrote: > > > Yeah, that is what I am using now. I was trying to get the speed that > supposedly is in the IN clause. :-) Thanks. > > BETWEEN

Re: [sqlite] The IN (... ) clause

On 14 Sep 2014, at 8:58pm, jose isaias cabrera wrote: > "Darren Duncan" wrote... >> >> >> BETWEEN '2014-01-01' AND '2014-01-05' > > Yeah, that is what I am using now. I was trying to get the speed that > supposedly is in the IN clause. :-) Thanks. BETWEEN is

Re: [sqlite] The IN (... ) clause

"Martin Engelschalk" wrote... Hi Jose, you are probably looking for the between-Operator: Open http://www.sqlite.org/lang_expr.html and search for "The BETWEEN operator" inn you case, date BETWEEN '2014-01-01' AND '2014-01-05' Martin Thanks, Martin. Yes, that is what I am using now...

Re: [sqlite] The IN (... ) clause

"Simon Slavin" wrote... On 14 Sep 2014, at 11:59am, Martin Engelschalk wrote: you are probably looking for the between-Operator: Open http://www.sqlite.org/lang_expr.html and search for "The BETWEEN operator" inn you case, date BETWEEN '2014-01-01' AND

Re: [sqlite] The IN (... ) clause

ng now. I was trying to get the speed that supposedly is in the IN clause. :-) Thanks. _______ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] The IN (... ) clause

jose isaias cabrera wrote: > I know that the IN clause contains a list of something. I.e. > > IN ('2014-01-01', '2014-01-02', '2014-01-03', '2014-01-04', '2014-01-05') > > So the question is, is there a shorter way for one to say something like, > > IN ('2014-01-01', ..., '2014-01-05') > > where

Re: [sqlite] The IN (... ) clause

On 14 Sep 2014, at 11:59am, Martin Engelschalk wrote: > you are probably looking for the between-Operator: Open > http://www.sqlite.org/lang_expr.html and search for "The BETWEEN operator" > > inn you case, date BETWEEN '2014-01-01' AND '2014-01-05' By the way,

Re: [sqlite] The IN (... ) clause

Hi Jose, you are probably looking for the between-Operator: Open http://www.sqlite.org/lang_expr.html and search for "The BETWEEN operator" inn you case, date BETWEEN '2014-01-01' AND '2014-01-05' Martin Am 14.09.2014 07:07, schrieb jose isaias cabrera: Greetings! I know that the IN

Re: [sqlite] The IN (... ) clause

On 2014-09-13, 10:07 PM, jose isaias cabrera wrote: I know that the IN clause contains a list of something. I.e. IN ('2014-01-01', '2014-01-02', '2014-01-03', '2014-01-04', '2014-01-05') So the question is, is there a shorter way for one to say something like, IN ('2014-01-01', ...,

[sqlite] The IN (... ) clause

Greetings! I know that the IN clause contains a list of something. I.e. IN ('2014-01-01', '2014-01-02', '2014-01-03', '2014-01-04', '2014-01-05') So the question is, is there a shorter way for one to say something like, IN ('2014-01-01', ..., '2014-01-05') where the content of the IN would

Re: [sqlite] Order By clause in aggregate functions?

On 21 Jul 2013, at 11:47pm, Igor Tandetnik wrote: > On 7/21/2013 5:01 PM, Simon Slavin wrote: >> I had to fake it. The parameter I passed to my aggregate function was a >> string as follows: >> >> theOrder||':'||theValue >> >> My function extension had to split the

Re: [sqlite] Order By clause in aggregate functions?

On 7/21/2013 5:01 PM, Simon Slavin wrote: I had to fake it. The parameter I passed to my aggregate function was a string as follows: theOrder||':'||theValue My function extension had to split the values into two parts Couldn't you just pass two parameters, separately? -- Igor Tandetnik

Re: [sqlite] Order By clause in aggregate functions?

On Sun, Jul 21, 2013 at 1:29 PM, Petite Abeille wrote: > > On Jul 21, 2013, at 10:15 PM, ss griffon wrote: > > > I'm writing an extension to SQLite that adds some aggregate functions. > > Some of them, require that the rows passed to the

Re: [sqlite] Order By clause in aggregate functions?

On 21 Jul 2013, at 9:29pm, Petite Abeille wrote: > Short answer: no. Right. I had to fake it. The parameter I passed to my aggregate function was a string as follows: theOrder||':'||theValue My function extension had to split the values into two parts, then sort

Re: [sqlite] Order By clause in aggregate functions?

On Jul 21, 2013, at 10:15 PM, ss griffon wrote: > I'm writing an extension to SQLite that adds some aggregate functions. > Some of them, require that the rows passed to the aggregate function > be sorted. It seems as if lots of data bases (MySQL, PostgreSQL) > support

[sqlite] Order By clause in aggregate functions?

I'm writing an extension to SQLite that adds some aggregate functions. Some of them, require that the rows passed to the aggregate function be sorted. It seems as if lots of data bases (MySQL, PostgreSQL) support an ORDER BY clause in their aggregate functions. Does SQLite support anything like

Re: [sqlite] C++ - WHERE clause - update

On 7 Sep 2012, at 2:18pm, Rob Richardson wrote: > One number (I don't know off hand which) is labeled SQLITE_ROW, which you are > using. Another is SQLITE_DONE. Another is SQLITE_MISUSE. There are several > of them. Quite right.

Re: [sqlite] C++ - WHERE clause - update

Discussion of SQLite Database' Subject: Re: [sqlite] C++ - WHERE clause - update Is this a tricky question? int sqlite3_step(sqlite3_stmt*); -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rob Richardson Sent: Thursday, September

Re: [sqlite] C++ - WHERE clause - 2nd update

org [sqlite-users-boun...@sqlite.org] on behalf of Arbol One [arbol...@gmail.com] Sent: Friday, September 07, 2012 4:07 AM To: 'General Discussion of SQLite Database' Subject: EXT :Re: [sqlite] C++ - WHERE clause - 2nd update Yes, thank? I gave you the answer you gave me, obviously I was right. I need yo

Re: [sqlite] C++ - WHERE clause - 2nd update

[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Marcus Grimm Sent: Friday, September 07, 2012 3:39 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] C++ - WHERE clause - 2nd update On 07.09.2012 08:58, Arbol One wrote: > I got this code to work, however, I am getting a segmentat

Re: [sqlite] C++ - WHERE clause - 2nd update

On 07.09.2012 08:58, Arbol One wrote: I got this code to work, however, I am getting a segmentation fault on this code. I pass to SQLite only one statement [db->setStmt(apstr);], I read the first of the 'fname', but I don't know how to get to the second 'fname' in the database. I am not very

[sqlite] C++ - WHERE clause - 2nd update

I got this code to work, however, I am getting a segmentation fault on this code. I pass to SQLite only one statement [db->setStmt(apstr);], I read the first of the 'fname', but I don't know how to get to the second 'fname' in the database. I am not very sure as to what do to tell the program to

Re: [sqlite] C++ - WHERE clause - update

: [sqlite] C++ - WHERE clause - update What is the value returned from sqlite3_step()? RobR -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Arbol One Sent: Thursday, September 06, 2012 12:14 PM To: 'General Discussion of SQLite

Re: [sqlite] C++ - WHERE clause - update

On 9/6/2012 12:14 PM, Arbol One wrote: rc = sqlite3_step(mystmt); if(rc == SQLITE_ROW ) { The code, in this case, does not process this statement!!?? It's possible that no row actually matches the condition, so sqlite3_step returns SQLITE_DONE on the first call. -- Igor Tandetnik

Re: [sqlite] C++ - WHERE clause - update

What is the value returned from sqlite3_step()? RobR -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Arbol One Sent: Thursday, September 06, 2012 12:14 PM To: 'General Discussion of SQLite Database' Subject: [sqlite] C

[sqlite] C++ - WHERE clause - update

rc = sqlite3_step(mystmt); if(rc == SQLITE_ROW ) { The code, in this case, does not process this statement!!?? <<-- apstr = (const char*)sqlite3_column_text(mystmt,pos); std::cout << apstr << std::endl; //<<-- this is not executed } Table: id | tile | fname | mname | lname |

Re: [sqlite] C++ - WHERE clause

Yeah -- I should've been in a better teaching mode Trying to keep things simple opens up these type of security problemsthough there are lots of situations where this works just fine and is no problem at all (e.g. when you don't have user input or it's completely under your own control

Re: [sqlite] C++ - WHERE clause

ail.com] > Sent: Thursday, September 06, 2012 3:45 AM > To: General Discussion of SQLite Database > Subject: EXT :Re: [sqlite] C++ - WHERE clause > > VALUES is used for INSERTing into a table, not for SELECTing. This is not > valid SQL (I would help you fix it, but I can't figure out w

Re: [sqlite] C++ - WHERE clause

te.org [sqlite-users-boun...@sqlite.org] on behalf of Baruch Burstein [bmburst...@gmail.com] Sent: Thursday, September 06, 2012 3:45 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] C++ - WHERE clause VALUES is used for INSERTing into a table, not for SELECTing. This is not val

Re: [sqlite] C++ - WHERE clause

On Thu, Sep 6, 2012 at 10:18 AM, Arbol One wrote: > As many of you know, I am trying to learn SQL using C++. > FWIW: it's much easier to learn SQL in its "native environment" (e.g. by using the sqlite shell app) and then apply that learning to your programming language of

Re: [sqlite] C++ - WHERE clause

VALUES is used for INSERTing into a table, not for SELECTing. This is not valid SQL (I would help you fix it, but I can't figure out what you were trying to achieve.) Here is a great reference: http://sqlite.org/lang_select.html On Thu, Sep 6, 2012 at 11:18 AM, Arbol One

[sqlite] C++ - WHERE clause

As many of you know, I am trying to learn SQL using C++. Below is an error I get when I try using the C++ example below it. Error Code: 1 Error Message: near "VALUES": syntax error Glib::ustring apstr; Glib::ustring sName; int

Re: [sqlite] order by clause should come after union not before

sers-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of YAN HONG YE > Sent: Friday, 15 June, 2012 03:57 > To: sqlite-users@sqlite.org > Subject: [sqlite] order by clause should come after union not before > > select * from hbc order by cmc desc limit 10

Re: [sqlite] order by clause should come after union not before

YAN HONG YE wrote: > select * from hbc order by cmc desc limit 10 > union > select * from hbc where qph>0 > union > select * from hbc where hctl=1 > > this sql cmd cause the error: > order by clause should come after union not before select * from hbc where rowid in

Re: [sqlite] order by clause should come after union not before

On Fri, Jun 15, 2012 at 08:10:20AM -0400, Kevin Benson scratched on the wall: > On Fri, Jun 15, 2012 at 5:56 AM, YAN HONG YE wrote: > > > > select * from hbc order by cmc desc limit 10 > > union > > select * from hbc where qph>0 > > union > > select * from hbc where hctl=1 >

Re: [sqlite] order by clause should come after union not before

On Fri, Jun 15, 2012 at 5:56 AM, YAN HONG YE wrote: > > select * from hbc order by cmc desc limit 10 > union > select * from hbc where qph>0 > union > select * from hbc where hctl=1 > > this sql cmd cause the error: > order by clause should come after union not before >

Re: [sqlite] order by clause should come after union not before

On 15 Jun 2012, at 10:56am, YAN HONG YE wrote: > select * from hbc order by cmc desc limit 10 > union > select * from hbc where qph>0 > union > select * from hbc where hctl=1 > > this sql cmd cause the error: > order by clause should come after union not before Yes. The

[sqlite] order by clause should come after union not before

select * from hbc order by cmc desc limit 10 union select * from hbc where qph>0 union select * from hbc where hctl=1 this sql cmd cause the error: order by clause should come after union not before ___ sqlite-users mailing list

Re: [sqlite] SQLITE LIMIT clause

a no-op. > Do you think otherwise? > There is no guarantee of this. Without the ORDER BY clause, SQLite might return the rows in PK1 order, or it might not. The answer depends on what other indices are available, whether or not you have run ANALYZE, the distribution of values for PK1 i

Re: [sqlite] SQLITE LIMIT clause

cricketfan wrote: > SELECT * FROM test WHERE PK1 > 100 LIMIT 100 ORDER BY PK1 ASC; > > Since I have the index on PK1, I believe the rows will be returned in the > ORDER of PK1. Putting an ORDER BY clause will be a no-op. Probably, but that's an implementation detail. If

Re: [sqlite] SQLITE LIMIT clause

t; http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://old.nabble.com/SQLITE-LIMIT-clause-tp32607006p32624793.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-user

Re: [sqlite] SQLITE LIMIT clause

Be aware that if you do not specify an ORDER BY clause, the order of the returned rows are undefined. You might not even end up with rows with a primary key even near 100. What you probably want is: SELECT * FROM test WHERE PK1 > 100 LIMIT 100 ORDER BY PK1 ASC; Other than that, those two

Re: [sqlite] SQLITE LIMIT clause

00 > from it? > > I delete records in my table (it is like a queue implementation) so I might > have gaps in between which is why I want to use the LIMIT clause. > > Any advice would be greatly appreciated. > -- > View this message in context: &g

[sqlite] SQLITE LIMIT clause

nt to use the LIMIT clause. Any advice would be greatly appreciated. -- View this message in context: http://old.nabble.com/SQLITE-LIMIT-clause-tp32607006p32607006.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing li

Re: [sqlite] No conflict clause in foreign key clause?

On May 28, 2010, at 10:54 PM, Robert Nickel wrote: > I notice that the foreign key clause > (http://www.sqlite.org/syntaxdiagrams.html#foreign-key-clause) does > not > include a conflict clause > (http://www.sqlite.org/syntaxdiagrams.html#conflict-clause). I always > specify "ON CONFLICT

[sqlite] No conflict clause in foreign key clause?

I notice that the foreign key clause (http://www.sqlite.org/syntaxdiagrams.html#foreign-key-clause) does not include a conflict clause (http://www.sqlite.org/syntaxdiagrams.html#conflict-clause). I always specify "ON CONFLICT ROLLBACK" with my other constraints, but what will happen when a foreign

Re: [sqlite] incorrect where clause does not throw error

On 10/05/2009 6:53 AM, Stefan Finzel wrote: > Porting an application to sqlite3.6.13 on Linux i made a mistake > creating a illegal query on a character field: > > select * from Test where Remark = NULL > > select * from Test where Remark <> NULL > > I was confused as there were neither data

[sqlite] incorrect where clause does not throw error

Porting an application to sqlite3.6.13 on Linux i made a mistake creating a illegal query on a character field: select * from Test where Remark = NULL select * from Test where Remark <> NULL I was confused as there were neither data nor an error. Shouldn't this cause at least an error

Re: [sqlite] PATCH: WHERE clause OR to UNION optimization

This new attached patch corrects a slight inefficiency in my previous patch. It now uses UNION ALL (TK_ALL) instead of UNION (TK_UNION) for the subselects, as was my initial intention. OR queries on dis-similar columns with large intermediate result sets are now even faster. Given the same

Re: [sqlite] PATCH: WHERE clause OR to UNION optimization

Joe Wilson <[EMAIL PROTECTED]> wrote: > The attached patch implements the WHERE clause "OR to UNION" > optimization as described in this post: > I just went thumbing through the firesafe and I do not think I have a copyright release on file for you, Joe. Please go print out a copy of one of

[sqlite] PATCH: WHERE clause OR to UNION optimization

The attached patch implements the WHERE clause "OR to UNION" optimization as described in this post: http://www.mail-archive.com/sqlite-users@sqlite.org/msg09004.html If the computed cost of the rewritten WHERE clause is lower than the original query when indexes are taken into account, then

[sqlite] New conflict clause: update

Hello, I was thinking on a new option that could be useful for sqlite. A new conflict clause called "update" so that the following SQL command: insert or update into table1 (a,b) values (1,2) was equivalent to update table1 set a=1,b=2 where ?unique-key-constrain

Re: [sqlite] ORDER BY clause with column values not case-sensitive

Dennis, It works. Thanks a lot. Srikanth. On 6/27/06, Dennis Cote <[EMAIL PROTECTED]> wrote: Srikanth wrote: > Is there a way to order the results of a select statement using the > values of a column name, with the values in that column insensitive? > E.g., If the column has the following

Re: [sqlite] ORDER BY clause with column values not case-sensitive

Srikanth wrote: Is there a way to order the results of a select statement using the values of a column name, with the values in that column insensitive? E.g., If the column has the following values: Zambia, italy,Iceland, then a regular "ORDER BY tablename.country DESC" would result in: italy

[sqlite] ORDER BY clause with column values not case-sensitive

Hi, Is there a way to order the results of a select statement using the values of a column name, with the values in that column insensitive? E.g., If the column has the following values: Zambia, italy,Iceland, then a regular "ORDER BY tablename.country DESC" would result in: italy Zambia Iceland

Re: [sqlite] SQLite where clause parsing?

On Tue, 2005-09-06 at 13:07 -0700, R S wrote: > Hi, > I have a million records in my DB. I tried using .explain on and ran the > query below which took a long time although I just want the last 100,000 > records...(It ran much faster when my table had a 100,000 records) so I > assume it is

[sqlite] SQLite where clause parsing?

Hi, I have a million records in my DB. I tried using .explain on and ran the query below which took a long time although I just want the last 100,000 records...(It ran much faster when my table had a 100,000 records) so I assume it is related to how I constructed the statement. select columns