[Suggestion]when the query rows is bigger than 1000...

[HaveF]

The default query max rows is 1000

  max_returned_rows            Maximum rows that can be returned from a table or
                               custom query (default=1000)

However, the number of points on the chart can exceeds 1000 easily. While we have the option to increase the number of rows, it would be beneficial to display a tooltip for points that exceed this limit. This would help viewers understand that they are only seeing a subset of the query results...

If the number of query rows exceeds the maximum number of rows to be returned, a helpful hint will appear. Simply move the mouse upwards to reveal the hints. What do you think?

[rclement]

Oh good catch! Even though most charts will make use of aggregated metrics (way less than a thousand data points) this is still an issue to deal with.

I cannot find a way for queries (CSV, JSON) to return the truncation information so that charts could display a warning. Do you have some pointers?

[rclement]

Ok so using the JSON API with ?_shape=objects instead of ?_shape=array, we can extract the truncation information:

https://datasette-dashboards-demo.vercel.app/jobs.json?sql=SELECT date(date) as Date, title as Title, company as Company, source as Source, city as City, longitude, latitude FROM offers_view WHERE longitude IS NOT NULL AND latitude IS NOT NULL AND date >= date(:date_start) &date_start=2021-01-01&_shape=objects

{
    "ok": true,
    "database": "jobs",
    "query_name": null,
    "rows": [
        {
            "Date": "2021-04-21",
            "Title": "Devops Python H/F",
            "Company": "Sully Group",
            "Source": "RegionsJob",
            "City": "Aix-en-Provence",
            "longitude": 5.4451153,
            "latitude": 43.5227288
        },
        ...
    ],
    "truncated": true,
    "columns": [
        "Date",
        "Title",
        "Company",
        "Source",
        "City",
        "longitude",
        "latitude"
    ],
    "query": {
        "sql": "SELECT\n  date(date) as Date,\n  title as Title,\n  company as Company,\n  source as Source,\n  city as City,\n  longitude,\n  latitude\nFROM offers_view WHERE longitude IS NOT NULL AND latitude IS NOT NULL\n   AND date >= date(:date_start) \n  \n  \n  ",
        "params": {
            "date_start": "2021-01-01"
        }
    },
    "error": null,
    "private": false,
    "allow_execute_sql": true,
    "query_ms": 6.713455999943108
}

Charts are currently using either the CSV API or the JSON API (_shape=array and _shape=arrays) for convenience reasons. All charts would have to be converted to using exclusively the JSON API with _shape=objects to retrieve the truncation boolean and still obtain some manageable data objects from JavaScript.

However, let's keep in mind the JSON API might change with Datasette 1.0: https://github.com/simonw/datasette/milestone/7

[rclement]

As a comparison reference, here is how Metabase handles that:

• A row limit of 2000 by default for unaggregated data
• A row limit of 10000 for aggregated data
• All charts display a small warning tooltip when the limit is exceeded
• The row limit can be customized from the UI (a recent addition, it used to be hardcoded)

Here are some screenshots showing the unified warning tooltip for all chart types:

[rclement]

I'll open an issue to address this

[rclement]

Issue #144

[HaveF]

I don't know how datasettle check this. What I notice is here:

https://datasette-dashboards-demo.vercel.app/jobs?sql=SELECT+date%28date%29+as+day%2C+count%28%2A%29+as+count+FROM+offers_view+WHERE+TRUE++AND+date+%3E%3D+date%28%3Adate_start%29+++++GROUP+BY+day+ORDER+BY+day&date_start=2021-01-01

If you change the SQL, you will get:

The "more than" part is here

[rclement]

Resolved with PR #156