Schema incompatibility error when creating 2 ksqldb tables with same kafka topic

[PitsBee]

Hey Guys,
Schema incompatibility error when creating 2 ksqldb tables with same kafka topic
i am posting my full query,
created 2 tables with same topics,
The first query executed without errors, when i created the second table “table_AT_EditorRevokedOEV1”
i am getting the following error
Could not register schema for topic: Schema being registered is incompatible with an earlier schema for
subject “streaming.at.InvitationHistoryOEv1-key”, details: [Incompatibility{type:NAME_MISMATCH, location:/name, message:expected:
io.confluent.ksql.avro_schemas.TableaTEditorsentoev1Key, reader:{“type”:“record”,“name”:“TableaTEditorrevokedoev1Key”,t
“namespace”:“io.confluent.ksql.avro_schemas”,
“fields”:[{“name”:“SPACEID”,“type”:[“null”,“int”],“default”:null},{“name”:“ARID”,“type”:[“null”,“long”],
“default”:null},{“name”:“ARTICLEID”,“type”:[“null”,“int”],“default”:null}],
“connect.name”:“io.confluent.ksql.avro_schemas.TableaTEditorrevokedoev1Key”},
writer:{“type”:“record”,“name”:“TableaTEditorsentoev1Key”,“namespace”:“io.confluent.ksql.avro_schemas”,
“fields”:[{“name”:“SPACEID”,“type”:[“null”,“int”],“default”:null},{“name”:“ArID”,“type”:[“null”,“long”],“default”:null},
{“name”:“ARTICLEID”,“type”:[“null”,“int”],“default”:null}]}}]; error code: 409; error code: 409

why is that 2 schema getting created ?
one schema with “connect-name” and other without name??

this is my query
CREATE TABLE IF NOT EXISTS table_ArticleTimeline_EditorSentOEV1
WITH (KAFKA_TOPIC=‘streaming.ArticleTimeline.InvitationHistoryOEv1’,VALUE_FORMAT=‘AVRO’)
AS
SELECT i.id,i.id->SpaceId AS SpaceId,i.id->ArticleReviewInvitationId AS ArticleReviewInvitationId,
1 AS ArticleReviewInvitationStatusId,
i.rowtime AS Timestamp
FROM table_ArticleTimeline_EditorInvitation_sent i
EMIT CHANGES;

CREATE TABLE IF NOT EXISTS table_ArticleTimeline_EditorRevokedOEV1
WITH (KAFKA_TOPIC=‘streaming.ArticleTimeline.InvitationHistoryOEv1’,VALUE_FORMAT=‘AVRO’)
AS
SELECT r.id,r.id->SpaceId AS SpaceId,r.id->ArticleReviewInvitationId AS ArticleReviewInvitationId,
4 AS ArticleReviewInvitationStatusId,
r.rowtime AS Timestamp
FROM table_ArticleTimeline_EditorInvitation_revoked r
EMIT CHANGES;

[bbejeck]

Hi @PitsBee ,

I’m wondering if you could solve this issue by providing the VALUE_SCHEMA_ID in your second table definition.

HTH,
Bill

[PitsBee]

Thank you @bbejeck
I could create a table using Value_Schema_ID, but when i create a derived view using Select , i am getting the following error.
line 3:1: mismatched input ‘AS’ expecting ‘;’

CREATE TABLE IF NOT EXISTS table_status_history3 (id STRUCT<SpaceId int,ArticleInvitationId bigint,ArticleId int> PRIMARY KEY)
WITH (KAFKA_TOPIC=‘InvitationHistoryStatusTest1’,VALUE_FORMAT=‘AVRO’,KEY_SCHEMA_ID=1,KEY_FORMAT=‘AVRO’)
AS
SELECT i.id, i.id->SpaceId AS SpaceId,i.id->ArticleInvitationId AS ArticleReviewInvitationId,
4 AS ArticleInvitationStatusId,
i.rowtime AS Timestamp
FROM table_EditorInvitation_revoked i
EMIT CHANGES;

Can you please correct me what is the error in this?