There are two columns in the table which are event date and event hour.
I have attached the result and the plans for all three queries that I ran.
The first query will *not* show the value for date 2016-09-28 and hour 0 in
the result
The second query will show the value for date 2016-09-28 and hour 0 in the
result
The third query will also show the value for date 2016-09-28 and hour 0 in
the result
According to me the first query should also show the value for date
2016-09-28 and hour 0 in the result
All the three queries look correct to me and their plans look correct as
well.
I would appreciate any help in debugging this.
Following are the three queries:
Query one:
SELECT
event_date, event_hour, SUM(cnt)
FROM analytics_issue_report
WHERE ((((event_date IN ('2016-09-28')) AND event_hour = 0)
OR ((event_date IN ('2016-09-28')) AND event_hour >= 1)
OR ((event_date IN ('2016-10-04')) AND event_hour = 23)
OR ((event_date IN ('2016-10-04')) AND event_hour < 23)
OR event_date BETWEEN '2016-09-29' AND '2016-10-03')
AND action_type=1)
GROUP BY event_date, event_hour ORDER BY event_date, event_hour;
Query two:
SELECT
event_date, event_hour, SUM(cnt)
FROM analytics_issue_report
WHERE ((event_date BETWEEN '2016-09-28' AND '2016-10-04')
AND action_type=1)
GROUP BY event_date, event_hour ORDER BY event_date, event_hour;
Query three:
SELECT
event_date, event_hour, SUM(cnt)
FROM analytics_issue_report
WHERE ((((event_date IN ('2016-09-28')) AND event_hour <= 23)
OR ((event_date IN ('2016-10-04')) AND event_hour = 23)
OR ((event_date IN ('2016-10-04')) AND event_hour < 23)
OR event_date BETWEEN '2016-09-29' AND '2016-10-03')
AND action_type=1)
GROUP BY event_date, event_hour ORDER BY event_date, event_hour;
EVENT_DATE,EVENT_HOUR,EXPR$2
2016-09-28,0,3911
2016-09-28,1,3945
2016-09-28,2,3810
2016-09-28,3,3771
2016-09-28,4,3650
2016-09-28,5,3978
2016-09-28,6,3743
2016-09-28,7,4196
2016-09-28,8,3772
2016-09-28,9,4300
2016-09-28,10,4883
2016-09-28,11,5763
2016-09-28,12,6146
2016-09-28,13,6277
2016-09-28,14,6334
2016-09-28,15,6427
2016-09-28,16,7810
2016-09-28,17,6879
2016-09-28,18,5732
2016-09-28,19,5507
2016-09-28,20,4773
2016-09-28,21,4304
2016-09-28,22,3793
2016-09-28,23,3499
2016-09-29,0,3540
2016-09-29,1,3487
2016-09-29,2,3499
2016-09-29,3,3542
2016-09-29,4,3483
2016-09-29,5,3624
2016-09-29,6,3474
2016-09-29,7,3508
2016-09-29,8,3636
2016-09-29,9,4017
2016-09-29,10,4620
2016-09-29,11,5569
2016-09-29,12,6117
2016-09-29,13,6496
2016-09-29,14,6598
2016-09-29,15,6685
2016-09-29,16,6572
2016-09-29,17,6345
2016-09-29,18,6110
2016-09-29,19,5595
2016-09-29,20,4959
2016-09-29,21,4378
2016-09-29,22,4056
2016-09-29,23,3815
2016-09-30,0,3616
2016-09-30,1,3617
2016-09-30,2,3561
2016-09-30,3,3578
2016-09-30,4,3708
2016-09-30,5,4458
2016-09-30,6,4065
2016-09-30,7,4041
2016-09-30,8,4071
2016-09-30,9,4472
2016-09-30,10,5706
2016-09-30,11,5736
2016-09-30,12,6160
2016-09-30,13,6533
2016-09-30,14,6521
2016-09-30,15,6611
2016-09-30,16,6487
2016-09-30,17,6313
2016-09-30,18,6055
2016-09-30,19,5814
2016-09-30,20,5242
2016-09-30,21,4754
2016-09-30,22,4303
2016-09-30,23,3920
2016-10-01,0,3652
2016-10-01,1,3932
2016-10-01,2,3790
2016-10-01,3,4086
2016-10-01,4,3966
2016-10-01,5,4246
2016-10-01,6,4509
2016-10-01,7,4799
2016-10-01,8,4974
2016-10-01,9,5254
2016-10-01,10,5286
2016-10-01,11,5854
2016-10-01,12,6389
2016-10-01,13,6450
2016-10-01,14,6940
2016-10-01,15,6825
2016-10-01,16,6355
2016-10-01,17,6247
2016-10-01,18,5779
2016-10-01,19,5320
2016-10-01,20,5095
2016-10-01,21,4463
2016-10-01,22,4071
2016-10-01,23,3763
2016-10-02,0,3834
2016-10-02,1,3895
2016-10-02,2,3508
2016-10-02,3,3492
2016-10-02,4,3404
2016-10-02,5,3725
2016-10-02,6,4210
2016-10-02,7,4879
2016-10-02,8,4928
2016-10-02,9,5099
2016-10-02,10,5217
2016-10-02,11,5506
2016-10-02,12,5873
2016-10-02,13,6245
2016-10-02,14,6260
2016-10-02,15,6296
2016-10-02,16,6228
2016-10-02,17,6023
2016-10-02,18,5799
2016-10-02,19,5367
2016-10-02,20,4720
2016-10-02,21,4216
2016-10-02,22,3755
2016-10-02,23,3372
2016-10-03,0,3371
2016-10-03,1,3391
2016-10-03,2,3324
2016-10-03,3,3181
2016-10-03,4,3226
2016-10-03,5,3471
2016-10-03,6,3414
2016-10-03,7,3522
2016-10-03,8,3750
2016-10-03,9,3994
2016-10-03,10,4472
2016-10-03,11,5457
2016-10-03,12,6461
2016-10-03,13,6438
2016-10-03,14,6534
2016-10-03,15,6774
2016-10-03,16,6815
2016-10-03,17,6580
2016-10-03,18,6111
2016-10-03,19,5950
2016-10-03,20,5211
2016-10-03,21,4541
2016-10-03,22,4008
2016-10-03,23,3674
2016-10-04,0,3700
2016-10-04,1,3786
2016-10-04,2,3549
2016-10-04,3,3282
2016-10-04,4,3132
2016-10-04,5,3641
2016-10-04,6,3407
2016-10-04,7,3270
2016-10-04,8,3690
2016-10-04,9,3933
2016-10-04,10,4468
2016-10-04,11,5486
2016-10-04,12,6374
2016-10-04,13,6509
2016-10-04,14,6662
2016-10-04,15,6932
2016-10-04,16,6825
2016-10-04,17,6781
2016-10-04,18,6783
2016-10-04,19,6379
2016-10-04,20,5297
2016-10-04,21,4376
2016-10-04,22,3767
2016-10-04,23,3470
PLAN
"EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
OLAPToEnumerableConverter
OLAPSortRel(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
OLAPAggregateRel(group=[{0, 1}], EXPR$2=[SUM($2)])
OLAPProjectRel(EVENT_DATE=[$9], EVENT_HOUR=[$4], CNT=[$11])
OLAPFilterRel(condition=[AND(>=($9, CAST('2016-09-28'):DATE NOT NULL), <=($9, CAST('2016-10-04'):DATE NOT NULL), =($8, 1))])
OLAPTableScan(table=[[SUPPORT_ANALYTICS, ANALYTICS_ISSUE_REPORT]], fields=[[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13]])
"
EVENT_DATE,EVENT_HOUR,EXPR$2
2016-09-28,0,3911
2016-09-28,1,3945
2016-09-28,2,3810
2016-09-28,3,3771
2016-09-28,4,3650
2016-09-28,5,3978
2016-09-28,6,3743
2016-09-28,7,4196
2016-09-28,8,3772
2016-09-28,9,4300
2016-09-28,10,4883
2016-09-28,11,5763
2016-09-28,12,6146
2016-09-28,13,6277
2016-09-28,14,6334
2016-09-28,15,6427
2016-09-28,16,7810
2016-09-28,17,6879
2016-09-28,18,5732
2016-09-28,19,5507
2016-09-28,20,4773
2016-09-28,21,4304
2016-09-28,22,3793
2016-09-28,23,3499
2016-09-29,0,3540
2016-09-29,1,3487
2016-09-29,2,3499
2016-09-29,3,3542
2016-09-29,4,3483
2016-09-29,5,3624
2016-09-29,6,3474
2016-09-29,7,3508
2016-09-29,8,3636
2016-09-29,9,4017
2016-09-29,10,4620
2016-09-29,11,5569
2016-09-29,12,6117
2016-09-29,13,6496
2016-09-29,14,6598
2016-09-29,15,6685
2016-09-29,16,6572
2016-09-29,17,6345
2016-09-29,18,6110
2016-09-29,19,5595
2016-09-29,20,4959
2016-09-29,21,4378
2016-09-29,22,4056
2016-09-29,23,3815
2016-09-30,0,3616
2016-09-30,1,3617
2016-09-30,2,3561
2016-09-30,3,3578
2016-09-30,4,3708
2016-09-30,5,4458
2016-09-30,6,4065
2016-09-30,7,4041
2016-09-30,8,4071
2016-09-30,9,4472
2016-09-30,10,5706
2016-09-30,11,5736
2016-09-30,12,6160
2016-09-30,13,6533
2016-09-30,14,6521
2016-09-30,15,6611
2016-09-30,16,6487
2016-09-30,17,6313
2016-09-30,18,6055
2016-09-30,19,5814
2016-09-30,20,5242
2016-09-30,21,4754
2016-09-30,22,4303
2016-09-30,23,3920
2016-10-01,0,3652
2016-10-01,1,3932
2016-10-01,2,3790
2016-10-01,3,4086
2016-10-01,4,3966
2016-10-01,5,4246
2016-10-01,6,4509
2016-10-01,7,4799
2016-10-01,8,4974
2016-10-01,9,5254
2016-10-01,10,5286
2016-10-01,11,5854
2016-10-01,12,6389
2016-10-01,13,6450
2016-10-01,14,6940
2016-10-01,15,6825
2016-10-01,16,6355
2016-10-01,17,6247
2016-10-01,18,5779
2016-10-01,19,5320
2016-10-01,20,5095
2016-10-01,21,4463
2016-10-01,22,4071
2016-10-01,23,3763
2016-10-02,0,3834
2016-10-02,1,3895
2016-10-02,2,3508
2016-10-02,3,3492
2016-10-02,4,3404
2016-10-02,5,3725
2016-10-02,6,4210
2016-10-02,7,4879
2016-10-02,8,4928
2016-10-02,9,5099
2016-10-02,10,5217
2016-10-02,11,5506
2016-10-02,12,5873
2016-10-02,13,6245
2016-10-02,14,6260
2016-10-02,15,6296
2016-10-02,16,6228
2016-10-02,17,6023
2016-10-02,18,5799
2016-10-02,19,5367
2016-10-02,20,4720
2016-10-02,21,4216
2016-10-02,22,3755
2016-10-02,23,3372
2016-10-03,0,3371
2016-10-03,1,3391
2016-10-03,2,3324
2016-10-03,3,3181
2016-10-03,4,3226
2016-10-03,5,3471
2016-10-03,6,3414
2016-10-03,7,3522
2016-10-03,8,3750
2016-10-03,9,3994
2016-10-03,10,4472
2016-10-03,11,5457
2016-10-03,12,6461
2016-10-03,13,6438
2016-10-03,14,6534
2016-10-03,15,6774
2016-10-03,16,6815
2016-10-03,17,6580
2016-10-03,18,6111
2016-10-03,19,5950
2016-10-03,20,5211
2016-10-03,21,4541
2016-10-03,22,4008
2016-10-03,23,3674
2016-10-04,0,3700
2016-10-04,1,3786
2016-10-04,2,3549
2016-10-04,3,3282
2016-10-04,4,3132
2016-10-04,5,3641
2016-10-04,6,3407
2016-10-04,7,3270
2016-10-04,8,3690
2016-10-04,9,3933
2016-10-04,10,4468
2016-10-04,11,5486
2016-10-04,12,6374
2016-10-04,13,6509
2016-10-04,14,6662
2016-10-04,15,6932
2016-10-04,16,6825
2016-10-04,17,6781
2016-10-04,18,6783
2016-10-04,19,6379
2016-10-04,20,5297
2016-10-04,21,4376
2016-10-04,22,3767
2016-10-04,23,3470
PLAN
"EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
OLAPToEnumerableConverter
OLAPSortRel(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
OLAPAggregateRel(group=[{0, 1}], EXPR$2=[SUM($2)])
OLAPProjectRel(EVENT_DATE=[$9], EVENT_HOUR=[$4], CNT=[$11])
OLAPFilterRel(condition=[AND(OR(AND(=($9, CAST('2016-09-28'):DATE NOT NULL), <=($4, 23)), AND(=($9, CAST('2016-10-04'):DATE NOT NULL), =($4, 23)), AND(=($9, CAST('2016-10-04'):DATE NOT NULL), <($4, 23)), AND(>=($9, CAST('2016-09-29'):DATE NOT NULL), <=($9, CAST('2016-10-03'):DATE NOT NULL))), =($8, 1))])
OLAPTableScan(table=[[SUPPORT_ANALYTICS, ANALYTICS_ISSUE_REPORT]], fields=[[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13]])
"
EVENT_DATE,EVENT_HOUR,EXPR$2
2016-09-28,1,3945
2016-09-28,2,3810
2016-09-28,3,3771
2016-09-28,4,3650
2016-09-28,5,3978
2016-09-28,6,3743
2016-09-28,7,4196
2016-09-28,8,3772
2016-09-28,9,4300
2016-09-28,10,4883
2016-09-28,11,5763
2016-09-28,12,6146
2016-09-28,13,6277
2016-09-28,14,6334
2016-09-28,15,6427
2016-09-28,16,7810
2016-09-28,17,6879
2016-09-28,18,5732
2016-09-28,19,5507
2016-09-28,20,4773
2016-09-28,21,4304
2016-09-28,22,3793
2016-09-28,23,3499
2016-09-29,0,3540
2016-09-29,1,3487
2016-09-29,2,3499
2016-09-29,3,3542
2016-09-29,4,3483
2016-09-29,5,3624
2016-09-29,6,3474
2016-09-29,7,3508
2016-09-29,8,3636
2016-09-29,9,4017
2016-09-29,10,4620
2016-09-29,11,5569
2016-09-29,12,6117
2016-09-29,13,6496
2016-09-29,14,6598
2016-09-29,15,6685
2016-09-29,16,6572
2016-09-29,17,6345
2016-09-29,18,6110
2016-09-29,19,5595
2016-09-29,20,4959
2016-09-29,21,4378
2016-09-29,22,4056
2016-09-29,23,3815
2016-09-30,0,3616
2016-09-30,1,3617
2016-09-30,2,3561
2016-09-30,3,3578
2016-09-30,4,3708
2016-09-30,5,4458
2016-09-30,6,4065
2016-09-30,7,4041
2016-09-30,8,4071
2016-09-30,9,4472
2016-09-30,10,5706
2016-09-30,11,5736
2016-09-30,12,6160
2016-09-30,13,6533
2016-09-30,14,6521
2016-09-30,15,6611
2016-09-30,16,6487
2016-09-30,17,6313
2016-09-30,18,6055
2016-09-30,19,5814
2016-09-30,20,5242
2016-09-30,21,4754
2016-09-30,22,4303
2016-09-30,23,3920
2016-10-01,0,3652
2016-10-01,1,3932
2016-10-01,2,3790
2016-10-01,3,4086
2016-10-01,4,3966
2016-10-01,5,4246
2016-10-01,6,4509
2016-10-01,7,4799
2016-10-01,8,4974
2016-10-01,9,5254
2016-10-01,10,5286
2016-10-01,11,5854
2016-10-01,12,6389
2016-10-01,13,6450
2016-10-01,14,6940
2016-10-01,15,6825
2016-10-01,16,6355
2016-10-01,17,6247
2016-10-01,18,5779
2016-10-01,19,5320
2016-10-01,20,5095
2016-10-01,21,4463
2016-10-01,22,4071
2016-10-01,23,3763
2016-10-02,0,3834
2016-10-02,1,3895
2016-10-02,2,3508
2016-10-02,3,3492
2016-10-02,4,3404
2016-10-02,5,3725
2016-10-02,6,4210
2016-10-02,7,4879
2016-10-02,8,4928
2016-10-02,9,5099
2016-10-02,10,5217
2016-10-02,11,5506
2016-10-02,12,5873
2016-10-02,13,6245
2016-10-02,14,6260
2016-10-02,15,6296
2016-10-02,16,6228
2016-10-02,17,6023
2016-10-02,18,5799
2016-10-02,19,5367
2016-10-02,20,4720
2016-10-02,21,4216
2016-10-02,22,3755
2016-10-02,23,3372
2016-10-03,0,3371
2016-10-03,1,3391
2016-10-03,2,3324
2016-10-03,3,3181
2016-10-03,4,3226
2016-10-03,5,3471
2016-10-03,6,3414
2016-10-03,7,3522
2016-10-03,8,3750
2016-10-03,9,3994
2016-10-03,10,4472
2016-10-03,11,5457
2016-10-03,12,6461
2016-10-03,13,6438
2016-10-03,14,6534
2016-10-03,15,6774
2016-10-03,16,6815
2016-10-03,17,6580
2016-10-03,18,6111
2016-10-03,19,5950
2016-10-03,20,5211
2016-10-03,21,4541
2016-10-03,22,4008
2016-10-03,23,3674
2016-10-04,0,3700
2016-10-04,1,3786
2016-10-04,2,3549
2016-10-04,3,3282
2016-10-04,4,3132
2016-10-04,5,3641
2016-10-04,6,3407
2016-10-04,7,3270
2016-10-04,8,3690
2016-10-04,9,3933
2016-10-04,10,4468
2016-10-04,11,5486
2016-10-04,12,6374
2016-10-04,13,6509
2016-10-04,14,6662
2016-10-04,15,6932
2016-10-04,16,6825
2016-10-04,17,6781
2016-10-04,18,6783
2016-10-04,19,6379
2016-10-04,20,5297
2016-10-04,21,4376
2016-10-04,22,3767
2016-10-04,23,3470
PLAN
"EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
OLAPToEnumerableConverter
OLAPSortRel(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
OLAPAggregateRel(group=[{0, 1}], EXPR$2=[SUM($2)])
OLAPProjectRel(EVENT_DATE=[$9], EVENT_HOUR=[$4], CNT=[$11])
OLAPFilterRel(condition=[AND(OR(AND(=($9, CAST('2016-09-28'):DATE NOT NULL), =($4, 0)), AND(=($9, CAST('2016-09-28'):DATE NOT NULL), >=($4, 1)), AND(=($9, CAST('2016-10-04'):DATE NOT NULL), =($4, 23)), AND(=($9, CAST('2016-10-04'):DATE NOT NULL), <($4, 23)), AND(>=($9, CAST('2016-09-29'):DATE NOT NULL), <=($9, CAST('2016-10-03'):DATE NOT NULL))), =($8, 1))])
OLAPTableScan(table=[[SUPPORT_ANALYTICS, ANALYTICS_ISSUE_REPORT]], fields=[[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13]])
"
