Re: [sqlite] Bug where query does not give a result while it should

[Harmen de Jong - CoachR Group B . V .]

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Dan Kennedy
Sent: woensdag 18 december 2013 16:23
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Bug where query does not give a result while it should

>> Are we missing something, or is this a bug?
>Are there any NULL values in the productionentryid column of table zentry?

You're right! Thanks for pointing us into that direction.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug where query does not give a result while it should

[Dan Kennedy]

On 12/18/2013 10:10 PM, Harmen de Jong - CoachR Group B.V. wrote:

We have found a query that does not give a result, while it should.

The query is:
select id
from productionentry
where id NOT IN (select productionentryid from zentry)
AND examid=12581;

There are rows in the table productionentry for which the id does not exist in 
the column productionentryid in the table zentry.
However, when we execute the following query (which should be practically the 
same), we do get results:
select id
from productionentry
where id NOT IN (select id
from productionentry
where id IN (select productionentryid from zentry)
AND examid=12581)
AND examid=12581;

So for some reason the NOT IN keyword does not work correctly. We created a 
test database that can be downloaded here: 
http://www.coachrdevelopment.com/share/querybug.zip .

Are we missing something, or is this a bug?

Are there any NULL values in the productionentryid column of
table zentry?

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug where query does not give a result while it should

[Harmen de Jong - CoachR Group B . V .]

We have found a query that does not give a result, while it should.

The query is:
select id
from productionentry
where id NOT IN (select productionentryid from zentry)
AND examid=12581;

There are rows in the table productionentry for which the id does not exist in 
the column productionentryid in the table zentry.
However, when we execute the following query (which should be practically the 
same), we do get results:
select id
from productionentry
where id NOT IN (select id
from productionentry
where id IN (select productionentryid from zentry)
AND examid=12581)
AND examid=12581;

So for some reason the NOT IN keyword does not work correctly. We created a 
test database that can be downloaded here: 
http://www.coachrdevelopment.com/share/querybug.zip .

Are we missing something, or is this a bug?

Best regards,

Harmen de Jong
CoachR Group B.V.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] virtual tables and group by: how could we prevent to retrieve all rows from a vtab and sort them later ?

[Perrin, Lionel]

Hello,

I plan to use sqlite to implement an 'aggregation tool'. Basically, the design 
would be the following:


1.   I implement a virtual table CFL(contract, amount) which may provide up 
to 1 billion unsorted rows.

2.   The aggregation phasis will be defined at run time and may consists in 
something as simple as 'select contract, sum(amount) from cfl group by 
contract'.

3.   Since there will be only ~1000 different contracts, I expect SQLite to 
aggregate 'on the fly' the rows from CFL.

4.   Unfortunately, as shown by the explain plan, it looks that SQLite 
first select all rows from CFL. When all rows are retrieved (which implies 
something like a 8Go temporary file), the rows are sorted and aggregated.

[0, 0, 0, "SCAN TABLE CFL VIRTUAL TABLE INDEX 0: (~0 rows)"]

[0, 0, 0, "USE TEMP B-TREE FOR GROUP BY"]


Is there a way to force rows to be dispatched (using the group by) and 
aggregated on the fly instead of being stored, sorted and then aggregated? Note 
that I can't change the production order of the rows in the virtual table.

Thanks for your help,

Regards,

Lionel
-

Moody's monitors email communications through its networks for regulatory 
compliance purposes and to protect its clients, employees and business and 
where allowed to do so by applicable law. Parties communicating with Moody's 
consent to such monitoring by their use of the email communication. The 
information contained in this e-mail message, and any attachment thereto, is 
confidential and may not be disclosed without our express permission. If you 
are not the intended recipient or an employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
you have received this message in error and that any review, dissemination, 
distribution or copying of this message, or any attachment thereto, in whole or 
in part, is strictly prohibited. If you have received this message in error, 
please immediately notify us by telephone, fax or e-mail and delete the message 
and all of its attachments. Thank you. Every effort is made to keep our network 
fr
 ee from viruses. You should, however, review this e-mail message, as well as 
any attachment thereto, for viruses. We take no responsibility and have no 
liability for any computer virus which may be transferred via this e-mail 
message. 

-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] General R*Tree query

[Brian T. Carcich]

On Wed, Dec 18, 2013 at 1:53 AM, Roman Fleysher <
roman.fleys...@einstein.yu.edu> wrote:

> Perhaps this is a weird way for me to get deeper knowledge of R trees, and
> because I vaguely remember that Tyco refers to a specific epoch in which
> coordinates are defined, but would it be possible to search R tree using a
> cone, i.e. stars within a cone of certain degree around given star? This
> would require a trigonometric calculation before comparison can be made but
> can be done in a single comparison.
>
> Or, since RA and DEC coordinates are not area preserving (nor distance) --
> i.e. angle between stars at DEC =0 is bigger than angle between stars at
> DEC=80 when they are the same delta RA apart -- then maybe instead of
> defining rectangular FOV in RA and DEC one should be defining rectangular
> FOV in DEC, sin(RA)? Then one would not need two searches.
>
> The goal is to find neighbors to a given star defined roughly by some
> metric? Since there's nothing magical in RA , DEC coordinates the metric
> could use some other coordinates?



Every [RA,DEC] pair resolves to a unit vector in Cartesian coordinate space
i.e. an [X,Y,Z] triplet on the surface of a unit sphere; that would be a
continuous metric without the RA=0=360 issue.  I don't see why the R*Tree
could not be set up with X, Y, and Z, plus magnitude limits; the set of
nodes is hollow in a 3D sense so the first-level non-leaf nodes would have
a lot of empty space, but I don't think that matters; I've been thinking
about doing it this way for some time.  For my app I already store XYZs in
the outer, non-R*Tree table because all final comparisons have to be in
Cartesian space anyway.

But in general the search region is so small that the cosine[DEC]
dependence of distance per degree of RA is effectively constant for any one
search, and  an [RA,DEC,Mag] tree should be "good enough" because it pares
down the search space quickly from 2.5M stars in Tycho=2 to a few hundred
very quickly, with the caveat that there is a special case near RA=0=360.

In any event the R*Tree is not going to do the final geometric comparison,
rather it reduces the number of stars that need that comparison, and that's
the goal.

Btw, if you want to see something cool, look at chapter 4 of Dustin Lang's
thesis (www.astro.princeton.edu/~dstn/lang-thesis.pdf); the whole paper is
cool, but I really enjoy that chapter.

-b
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An "unable to open database file" error that has nothing to do with opening database file

[Jan Nijtmans]

2013/12/17 margave :
> Well, I gave up on sqlite inside of Cygwin.
>
> I found a standalone sqlite3.exe that does not depend upon (or install with)
> Cygwin.
> It's from http://www.sqlite.org
> One executable, no .dlls ... and it just runs, no problems.

I think I know what's the problem: The SQLite version included
in Fossil 1.27 had a bug in the calculation of the temp path:
It should be "/tmp/etilqs." but the slash was missing, so
actual temporary files were named "/tmpetilqs.". Whether
it works or not depends on your rights on the '/' directory.

Workaround: Create a dummy directory "foo" inside
/tmp, and set the SQLITE_TMPDIR environment
variable to "/tmp/foo". Then temporary files should
be created in the right directory (even though the name
is not right) which should make it work.

This SQLite bug is already fixed here:
   
so next fossil version should be OK.

Thanks!
Jan Nijtmans
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users