Comet returns different results for 5 TPCDS queries compared with Spark

[sunchao]

Describe the bug

TPC-DS q34, q66, q64, q71, q88, q90, q96 are disabled right now because they generate different results from Spark.

q34 seems something to do with sorting. The order is different.

  java.lang.Exception: Expected "...NULL	NULL	NULL	NULL	[126143	15
NULL	NULL	NULL	NULL	21529]3	15
NULL	NULL	Mrs. ...", but got "...NULL	NULL	NULL	NULL	[215293	15
NULL	NULL	NULL	NULL	12614]3	15
NULL	NULL	Mrs. ..." Result did not match

q64 is also about sorting

  java.lang.Exception: Expected "...  	ation	35709     	[996       	NULL	Bridgeport	65817     	752       	Lakeview Lincoln	Friendship	74536     	1999	1	15.78	24.93	0.00	17.09	26.31	0.00	2000	1
ablepricallyantiought                             	ation	35709     	71        	River River	Friendship	34536     	NULL	NULL	Newport	NULL	1999	1	22.60	38.87	0.00	17.09	26.31	0.00	2000	1
...", but got "...  	ation	35709     	[71        	River River	Friendship	34536     	NULL	NULL	Newport	NULL	1999	1	22.60	38.87	0.00	17.09	26.31	0.00	2000	1
ablepricallyantiought                             	ation	35709     	996       	NULL	Bridgeport	65817     	752       	Lakeview Lincoln	Friendship	74536     	1999	1	15.78	24.93	0.00	17.09	26.31	0.00	2000	1
..." Result did not match

Steps to reproduce

Enable these 3 queries in CometTPCDSQuerySuite and run the test suite.

Expected behavior

Comet should produce the same query results as Spark for these 3 queries.

Additional context

No response

[viirya]

I remember @kazuyukitanimura has some observations on this issue internally.

[viirya]

I think q39a, q39b were fixed internally and are part of CI. @sunchao You mean they are failed in particular with Comet?

[sunchao]

I think these two were also disabled internally. I haven't tried them in Comet yet.

[sunchao]

The query list should be q34, q41, and q64. I updated the ticket.

[kazuyukitanimura]

I have not tried with Comet either, but from internal experiment;

q34 v1.4 does not specify ss_ticket_number as a sort key, so Comet result is technically not wrong. It is just golden has different order for ss_ticket_number column
In comparison, q34 v2.7 specifies ss_ticket_number as a sort key and Comet should pass the test.

It looks like sort stability difference...

[viirya]

Actually q41 is passed now. I've verified it locally: #98

[viirya]

The latest excluded TPCDS query list: "q34", "q66", "q64", "q71", "q88", "q90", "q96"

See https://github.com/apache/arrow-datafusion-comet/actions/runs/8025460291/job/21926164052

[viirya]

- q96 *** FAILED ***
  java.lang.Exception: Expected "[0]", but got "[888]" Result did not match

[viirya]

- q90 *** FAILED ***
  java.lang.Exception: Expected "[NULL]", but got "[0.58933333333333333333]" Result did not match

[viirya]

- q88 *** FAILED ***
  java.lang.Exception: Expected "[0	0	0	0	0	0	0	0]", but got "[2358	4664	4828	7447	6997	3886	4073	4923]" Result did not match

[viirya]

- q71 *** FAILED ***
  java.lang.Exception: Expected "[]", but got "[10016002	corpamalgamalg #x                                 	19	7	23183.08
10005002	scholarunivamalg #x                               	8	8	17109.63
8014006	edu packmaxi #x                                   	9	53	16420.75
3003002	exportiexporti #x                                 	17	44	16337.23
3001001	amalgexporti #x                                   	17	46	16088.53
4001001	amalgedu pack #x                                  	9	28	15557.83
...

The actual (wrong) result is pretty long, I ignore it above.

[viirya]

- q66 *** FAILED ***
  java.lang.Exception: Expected "[]", but got "[Just good amou	933435	Midway	Williamson County	TN	United States	DHL,BARIAN	2001	9246702.65	4973596.11	8243830.24	11536095.04	8848441.04	6589907.46	14946163.85	24260830.90	23156311.42	21897174.69	33046376.63	24591398.12	9.906102353136	5.328272573880	8.831713231238	
...

Same here.

[viirya]

The latest excluded queries are: "q66", "q71", "q88", "q90", "q96".