If data is being written, execute group by to obtain duplicate data

[imgoby]

Bug Description:

（1）columnar table:
mysql> desc test_table;
+-----------+-----------+---------------------+
| Field | Type | Properties |
+-----------+-----------+---------------------+
| id | bigint | columnar fast_fetch |
| biz_code | string | columnar fast_fetch |
| user_id | string | columnar fast_fetch |
| charge | uint | columnar fast_fetch |

(2)See duplicate data：

mysql> SELECT biz_code,user_id from test_table group by biz_code,user_id;
+--------------------------+--------+
| biz_code | user_id |
+--------------------------+--------+
| test | 200002 |
| test | 200002 |

(3)How to resolve?
My current solution is to statistics data from a few minutes ago.

Manticore Search Version:

9.3.2

Operating System Version:

Docker

Have you tried the latest development version?

None

Internal Checklist:

^{To be completed by the assignee. Check off tasks that have been completed or are not applicable.}

• Implementation completed
• Tests developed
• Documentation updated
• Documentation reviewed

[sanikolaev]

@imgoby Thanks for opening the issue. Can you tell us how to reproduce it? When you say "data is being written," do you mean the issue happens exactly when a write command is being executed on the table?

[sanikolaev]

Nevermind. I can reproduce it so:

manticore-load \
--drop \
--quiet \
--batch-size=10 \
--threads=5 \
--total=1000000 \
--init="CREATE TABLE test_table(id bigint, biz_code string, user_id string, charge int) engine='columnar'" \
--load="INSERT INTO test_table(id,biz_code,user_id) VALUES(<increment>,'test','<int/200002/200003>')"


Threads ; Batch     ; Time        ; Total Docs  ; Docs/Sec    ; Avg QPS     ; p99 QPS     ; p95 QPS     ; p5 QPS      ; p1 QPS      ; Lat Avg     ; Lat p50     ; Lat p95     ; Lat p99     ;
5       ; 10        ; 00:06       ; 1000000     ; 152899      ; 15239       ; 15617       ; 15617       ; 14925       ; 14925       ; 0.3         ; 0.5         ; 0.5         ; 6.5         ;

While the above is running:

 ~  mysql -P9306 -h0 -e "SELECT biz_code,user_id from test_table group by biz_code,user_id"
+----------+---------+
| biz_code | user_id |
+----------+---------+
| test     | 200003  |
| test     | 200002  |
+----------+---------+
 ~  mysql -P9306 -h0 -e "SELECT biz_code,user_id from test_table group by biz_code,user_id"
+----------+---------+
| biz_code | user_id |
+----------+---------+
| test     | 200002  |
| test     | 200003  |
| test     | 200002  |
| test     | 200003  |
+----------+---------+
 ~  mysql -P9306 -h0 -e "SELECT biz_code,user_id from test_table group by biz_code,user_id"
+----------+---------+
| biz_code | user_id |
+----------+---------+
| test     | 200003  |
| test     | 200002  |
| test     | 200002  |
| test     | 200003  |
+----------+---------+
 ~  mysql -P9306 -h0 -e "SELECT biz_code,user_id from test_table group by biz_code,user_id"
+----------+---------+
| biz_code | user_id |
+----------+---------+
| test     | 200002  |
| test     | 200003  |
+----------+---------+

[sanikolaev]

It seems the issue isn't with data being written to the table, but rather with the query order or some kind of caching. Even when no data has been written for a long time, the first few queries can still fail.

 ✘  ~/manticore_test   master  manticore-load \
--drop \
--batch-size=10 \
--threads=5 \
--total=1000000 \
--init="CREATE TABLE test_table(id bigint, biz_code string, user_id string) engine='columnar'" \
--load="INSERT INTO test_table(id,biz_code,user_id) VALUES(<increment>,'test','<int/200002/200003>')"

======================================================================================
Process 1: Running with threads=5, batch-size=10
Process 1: Dropping table: DROP TABLE IF EXISTS test_table
Process 1: Executing init command: CREATE TABLE test_table(id bigint, biz_code string, user_id string) engine='columnar'
Process 1: Using cached data from: /tmp/manticore_load_753ba138ad34c78109ac69897ecd104f
-------------------------------------------------------------------------------------------------------------
Time      Elapsed   Progress    QPS      DPS      CPU     Workers    Chunks    Merging   Disk        Inserted
-------------------------------------------------------------------------------------------------------------
08:22:20  1ms       -           -        -        N/A     -          0                   0B          0
08:22:21  1.0s      15%         15493    154.9K   N/A     3          0                   626B        155.3K
08:22:22  2.0s      30%         14401    149.4K   N/A     1          0                   626B        299.4K
08:22:23  3.0s      44%         14367    147.5K   N/A     2          0                   626B        443.3K
08:22:24  4.0s      59%         14376    146.6K   N/A     4          0                   626B        587.2K
08:22:25  5.0s      74%         15662    148.6K   N/A     2          0                   626B        744.6K
08:22:26  6.0s      89%         14749    148.4K   N/A     2          1                   15MB        892.3K
--------------------------------------------------------------------------------------
Process 1 final statistics:
Init command:     CREATE TABLE test_table(id bigint, biz_code string, user_id string) engine='columnar'
Load command:     INSERT INTO test_table(id,biz_code,user_id) VALUES(<increment>,'test','<int/200002/200003>')
Total time:       6.7s
Total queries:    100000
Threads:          5
Batch size:       10
Total docs:       1000000
Docs per sec avg: 148367
QPS avg:          14841
QPS 1p:           14367
QPS 5p:           14367
QPS 95p:          15662
QPS 99p:          15662
Latency avg:      0.3 ms
Latency 50p:      0.5 ms
Latency 95p:      0.5 ms
Latency 99p:      7.5 ms
--------------------------------------------------------------------------------------
 ~/manticore_test   master  sleep 120; for n in `seq 1 10`; do mysql -v -P9306 -h0 -e "SELECT biz_code,user_id from test_table group by biz_code,user_id"; done;

--------------
SELECT biz_code,user_id from test_table group by biz_code,user_id
--------------

+----------+---------+
| biz_code | user_id |
+----------+---------+
| test     | 200003  |
| test     | 200002  |
| test     | 200002  |
| test     | 200003  |
+----------+---------+
--------------
SELECT biz_code,user_id from test_table group by biz_code,user_id
--------------

+----------+---------+
| biz_code | user_id |
+----------+---------+
| test     | 200003  |
| test     | 200002  |
| test     | 200002  |
| test     | 200003  |
+----------+---------+
--------------
SELECT biz_code,user_id from test_table group by biz_code,user_id
--------------

+----------+---------+
| biz_code | user_id |
+----------+---------+
| test     | 200003  |
| test     | 200002  |
| test     | 200002  |
| test     | 200003  |
+----------+---------+
--------------
SELECT biz_code,user_id from test_table group by biz_code,user_id
--------------

+----------+---------+
| biz_code | user_id |
+----------+---------+
| test     | 200003  |
| test     | 200002  |
| test     | 200002  |
| test     | 200003  |
+----------+---------+
--------------
SELECT biz_code,user_id from test_table group by biz_code,user_id
--------------

+----------+---------+
| biz_code | user_id |
+----------+---------+
| test     | 200003  |
| test     | 200002  |
| test     | 200002  |
| test     | 200003  |
+----------+---------+
--------------
SELECT biz_code,user_id from test_table group by biz_code,user_id
--------------

+----------+---------+
| biz_code | user_id |
+----------+---------+
| test     | 200003  |
| test     | 200002  |
| test     | 200002  |
| test     | 200003  |
+----------+---------+
--------------
SELECT biz_code,user_id from test_table group by biz_code,user_id
--------------

+----------+---------+
| biz_code | user_id |
+----------+---------+
| test     | 200002  |
| test     | 200003  |
+----------+---------+
--------------
SELECT biz_code,user_id from test_table group by biz_code,user_id
--------------

+----------+---------+
| biz_code | user_id |
+----------+---------+
| test     | 200002  |
| test     | 200003  |
+----------+---------+
--------------
SELECT biz_code,user_id from test_table group by biz_code,user_id
--------------

+----------+---------+
| biz_code | user_id |
+----------+---------+
| test     | 200002  |
| test     | 200003  |
+----------+---------+
--------------
SELECT biz_code,user_id from test_table group by biz_code,user_id
--------------

+----------+---------+
| biz_code | user_id |
+----------+---------+
| test     | 200002  |
| test     | 200003  |
+----------+---------+