Skip to content

Arithmetic operations adding casts to SQLite that cause incorrect results #2792

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Closed
PleasantD opened this issue May 26, 2021 · 1 comment · Fixed by #2807
Closed

Arithmetic operations adding casts to SQLite that cause incorrect results #2792

PleasantD opened this issue May 26, 2021 · 1 comment · Fixed by #2807

Comments

@PleasantD
Copy link
Contributor

Affects v5.3.0+

How to Reproduce
var result = session.Query<Entity>().Where(e => e.DecimalValue != null && (offset + e.DecimalValue) == test).ToList();
var result = session.Query<Entity>().Where(e => e.IntValue != null && (offset + (decimal?)e.IntValue) == test).ToList();

Where offset and test are decimal or decimal? types.

Expected Result
result contains a list of entities where the equality condition matches.
This works fine for PostgreSQL and MS SQL Server.

Actual Result
SQLite gives no errors and always produces 0 results.

@PleasantD
Copy link
Contributor Author

Prior to v5.3.0 the SQL generated for all 3 DB types looked like

SELECT /* omitted for berevity*/
FROM "Entity" entity0_
WHERE (entity0_."IntValue" IS NOT NULL)
	AND 5 /* @p1 */ + entity0_."IntValue" = 15 /* @p2 */

After v5.3.0 the PostgreSQL and MSSQL queries look like

SELECT /* omitted for berevity*/
FROM "Entity" entity0_
WHERE (entity0_."IntValue" IS NOT NULL)
	AND 5 /* @p1 */ +  cast(entity0_."IntValue" AS DECIMAL(29, 10)) = 15 /* @p2 */

While the SQLite query looks like

SELECT /* omitted for berevity*/
FROM "Entity" entity0_
WHERE (entity0_."IntValue" IS NOT NULL)
	AND 5 /* @p1 */ +  cast(entity0_."IntValue" AS REAL) = 15 /* @p2 */

@bahusoid bahusoid linked a pull request Jun 3, 2021 that will close this issue
@fredericDelaporte fredericDelaporte added this to the 5.3.9 milestone Jun 6, 2021
@fredericDelaporte fredericDelaporte changed the title Regression - Arithmetic operations adding casts to SQLite that cause incorrect results Arithmetic operations adding casts to SQLite that cause incorrect results Jun 6, 2021
hazzik pushed a commit that referenced this issue Jun 16, 2021
@hazzik hazzik closed this as completed Jun 16, 2021
bahusoid pushed a commit to bahusoid/nhibernate-core that referenced this issue Aug 10, 2021
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

Successfully merging a pull request may close this issue.

3 participants