You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@superset.apache.org by "eschutho (via GitHub)" <gi...@apache.org> on 2024/03/14 00:20:30 UTC
[I] Time comparison does not always capture the correct data when the result set is limited [superset]
eschutho opened a new issue, #27504:
URL: https://github.com/apache/superset/issues/27504
### Bug description
When fetching a set of data over a period of time and adding a time comparison, if the result set is truncated, some of the comparison information will also be missing.
<img width="2324" alt="Screenshot 2024-03-13 at 4 55 31 PM" src="https://github.com/apache/superset/assets/5186919/87759e39-ef7e-4288-8dae-5082860951ce">
In the above example, I am trying to fetch the top 10 years from the years 1989 - 2004 where the name Alex was the most popular and compare that count to the period 10 years prior. As you can see, the values for 1981, 1982, 1983, and 1984 are missing, although I would expect to get those values, because as you can see, if I fetch more data by removing the row limit, those values exist.
<img width="2324" alt="Screenshot 2024-03-13 at 4 55 59 PM" src="https://github.com/apache/superset/assets/5186919/b58663c7-f38f-424f-995e-f7c25ce0e4b5">
I believe the reason why this bug exists is because we are making two separate db requests, one for each time period when the name is "Alex", and limiting each request by 10 and sorting on the count. Because the count for each response is going to be different, when we make the comparison, the values for the dates in the original time series may not exist.
Here are the db requests:
<img width="894" alt="Screenshot 2024-03-13 at 5 01 33 PM" src="https://github.com/apache/superset/assets/5186919/77f9b08c-a8b6-498b-8268-2eba5c2d6024">
<img width="894" alt="Screenshot 2024-03-13 at 5 01 59 PM" src="https://github.com/apache/superset/assets/5186919/f4647d8c-528e-480b-b702-b3a9aea8c3c5">
I believe what needs to happen instead is that the first query with timerange A needs to join the data from timerange B on a left outer join, and then apply the limit, and that would give the correct results. Something like:
```
SELECT
query1.ds AS ds1,
query1.count AS count1,
query2.ds AS ds2,
query2.count AS count2
FROM
(
SELECT
DATE_TRUNC('year', ds) AS ds,
COUNT(*) AS count
FROM
public.birth_names
WHERE
ds >= TO_TIMESTAMP('1989-03-13 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.US') AND
ds < TO_TIMESTAMP('2004-03-13 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.US') AND
name IN ('Alex')
GROUP BY
DATE_TRUNC('year', ds)
ORDER BY
ds DESC
) AS query1
LEFT JOIN
(
SELECT
DATE_TRUNC('year', ds) AS ds,
COUNT(*) AS count
FROM
public.birth_names
WHERE
ds >= TO_TIMESTAMP('1979-03-13 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.US') AND
ds < TO_TIMESTAMP('1994-03-13 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.US') AND
name IN ('Alex')
GROUP BY
DATE_TRUNC('year', ds)
ORDER BY
ds DESC
) AS query2
ON
query1.ds = query2.ds + INTERVAL '10 years'
ORDER BY
query1.ds DESC
LIMIT 10;
```
<img width="1926" alt="Screenshot 2024-03-13 at 5 16 56 PM" src="https://github.com/apache/superset/assets/5186919/e1c9b3f3-934d-4666-a342-f9751ac3466c">
### How to reproduce the bug
1. create a line chart with a date range
2. create a time comparison to the chart
3. truncate the values by limiting the top rows
### Screenshots/recordings
See above
### Superset version
master / latest-dev
### Python version
3.10
### Node version
18 or greater
### Browser
Chrome
### Additional context
I am using the examples db on Postgres with the birth_names dataset
### Checklist
- [X] I have searched Superset docs and Slack and didn't find a solution to my problem.
- [X] I have searched the GitHub issue tracker and didn't find a similar bug report.
- [ ] I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.
--
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: notifications-unsubscribe@superset.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Time comparison does not always capture the correct data when the result set is limited [superset]
Posted by "zhaoyongjie (via GitHub)" <gi...@apache.org>.
zhaoyongjie commented on issue #27504:
URL: https://github.com/apache/superset/issues/27504#issuecomment-1998017192
@eschutho BTW, I'll open the SIP as soon as possible in the week.
--
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Time comparison does not always capture the correct data when the result set is limited [superset]
Posted by "Antonio-RiveroMartnez (via GitHub)" <gi...@apache.org>.
Antonio-RiveroMartnez commented on issue #27504:
URL: https://github.com/apache/superset/issues/27504#issuecomment-2004150125
> 1. ... The subquery from your post didn't provide limit clause in subquery.
> 3. Significant extra computing, 10 records dataset join <---> unpredictable records.
Including that in the subquery would truncate the set used to JOIN correct? Shouldn't we leverage the DBMSs optimizations when executing the query as a whole instead? Otherwise we end up having incomplete result sets if for example the LIMIT is too low, as as stated before data inconsistency is something we should never aim for.
To tackle this bug I would say that in general we have two limitations depending on which database are we working on:
1. Not all databases support CTE
2. Not all databases support JOINS
For the first one we would have to write something that check whether or not CTE are supported, if it does, make use of it, if not, write the outer join manually.
For the second, if the database supports joins we should ensure data consistency at pre-query execution with the outer joins, if not supported we should fallback to DataFrame joins at post processing time, but again, just as a fallback because data inconsistency might be arise.
--
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Time comparison does not always capture the correct data when the result set is limited [superset]
Posted by "eschutho (via GitHub)" <gi...@apache.org>.
eschutho commented on issue #27504:
URL: https://github.com/apache/superset/issues/27504#issuecomment-2005288123
Right, I agree, because we are fetching the data separately, in two separate queries, we aren't guaranteed that the results in the first set will be available in the second set, which makes it difficult to do the pandas post-processing comparison. My original example shows this happening on the first and seventh rows as another example. I'm not sure that most users would understand that there is actually data there, but that there are discrepancies in the row limit because of how our business logic is written. Do you think we can improve on this so that we can show all the data in these comparisons?
<img width="2324" alt="Screenshot 2024-03-13 at 4 48 53 PM" src="https://github.com/apache/superset/assets/5186919/658360fd-c445-4500-89ab-b5f4b146b206">
--
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Time comparison does not always capture the correct data when the result set is limited [superset]
Posted by "zhaoyongjie (via GitHub)" <gi...@apache.org>.
zhaoyongjie commented on issue #27504:
URL: https://github.com/apache/superset/issues/27504#issuecomment-2012300226
@eschutho
> Do you think by putting the limit on the outer scope after the join, we can solve this?
The key issue is that if we change any `order by` or `limit` in the _inner query_, in the other words, move the limit cluase to the _outer query_, we would get **an inconsistent result set** between time comparison and without time comparison.
> I had a similar question on the example https://github.com/apache/superset/pull/27545 and ....
I've replied on the Table with time comparison PR
--
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Time comparison does not always capture the correct data when the result set is limited [superset]
Posted by "Antonio-RiveroMartnez (via GitHub)" <gi...@apache.org>.
Antonio-RiveroMartnez commented on issue #27504:
URL: https://github.com/apache/superset/issues/27504#issuecomment-2004499272
> ... Superset doesn't have a way to **define sorting by DS and returning the top 10, then sorting by count and returning the top 10**.
Totally agree on that, but that's not the point of my comment, I'm talking when sorting and limiting on just 1 column. Just with one column for example ds as you mentioned, and limiting such result.
I disagree with the result being the same whether you use Pandas or SQL joins, unless I'm missing something an you can join Pandas at pre-query execution, if you rely on Pandas, the result set may have changed from execution of query A and query B plus you don't necessarily have the comparison you might be looking to apply since data from outer query might not be present in query B.
I believe the main thing to tackle is how LIMIT and OFFSET could impact data consistency when the comparison is made?
--
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Time comparison does not always capture the correct data when the result set is limited [superset]
Posted by "zhaoyongjie (via GitHub)" <gi...@apache.org>.
zhaoyongjie commented on issue #27504:
URL: https://github.com/apache/superset/issues/27504#issuecomment-2010873535
> we aren't guaranteed that the results in the first set will be available in the second set, which makes it difficult to do the pandas post-processing comparison. My original example shows this happening on the first and seventh rows as another example.
@eschutho, I believe this issue may not be stemming from the JOIN logic, but rather from the ORDER BY clause. Depending on human intuition, when constructing a time comparison, our intuition typically prefers to navigate through the time dimension. Thus, you've addressed the question in your initial comment — sorting by `ds` in the subquery and then applying the `COUNT` in the outer query.
However, this approach might lead to incorrect results.
Let's revisit your last screenshot. The value `1875` indicates the highest value from `1989` to `2004` (the first data slice), and the corresponding dimension value is `1990`. However, the shifted time data slice does not contain data for `1980`, so a left join will return a null. The **KEY** point is why `1980` is not in the second data slice? It's because it's not in the TopN series in the shifted data slice. If you don't use time comparison in Explore and directly select a time range, 1980 will still not be in the result set.
If we automatically apply the `ds` in the subquery, `1980` will appear in the result set when building the time comparison. However, if we directly pick a time range, 1980 should disappear again.
--
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Time comparison does not always capture the correct data when the result set is limited [superset]
Posted by "zhaoyongjie (via GitHub)" <gi...@apache.org>.
zhaoyongjie commented on issue #27504:
URL: https://github.com/apache/superset/issues/27504#issuecomment-2004460578
>
> 1. Not all databases support CTE
> 2. Not all databases support JOINS
>
It's not only compatibility about CTE and JOIN, but also how to how to define time delta in different DB.
--
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Time comparison does not always capture the correct data when the result set is limited [superset]
Posted by "eschutho (via GitHub)" <gi...@apache.org>.
eschutho commented on issue #27504:
URL: https://github.com/apache/superset/issues/27504#issuecomment-2000429878
Hi, thanks @zhaoyongjie. Apologies, but the last query that I showed as the correct example I should have sorted by count instead of by date. That may be where the confusion came from. I updated it in my example. But essentially, I'm looking to sort by the count, so in this case in 1995 there were 12 instances, and that row should be first. I want the top ten years where the name had the highest count.
--
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Time comparison does not always capture the correct data when the result set is limited [superset]
Posted by "zhaoyongjie (via GitHub)" <gi...@apache.org>.
zhaoyongjie commented on issue #27504:
URL: https://github.com/apache/superset/issues/27504#issuecomment-2002415721
> so in this case in 1995 there were 12 instances, and that row should be first. I want the top ten years where the name had the highest count. This is the corrected version:
it should be another topic that how to sort the result set after fetched the data from database, there is a `sort` operator in the post processing for it.
Your SQL have some issues:
1. The `order by` and `limit` clause should combine together always, it means getting "top N" records from the table. The subquery from your post didn't provide limit clause.
2. The SQL and the user interface are inconsistent, "top ten years" was selected in the UI, then the SQL clause should be `order by ds limit 10` not only in subquery, but also in the outer query.
As the mention before, if you really need a secondary sort by, the `sort` operator might help.
--
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Time comparison does not always capture the correct data when the result set is limited [superset]
Posted by "zhaoyongjie (via GitHub)" <gi...@apache.org>.
zhaoyongjie commented on issue #27504:
URL: https://github.com/apache/superset/issues/27504#issuecomment-2004930524
@eschutho
This behavior is totally by design, the SQL from your second screen to a plain speaking is that "show me the top 10 results sort by birth number and descending",
The 10th record in the first data slice(1979-2004) might be 1994 or 1995, because they have same count:
```text
ds count
1994-01-01 19
```
**or**
```text
ds count
1995-01-01 19
```
The 10th record in the second data slice(1969-1994) is :
```text
ds count
1985-01-01 10
```
Since the 10th record in the first data slice is unpredictable, you also can't predicate the results of join in the 10th row. Apparently, your database returns `1995-01-01` as the 10th record, the left join will give a null.
--
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Time comparison does not always capture the correct data when the result set is limited [superset]
Posted by "eschutho (via GitHub)" <gi...@apache.org>.
eschutho commented on issue #27504:
URL: https://github.com/apache/superset/issues/27504#issuecomment-2004666155
@zhaoyongjie I was trying this out with a name with more results. Do you think this is expected behavior if I were to fetch 50 rows sorted by count vs 10 rows? It seems that the data should be consistent if I put a row limit on it. WDYT?
<img width="1279" alt="Screenshot 2024-03-15 at 1 50 42 PM" src="https://github.com/apache/superset/assets/5186919/fd4e9f4a-2219-4f60-9dcd-c663214c86cc">
<img width="1279" alt="Screenshot 2024-03-15 at 1 50 28 PM" src="https://github.com/apache/superset/assets/5186919/43b5b304-6d6c-48ec-9726-da48b164865a">
--
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Time comparison does not always capture the correct data when the result set is limited [superset]
Posted by "eschutho (via GitHub)" <gi...@apache.org>.
eschutho commented on issue #27504:
URL: https://github.com/apache/superset/issues/27504#issuecomment-2010935507
Thanks @zhaoyongjie! I agree on your assessment of why we're missing data, but quick question on this:
>It's because it's not in the TopN series in the shifted data slice.
Do you think by putting the limit on the _outer_ scope _after_ the join, we can solve this?
>I also consider this issue, I don't know if should provide a way to tell user should use a time column as ORDER BY when they want to construct a time comparison?
I had a similar question on the example [here](https://github.com/apache/superset/pull/27545) and maybe this is at the crux of what we're trying to solve: the use case where someone is not wanting the time dimension as the order or group by. It seems like a common use case to sort by top 10 names or want to group by product type rather than date. How can we do this, and do you think the join query could solve both these problems?
--
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Time comparison does not always capture the correct data when the result set is limited [superset]
Posted by "zhaoyongjie (via GitHub)" <gi...@apache.org>.
zhaoyongjie commented on issue #27504:
URL: https://github.com/apache/superset/issues/27504#issuecomment-1998016219
> In the above example, I am trying to fetch the top 10 years from the years 1989 - 2004 where the name Alex was the most popular and compare that count to the period 10 years prior.
@eschutho I think you mislead the "TOK N" analytics meaning, and what is "order by" and "limit" clause in SQL meaning.
The first screenshot in the explore page will ask _sort by the **count**, and then only retrieve the top 10 rows_. If you really want to fetch the top 10 years, you should set `ds` as order by.
<img width="1531" alt="image" src="https://github.com/apache/superset/assets/2016594/32ab30cb-f9e4-4443-ba89-2183f4daa34e">
--
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Time comparison does not always capture the correct data when the result set is limited [superset]
Posted by "zhaoyongjie (via GitHub)" <gi...@apache.org>.
zhaoyongjie commented on issue #27504:
URL: https://github.com/apache/superset/issues/27504#issuecomment-2004450781
Upon rereading this issue, I believe the confusion lies in understanding the 'limit' control and the 'sort by' control in the UI, as well as how to apply them in SQL.
1. The value of the 'limit' control is 10, and the 'sort by' control is 'DS'. This indicates sorting by the 'DS' column and then returning the top 10 records.
2. The value of the 'limit' control is 10, and the 'sort by' control is 'count'. This indicates sorting by the 'count' column (metric) and then returning the top 10 records.
The logic is included in all visualizations. Currently, there isn't a control to specify how to sort a column as a secondary sort by. In other words, Superset doesn't have a way to **define sorting by DS and returning the top 10, then sorting by count and returning the top 10**. I also posted the [result](https://github.com/apache/superset/issues/27504#issuecomment-1998016219) in the comment. The result remains the same whether you use Pandas join or SQL join.
--
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Time comparison does not always capture the correct data when the result set is limited [superset]
Posted by "eschutho (via GitHub)" <gi...@apache.org>.
eschutho closed issue #27504: Time comparison does not always capture the correct data when the result set is limited
URL: https://github.com/apache/superset/issues/27504
--
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] Time comparison does not always capture the correct data when the result set is limited [superset]
Posted by "eschutho (via GitHub)" <gi...@apache.org>.
eschutho commented on issue #27504:
URL: https://github.com/apache/superset/issues/27504#issuecomment-2096644228
Fix for this is in https://github.com/apache/superset/pull/27718/files#diff-d3bf055ecf6a74aa0acbb0650d176b6c251aea7796543f77a2df3fd8b7e4c4b4
```
df = dataframe_utils.full_outer_join_df(
left_df=df,
right_df=offset_df,
rsuffix=R_SUFFIX,
)
```
--
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org