[BUG]: Issue when we use several columns on group by on query builder and paginator

[jmar1998]

Describe the bug This happens when we use several columsn on group by, and when phalcon is calculating the number of rows, reusing the query. Im getting an issue.

Steps to reproduce the behavior:

      $builder = new Builder();
      $builder
          ->addFrom(Clientes::class, "client")
          ->columns(["client.nome", "seller.nome"])
          ->leftJoin(Comerciais::class, "seller.id = client.vendedor", "seller")
          ->groupBy(["client.id", "seller.nome"]);
      $paginator = new QueryBuilder([
          "builder" => $builder,
          'limit' => 30
      ]);
    echo "<pre>" . print_r($paginator->paginate(), true) . "</pre>";die();

Screenshots As you can see on this part, phalcon is generating this query SELECT COUNT(DISTINCT "client"."id", "seller"."nome") -> this is not acceptable because postgres confuse the columns with parameters. The right query should be SELECT COUNT(DISTINCT("client"."id", "seller"."nome"))

Details

• Phalcon version: 3.4.5
• PHP Version: 7.3
• Database : Postgres
• Operating System: Windows
• Installation type: Dll
• Zephir version (if any):
• Server: Apache

[jmar1998]

I see the code of future versions of phalcon, and the code is already the same

[gycsabesz]

I'm experiencing the same issue with the latest Phalcon 5.2.1 version.

Contrary to MySQL's COUNT(DISTINCT expr, [expr...]), Postgres' COUNT function accepts only one expression argument: COUNT(expression) - see Postgres reference

Line #190 and #198 in the current QueryBuilder implementation results in generating invalid Postgres SQL query.

https://github.com/phalcon/cphalcon/blob/fc183e11e8b96c43daf7d893244846206dc2aa73/phalcon/Paginator/Adapter/QueryBuilder.zep#L188-L200

Possible solution, in case of Postgres DB, could be to modify line #198, put column names into parentheses so that COUNT will have only one expression parameter and DISTINCT aggregator function will receive the column names as parameters.

This does not work in Postgres:

SELECT COUNT(DISTINCT col1, col2)

This works in Postgres:

SELECT COUNT(DISTINCT(col1, col2))