Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Crashes when browsing a empty view with view editing enabled #1774

Open
noenandre opened this Issue Mar 1, 2019 · 10 comments

Comments

Projects
None yet
3 participants
@noenandre
Copy link

noenandre commented Mar 1, 2019

Details for the issue

What did you do?

Create a view, open it in data Browser, activated view editing and set a pseudokey.
Open the table the view was based on, remove all records, and try to go back to the view.

What did you expect to see?

Empty view

What did you see instead?

Crash to desktop

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 pro)
  • Linux: ( distro: ___ )
  • Mac OS: ( version: ___ )
  • Other: ___

What is your DB4S version?

  • 3.11.1
  • 3.11.0
  • 3.10.1
  • Other: ___

Did you also

@justinclift justinclift added the bug label Mar 1, 2019

@justinclift

This comment has been minimized.

Copy link
Member

justinclift commented Mar 1, 2019

Thanks @noenandre. This sounds like a bug we'd better get fixed for 3.11.2. 😄

@MKleusberg

This comment has been minimized.

Copy link
Member

MKleusberg commented Mar 1, 2019

I couldn't reproduce this problem yet. Did you do anything besides unlocking the view for editing, @noenandre? And can you maybe copy the schema of the table and the view in here, if possible?

@noenandre

This comment has been minimized.

Copy link
Author

noenandre commented Mar 1, 2019

I was struggling to reproduce the error myself, so i'm not so sure what really triggers the error anymore.
A tried to create a minimum working example, but it took some fiddling around before the application crashed accessing the view.
Here are the minimum (crashing) example.
https://www.dropbox.com/sh/awst7j95fqyws6s/AAAdYS4v3PbPA1njF00B5Azua?
The view is the active window, so the application crashes shortly after opening the project file.

@noenandre

This comment has been minimized.

Copy link
Author

noenandre commented Mar 1, 2019

Or recreate the database with:

BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "records" (
	"Nummer"	INTEGER,
	"Key"	TEXT,
	"Value"	INTEGER,
	PRIMARY KEY("Nummer","Key")
);
CREATE TABLE IF NOT EXISTS "my_table" (
	"Nummer"	INTEGER,
	PRIMARY KEY("Nummer")
);
CREATE VIEW "pivot" AS
SELECT 
	my_table.Nummer,
	SUM(CASE WHEN "key" = 'attribute1' THEN "Value" ELSE 0 END) AS "attribute1",
	SUM(CASE WHEN "key" = 'attribute2' THEN "Value" ELSE 0 END) AS "attribute2"
from "my_table"
left join "records"
GROUP BY my_table.Nummer;
CREATE TRIGGER "my_trigger" INSTEAD OF UPDATE ON "pivot"
BEGIN
  INSERT INTO records("Nummer", "Key", "Value")
	VALUES (new.Nummer, 'attribute1', new."attribute1")
  ON CONFLICT ("Nummer", "Key")
	DO UPDATE SET "Value"=excluded."Value";
  INSERT INTO records("Nummer", "Key", "Value")
	VALUES (new.Nummer, 'attribute2', new."attribute2")
  ON CONFLICT ("Nummer", "Key")
	DO UPDATE SET "Value"=excluded."Value";
END
COMMIT;

save as test.db and open it with the following .sqbpro project file:

<?xml version="1.0" encoding="UTF-8"?>
<sqlb_project>
    <db path="test.db" foreign_keys="1" case_sensitive_like="0" temp_store="0" wal_autocheckpoint="1000"
        synchronous="2"/>
    <attached/>
    <window>
        <current_tab id="1"/>
    </window>
    <tab_structure>
        <column_width id="0" width="300"/>
        <column_width id="1" width="0"/>
        <column_width id="2" width="100"/>
        <column_width id="3" width="2305"/>
        <column_width id="4" width="0"/>
        <expanded_item id="0" parent="1"/>
        <expanded_item id="1" parent="1"/>
        <expanded_item id="2" parent="1"/>
        <expanded_item id="3" parent="1"/>
    </tab_structure>
    <tab_browse>
        <current_table name="pivot"/>
        <default_encoding codec=""/>
        <browse_table_settings>
            <table schema="main" name="my_table" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="">
                <sort/>
                <column_widths/>
                <filter_values>
                    <column index="1" value=""/>
                </filter_values>
                <display_formats/>
                <hidden_columns/>
                <plot_y_axes/>
            </table>
            <table schema="main" name="pivot" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="Nummer">
                <sort>
                    <column index="3" mode="1"/>
                </sort>
                <column_widths/>
                <filter_values/>
                <display_formats/>
                <hidden_columns/>
                <plot_y_axes/>
            </table>
            <table schema="main" name="records" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="">
                <sort>
                    <column index="3" mode="1"/>
                </sort>
                <column_widths/>
                <filter_values/>
                <display_formats/>
                <hidden_columns/>
                <plot_y_axes/>
            </table>
        </browse_table_settings>
    </tab_browse>
    <tab_sql>
        <sql name="SQL 1"></sql>
        <current_tab id="0"/>
    </tab_sql>
</sqlb_project>

MKleusberg added a commit that referenced this issue Mar 6, 2019

Fix possible crash when sorted column does not exist anymore
When sorting a table or a view by a column, then removing enough columns
from that table, that view, or the underlying table of the view so that
the column index get out of bound, and then going back to browse that
table the application crashes. This commit makes sure to ignore such
columns which would cause a crash.

See issue #1774.
@MKleusberg

This comment has been minimized.

Copy link
Member

MKleusberg commented Mar 6, 2019

Thank you very much, @noenandre! The project file really did help a lot 👍

I think I have found the problem and fixed it. Can you check with tomorrow's nightly build to confirm it's working for you as well? By the way, the issue I found depends on sorting the view. When you remove all records from the table, the view obviously has no data. But we need some data to figure out how many columns a view has. So a view without data basically has zero columns for us. The bug was in the sorting: When you have sorted the view by, let's say, the third column, then delete all the data so the view has no columns at all, we still tried to sort it by the third column. This in effect led to the crash.

@justinclift

This comment has been minimized.

Copy link
Member

justinclift commented Mar 6, 2019

Seems like the right kind of thing for v3.11.x branch?

@MKleusberg

This comment has been minimized.

Copy link
Member

MKleusberg commented Mar 6, 2019

Definitely 😄

MKleusberg added a commit that referenced this issue Mar 6, 2019

Fix possible crash when sorted column does not exist anymore
When sorting a table or a view by a column, then removing enough columns
from that table, that view, or the underlying table of the view so that
the column index get out of bound, and then going back to browse that
table the application crashes. This commit makes sure to ignore such
columns which would cause a crash.

See issue #1774.
@MKleusberg

This comment has been minimized.

Copy link
Member

MKleusberg commented Mar 6, 2019

Turns out we cannot cherry-pick this one to the v3.11.x branch because the code has changed completely between the two branches. No two lines look alike anymore. Apparently the only thing which has not changed is this bug. But I have rewritten the fix for v3.11.x now. So it should be fixed there too 😄

@justinclift

This comment has been minimized.

Copy link
Member

justinclift commented Mar 6, 2019

Heh Heh Heh. Good stuff @MKleusberg. 😄

@justinclift justinclift referenced this issue Mar 6, 2019

Closed

3.11.2 - outstanding pieces #1773

13 of 13 tasks complete
@noenandre

This comment has been minimized.

Copy link
Author

noenandre commented Mar 8, 2019

Everything works well in today's nightly build.
Good to hear that i could be of help, even if my initial diagnosis was't right.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.