SqlServer compatibility

This is a Crumbs issue with Postgres / SQLServer, there's a patch at #1788214: Crumbs compatibility issue with PostgreSQL.

We never guaranteed SqlServer compatibility, so there might be significant work needed to make this work.
Reopening the issue so that we can track this.

Related: #1953878: PDO Exception with Password.

I've gotten everything to work fine on Windows Server 2012, SQL Server 2012. Except for this one error. I'm using Commerce Kickstart ver 2.12.

I've played with the sorts, facets and fields. Toggling the price field and clearing cache makes it work temporarily. But then the error eventually comes back. I'm not sure where to begin with this error. Any ideas? I am using MSFT SQL Server.

I get why the query doesn't work, but I'm not sure what I'm doing wrong. The only thing I've really changed is I added a new Product Type with new fields of its own. But I'm using the regular field for price just as the other commerce kickstart default products do. Not sure if this is important, but selecting the "All Products" link works fine. This only shows up when I search for a product by its name.

I want to reiterate, it works when I toggle the price field (include, exclude and reset cache). But then it stops working later again. Basically the price field is not included in the Group By clause.

PDOException: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Column 'search_api_db_product_display.field_product_commerce_price_amount_decimal_asc' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.: SELECT TOP(1) t.[item_id] AS [item_id], SUM(t.score) AS score, t_2.field_product_commerce_price_amount_decimal_asc AS _field_0, t.item_id AS _field_1 FROM (SELECT t.* FROM {search_api_db_product_display_search_api_aggregation_1} t WHERE ( ([word] = :db_condition_placeholder_0) ) UNION ALL SELECT t.* FROM {search_api_db_product_display_search_api_aggregation_3} t WHERE ( ([word] = :db_condition_placeholder_1) ) UNION ALL SELECT t.* FROM {search_api_db_product_display_search_api_aggregation_4} t WHERE ( ([word] = :db_condition_placeholder_2) ) UNION ALL SELECT t.* FROM {search_api_db_product_display_search_api_aggregation_6} t WHERE ( ([word] = :db_condition_placeholder_3) ) UNION ALL SELECT t.* FROM {search_api_db_product_display_search_api_aggregation_7} t WHERE ( ([word] = :db_condition_placeholder_4) )) t LEFT OUTER JOIN {search_api_db_product_display} t_2 ON t.item_id = t_2.item_id WHERE ( ( ([t_2].[field_product_commerce_price_amount_decimal_asc] IS NOT NULL ) ) ) GROUP BY t.item_id ORDER BY t_2.field_product_commerce_price_amount_decimal_asc ASC; Array ( [:db_condition_placeholder_0] => crimp [:db_condition_placeholder_1] => crimp [:db_condition_placeholder_2] => crimp [:db_condition_placeholder_3] => crimp [:db_condition_placeholder_4] => crimp ) in SearchApiDbService->search() (line 979 of C:\inetpub\wwwroot\profiles\commerce_kickstart\modules\contrib\search_api_db\service.inc)

Could you please try with CK 2.13 to see if you still got this problem?

More information #2265761: PDOException: SQLSTATE[22018]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Conversion failed when converting the nvarchar value 'types' to data type int.

Based on information in #2265761: PDOException: SQLSTATE[22018]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Conversion failed when converting the nvarchar value 'types' to data type int. this was a core bug and is fixed.