Re: [sqlite] inner vs. outer join inconsistency

> I would agree that no warning is needed for for columns that don't state > any affinity, or for a non-affinity FK that refers to some PK with > affinity. > > I tend to agree with OP that an explicitly text foreign key referring to > an explicitly int primary key is probably worth a warning

Re: [sqlite] inner vs. outer join inconsistency

I believe a check constraint with an appropriate typeof comparison has been suggested for this usage. On 3/6/2013 6:29 AM, Ryan Johnson wrote: I would agree that no warning is needed for for columns that don't state any affinity, or for a non-affinity FK that refers to some PK with affinity.

Re: [sqlite] inner vs. outer join inconsistency

On 6 Mar 2013, at 9:50am, Tom Matrix wrote: > My only remaining concern is, however: Should not SQLite give an error (or at > least a warning) in cases where a foreign key constraint refers to a different > data type? I see why you asked but that won't work in SQLite.

Re: [sqlite] inner vs. outer join inconsistency

I would agree that no warning is needed for for columns that don't state any affinity, or for a non-affinity FK that refers to some PK with affinity. I tend to agree with OP that an explicitly text foreign key referring to an explicitly int primary key is probably worth a warning (perhaps

Re: [sqlite] inner vs. outer join inconsistency

On 06/03/2013 4:50 AM, Tom Matrix wrote: Richard Hipp writes: On Sun, Mar 3, 2013 at 2:48 PM, Tom Matrix wrote: I’ve encountered a problem, which is hardly reproducable on arbitrary databases, therefore I attached one. A simple, reproducible test case for (what

Re: [sqlite] inner vs. outer join inconsistency

SQLite doesn't care what kind of data type you are using, so, no, it shouldn't throw an error. The logic of this database engine is that you will always be comparing apples to apples, regardless if one happens to be orange. On Wed, Mar 6, 2013 at 4:50 AM, Tom Matrix wrote:

Re: [sqlite] inner vs. outer join inconsistency

Richard Hipp writes: > > On Sun, Mar 3, 2013 at 2:48 PM, Tom Matrix wrote: > > > > > The following query reports 18900080 rows (after some computation time): > > > > Is this the correct answer for the query below? Yes, it seems to be correct.

Re: [sqlite] inner vs. outer join inconsistency

Richard Hipp writes: > > On Sun, Mar 3, 2013 at 2:48 PM, Tom Matrix wrote: > > > > > I’ve encountered a problem, which is hardly reproducable on arbitrary > > databases, therefore I attached one. > > > > A simple, reproducible test case for (what we think is) your

Re: [sqlite] inner vs. outer join inconsistency

On Tue, Mar 5, 2013 at 2:29 PM, Richard Hipp wrote: > > > On Sun, Mar 3, 2013 at 2:48 PM, Tom Matrix wrote: > >> >> I’ve encountered a problem, which is hardly reproducable on arbitrary >> databases, therefore I attached one. >> > > A simple, reproducible

Re: [sqlite] inner vs. outer join inconsistency

On Sun, Mar 3, 2013 at 2:48 PM, Tom Matrix wrote: > > I’ve encountered a problem, which is hardly reproducable on arbitrary > databases, therefore I attached one. > A simple, reproducible test case for (what we think is) your problem can be seen in this ticket:

Re: [sqlite] inner vs. outer join inconsistency

On Sun, Mar 3, 2013 at 2:48 PM, Tom Matrix wrote: > > The following query reports 18900080 rows (after some computation time): > Is this the correct answer for the query below? > > SELECT COUNT(*) > FROM entryintervals ei1 > JOIN interval2label il1 ON ei1.id =

Re: [sqlite] inner vs. outer join inconsistency

> Can you send the database directly to me at drh@... please? It's already sent, and is also available in this shared folder: https://docs.google.com/folder/d/0B7kiuyPBHpjqYm8wZmdNcGI3c1E/edit?usp=sharing ___ sqlite-users mailing list

Re: [sqlite] inner vs. outer join inconsistency

> First of all attachments are stripped out from this list, so nobody > saw your database. Sorry for that. Now I created a shared folder so everyone has access and can take a look at it: https://docs.google.com/folder/d/0B7kiuyPBHpjqYm8wZmdNcGI3c1E/edit?usp=sharing It contains the database and

Re: [sqlite] inner vs. outer join inconsistency

> Could it be some of the features that SQLite doesn't support? > > http://www.sqlite.org/omitted.html > > /Patrik No, I think this problem is independent from those thing. Only basic SQL features are used. ___ sqlite-users mailing list

Re: [sqlite] inner vs. outer join inconsistency

On Sun, Mar 3, 2013 at 2:48 PM, Tom Matrix wrote: > Hello, > > I’ve encountered a problem, which is hardly reproducable on arbitrary > databases, therefore I attached one. > Can you send the database directly to me at d...@sqlite.org please? > The problem is that inner

Re: [sqlite] inner vs. outer join inconsistency

On Sun, Mar 3, 2013 at 11:48 AM, Tom Matrix wrote: > Hello, > > I’ve encountered a problem, which is hardly reproducable on arbitrary > databases, therefore I attached one. > The problem is that inner join omits the result on large datasets. But > again, ’large’ does not

Re: [sqlite] inner vs. outer join inconsistency

Could it be some of the features that SQLite doesn't support? http://www.sqlite.org/omitted.html /Patrik On 03/03/2013 08:48 PM, Tom Matrix wrote: > Hello, > > I’ve encountered a problem, which is hardly reproducable on arbitrary > databases, therefore I attached one. > The problem is that

[sqlite] inner vs. outer join inconsistency

Hello, I’ve encountered a problem, which is hardly reproducable on arbitrary databases, therefore I attached one. The problem is that inner join omits the result on large datasets. But again, ’large’ does not necessarly refer simply the amount of data; I couldn’t reproduce it on different