ksqlDB should be able to "create or replace stream/tables as select" with re-ordered columns in the query

[daigotanaka]

Is your feature request related to a problem? Please describe. I'm always frustrated when we need to maintain the column orders when running CREATE OR REPLACE STREAM/TABLE AS SELECT

To Reproduce

• ksqlDB version: 0.27.3-rc6 (on Confluent)

Just 2 steps to reproduce this issue:

Step 1. Create a stream

CREATE STREAM test AS
SELECT
  id,
  value
FROM STREAM_A
EMIT CHANGES;

A successful response:

{
  "@type": "currentStatus",
  "statementText": "CREATE STREAM TEST WITH (KAFKA_TOPIC='pksqlc-xxxxTEST', PARTITIONS=1, REPLICAS=3) AS SELECT\n  STREAM_A.ID ID,\n  STREAM_A.VALUE VALUE\nFROM STREAM_A STREAM_A\nEMIT CHANGES;",
  "commandId": "stream/`TEST`/create",
  "commandStatus": {
    "status": "SUCCESS",
    "message": "Created query with ID CSAS_TEST_171",
    "queryId": "CSAS_TEST_171"
  },
  "commandSequenceNumber": 172,
  "warnings": [
  ]
}

Step 2. Try to add a new column in between:

CREATE OR REPLACE STREAM test AS
SELECT
  id,
  description, -- new column in between
  value
FROM STREAM_A
EMIT CHANGES;

What we get today:

Cannot upgrade data source: DataSource '`TEST`' has schema = `ID` STRING KEY, `VALUE` STRING which is not upgradeable to `ID` STRING KEY, `DESCRIPTION` STRING, `VALUE` STRING. (The following columns are changed, missing or reordered: [`VALUE` STRING])

Describe the solution you'd like I'd like ksqlDB to be able to update the stream and table when columns are re-ordered or a new column is added in between.

Additional context The current strict order requirement is a big pain when it comes to maintaining a ksqlDB code. We cannot even reorder the columns for readability.

I'm developing dbt-ksql (https://github.com/anelendata/dbt-ksql) so we can effectively manage ksql models at scale. ksqlDB is very limited when it comes to replacing existing streams and tables, some are understandable reasons, but I think the limitation I describe above is solvable at the query compilation level.