abhioncbr commented on code in PR #10561:
URL: https://github.com/apache/pinot/pull/10561#discussion_r1161265182
##########
pinot-integration-tests/src/test/resources/On_Time_On_Time_Performance_2014_100k_subset.test_queries_200.sql:
##########
@@ -117,54 +117,54 @@
{"sql":"SELECT WheelsOn, OriginStateName, AVG(DepDel15) FROM mytable GROUP BY
WheelsOn, OriginStateName ORDER BY WheelsOn, OriginStateName LIMIT
18","hsqls":["SELECT WheelsOn, OriginStateName, AVG(DepDel15) FROM mytable
GROUP BY WheelsOn, OriginStateName ORDER BY WheelsOn, OriginStateName LIMIT
18"]}
# Filtering on Aggregation
-{"sql":"SELECT AVG(AirTime), SUM(OriginStateFips) FROM mytable WHERE
DivTailNums BETWEEN 'N8315C' AND 'N130DL' OR DivAirports IN ('PIH', 'DLH',
'MTJ', 'SEA', 'LIT') AND TaxiOut NOT IN (59, 112, 57, 72) LIMIT
5","hsqls":["SELECT AVG(AirTime), SUM(OriginStateFips) FROM mytable WHERE
(DivTailNums__MV0 BETWEEN 'N8315C' AND 'N130DL' OR DivTailNums__MV1 BETWEEN
'N8315C' AND 'N130DL' OR DivTailNums__MV2 BETWEEN 'N8315C' AND 'N130DL' OR
DivTailNums__MV3 BETWEEN 'N8315C' AND 'N130DL' OR DivTailNums__MV4 BETWEEN
'N8315C' AND 'N130DL') OR (DivAirports__MV0 IN ('PIH', 'DLH', 'MTJ', 'SEA',
'LIT') OR DivAirports__MV1 IN ('PIH', 'DLH', 'MTJ', 'SEA', 'LIT') OR
DivAirports__MV2 IN ('PIH', 'DLH', 'MTJ', 'SEA', 'LIT') OR DivAirports__MV3 IN
('PIH', 'DLH', 'MTJ', 'SEA', 'LIT') OR DivAirports__MV4 IN ('PIH', 'DLH',
'MTJ', 'SEA', 'LIT')) AND TaxiOut NOT IN (59, 112, 57, 72) LIMIT 10000"]}
-{"sql":"SELECT AVG(ArrDel15) FROM mytable WHERE DivWheelsOffs >= 1610 LIMIT
13","hsqls":["SELECT AVG(CAST(ArrDel15 AS DOUBLE)) FROM mytable WHERE
(DivWheelsOffs__MV0 >= 1610 OR DivWheelsOffs__MV1 >= 1610 OR DivWheelsOffs__MV2
>= 1610 OR DivWheelsOffs__MV3 >= 1610 OR DivWheelsOffs__MV4 >= 1610) LIMIT
10000"]}
+{"sql":"SELECT AVG(AirTime), SUM(OriginStateFips) FROM mytable WHERE
DivTailNums BETWEEN 'N8315C' AND 'N130DL' OR DivAirports IN ('PIH', 'DLH',
'MTJ', 'SEA', 'LIT') AND TaxiOut NOT IN (59, 112, 57, 72) LIMIT
5","hsqls":["SELECT AVG(AirTime), SUM(OriginStateFips) FROM mytable WHERE
(DivTailNums[1] BETWEEN 'N8315C' AND 'N130DL' OR DivTailNums[2] BETWEEN
'N8315C' AND 'N130DL' OR DivTailNums[3] BETWEEN 'N8315C' AND 'N130DL' OR
DivTailNums[4] BETWEEN 'N8315C' AND 'N130DL' OR DivTailNums[5] BETWEEN 'N8315C'
AND 'N130DL') OR (ARRAY_CONTAINS(DivAirports, 'PIH') OR
ARRAY_CONTAINS(DivAirports, 'DLH') OR ARRAY_CONTAINS(DivAirports, 'MTJ') OR
ARRAY_CONTAINS(DivAirports, 'SEA') OR ARRAY_CONTAINS(DivAirports, 'LIT')) AND
TaxiOut NOT IN (59, 112, 57, 72) LIMIT 10000"]}
Review Comment:
I think, we can not make the query changes for Array datatype with the
`BETWEEN` clause or comparison operation like below
```
SELECT AVG(DayOfWeek), MAX(Diverted) FROM mytable WHERE (DivAirportSeqIDs
>= 1281902) OR DestAirportSeqID NOT IN (1463502, 1530402, 1068502) AND
DepDelayMinutes BETWEEN 48.0 AND 94.0 LIMIT 10000
SELECT AVG(TotalAddGTime), SUM(ArrivalDelayGroups), SUM(SecurityDelay) FROM
mytable WHERE UniqueCarrier = 'AA' OR Cancelled IN (1, 0) OR (DivWheelsOffs
BETWEEN 1719 AND 1720 ) LIMIT 10000
```
because of the H2 error
`[Values of types "BIGINT ARRAY[5]" and "INTEGER" are not comparable; SQL
statement:]`
Please, let me know if it is possible. Thanks.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscr...@pinot.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@pinot.apache.org
For additional commands, e-mail: commits-h...@pinot.apache.org
