risingwave: the auto schema detection does not order the columns in the same way for different ables

Describe the bug

The user reported that even when the upstream table’s schemas are the same, the ordering of the columns is “sometimes” not the same.

Therefore, union cannot union them by default via *.

Error message/log

No response

To Reproduce

No response

Expected behavior

No response

How did you deploy RisingWave?

No response

The version of RisingWave

No response

Additional context

No response

About this issue

  • Original URL
  • State: open
  • Created 2 years ago
  • Reactions: 1
  • Comments: 19 (19 by maintainers)

Most upvoted comments

It seems we agreed to help via UNION BY NAME. 👀 Where’s the disagreement?

Addendum: The reason DuckDB uses by name instead of corresponding is that they decided to support a different number of columns on each side of the union, filling missing columns on the other side with NULL. I believe we do not need to support this behavior, and in fact, this silent tolerance might be harmful. Therefore, I think we should still use corresponding.

https://github.com/duckdb/duckdb/issues/4320#issuecomment-1209245589

Another approach is that we just sort the columns by their name on our side, is there any concern?

It would be a surprise to user that the order of columns in cdc table is different from its upstream. I think the elegant way is to support UNION CORRESPONDING. The other quick and dirty way is to add a with option to CREATE TABLE to sort columns by name.

Another approach is that we just sort the columns by their name on our side, is there any concern?

cc @xiangjinwu @neverchanje might want to chime in

Why not one step further, automatically order the columns of the CDC table according to the name.

I don’t understand what you mean by “automatically order”. How to order? 👀

  1. Order affects select * (perhaps not that important)
  2. More importantly for UNION, if user have t1(a int, b int), t2(b int, a int), you can see order will affect the result for UNION without BY NAME. That’s why we cannot arbitrarily reorder according to what we think might be good. @StrikeW 's idea of introducing an option is somehow more reasonable.

Manual re-ordering by specifying the names of columns again makes the auto schema detection essentially meaningless because we want to avoid typing the names in the first place. But we now need to do it again.

However, users are also not able to directly UNION these two tables in the upstream system (without BY NAME). 😕 I don’t think it’s a problem that we should help to address. Though supporting UNION BY NAME is definitely good.

Sounds like CORRESPONDING is the standard version of BY NAME mentioned in https://github.com/risingwavelabs/risingwave/issues/15637#issuecomment-1991060486

Without it, the user can easily workaround by manually reordering the columns before union. Or for ease of reuse, create a view over the table just to reorder columns, and use the view rather than underlying table afterwards. Specifying all columns again in correct order may sound receptive but it is no harder than redefining the upstream with correct column order, which is out of their control but willing to do. - Or does turning off aut-schema detection work?

Furthermore, the original issue description stated:

The user reported that even when the upstream table’s schemas are the same, the ordering of the columns is “sometimes” not the same.

The example in https://github.com/risingwavelabs/risingwave/issues/17451#issuecomment-2189145094 above does not satisfy the even when part. Does auto-schema detection guarantee the column orders in RisingWave is always the same as upstream?