[sqlite] Outer Join with Where terms - possible optimization

On 04/29/2016 09:41 PM, Josef Ku?era wrote: > Hello, > I have discovered a possible query planner improvement. I am using the SQLite > with virtual table modules. > > Situation 1: Lets have a query like 'SELECT V.* FROM (select A.RowID A_ROWID, > B.RowID B_ROWID, A.*, B.* from A left outer join B

[sqlite] Illegal SQL not rejected

> > > If a HAVING clause is specified, it is evaluated once for each group of > rows as a boolean expression . > If the result of evaluating the HAVING clause is false, the group is > discarded. If the HAVING clause is an aggregate expression, it is ev

[sqlite] [System.Data.SQLite] int overflow in date handling when unixepoch is used

> I am able to recreate the exception here. The value of Int64.MaxValue is > 9223372036854775807. The UnixEpoch values are measured in seconds from > the epoch 1970-01-01 00:00:00Z. Adding 9223372036854775807 seconds to the > UnixEpoch would result in a DateTime far beyond the allowed maximum va

[sqlite] Illegal SQL not rejected

I'm sure we could ALL write books on the hours spent tracking down an error that had nothing remotely to do with the error message returned by MS. :) -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf O

[sqlite] Illegal SQL not rejected

That error is saying that you can't using HAVING on a column unless it's in a group by or it's referenced in an aggregate in the HAVING clause You could say, HAVING SUM(A) <> 0 or something. The query as stated SELECT SUM(A) ... HAVING A<>0 makes no sense because A is not in the select list.

[sqlite] Illegal SQL not rejected

Hi, It seems like a too obvious omission to not be intentional. But I wonder why a query like SELECT SUM(A) FROM TABLE GROUP BY B HAVING A <> 0 Is not rejected. MS SQL server gives you this error message in this case: Column ?A? is invalid in the HAVING clause because it is not contained in ei

[sqlite] [System.Data.SQLite] int overflow in date handling when unixepoch is used

Makes sense, Somehow I had in my head that int64 would still only get you to the year 5000 or so. I guess the question is if it should fail like that in this case. Capping it at DateTime.MaxValue does not seem ideal either. Jann -Original Message- From: sqlite-users-bounces at mailingli

[sqlite] Outer Join with Where terms - possible optimization

Hello, I have discovered a possible query planner improvement. I am using the SQLite with virtual table modules. Situation 1: Lets have a query like 'SELECT V.* FROM (select A.RowID A_ROWID, B.RowID B_ROWID, A.*, B.* from A left outer join B on B.ref=A.key) V WHERE V.B_ROWID=?', where A and B a

[sqlite] Illegal SQL not rejected

Marc, On Fri, Apr 29, 2016 at 1:50 PM, Marc L. Allen wrote: > That error is saying that you can't using HAVING on a column unless it's in a > group by or it's referenced in an aggregate in the HAVING clause > > You could say, HAVING SUM(A) <> 0 or something. > > The query as stated > > SELECT SU

[sqlite] Illegal SQL not rejected

Hi, On Fri, Apr 29, 2016 at 1:28 PM, John McKown wrote: > On Fri, Apr 29, 2016 at 12:00 PM, Jann Roder > wrote: > >> Hi, >> It seems like a too obvious omission to not be intentional. But I wonder >> why a query like >> >> SELECT SUM(A) >> FROM TABLE >> GROUP BY B >> HAVING A <> 0 >> >> Is not r

[sqlite] Illegal SQL not rejected

On Fri, Apr 29, 2016 at 1:23 PM, Igor Korot wrote: > Marc, > > On Fri, Apr 29, 2016 at 1:50 PM, Marc L. Allen > wrote: > > That error is saying that you can't using HAVING on a column unless it's > in a group by or it's referenced in an aggregate in the HAVING clause > > > > You could say, HAVIN

[sqlite] Illegal SQL not rejected

On Fri, Apr 29, 2016 at 12:41 PM, Igor Korot wrote: > Hi, > > On Fri, Apr 29, 2016 at 1:28 PM, John McKown > wrote: > > On Fri, Apr 29, 2016 at 12:00 PM, Jann Roder > > wrote: > > > >> Hi, > >> It seems like a too obvious omission to not be intentional. But I wonder > >> why a query like > >> >

[sqlite] Illegal SQL not rejected

On Fri, Apr 29, 2016 at 10:00 AM, Jann Roder wrote: > Hi, > It seems like a too obvious omission to not be intentional. But I wonder why > a query like > > SELECT SUM(A) > FROM TABLE > GROUP BY B > HAVING A <> 0 > > Is not rejected. MS SQL server gives you this error message in this case: > > Co

[sqlite] Illegal SQL not rejected

On Fri, Apr 29, 2016 at 12:00 PM, Jann Roder wrote: > Hi, > It seems like a too obvious omission to not be intentional. But I wonder > why a query like > > SELECT SUM(A) > FROM TABLE > GROUP BY B > HAVING A <> 0 > > Is not rejected. MS SQL server gives you this error message in this case: > > Col

[sqlite] Lemon reduce action merge bug

On 4/28/16, Kelvin Sherlock wrote: > I believe the lemon reduce action optimizer needs to compare the codePrefix > and codeSuffix. Thanks for the bug report. A fix has now been checked in. -- D. Richard Hipp drh at sqlite.org

[sqlite] Working with blob

> I don't know a way to write binary data to a file using the > command-line tool. Maybe there's one out there someone else knows of. The SQL functions readfile and writefile mayhaps? They have to be loaded as an extension (or compiled into) to lib/dll if you want them there.

[sqlite] Working with blob

On 29 Apr 2016, at 3:00am, James K. Lowden wrote: > I don't know a way to write binary data to a file using the > command-line tool. Maybe there's one out there someone else knows of. Nobody else knew of a way either, so one was specially introduced into a recent version. On this page

[sqlite] Working with blob

On 2016/04/28 8:27 PM, deltagamma1 at gmx.net wrote: > What is a convenient way to store the path from a external blob (jpg, > pdf) ? > How can I retrieve this blob ? > Is there a Frontend which opens the jpg directly from the listed > table-content ? > > If I store the blob directly in the sqlit