Repository navigation
Plot only allows Axis Type = Numeric #2286
Description
Details for the issue
What did you do?
Attempted to plot results from a SQL statement.
What did you expect to see?
In the previous release, I was able to display my record results with text labels as a bar chart
What did you see instead?
The Axis Type for all columns is set to Numeric and cannot be changed.
Useful extra information
The info below often helps, please fill it out if you're able to. :)
What operating system are you using?
- Windows: ( _version:10 )
- Linux: ( distro: ___ )
- macOS: ( version: ___ )
- Other: ___
What is your DB4S version?
- 3.12.0-(alpha or betas)
- 3.11.2
- 3.11.1
- 3.10.1
- Other: 3.12.0 released version
Did you also
- Try out the latest nightly version: https://github.com/sqlitebrowser/sqlitebrowser#nightly-builds
- Search for an existing similar issue: https://github.com/sqlitebrowser/sqlitebrowser/issues?utf8=%E2%9C%93&q=is%3Aissue%20
Activity
justinclift commentedon Jun 18, 2020
Out of curiosity, was the database loaded using a project file (eg it should have restored existing settings), or was it loaded directly (eg from File -> Open)? 😄
ecanfi commentedon Jun 18, 2020
Update: The plot feature works correctly when viewing tables, but when executing a sql statement in the "Execute SQL" tab, all the Axis Type fields are numeric only. Same results if loading database from "Open Database" or loading from "Open Project"
justinclift commentedon Jun 19, 2020
Interesting. Sounds like something we should fix for the upcoming 3.12.1 update. No firm plans for an ETA yet though. 😉
Bar charts did not display labels in x axis when NULL values present
mgrojo commentedon Jun 20, 2020
I couldn't reproduce the problem, but I've found a bug which wasn't present in 3.11. If there are NULL values in a column used as labels for a bar chart, the plot is done, but the labels are not displayed and the zoom is not reset. This should be fixed in tomorrow's nightly build.
Regarding your issue, I can only guess that the type detection is not working. We check the first 10 values for valid data type. If the first 10 values are numbers or NULL (even if the column affinity is string) the type for the axis is guessed as numeric. But that behaviour hasn't changed since 3.11. Does it make any sense for you? Otherwise, could you publish the table structure and data for reproducing the problem?
grumble1965 commentedon Jun 24, 2020
I think I'm seeing something similar since upgrading. Before upgrading this query showed time series plot, with version 3.12.0 I see a vertical line of data. I assume that X axis is being interpreted differently than before.
Thanks!
FYI - data is NYTimes US Covid-19 data
Schema:
CREATE TABLE ss( "date" TEXT, "state" TEXT, "fips" TEXT, "cases" TEXT, "deaths" TEXT )Select:
Sample Data:
mgrojo commentedon Jun 27, 2020
@grumble1965 I wasn't able to reproduce the problem. I couldn't get any data out of the query and the database, but the sample data is working for me:
Could you provide more data to analyse the problem, like a screenshot, more detailed steps or anything that could be useful?
justinclift commentedon Jun 27, 2020
@grumble1965 The database you created when importing the data... do you still have it around? 😄
grumble1965 commentedon Jun 28, 2020
Thanks for trying to repro this. This is not an obvious thing, but I think I can reproduce this.
The data is from https://github.com/nytimes/covid-19-data
sqlite> .import /mnt/g/Code/covid-19-data/us.csv us
sqlite> .import /mnt/g/Code/covid-19-data/us-counties.csv cc
sqlite> .import /mnt/g/Code/covid-19-data/us-states.csv ss
sqlite> delete from ss where date='date'; delete from cc where date='date'; delete from us where date='date' ;
I open DB Browser, open that database, and try a simple query - no problems and things look good (date column is flagged as type 'date'). See screen caps named 'before'.
I try increasingly complex queries, and after maybe 5 or 6 tries, the date column is now recognized as type 'numeric' and the plot is broken. See screen caps named 'after'.
Trying simpler queries doesn't change the type back to 'date', neither does closing and reopening the database. The only fix is to close and reload DB Browser.
Hope this helps!
mgrojo commentedon Jun 29, 2020
Ok, thank you. I'm reproducing it now, although in my case, a simple reexecution of the query, or pressing the button to load all data, solves the problem.
This seems a race condition, because each time the query is executed, the plot loads more or less data. When I reproduce it, only three dots are present from two different columns, and the date value seems to be 0. I guess the plot is drawn before the model has enough data.
@MKleusberg I suppose you can help with the concurrent code.
mgrojo commentedon Aug 9, 2020
@MKleusberg could you take a quick look to this? What I've seen so far is that when the SqliteTableModel::finishedFetch signal is emitted, which is connected to the plot update, the cache isn't still loaded. That means the plot dock reads data which is still in the "loading..." state (null data when read in the edit role). Then the plot has only a few elements or even in some cases, there isn't any value to make the guess of type, causing it to default to Numeric. Is it expected that finishedFetch is emitted and the cache is not yet complete?
justinclift commentedon Aug 9, 2020
@mgrojo Is there any change in the behaviour after my two changes to signal and slots related code today?
In theory I'm thinking there shouldn't be, but am mentioning just in case there's some unexpected side effect. 😉
mgrojo commentedon Aug 9, 2020
@justinclift No, I've just pulled them and the behaviour is the same 😄 This is related to the model between the table widgets (and the plot in this case) and the SQLite database.
Avoid attaching the plot when the signal is notifying the row count
Avoid attaching the plot when the signal is notifying the row count
mgrojo commentedon Aug 16, 2020
@ecanfi, we think we have fixed this issue in our development version. The fix is included in our nightly build. Would you mind installing it to confirm that we can close this issue?
Avoid attaching the plot when the signal is notifying the row count
grumble1965 commentedon Oct 1, 2020
I cannot reproduce this issue in 1.12.1-RC1.
Thank you very much!
mgrojo commentedon Oct 1, 2020
Thanks for confirming!