Publish to a scan table to update one record should not overwrite the record of another key.

[zshwei]

Environment:

• eKuiper version (e.g. 1.3.0): 1.11.1 and 1.8.1
• Hardware configuration (e.g. lscpu): arm64 and x86_64
• OS (e.g. cat /etc/os-release): Linux
• Others:

What happened and what you expected to happen:

Publish to a scan table to update one record should not overwrite the record of another key.

How to reproduce it (as minimally and precisely as possible):

1. Create a stream for inputs.
   curl --location 'http://localhost:9081/streams'
   --header 'Content-Type: application/json'
   --data '{
   "sql":"create stream welders () WITH ( datasource = "physical_welders", FORMAT = "json")"
   }'

2. Create a scan table to hold the average for the welder average current.
   curl --location 'http://localhost:9081/tables'
   --header 'Content-Type: application/json'
   --data '{
   "sql": "CREATE TABLE scan_tbl_avg_current (deviceId string, avg_current float) WITH (DATASOURCE="avg_current", FORMAT="JSON", TYPE="mqtt", KEY="deviceId");"
   }'

3. Create a rule to join the current input and the the scan table.
   curl --location 'http://localhost:9081/rules'
   --header 'Content-Type: application/json'
   --data '{
   "id": "with_average",
   "sql": "SELECT *, scan_tbl_avg_current.avg_current as avg_current FROM welders LEFT JOIN scan_tbl_avg_current on welders.deviceId = scan_tbl_avg_current.deviceId",
   "actions": [
   {
   "mqtt": {
   "server": "tcp://localhost:1883",
   "topic": "with_average",
   "sendSingle": true
   }
   }
   ]
   }'

4. Update one average for deviceId:welder01.
   mosquitto_pub -h localhost -t avg_current -m "{"deviceId":"welder01", "avg_current":7}"

5. Input one record with welder01 and you will see the output, which is expected.
   mosquitto_pub -h localhost -t physical_welders -m "{"deviceId":"welder01","properties":{"current":2}}"

Output: {"avg_current":7,"deviceId":"welder01","properties":{"current":2}}

7. Update another average for deviceId:welder02
   mosquitto_pub -h localhost -t avg_current -m "{"deviceId":"welder02", "avg_current":11}"

8. Input another record with welder01 and the expected result should be the same as step 5, but it did not output the average.
   mosquitto_pub -h localhost -t physical_welders -m "{"deviceId":"welder01","properties":{"current":2}}"

Output: {"deviceId":"welder01","properties":{"current":2}}

Anything else we need to know?:

[ngjaying]

@zshwei The scan table must set RETAIN_SIZE to retain multiple rows. Like below:

CREATE TABLE scan_tbl_avg_current (deviceId string, avg_current float) WITH (DATASOURCE="avg_current", FORMAT="JSON", TYPE="mqtt", KEY="deviceId", RETAIN_SIZE=100)

[zshwei]

Thanks. The RETAIN_SIZE does keep more records, however, there is a new problem. Seems the keyword "KEY" didn't take effect. See the picture as follows, anything else I was missing?

[ngjaying]

@zshwei Currently, KEY is ignored in Scan table. It only affects lookup table.