If floating-point data is involved (common when values are stored as dollars and cents, e.g. 2.79 for 2 dollars 79 cents) you cannot use "=" for a comparison like this, because of small differences caused by the lack of exact representation for many floating-point numbers. See
http://en.wikipedia.org/wiki/Floating_point#Minimizing_the_effect_of_accuracy_problems and search for "The use of the equality test". (This is a very common issue when working with floating-point data. A lot of software stores money amounts as an integer number of pennies to avoid it.) The easiest solution is to subtract the two values in question, take the absolute value, and compare the difference to something reflecting the maximum difference you will allow -- something like SELECT * FROM orders_tbl WHERE 0.01 <= abs((ord_total+discount-tax1-tax2-tax3-tax4-delivery_tax-delivery_fee)-subtotal); (To reverse this to an "it's equal" test, change <= to > rather than != to =.) Using a value slightly less than 0.01 (e.g. 0.008) might prevent truly fringe cases from giving the wrong answer. J. Merrill -----Original Message----- Date: Fri, 06 Dec 2013 13:13:54 -0500 From: Scott Slater <sslat...@summitcn.com> To: sqlite-users@sqlite.org Subject: [sqlite] SELECT statement failure Hello, 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 client's data that demonstrates 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. If you manually look through the relevant column data all 4 orders are logically correct, and should be treated in the same manner by the select statements. I have tested this on Windows machines and posted it to a forum where another user was able to demonstrate the same issue. I have tried the windows command shell version (sqlite3.exe / sqlite-shell-win32-x86-3080200.zip) as well as the sqlite3.dll / sqlite-dll-win32-x86-3080100.zip and an earlier (not sure which) version of the dll. I have not tested on any other operating systems. Regards, Scott Slater Summit Computer Networks, Inc. (866) 922-9690 Ext. 7701 (724) 779-6390 Ext. 7701 sslat...@summitcn.com _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
