Ensure all integration tests to pass on PostgreSQL

[0pdd]

The puzzle 1054-7feb00d4 from #1054 has to be resolved:

mystamps/.travis.yml

Line 13 in d4b8073

# @todo #1054 Ensure all integration tests to pass on PostgreSQL

The puzzle was created by Slava Semushin on 19-Oct-19.

[php-coder]

Failure 1: when user adds a series to the collection.

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [UPDATE collections c SET c.updated_at = ? , c.updated_by = ? WHERE c.user_id = ?]; nested exception is org.postgresql.util.PSQLException: ERROR: column "c" of relation "collections" does not exist

[php-coder]

Failure 1: when user adds a series to the collection.

It worked after removing table alias (c) from query.

---

Failure 2: when user adds a series to the collection.

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT CASE WHEN 'ru' = ? THEN COALESCE(c.name_ru, c.name) ELSE c.name END AS name , SUM(s.quantity) AS counter FROM collections_series cs JOIN series s ON s.id = cs.series_id JOIN categories c ON c.id = s.category_id WHERE cs.collection_id = ? GROUP BY s.category_id]; nested exception is org.postgresql.util.PSQLException: ERROR: column "c.name_ru" must appear in the GROUP BY clause or be used in an aggregate function

[php-coder]

ERROR: column "c.name_ru" must appear in the GROUP BY clause or be used in an aggregate function

Related:

• https://stackoverflow.com/questions/19601948/must-appear-in-the-group-by-clause-or-be-used-in-an-aggregate-function
• https://dba.stackexchange.com/questions/88988/postgres-error-column-must-appear-in-the-group-by-clause-or-be-used-in-an-aggre

[php-coder]

Failure 3: when admin adds additional image:

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [UPDATE series s SET s.updated_at = ? , s.updated_by = ? WHERE s.id = ?]; nested exception is org.postgresql.util.PSQLException: ERROR: column "s" of relation "series" does not exist

[php-coder]

Failure 4: when user creates a series with Michel catalog numbers:

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [INSERT INTO michel_catalog(code) SELECT ? AS code FROM dual WHERE NOT EXISTS( SELECT * FROM michel_catalog WHERE code = ? )]; nested exception is org.postgresql.util.PSQLException: ERROR: relation "dual" does not exist

[php-coder]

Failure 5: when user adds a series to the collection

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT COALESCE(CASE WHEN 'ru' = ? THEN COALESCE(c.name_ru, c.name) ELSE c.name END, 'Unknown') AS name , SUM(s.quantity) AS counter FROM collections_series cs JOIN series s ON s.id = cs.series_id LEFT JOIN countries c ON c.id = s.country_id WHERE cs.collection_id = ? GROUP BY s.country_id]; nested exception is org.postgresql.util.PSQLException: ERROR: column "c.name_ru" must appear in the GROUP BY clause or be used in an aggregate function

[php-coder]

Failure 2: when user adds a series to the collection.
Failure 5: when user adds a series to the collection.

Both these cases were fixed by using id from a primary table. For example, instead of s.country_id we used c.id and likewise for categories table.

Thanks @asm0dey for the hint!

[php-coder]

Failure 4: when user creates a series with Michel catalog numbers:

It seems like removing FROM dual helps. But I'm surprised that it works on MySQL given that I added that clause exactly for this database in order to fix incorrect syntax (see b2eb7d0).

[php-coder]

/spent 52m