# Prepared Queries Not Substituting Floats Correctly **URL:** \ **Category:** Ignition\ **Tags:** ignition81\ **Created:** [November 25, 2021, 8:09pm UTC](https://forum.inductiveautomation.com/t/prepared-queries-not-substituting-floats-correctly/53471 "2021-11-25T20:09:25Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![catherine.fowler](https://avatars.discourse-cdn.com/v4/letter/c/34f0e0/32.png) [@catherine.fowler](https://forum.inductiveautomation.com/u/catherine.fowler)\ **Post date:** [November 25, 2021, 8:09pm UTC](https://forum.inductiveautomation.com/t/prepared-queries-not-substituting-floats-correctly/53471/1 "2021-11-25T20:09:25Z")
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: ```auto # 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: ```auto Example queries with width 5 Example queries with width 9.85 ``` 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? ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [November 25, 2021, 11:43pm UTC](https://forum.inductiveautomation.com/t/prepared-queries-not-substituting-floats-correctly/53471/2 "2021-11-25T23:43:55Z")
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. ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [November 26, 2021, 1:36am UTC](https://forum.inductiveautomation.com/t/prepared-queries-not-substituting-floats-correctly/53471/3 "2021-11-26T01:36:42Z")
> [@pturmel](#): > > Don’t expect equality from floating point numbers. Ever. But you can always format them to x decimal points which should work 99.99998% of the time. Edit: Damnit, that should be 5x 9's after the decimal 😄 ---
**Author:** ![SKA](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ska/32/29528_2.png) [@SKA](https://forum.inductiveautomation.com/u/SKA)\ **Post date:** [November 26, 2021, 8:38am UTC](https://forum.inductiveautomation.com/t/prepared-queries-not-substituting-floats-correctly/53471/4 "2021-11-26T08:38:58Z")
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. ---
**Author:** ![catherine.fowler](https://avatars.discourse-cdn.com/v4/letter/c/34f0e0/32.png) [@catherine.fowler](https://forum.inductiveautomation.com/u/catherine.fowler)\ **Post date:** [November 26, 2021, 1:46pm UTC](https://forum.inductiveautomation.com/t/prepared-queries-not-substituting-floats-correctly/53471/5 "2021-11-26T13:46:29Z")
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? ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [November 26, 2021, 3:48pm UTC](https://forum.inductiveautomation.com/t/prepared-queries-not-substituting-floats-correctly/53471/6 "2021-11-26T15:48:18Z")
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. ---
**Author:** ![catherine.fowler](https://avatars.discourse-cdn.com/v4/letter/c/34f0e0/32.png) [@catherine.fowler](https://forum.inductiveautomation.com/u/catherine.fowler)\ **Post date:** [November 26, 2021, 4:14pm UTC](https://forum.inductiveautomation.com/t/prepared-queries-not-substituting-floats-correctly/53471/7 "2021-11-26T16:14:40Z")
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: ```auto system.db.runPrepQuery("SELECT TOP 1 priority FROM line_configuration WHERE width?", [width+0.001, width-0.001]) ``` Or this: ```auto system.db.runPrepQuery("SELECT TOP 1 priority FROM line_configuration WHERE width<(?+0.001) AND width>(?-0.001)", [width, width]) ``` ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [November 26, 2021, 4:20pm UTC](https://forum.inductiveautomation.com/t/prepared-queries-not-substituting-floats-correctly/53471/8 "2021-11-26T16:20:51Z")
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.