0

I use PHP 7.1 with Pgsql 9.2.24. Have two similar copy of project (production and development) and two similar databases with the same data on it. But queries work differently in GROUP BY case.

I tried to check all the sql settings with SHOW ALL query, but found no difference. I know, that rewriting query can solve my problem, but there is too many code with GROUP BY operator. Full SQL version: PostgreSQL 9.2.24 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit. Trying run sql-query:

SELECT chr.*, usr.image, usr.first_name, usr.second_name, usr.last_activity, CASE WHEN MAX(chrm.created_at) IS NOT NUll THEN MAX(chrm.created_at) ELSE 0 END AS created_m, MAX(chrm.id) AS id_m FROM chat_rooms as chr LEFT JOIN users usr ON chr.author_id = usr.id LEFT JOIN chat_messages chrm ON chr.id = chrm.chat_id LEFT JOIN chat_rooms_user chru ON chr.id = chru.chat_rooms_id WHERE chru.user_id = 1104 AND chru.status = 1 AND ( chrm.status != 10 OR chrm.status IS NULL) AND chr.status = 1 GROUP BY chr.id, usr.id HAVING coalesce(COUNT(chrm.id),0) > 0 ORDER BY created_m DESC LIMIT 10 OFFSET 0

And get error (server status 500): Uncaught Exception: ERROR: column "chr.parent_id" must appear in the GROUP BY clause or be used in an aggregate function

Other database returns me not errors but correct result with the data.

Valik Tralik
  • 69
  • 1
  • 12
  • I don't think that error message matches the query? Unless `cr.*` was meant to be `chr.*`? – Jonnix Sep 02 '19 at 11:16
  • Sorry, I have changed query for sequrity reason, now I brought back correct example in description – Valik Tralik Sep 02 '19 at 11:28
  • Does one have a unique constraint on chr.id and one not? – jjanes Sep 02 '19 at 18:47
  • Yes. Actually that was the problem. Some pkeys didn`t been transferred because of cascade dependencies. And I didn`t noticed it, so the schema structure wasn`t right the same. – Valik Tralik Sep 04 '19 at 15:30

0 Answers0