"group by" response contains duplicate group columns

In addition to the duplicate group by columns we are seeing non deterministic responses (differently duplicated responses and different values on the aggregated column).

Here is an example of the kind of response we are seeing:
>>> select count(*), node_id from our_table where project_id=‘someid’ group by node_id;

+---------+-------------------+
| count   | node_id           |
+---------+-------------------+
| 2922677 | a-r-1   |
| 425287  | a-r-2   |
| 1064013 | a-r-2   |
| 1434542 | a-r-2   |
| 3829    | a-r-2b  |
| 375724  | a-r-3   |
| 751525  | a-r-3   |
| 1796977 | a-r-3   |
| 2924973 | ad-r-1 |
| 1127401 | ad-r-2 |
| 1793973 | ad-r-2 |
| 2925914 | p-r |
| 2033684 | r-r-1   |
| 890752  | r-r-1   |
| 720087  | r-r-2   |
| 2204704 | r-r-2   |
| 1346    | r-r-2b  |
+---------+-------------------+

Hi @lschneider-apixio,

That’s not good. Can you provide your schema, EXPLAIN and EXPLAIN(DISTSQL) output for the query in question?

Thank you,
Jordan

Yes, we can get the schema and explain results.

Also we’ve found a surprising workaround. If you add an “order by” on the group by columns, then the duplicated rows go away.

CRDB:26257/my_db> explain select node_id, count(*) from work_item_count_m where project_id = 'PID' group by node_id;                                                                                                           
+----------------+-------------+-------------------------------------------------------------------------------------------------------+
|      tree      |    field    |                                              description                                              |
+----------------+-------------+-------------------------------------------------------------------------------------------------------+
| group          |             |                                                                                                       |
|  │             | aggregate 0 | node_id                                                                                               |
|  │             | aggregate 1 | count_rows()                                                                                          |
|  │             | group by    | @1                                                                                                    |
|  │             | ordered     | @1                                                                                                    |
|  └── render    |             |                                                                                                       |
|       └── scan |             |                                                                                                       |
|                | table       | work_item_count_m@work_item_count_m_project_id_node_id_idx                                            |
|                | spans       | /"PID"-/"PID"/PrefixEnd |
+----------------+-------------+-------------------------------------------------------------------------------------------------------+
(9 rows)

Time: 42.4519ms

CRDB:26257/my_db> explain(distsql) select node_id, count(*) from work_item_count_m where project_id = 'PID' group by node_id;                                                                                                  
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| automatic |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    url                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|   true    | https://cockroachdb.github.io/distsqlplan/decode.html#eJzMlk1vm0wUhffvr4hmlUi8socPf7By1JWlFCrHVlVVFiJw5dDajDUMaqrI_70aWBjb7Vzsa8VsiDA8M4d7Tji8s1ykEMQbKJj_nY2ZxXhfH7g-2Prg6IPLlhbbSpFAUQip763JafrG_L7FsnxbKv3z0mKJkMD8d6YytQbms3n8soYZxCnInl4xBRVn62q_X0L-jDIFmygRZa6iTbSV4gckKsrSSK-v_2bp2-TkRmaxsFT-3cRjy53FRKn2AgoVr4D5fGe1F_m4WklYxUrI3uhQ40SPIpQpSEj9u-rsMfgWBeE8ChZPT_cT_sAs9ilcBPNoFn59vn84UrTf5OX33WtcvJ6sv9ztVdv_VL1fp8xFredgpWoVw3Nx95IHe158jqbB_H5iHz_XXrNzoJm3j0P_ZnFARDbGNuhQHPg142B_fBwu0hyI_8W2x0dHd_59b_dgb7t9FPnNooiIbFg27FAU7WtG0fmYKDrt4-DeLA6IyObY-h3Kg3PNPFz0zj0_D277PHg3ywMisjk23qE8uNfMw0UvvvPz4LXPg3OzPCAiG2MbdygO3jXj4HXiQ3YGxVbkBbT6LunrZ4J0BfUAClHKBL5IkVTf-PVpWHHVDykUqr7K65NpXl_SApswP4Z5E_YOYH4ePKbANgXmIxJN2xuZmW10a2CGHSPsmq12KVabYcRqM4yM2wxjViM0bW9kZh7F6oH5v3ps9npopEdmeEQJihlGgmKGEbPMMBYUhKbtjcxsTAkKR97_WAHQGoBWAbQOIJYAsQVoNcBJPcDNRcCRJuCkKkBozHNSGSA06jmtDjAcGxypEDjSCEPE85NKOMtzM415bqaxqZtp1HMEJ-6ODe6kGYyeL3f__QkAAP__x1tXXw== |
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
(1 row)

Time: 51.8223ms

Surprisingly, an EXPLAIN on the “working” query shows no difference:

CRDB:26257/my_db> explain select node_id, count(*) from work_item_count_m where project_id = 'PID' group by node_id order by node_id;
+----------------+-------------+-------------------------------------------------------------------------------------------------------+
|      tree      |    field    |                                              description                                              |
+----------------+-------------+-------------------------------------------------------------------------------------------------------+
| group          |             |                                                                                                       |
|  │             | aggregate 0 | node_id                                                                                               |
|  │             | aggregate 1 | count_rows()                                                                                          |
|  │             | group by    | @1                                                                                                    |
|  │             | ordered     | @1                                                                                                    |
|  └── render    |             |                                                                                                       |
|       └── scan |             |                                                                                                       |
|                | table       | work_item_count_m@work_item_count_m_project_id_node_id_idx                                            |
|                | spans       | /"PID"-/"PID"/PrefixEnd |
+----------------+-------------+-------------------------------------------------------------------------------------------------------+
(9 rows)

Time: 37.0151ms

CRDB:26257/my_db> explain(distsql) select node_id, count(*) from work_item_count_m where project_id = 'PID' group by node_id order by node_id;
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| automatic |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              url                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|   true    | https://cockroachdb.github.io/distsqlplan/decode.html#eJzMlk9v2jAYxu_7FJVPrZapcf6UkhPVTpW6ZKKgaZpQlCYWzQYYOUbrVPHdJ5MDgajvkxAEXFKl8HOe-PfIL-9sITMRJnNRsOAX6zOLcdtcuLk45uKai8cmFlsqmYqikMp8tyQfszcW2BbLF8uVNv-eWCyVSrDgnelczwQL2Ch5mYmhSDKhbs2KmdBJPts8769Uf-Jci3mcytVCx_N4qeRvkeo4z2KzvvmbZ2-D2heZxaKVDq4GPpusLSZXehug0MlUsICvreYhH6ZTJaaJlur2fjfjwGxFpDKhRBZcbe4ewp9xGI3icPz0dD3gN8xiX6NxOIqH0Y_n65u9RNuHvPy7ek2K19r6k_U2tfNh6u06skyzv87nciHi1bh3yLs9j7_Fj-HoeuDsv9o2trsTmzdvhH22RoCQlW27u6BG8CM3wjlNI5zmjeBnawQIWdm23gU1wjlyI9zTNMJt3gjvbI0AIavbZl9QJdwjV-KgA7B9JbzmlfDPVgkQsrpt_IIq4R25EgedgO0r4TevhHu2SoCQlW3rX1Aj_CM3wj99Iw6NHcovcnnLd3_pf_R4r43roSiWclGIRivbJpvIpqJ8l0KuVCq-K5lufviXt9GG2_wjE4UuP-XlzeOi_MgErMJ8H-ZV2N-BeTv4vgvsdIF5rxPNO9EeTTu0LbBpLkl7tGuvi2saBrFpGLimYeQa0MA1oIFrv5PrO5Lu0a57XVzTMIhNw8A1DSPXgAauAQ1c33dy3adpGxzitRHQ6hSnaXSM0zQ6x2kaHuQARyc5wIFyXpsErZxzMApcIL02C1pJp2kUnaaRdJqG0gGOpAMcSa-NhHbSwUy4A9JrQ6GVdJpG0WkaSadpKB3gSDrAkfTabGgnHQyH_p70yfrT_wAAAP__rf1YVA== |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
(1 row)

Time: 63.3246ms

And here’s the schema:

CRDB:26257/my_db> show create table work_item_count_m;
+-------------------+--------------------------------------------------------------------------+
|    table_name     |                             create_statement                             |
+-------------------+--------------------------------------------------------------------------+
| work_item_count_m | CREATE TABLE work_item_count_m (                                         |
|                   |                                                                          |
|                   |     phase STRING NOT NULL,                                               |
|                   |                                                                          |
|                   |     action STRING NOT NULL,                                              |
|                   |                                                                          |
|                   |     workitem_count INT4 NOT NULL,                                        |
|                   |                                                                          |
|                   |     when_measured TIMESTAMPTZ NOT NULL,                                  |
|                   |                                                                          |
|                   |     node_id STRING NOT NULL,                                             |
|                   |                                                                          |
|                   |     project_id STRING(63) NOT NULL,                                      |
|                   |                                                                          |
|                   |     source STRING NOT NULL,                                              |
|                   |                                                                          |
|                   |     id STRING NOT NULL,                                                  |
|                   |                                                                          |
|                   |     CONSTRAINT "primary" PRIMARY KEY (id ASC),                           |
|                   |                                                                          |
|                   |     INDEX node_id_idx (node_id ASC),                                     |
|                   |                                                                          |
|                   |     INDEX phase_idx (phase ASC),                                         |
|                   |                                                                          |
|                   |     INDEX when_measured_idx (when_measured ASC),                         |
|                   |                                                                          |
|                   |     INDEX project_id_idx (project_id ASC),                               |
|                   |                                                                          |
|                   |     INDEX id_idx (id ASC),                                               |
|                   |                                                                          |
|                   |     INDEX action_idx (action ASC),                                       |
|                   |                                                                          |
|                   |     INDEX work_item_count_m_project_id_node_id_idx (project_id ASC,      |
|                   | node_id ASC),                                                            |
|                   |                                                                          |
|                   |     INDEX work_item_count_m_project_id_node_id_idx1 (project_id ASC,     |
|                   | node_id ASC) STORING (workitem_count, when_measured),                    |
|                   |                                                                          |
|                   |     FAMILY "primary" (phase, action, workitem_count, when_measured,      |
|                   | node_id, project_id, source, id)                                         |
|                   |                                                                          |
|                   | )                                                                        |
+-------------------+--------------------------------------------------------------------------+
(1 row)

Time: 49.522ms

Hi @mbatema-apixio,

Thanks for the further detail. I think I see the problem - the planner is assuming ordered inputs to the aggregation stages when you don’t include the ORDER BY, but isn’t enforcing the order in the distributed plan.

What version of CockroachDB are you running? I believe this bug may have already been fixed, but I just want to make sure.

Jordan

@jordan I believe we are running v2.1.3 on all nodes

Hmm, ok, then my theory doesn’t hold up - the bugfix I’m thinking of is definitely in 2.1.3.

In the short term we have added order by to all our regular queries. I hope you guys can fix the bug soon because aggregations are one of our primary use cases.

@lschneider-apixio thanks for your patience, I’ve figured out how to reproduce the issue and filed an issue in GitHub. Hopefully we’ll have a fix available soon.

1 Like

We are glad to hear that!

We’ve got a candidate bugfix here: https://github.com/cockroachdb/cockroach/pull/35221

When it merges, we’ll backport to the 2.1 branch and it’ll make it into the next 2.1 patch release. Thanks very much for noticing and reporting this problem - we really appreciate it, as it’s of the utmost importance for us to avoid query incorrectness of this kind.