Query results different on 1.5 on static query.

[mdelcambre]

There are some features which are not supported yet. Please check the Limitations first to see if your bug is listed.

Software versions

• Django: 4.2.11
• mssql-django: 1.5
• python: 3.9.19
• SQL Server: Microsoft SQL Server 2019 (RTM-CU22) (KB5027702) - 15.0.4322.2 (X64)
• OS: client MacOS Sonoma

Table schema and Model I don't know how much I can share and haven't been able to narrow down exactly to an easy demo. Will report back if I can, but wanted to just simply flag that something changed.

Database Connection Settings

{
        "ENGINE": "mssql",
        "NAME": "NAME",
        "USER": "USER",
        "PASSWORD": PASSWORD,
        "HOST": "HOST",
        "PORT": 1433,
        "OPTIONS": {
            "driver": "ODBC Driver 18 for SQL Server",
            "extra_params": "TrustServerCertificate=yes",
        },

Problem description and steps to reproduce Again I know this is a kind of useless report as I don't know how much I can share about our query and schema, but mostly just putting on your radar. Feel free to close as unhelpful.

We have an ETL that runs and reads from a DB that is not managed by django, we have a prepared query we read from a flat file and execute with a cursor directly as part of a very simple ETL from a legacy system. After updating to 1.5, the query results changed to have fewer results (1.1M rows). The query has no templating/rendering, it is just running a set SQL statement. On 1.4.2 we get approximately 1.4M rows, this agrees with the results when running on SQL Management Studio directly (and dbeaver with Microsoft JDBC Driver for SQL Server driver, unknown version).

The query uses a couple of CTEs, lots of CASE statements, joins, LIKE comparisons, and other standard comparisons (IS NULL, IN list) but nothing too exotic that should be pushing the bounds.

Expected behavior and actual behavior After updating to 1.5 I would expect that a query result would not change and would agree with all other database tools.

Error message/stack trace

Any other details that can be helpful I had django log the exact query it was using, there was no change to rendering, etc (as expected).

I wish I could be more helpful. I'll see if I can reproduce in a small demo setup and identify exactly what the issue was. But since the update appears to be silently modifying the results of queries, I wanted to flag in case other users have similar results.

[mShan0]

Thanks for the report. If you manage to get some repro steps, that would be great.

[ElRoberto538]

I have the same issue, running MyModel.objects.raw("some sql with a LIKE here") returns different results when run in the ORM vs when I run it in SQL Server Management Studio. I'm trying to make a demo, but when I dumb the query down a bit it works properly.

For reference, our original query with the issue has CTE's, subqueries etc, with the problematic "LIKE" being in an outer apply.

[mdelcambre]

My initial attempts to recreate with simple LIKEs with CTEs have not resulted in reproducing this bug. So unsurprisingly seems like a more subtle bug.

[ElRoberto538]

So we worked out the issue on our end, when we updated to 1.5, one of our raw queries had LIKE '%machine%' with the new update the % symbols broke it, so it got changed to LIKE '%s' and the word 'machine' passed in as a parameter. However the single quotes were left there wrapping the %s... Which somehow ran, but produced the wrong results. When I was trying to make an example I was correctly not wrapping the %s in single quotes and so it worked.

[mShan0]

We did make some changes in 1.5 that would affect those queries. Thanks for figuring it out, will fix this in the next release.