select count(1) from <table> does not return 1

[timsehn]

With no tables works as expected:

mysql> select sum(1);
+--------+
| sum(1) |
+--------+
| 1      |
+--------+
1 row in set (0.00 sec)

mysql> select count(1);
+----------+
| count(1) |
+----------+
| 1        |
+----------+
1 row in set (0.00 sec)

mysql>

Adding a table confuses the analyzer:

mysql> select count(1) from queries;
+----------+
| count(1) |
+----------+
| 0        |
+----------+
1 row in set (0.00 sec)

mysql> select sum(1) from queries;
+--------+
| sum(1) |
+--------+
| NULL   |
+--------+
1 row in set (0.00 sec)
mysql> explain select sum(1) from queries;
+---------------------------+
| plan                      |
+---------------------------+
| GroupBy                   |
|  ├─ SelectedExprs(SUM(1)) |
|  ├─ Grouping()            |
|  └─ Table                 |
|      ├─ name: queries     |
|      └─ columns: []       |
+---------------------------+
6 rows in set (0.00 sec)

mysql> explain select count(1) from queries;
+-----------------------------+
| plan                        |
+-----------------------------+
| GroupBy                     |
|  ├─ SelectedExprs(COUNT(1)) |
|  ├─ Grouping()              |
|  └─ Table                   |
|      ├─ name: queries       |
|      └─ columns: []         |
+-----------------------------+
6 rows in set (0.00 sec)

[jycor]

I think this is actually expected behavior

MySQL:

mysql> select count(1);
+----------+
| count(1) |
+----------+
|        1 |
+----------+
1 row in set (0.0003 sec)
mysql> select sum(1);
+--------+
| sum(1) |
+--------+
|      1 |
+--------+
1 row in set (0.0003 sec)
mysql> select count(1) from dual;
+----------+
| count(1) |
+----------+
|        1 |
+----------+
1 row in set (0.0003 sec)
mysql> select sum(1) from dual;
+--------+
| sum(1) |
+--------+
|      1 |
+--------+
1 row in set (0.0003 sec)
mysql> create table t (i int primary key);
Query OK, 0 rows affected (0.0114 sec)
mysql> select count(1) from t;
+----------+
| count(1) |
+----------+
|        0 |
+----------+
1 row in set (0.0019 sec)
mysql> select sum(1) from t;
+--------+
| sum(1) |
+--------+
|   NULL |
+--------+
1 row in set (0.0004 sec)
mysql> insert into t values (1);
Query OK, 1 row affected (0.0016 sec)
mysql> select count(1) from t;
+----------+
| count(1) |
+----------+
|        1 |
+----------+
1 row in set (0.0009 sec)
mysql> select sum(1) from t;
+--------+
| sum(1) |
+--------+
|      1 |
+--------+
1 row in set (0.0004 sec)
mysql> insert into t values (2);
Query OK, 1 row affected (0.0045 sec)
mysql> select count(1) from t;
+----------+
| count(1) |
+----------+
|        2 |
+----------+
1 row in set (0.0010 sec)
mysql> select sum(1) from t;
+--------+
| sum(1) |
+--------+
|      2 |
+--------+
1 row in set (0.0004 sec)

Dolt:

tmp> select count(1);
+----------+
| count(1) |
+----------+
| 1        |
+----------+
1 row in set (0.00 sec)

tmp> select sum(1);
+--------+
| sum(1) |
+--------+
| 1      |
+--------+
1 row in set (0.00 sec)

tmp> select count(1) from dual;
+----------+
| count(1) |
+----------+
| 1        |
+----------+
1 row in set (0.00 sec)

tmp> select sum(1) from dual;
+--------+
| sum(1) |
+--------+
| 1      |
+--------+
1 row in set (0.00 sec)

tmp> create table t (i int primary key);
tmp> select sum(1) from t;
+--------+
| sum(1) |
+--------+
| NULL   |
+--------+
1 row in set (0.00 sec)

tmp> select count(1) from t;
+----------+
| count(1) |
+----------+
| 0        |
+----------+
1 row in set (0.00 sec)

tmp> insert into t values (1);
Query OK, 1 row affected (0.00 sec)
tmp> select count(1) from t;
+----------+
| count(1) |
+----------+
| 1        |
+----------+
1 row in set (0.00 sec)

tmp> select sum(1) from t;
+--------+
| sum(1) |
+--------+
| 1      |
+--------+
1 row in set (0.00 sec)

tmp> insert into t values (2);
Query OK, 1 row affected (0.00 sec)
tmp> select count(1) from t;
+----------+
| count(1) |
+----------+
| 2        |
+----------+
1 row in set (0.00 sec)

tmp> select sum(1) from t;
+--------+
| sum(1) |
+--------+
| 2      |
+--------+
1 row in set (0.00 sec)

tmp> exit

I could add some enginetests for specifically count, sum, and other window functions over dual?
There are some tests that use this here: /~https://github.com/dolthub/go-mysql-server/blob/main/enginetest/queries/queries.go#L2067

[jycor]

Update: I was able to find a repro, this happens in dolt sql-client, but not in the shell.
Likely that it's some bad conversion we're doing over the wire, working on a fix now.

[jycor]

Fix for this is in dolt main. Will be in next release.