Re: [sqlite] Possible issue in optimizer, strips away order by

On Tue, Feb 25, 2014 at 1:11 PM, nobre wrote: > Hi! Given this schema: > > create table q (id integer primary key, idLevel integer); > create table level (id integer primary key); > > insert into q values(1, 1); > insert into q values(2, 1); > insert into q values(3, 1); >

Re: [sqlite] Strange UNION ALL / ORDER BY behaviour

On Tue, Feb 25, 2014 at 3:37 PM, Mark Lawrence wrote: > If you manage to determine some > kind of work-around for the condition occuring I'd appreciate hearing > about it > No work-around, yet. There is a patch online ( http://www.sqlite.org/src/info/cc5eda5422) that should

Re: [sqlite] System.Data.SQLite fails to load

William Drago wrote: > > I am using System.Data.SQLite with a relatively uncommon > language called VEE. This is an interpreted language that > runs in a 32bit development/runtime environment. > I've never heard of this language before; however, it sounds like it hosts the CLR within its

Re: [sqlite] Strange UNION ALL / ORDER BY behaviour

> The ticket is http://www.sqlite.org/src/info/8c63ff0eca > > The problem is that in the virtual machine that SQLite uses, a pair > of concurrent co-routines (one for each of the two SELECTs in the > UNION ALL) are both trying to use the same temporary register at the > same time. Bummer. Yep,

Re: [sqlite] Query Flattening / Query Optimization / Correlated Subquery Substitution

Hello, thank you for your reply, that works for me. To give a context and a background; my comment was implying a change of design by adding an abstraction layer between the data and the representation, a change of raw-data storage with the support of a middle-man-linker e.g async data events,

Re: [sqlite] Strange UNION ALL / ORDER BY behaviour

On Tue, Feb 25, 2014 at 11:32 AM, Mark Lawrence wrote: > > Righto, now included *inline* is the following: > > * Schema for the tables > > * Rows for the tables > > * 4 queries demonstrating the issue: > 1. Single SELECT query #1 > 2. Single SELECT

[sqlite] Possible issue in optimizer, strips away order by

Hi! Given this schema: create table q (id integer primary key, idLevel integer); create table level (id integer primary key); insert into q values(1, 1); insert into q values(2, 1); insert into q values(3, 1); insert into level values(1); When running this query: select p.* FROM q as p inner

Re: [sqlite] Strange UNION ALL / ORDER BY behaviour

On Tue Feb 25, 2014 at 05:24:55PM +0100, Mark Lawrence wrote: > On Tue Feb 25, 2014 at 09:37:41AM -0500, Richard Hipp wrote: > > Can you please send the database schema, and possibly some test data? > > Attached is an SQL file containing enough to reproduce the issue on my > system: Righto, now

Re: [sqlite] Strange UNION ALL / ORDER BY behaviour

On 25 Feb 2014, at 4:24pm, Mark Lawrence wrote: > Attached Sorry, but you can't attach files to posts to this list. We don't want everyone sending us their homework. Could you email directly or put the files on a server somewhere ? Simon.

Re: [sqlite] Strange UNION ALL / ORDER BY behaviour

This mailing list strips attachments. Please include the SQL text in-line. On Tue, Feb 25, 2014 at 11:24 AM, Mark Lawrence wrote: > On Tue Feb 25, 2014 at 09:37:41AM -0500, Richard Hipp wrote: > > Can you please send the database schema, and possibly some test data? > >

Re: [sqlite] Strange UNION ALL / ORDER BY behaviour

On Tue Feb 25, 2014 at 09:37:41AM -0500, Richard Hipp wrote: > Can you please send the database schema, and possibly some test data? Attached is an SQL file containing enough to reproduce the issue on my system: * Schema for the tables * Rows for the tables * 4 queries

Re: [sqlite] Strange UNION ALL / ORDER BY behaviour

Can you please send the database schema, and possibly some test data? On Tue, Feb 25, 2014 at 6:21 AM, Mark Lawrence wrote: > I am seeing what I think may be buggy behaviour, using a recent sqlite > packaged by debian: > > 3.8.3.1 2014-02-11 14:52:19

[sqlite] Strange UNION ALL / ORDER BY behaviour

I am seeing what I think may be buggy behaviour, using a recent sqlite packaged by debian: 3.8.3.1 2014-02-11 14:52:19 ea3317a4803d71d88183b29f1d3086f46d68a00e What I am seeing is too few rows returned from a UNION ALL query. I can break it down as follows. Query 1 on its own works fine,

Re: [sqlite] Query Flattening / Query Optimization / Correlated Subquery Substitution

On Monday, 24 February, 2014 21:53, mm.w <0xcafef...@gmail.com> said: >I don't want to be annoying but why nowadays people are >sub-abusing-sub-selecting instead of using JOINs? moreover, that is in >most cases faster (a lot) and certainly more Human Readable. Neither JOIN nor LEFT JOIN will

Re: [sqlite] check constraint

d b wrote: > I would like to add check constraint for existing database. Is it > possible? SQLite has no built-in function (such as ALTER TABLE) to do this. However, if you want to do a change that does not affect how the table's data is stored in the database file, then you can change the

[sqlite] check constraint

Hi, I would like to add check constraint for existing database. Is it possible? for ex: create table emp(id integer primary key autoincrement, fullname,fathername,mothername as text); insert into emp(fullname,fathername,mothername) values("a","b","c"); insert into

[sqlite] System.Data.SQLite fails to load

All, I am using System.Data.SQLite with a relatively uncommon language called VEE. This is an interpreted language that runs in a 32bit development/runtime environment. My application works fine when run on an internal or USB thumb drive. However, if I try running it from a network drive

Re: [sqlite] Query Flattening / Query Optimization

On 25 Feb 2014, at 4:31am, Keith Medcalf wrote: > does generate a plan with only one execution of each correlated subquery, but > does not give me access to the intermediate results You might experiment with creating a view for the subquery instead of for the query as a