CREATE SINK INTO TABLE not working selecting from SOURCE

[mbombonato]

Describe the bug

I started a conversation on Slack a few weeks ago and as no solution was found, I'm opening a issue here.

Here is the Slack conversation with all my tests and samples:
https://risingwave-community.slack.com/archives/C03BW71523T/p1722967449567879

I'm trying to read data from a kafka topic and persist on a table using "create sink into table". Unfortunately, it's not working.

Error message/log

No errors. Just an empty table.

To Reproduce

1- Create a Source from Kafka;
2- Create a table to persist;
3- Create the sink into table;
Optional: Create a materialized view to make sure this options is working.
4- Generate a kafka event
5- Query the source, table and the mv to see the difference

dev=> CREATE SOURCE kafkasource (
    id int,
    name string,
)
WITH (
    connector = 'kafka',
    topic = 'topic-test',
    properties.bootstrap.server = 'localhost:9092',
    scan.startup.mode = 'earliest'
) FORMAT PLAIN ENCODE JSON;
CREATE_SOURCE


dev=> CREATE TABLE compact_table (
  id int,
  name varchar,
  PRIMARY KEY (id)
)
ON CONFLICT OVERWRITE;
CREATE_TABLE

dev=> CREATE MATERIALIZED VIEW mv_sink AS SELECT * FROM kafkasource;
CREATE_MATERIALIZED_VIEW

dev=> CREATE SINK table_sink INTO compact_table AS SELECT * FROM kafkasource;
CREATE_SINK

// Generate a kafka event
/*
{ "id": 1, "name": "My Name" }
*/


dev=> select * from kafkasource;
 id |  name   
----+---------
  1 | My Name
(1 row)

dev=> select * from compact_table;
 id | name 
----+------
(0 rows)

dev=> select * from mv_sink;
 id |  name   
----+---------
  1 | My Name
(1 row)

Expected behavior

dev=> select * from compact_table;
 id |  name   
----+---------
  1 | My Name
(1 row)

How did you deploy RisingWave?

Tested using docker and also the standalone

The version of RisingWave

PostgreSQL 13.14.0-RisingWave-1.10.1 (5920565)

Additional context

As a workaround I'm using a intermediate materialized view to 'send' data to the table. But I feel this is not the best path we should follow. I need to use the option "sink to table" because I'm joining to topics into the same table. As we have in this example:
https://docs.risingwave.com/docs/current/sql-create-sink-into/#examples

So... Source > Materialized View > Sink > Table is working. But the best path would be Source > Sink > Table.
As I have several similar scenarios, the amount of unnecessary MVs is high.

[BugenZhao]

Thank you for your feedback. We will look into this issue as it is reproducible.