Line Chart Groups Don't Auto Sort Correctly

[brad-natelborg]

Issue Summary

I'm using the line chart to graph ~100 or so lines on one graph. I'm grouping them by a string which is of the format "012345 || This is a String". See picture below.

I have a filter set up to automatically only show me the last week of data. Which means every day when I look at this graph, it can show me different data, since we don't always have the same ~100 devices for the last week (they only report to the cloud once a week).

Long story short, every day when I go to the page, the graph does not auto sort the "Group By" column correctly automatically. You can see in the above picture, that it goes from 010029 to 010070, but there are actually 01003X values lower down. To fix this, I can go into the query and click "edit visualization" and then "save", at which point it magically sorts correctly again. But I need to do this manually every day if I want to see the correct sort order on the right side of the chart.

See correct sorting below.

Steps to Reproduce

See description above. Visualization settings are below.

I would expect the chart to automatically update and sort in ascending order correctly, but it only sorts correctly when I manually go into the visualation menu and then click "save" again.

I think the important piece is that every day I look at the chart, it will show a different set of ~100 lines due to the nature of the data. So the chart should re-do the sort every time it loads.

Technical details:

• Redash Version: 7.0.0
• Browser/OS: Chrome

[kravets-levko]

Hi @brad-natelborg! If you didn't set order of series explicitly on Series tab, Redash will not sort groups created by Order by option - it uses order of records in query result (if you have, say, records A and B both in a different group - if A comes first - it's group will be first; if B comes first - it's group will be first, and so on). Try to add ORDER BY to your query - it should help.

[brad-natelborg]

Thanks for quick response!

@kravets-levko We do have the ORDER BY in our query already, set to order by the name, which is the "012345 || string" name. Is that what you're suggesting?

[brad-natelborg]

Here's our query for reference:

SELECT liters,
DATEADD(HOUR,hour_of_day,gmt_datetime) AS dtm,
qr_code + ' || ' + partner_water_point_name AS new_name
FROM hourly_logs
WHERE partner_country = '{{ country }}'
AND partner_water_point_name <> ''
AND qr_code LIKE '01%'
AND dtm BETWEEN DATEADD(d, -{{ duration }}, '{{ end }}') AND '{{ end }}'
ORDER BY new_name ;

[kravets-levko]

Is that what you're suggesting?

Yes, exactly. And it's strange that it doesn't work. Thank you for reporting this, I'll check ASAP

[brad-natelborg]

Any update on this? @kravets-levko

[kravets-levko]

Hi @brad-natelborg! Sorry, I tried a lot but I cannot reproduce this bug 😞 Also, I checked related code but didn't find anything that may cause this bug. So I have to ask you for more info:

1. please check your data: ensure that new_name field is properly sorted. Try to find differences between cases when chart is displayed correctly and when it's not;
2. if it's possible - prepare a demo which can help me reproduce this bug (you can do it on our preview instance if you want, or add that demo dataset to this issue);
3. if it's possible - try to reproduce it in newer Redash version (v8 or latest).

Thanks you!

[kravets-levko]

One more thing: can you please show me a screenshot from Series tab?

[brad-natelborg]

@kravets-levko Thanks for looking into it more. It is quite strange as far as I can tell.

If I go into the "view query" view, my data is still showing incorrectly, as shown below.

But when I click "edit visualization", literally it is ordered immediately in that view. It's almost as if by clicking that button it is triggering it to be resorted correctly. See view below once I click "edit visulation":

I am definitely ordering by new_name, there's nothing more I can do there. I tried sorting by descending/ascending with no change. It may be worth noting that the column "new name" is a string that is created by combining an integer + string field.

On the series tab, it is always sorted correctly. See image below. But again, any time I try to edit the visualization it fixes itself.