You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@kylin.apache.org by Joel Victor <jo...@gmail.com> on 2016/10/10 14:30:31 UTC
Incorrect query result
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;
Re: Incorrect query result
Posted by Li Yang <li...@apache.org>.
Hi Joel,
What is your Kylin version? Does the problem reproduces with latest
v1.5.4.1? If yes, then the bug needs a JIRA.
Thanks
Yang
On Tue, Oct 11, 2016 at 2:16 AM, Alberto Ramón <a....@gmail.com>
wrote:
> can you try this
> <http://apache-kylin.74782.x6.nabble.com/about-kylin-sql-
> key-words-IN-td5908.html>
>
>
> BR, Alb
>
> 2016-10-10 16:30 GMT+02:00 Joel Victor <jo...@gmail.com>:
>
> > 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;
> >
> >
>
Re: Incorrect query result
Posted by Alberto Ramón <a....@gmail.com>.
can you try this
<http://apache-kylin.74782.x6.nabble.com/about-kylin-sql-key-words-IN-td5908.html>
BR, Alb
2016-10-10 16:30 GMT+02:00 Joel Victor <jo...@gmail.com>:
> 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;
>
>