There is a discrepancy between the statistical results of queries on the distributed system and the actual data.

[guoxueliang-coder]

I’m using Manticore Search 4.2.0，I also tried the latest version but there were issues，Please refer to the attached document for details.May I ask if there is any solution,Thank you！
question.docx

[sanikolaev]

Thanks for asking, @guoxueliang-coder.

Would it be possible to share your actual data so we can reproduce and debug the issue locally? You can upload that to our write-only S3 storage - https://manual.manticoresearch.com/Reporting_bugs#Uploading-your-data

Even better, could you provide a reproducible example based on https://github.com/manticoresoftware/manticore-load?

[guoxueliang-coder]

Due to some reasons, I am unable to upload the original data, but I can reconstruct the test data to reproduce it. Just a few simple steps are needed：
1、Create a table in a child node：
CREATE TABLE test_table(id bigint,name text);
2、Create a table on the master node：
CREATE TABLE test_table(id bigint,name text);
CREATE TABLE test_table_dist type='distributed' local='test_table' agent='10.50.108.20:9313:test_table';
3、Inserting data into child nodes
INSERT INTO test_table (id, name) VALUES (1, 'test1');
INSERT INTO test_table (id, name) VALUES (2, 'test2');
INSERT INTO test_table (id, name) VALUES (3, 'test3');
4、Insert data into the master node
INSERT INTO test_table (id, name) VALUES (1, 'test1');
INSERT INTO test_table (id, name) VALUES (3, 'test3');

The data is ready and can be queried and reproduced：

You can see that the total number of queried data is 3

      MySQL [(none)]> SELECT * from test_table_dist;
      +------+-------+
      | id   | name  |
      +------+-------+
      |    1 | test1 |
      |    3 | test3 |
      |    2 | test2 |
      +------+-------+
      3 rows in set (0.002 sec)

But the count shows 4：
MySQL [(none)]> SELECT count (distinct id) from test_table_dist;
+---------------------+
| count (distinct id) |
+---------------------+
| 4 |
+---------------------+
1 row in set (0.015 sec)

Could you please help me take a look at this issue? Thank you very much

[sanikolaev]

The MRE based on #3807 (comment) is:

mysql> drop table if exists t_agent; CREATE TABLE t_agent;drop table if exists t;CREATE TABLE t;drop table if exists d;CREATE TABLE d type='distributed' local='t' agent='127.0.0.1:9312:t_agent';INSERT INTO t_agent VALUES (1),(2);INSERT INTO t VALUES (2);SELECT * from d;SELECT count (distinct id) from d;
--------------
drop table if exists t_agent
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
CREATE TABLE t_agent
--------------

Query OK, 0 rows affected (0.01 sec)

--------------
drop table if exists t
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
CREATE TABLE t
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
drop table if exists d
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
CREATE TABLE d type='distributed' local='t' agent='127.0.0.1:9312:t_agent'
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
INSERT INTO t_agent VALUES (1),(2)
--------------

Query OK, 2 rows affected (0.00 sec)

--------------
INSERT INTO t VALUES (2)
--------------

Query OK, 1 row affected (0.00 sec)

--------------
SELECT * from d
--------------

+------+
| id   |
+------+
|    2 |
|    1 |
+------+
2 rows in set (0.00 sec)
--- 2 out of 2 results in 0ms ---

--------------
SELECT count (distinct id) from d
--------------

+---------------------+
| count (distinct id) |
+---------------------+
|                   3 |
+---------------------+
1 row in set (0.00 sec)
--- 1 out of 1 results in 0ms ---

so instead of the expected value of 2 for count(distinct id) 3 was returned.

Unfortunately, you have to find another way to calculate count distinct if you need to do it against a table which includes a remote table. There's the related issue #1131

[guoxueliang-coder]

Thank you very much！by the way，is there any plans to solve this problem in the future.

[sanikolaev]

It’s not in our immediate plans, but if we see that many users are affected by this issue, we'll make it a higher priority.

[guoxueliang-coder]

ok, Thank you very much！