I’m using Ignition 8.1.12 Perspective and found one of my queries was executing fine when I was checking for an integer in my WHERE clause, but when checking for a float it was failing. I boiled it down to a simpler query and found that unless a decimal number is treated as a string, prepared queries (runPrepQuery, runPrepScalarQuery) don’t seem to work with it?
For example, I ran this in the script console:
# Base query
query = "SELECT TOP 1 priority FROM line_configuration WHERE width="
# Integer in WHERE clause is fine
width = 5
print "Example queries with width %g"%width
print system.db.runPrepQuery(query + str(width))
print system.db.runPrepQuery(query + "?", [width])
print system.db.runPrepQuery(query + "?", [str(width)])
# But decimals in WHERE clause are not
width = 9.85
print "\nExample queries with width %g"%width
print system.db.runPrepQuery(query + str(width))
print system.db.runPrepQuery(query + "?", [width])
print system.db.runPrepQuery(query + "?", [str(width)])
And got this as outputs:
Example queries with width 5
<PyDataset rows:1 cols:1>
<PyDataset rows:1 cols:1>
<PyDataset rows:1 cols:1>
Example queries with width 9.85
<PyDataset rows:1 cols:1>
<PyDataset rows:0 cols:1>
<PyDataset rows:1 cols:1>
I would expect the second query output for a width of 9.85 should be returning 1 row, like the other theoretically identical queries. Am I misunderstanding how this should work? Why don’t I get any returned rows when using a ? placeholder with a float?
9.85 doesn’t have an exact binary representation. The DB and python resolve that to a different number of places, you won’t ever get equality. Basic rule: Don’t expect equality from floating point numbers. Ever.
This came very apparent for me with omron plc systems.
I had a place where i ended up with “100/10” (basically in the end).
Data type was real(float), and omron plc thought that would be 9.999999994 or something like this, where i would assuve it to be 10.0.
Thanks for the answers. Makes sense it’s a precision issue, but why taking the floating point representation and casting it to a string before giving it to SQL works (when directly passing it doesn’t) throws me for a loop a bit. I take it the ? substitution doesn’t take the value and plug it in as a string representation to query the database. Is it fair to say that by substituting the float directly I would be effectively writing a query like SELECT...width=9.8500000000000000001 or whatever slightly off representation 9.85 comes out to be for Python? I guess Python and SQL represent 9.85 differently numerically but would resolve their representations to the same string, so we need to use the string representation to go between the two?
The whole point of “?” substitution is to not perform string conversions. On either end. So the raw binary on the java end gets compared to the raw binary on the DB end. If the data types are different numbers of bits, a repeating binary fraction will definitely not be equal. (The shorter one will be extended with zero bits.)
Not performing string conversions is crucial to both performance and security. If you need to do the equivalent of equals with a float, instead do a pair of comparisons with less-than and greater-than that include a tiny offset to surround the target value. The ± offsets should correspond to the actual tolerance on equals needed by your algorithm. Doing two comparisons in raw binary will always be faster than a string conversion + single comparision combination.
Thanks for the explanation! I didn’t realize string conversions were that bad. One more question, if you don’t mind: is it better to calculate the offset in the SQL query or in the script (or is there a tangible difference)?
E.g. is it better to do this:
system.db.runPrepQuery("SELECT TOP 1 priority FROM line_configuration WHERE width<? AND width>?", [width+0.001, width-0.001])
Or this:
system.db.runPrepQuery("SELECT TOP 1 priority FROM line_configuration WHERE width<(?+0.001) AND width>(?-0.001)", [width, width])
I would expect no tangible difference in any modern DB. In both forms, the comparison constants are calculated once, either on Ignition’s end or via constant folding in execution setup on the DB end. I can imagine a very naïve DB implementation favoring the first case.
