currently_valid scope in Spree::Price

[m-otiv]

I'm trying to use solidus with Microsoft SqlServer, please, don't ask me why.

Solidus Version: 2.9.6

Current behavior When i try to open the list of product with some product in it, raise me this error: TinyTds::Error: Incorrect syntax near the keyword 'IS'.: EXEC sp_executesql N'SELECT [spree_prices].* FROM [spree_prices] WHERE [spree_prices].[currency] = @0 AND [spree_prices].[country_iso] IS NULL AND [spree_prices].[variant_id] = @1 ORDER BY country_iso IS NULL, [spree_prices].[updated_at] DESC, [spree_prices].[id] DESC', N'@0 nvarchar(4000), @1 int', @0 = N'EUR', @1 = 1

Screenshots

Solution I found out that this line use the order instead of where to check if country_iso is null

[kennyadsl]

Can you please add some context to reproduce this issue? When/where does this happen?

[jarednorman]

I know Microsoft SQL Server has some stuff around sorting nulls that might be issue. I doubt the country_iso condition is the issue as that's very standard SQL, but I wouldn't be surprised if ORDER BY country_iso IS NULL were not playing nice with SQL Server, but that might be an ActiveRecord issue more than a Solidus one.

Regardless, more context/info would be helpful.

[gsmendoza]

Hello @m-otiv ! Were you able to resolve this issue with SQL server?

[jarednorman]

I think regardless we can probably close this, because if SQLServer doesn't support this query, the ActiveRecord adapter shouldn't be generating it. What we're doing is (as far as I know) a pretty standard way to work around how nulls aren't consistently sorted across different databases.

[jarednorman]

I'm going to close this, but we can reopen if there's any movement and we actually want to address it.