MySQL Bugs: #93824: "Query did not match any rows in the DB..." when using FLOAT or DOUBLE

Bug #93824	"Query did not match any rows in the DB..." when using FLOAT or DOUBLE
Submitted:	6 Jan 2019 18:13	Modified:	30 May 2019 20:42
Reporter:	Jonathan Wilbur	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL for Windows: MySQL for Excel	Severity:	S2 (Serious)
Version:	1.3.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When editing a table using the MySQL for Excel add-in, rows become un-editable when columns of type FLOAT (and presumably DOUBLE as well) are changed from NULL.

This is because, when the add-in generates the MySQL query to update the values, it searches the table for rows whose attributes match all but the changed attributes of the edited row, and updates that one. This means that a floating-point comparison is being done, which almost always (if not always) fails.

How to repeat:
DROP TABLE IF EXISTS BugTest;

CREATE TABLE IF NOT EXISTS BugTest (
id SERIAL PRIMARY KEY COMMENT 'A primary key is required for editing in MySQL for Excel.',
foo FLOAT NULL COMMENT 'This column will cause the test to fail, because this FLOAT will be used in equality comparison.',
bar VARCHAR(8) NULL COMMENT 'This column exists because we have to edit a column other than the one that contains the FLOAT for this bug to occur.'
);

INSERT INTO BugTest
VALUES
(1, 1.23, 'Hello'),
(2, NULL, 'World')
;

-- From within Excel:
-- 1. Ensure "Use optimistic updates on all Edit Data sessions" in the Options menu is unchecked. Restart Excel if you have to change it.
-- 2. Ensure that "Preview SQL statements before they are sent to the server" is selected from the Options menu.
-- 3. Edit `bar` in row #1.
-- 4. Inspect the generated SQL to confirm that it compares the current value for `foo` (which is a FLOAT) in Excel to the value of `foo` in the database using the equality comparison operator "=". This will never evaluate to true.
-- 5. Run the query.
-- 6. Observe that it fails to update.
-- 7. Check the warnings and observe "001: Query did not match any rows in the DB, values in the row 2 may have changed; please refresh values from the DB."
-- 8. Revert Data.
-- 9. Edit `bar` in row #2.
-- 10. Inspect the generated SQL to confirm that row #2 `foo` is being compared using `IS NULL` which will evaluate to true as it should.
-- 11. Observe that the command completes successfully with no warnings.

-- Notes:
-- 1. This does NOT occur when "Use optimistic updates on all Edit Data sessions" is checked, because the generated SQL only uses the primary key to select for updates when it is checked.
-- 2. It seems that Excel must be restarted for changes to the "Use optimistic updates on all Edit Data sessions" setting to be read.
-- 3. You can observe the generated SQL by selecting "Preview SQL statements before they are sent to the server" from the Options menu.

Suggested fix:
There are two fixes that come to mind:

1. Higher effort, better functionality: Compare FLOATs and DOUBLEs (and other floating-point types, where applicable) to each other within a range when using optimistic updates. This range could be configurable by a setting in the Options menu.

2. Lower effort, quicker resolution: Omit comparison of floating-point types entirely when using optimistic updates. This is a less desirable fix, because any columns of floating-point type will not be checked for conflicts.

Here is a non-fix, which SHOULD NOT be implemented:

1. Since a primary key is required for MySQL for Excel to allow you to edit a table anyway, the primary key should always be the only selection criterion for an UPDATE query.

This should not be used as a fix, because it conflicts with the "Use optimistic updates on all Edit Data sessions" options, which checks that none of the values of the row have changed since the row was originally pulled into Excel.

1

Attachment: 93824_1.png (image/png, text), 63.88 KiB.

1

Attachment: 93824_1.png (image/png, text), 63.88 KiB.

2

Attachment: 93824_2.png (image/png, text), 67.09 KiB.

3

Attachment: 93824_3.png (image/png, text), 33.30 KiB.

I couldn't repeat following your instructions, any additional info?.

Oops! I am sorry, Miguel. I made a mistake in my instructions: the "Use optimistic updates on all Edit Data sessions" option should be CHECKED. I will edit my original instructions to correct this, if possible.

Thank you for the feedback now verified.

Posted by developer:
 
The workaround in the meantime would be to turn off optimistic updates for the Edit Session, that would modify the where clause to just contain the primary key.

Added a new global option for Edit Sessions called "Tolerance for FLOAT and DOUBLE comparisons in WHERE clause", which is used along with the "Use optimistic updates on all Edit Data sessions" option.
Now WHERE clauses for optimistic updates use something like "´float_or_double_column´ BETWEEN [some_float_or_double_value]-[epsilon_tolerance] AND [some_float_or_double_value]+[epsilon_tolerance]" which allows for a proper row matching in the database.

Posted by developer:
 
Fixed as of the upcoming MySQL for Excel 1.3.8 release, and here's the changelog entry:

A new global option, Tolerance for FLOAT and DOUBLE comparisons in WHERE
clause, provides a way to edit data of these types that enables proper
row-matching in the database when it is used together with optimistic
updates (see Global Options, Edit Sessions Tab).

Thank you for the bug report.