SQLitening Support Forum

I had a customer contact me that a report was randomly "missing data" and have tracked down the problem to a single SQL query.  the problem is that some items that logically match the WHERE clause don't get selected.  I have put together a reverse logic of that statement below by changing the = to != .  I have attached a very small subset of this clients data that shows the problem.  Using the attached file which contains 4 orders none of which should get selected using the statement below;

SELECT * FROM orders_tbl WHERE (ord_total+discount-tax1-tax2-tax3-tax4-delivery_tax-delivery_fee)!=subtotal;

However, record #1 gets selected.  If you change the != back to an =, then you get the remaining 3.

I have updated to the latest SQLite dll file, and still get this problem with this data set.  I have manually looked at all of the data and did the math and they should all be treated the same way.   Can anyone see a reason or something that I am missing that is causing this issue?

Thanks in advance.

-Scott Slater

Mapped drives also introduce other problems such as delayed cache writes and other oddities on some users systems.  I am moving a Tsunami project to SQLitening for the sole purpose of using the C/S model.

Interesting.  I will need to convert my quads which I was going to use as record IDs to something else.  Maybe a combo of DATE & RECORD for that day.

This works without converting to scientific notation.

Using$("#",QUAD_VAR)

Nevermind...  duh...  I was half asleep when I wrote the last post.  During conversion to the string is where the problem is so it can be passed in the SQL query. 

Isn't SQLite's default INTEGER affinity a 64-Bit INT?  Given that this is the case, I would think that a QUAD could be saved in that column without converting to text.

Quote from: Marc Giao on February 21, 2009, 02:06:25 PM
...I've never done any comparison tests but my gut feeling is that Tsunami may be slightly faster...

It might be.  I am also converting a substantial application from Tsunami to SQLite/ning.  My guess is that SQLitening is faster when used in client/server modes since SQL allows complex queries to be executed on the server side only, thus reducing the data that flows on the actual network.

Imagine the req: SELECT food_item, COUNT(*) FROM master_items GROUP BY food_item HAVING COUNT(*) < 20;

To do a similar query in any non-SQL based system, you would have to do the sorting in your program.  Loading ALL of the records (across the network), and then weeding out what you actually want.  The point is that all of the data would have to travel from the server to the client.  In SQL only the results travel the network (and the initial requst).