INSERT VALUES fails if key field is not STRING

[big-andy-coates]

CREATE TABLE movies (id INT, title VARCHAR, release_year INT) WITH (kafka_topic='movies', key='id', partitions=1, value_format='avro');

CREATE STREAM ratings (id INT, rating DOUBLE) WITH (kafka_topic='ratings', partitions=1, value_format='avro');

INSERT INTO movies (id, title, release_year) VALUES (294, 'Die Hard', 1998);
-- fails with: 
-- Failed to insert values into stream/table: MOVIES
-- Caused by: Invalid Java object for schema type STRING: class java.lang.Integer
--	for field: "ROWKEY"

The issue here is that the id key field specified in the movies WITH clause is an INT.
KSQL sees that the id field is the same as the key and hence tries to set ROWKEY to 294, but ROWKEY is a STRING field, and KSQL blows up...

Until we have primitive keys other than String I think KSQL needs to support converting the value to a STRING to store in ROWKEY.

We need to make sure the conversion is compatible with what KSQL would do if storing, for example, a STRUCT field in the key as part of a GROUP BY clause.

A secondary issue is that even if the user attempts to explicitly supply ROWKEY, or ROWKEY and not Id, it still fails:

INSERT INTO ratings (rowkey, id, title, release_year) VALUES ('294', 294, 'Die Hard', 1998);
-- fails with:
-- Failed to insert values into stream/table: RATINGS
-- Caused by: java.lang.IllegalStateException

NSERT INTO ratings (rowkey, title, release_year) VALUES ('294', 'Die Hard', 1998);
-- fails with:
--  Failed to insert values into stream/table: RATINGS
--  Caused by: java.lang.IllegalStateException

[big-andy-coates]

Related to #3282, and maybe #3021

[big-andy-coates]

The only available work around at the moment is to drop the key field from the WITH clause and then explicitly supply both Id and ROWKEY:

CREATE TABLE movies (id INT, title VARCHAR, release_year INT) WITH (kafka_topic='movies', partitions=1, value_format='avro');

INSERT INTO movies (rowkey, id, title, release_year) VALUES ('294', 294, 'Die Hard', 1998);

Note, if you don't supply rowkey you get another error (another bug):

INSERT INTO movies (id, title, release_year) VALUES (294, 'Die Hard', 1998);
-- fails with:
-- Failed to insert values into stream/table: MOVIES
-- Caused by: Producer is closed forcefully.

[purplefox]

According to https://docs.confluent.io/current/ksql/docs/developer-guide/syntax-reference.html#ksql-key-requirements

"KEY must be set to a column of type VARCHAR aka STRING."

So the CREATE TABLE movies statement above should be failing as it's not valid to have an id field of type INT?

[big-andy-coates]

I believe key fields also work with no-STRING fields. But would be good to check this is the case, especially check joins work as expected.

If they do work, then fix this bug and update the docs. If the don't work, then we should fail the CREATE statement if the keyField is not a STRING, and also change a lot lot stuff internally in too. Let's hope its the former.