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)
It seems we agreed to help via
UNION BY NAME. 👀 Where’s the disagreement?Addendum: The reason DuckDB uses
by nameinstead ofcorrespondingis 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 usecorresponding.https://github.com/duckdb/duckdb/issues/4320#issuecomment-1209245589
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 toCREATE TABLEto 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
I don’t understand what you mean by “automatically order”. How to order? 👀
select *(perhaps not that important)UNION, if user havet1(a int, b int),t2(b int, a int), you can see order will affect the result forUNIONwithoutBY 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.However, users are also not able to directly
UNIONthese two tables in the upstream system (withoutBY NAME). 😕 I don’t think it’s a problem that we should help to address. Though supportingUNION BY NAMEis definitely good.Sounds like
CORRESPONDINGis the standard version ofBY NAMEmentioned in https://github.com/risingwavelabs/risingwave/issues/15637#issuecomment-1991060486Without it, the user can easily workaround by manually reordering the columns before union. Or for ease of reuse, create a
viewover 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 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?