Pending Update #7009 Failed on MSSQL/PostgreSQL

Created on 3 September 2019, almost 5 years ago
Updated 31 May 2023, about 1 year ago

Running the update script and attempting to implement the Pending Update #7009 produced the following error message:

---------------------------
The following updates returned messages
field_collection module
Update #7009

Failed: PDOException: SQLSTATE[42S22]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid column name 'vidcount'.: SELECT TOP(10) t.[field_recipe_ingredients_revision_id] AS [field_recipe_ingredients_revision_id], COUNT(t.field_recipe_ingredients_revision_id) AS vidcount, 1 as __tempsort FROM field_revision_field_recipe_ingredients t GROUP BY t.field_recipe_ingredients_revision_id HAVING ([vidcount] >= :db_condition_placeholder_0) ORDER BY __tempsort ; Array ( [:db_condition_placeholder_0] => 2 ) in field_collection_update_7009() (line 424 of ...\sites\all\modules\field_collection\field_collection.install).

Microsoft SQL server statements don't allow column alias' to be used in the HAVING clause (http://www.sqlservertutorial.net/sql-server-basics/sql-server-having/).

Replacing [vidcount] in the HAVING clause with COUNT(t.field_recipe_ingredients_revision_id) seems to fix the SQL statement, but this does not appear to be that simple to fix in the code. LINE 424 in field_collection.install has $query->havingCondition which accepts a field and not an aggregate function. Replacing field 'vidcount' with aggregate function "COUNT(t.{$sandbox['field_name']}_revision_id)", only creates another error as the function havingCondition tries to parse the field name and put in appropriate brackets surrounding the field name. Resulting in the following error:

Failed: PDOException: SQLSTATE[42000]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]The multi-part identifier "COUNTt.field_recipe_ingredients_revision_id" could not be bound.: SELECT TOP(10) t.[field_recipe_ingredients_revision_id] AS [field_recipe_ingredients_revision_id], COUNT(t.field_recipe_ingredients_revision_id) AS vidcount, 1 as __tempsort FROM field_revision_field_recipe_ingredients t GROUP BY t.field_recipe_ingredients_revision_id HAVING ([COUNTt].[field_recipe_ingredients_revision_id] >= :db_condition_placeholder_0) ORDER BY __tempsort ; Array ( [:db_condition_placeholder_0] => 2 ) in field_collection_update_7009() (line 424 of ...\sites\all\modules\field_collection\field_collection.install).

Need to be able to place an aggregate function, Count(), in the HAVING CLAUSE.

πŸ› Bug report
Status

Fixed

Version

1.0

Component

Code

Created by

πŸ‡ΊπŸ‡ΈUnited States rdellis87

Live updates comments and jobs are added and updated live.
Sign in to follow issues

Comments & Activities

Not all content is available!

It's likely this issue predates Contrib.social: some issue and comment data are missing.

Production build 0.69.0 2024