`count(*)` query returns differently from sqlite3 client

[sahidrahman404]

I use Ent as an ORM and I get this error message "sql/scan: missing struct field for column: COUNT (*) (COUNT )" if I do query like below

SELECT `t1`.`exercise_id`, 
    COUNT(*) FROM `muscles_groups` 
    JOIN `exercise_muscles_groups` AS `t1` ON `muscles_groups`.`id` = `t1`.`muscles_group_id` 
    WHERE `t1`.`exercise_id` IN ('EX2V6kpls9rDVg0602O2q3ZCoXmZB') GROUP BY `t1`.`exercise_id`;

I didn't get that error message if I use sqlite3 client

[sahidrahman404]

You can check the reproducer to the problem at this link https://github.com/sahidrahman404/ent-libsql-repro. The reproduction steps is in the readme

[haaawk]

I checked that the query works when run directly through a turso db shell that uses libsql-client-go as well. This is probably an issue on ent/libsql-client-go surface.

[haaawk]

It seems that the issue is that libsql returns the following column names for this query: exercise_id, COUNT (*) while sqlite returns: exercise_id and COUNT(*). Libsql adds a space between COUNT and (*)

[haaawk]

The error comes from https://github.com/ent/ent/blob/master/dialect/sql/scan.go#L225

[haaawk]

Here's how to get the reproducer to work with both sqlite and libsql:

diff --git a/ent/gql_collection.go b/ent/gql_collection.go
index e9df23f..b077017 100644
--- a/ent/gql_collection.go
+++ b/ent/gql_collection.go
@@ -72,7 +72,7 @@ func (e *ExerciseQuery) collectField(ctx context.Context, opCtx *graphql.Operati
                                                        joinT := sql.Table(exercise.MusclesGroupsTable)
                                                        s.Join(joinT).On(s.C(musclesgroup.FieldID), joinT.C(exercise.MusclesGroupsPrimaryKey[1]))
                                                        s.Where(sql.InValues(joinT.C(exercise.MusclesGroupsPrimaryKey[0]), ids...))
-                                                       s.Select(joinT.C(exercise.MusclesGroupsPrimaryKey[0]), sql.Count("*"))
+                                                       s.Select(joinT.C(exercise.MusclesGroupsPrimaryKey[0]), sql.As(sql.Count("*"), "count"))
                                                        s.GroupBy(joinT.C(exercise.MusclesGroupsPrimaryKey[0]))
                                                })
                                                if err := query.Select().Scan(ctx, &v); err != nil {

[haaawk]

This is not a driver issue but a SQLD problem so I'm closing this issue in favour of https://github.com/libsql/sqld/issues/666.

There's a work around to just name COUNT(*) selection AS count