Sqlitebrowser: Data Browser not using all parts of Composite Primary Key on non-ROWID table

[aneroid]

I've got a table with a Composite PK comprising of 3 columns PRIMARY KEY(`REPORT_DATE`,`PRODUCT`,`TID`) and it's done as a WITHOUT ROWID table, since that provides no value in my case.

When I'm browsing the table's data, the SQL Log shows it as:

SELECT `REPORT_DATE`,* FROM `tablename` ORDER BY `REPORT_DATE` ASC LIMIT 0, 50000;

This should be doing the SELECT using all 3 parts of the PK as well as for the ORDER BY clause:

SELECT `REPORT_DATE`,`PRODUCT`,`TID`,* FROM `tablename` ORDER BY `REPORT_DATE` ASC, `PRODUCT` ASC, `TID` ASC LIMIT 0, 50000;

Even more horrifyingly, when I update data in column X of one row via the data browser, it only uses that first PK column REPORT_DATE in its query. So all rows (45k) with that date have the modified data for the column X where I put the change. :scream:

UPDATE `tablename` SET `COL_X`=? WHERE REPORT_DATE='2016-05-04';

I've also tested 'Delete Record' and it does the same thing - only using the first column of the PK, so all the rows with that date got deleted. (Yay for no auto commit.)

DELETE FROM `tablename` WHERE `REPORT_DATE` IN ('2016-05-04');

As an aside, there's also a Performance issue: the data browser seems really really slow (53 secs) compared to if I execute either of the 2 SELECT's above (1.1 secs) in the 'Execute SQL' tab.

I'm opening this issue because:

• [ ] DB4S is crashing
• [x] DB4S has a bug
• [ ] DB4S needs a feature
• [ ] DB4S has another problem

I'm using DB4S on:

• [x] Windows: ( _version:_ _7_ )
• [ ] Linux: ( _distro:_ ___ )
• [ ] Mac OS: ( _version:_ ___ )
• [ ] Other: ___

I'm using DB4S version:

• [x] 3.10.0-beta1
• [ ] 3.9.1
• [ ] Other: ___

I have also:

• [ ] Tried out the latest nightly version: https://github.com/sqlitebrowser/sqlitebrowser#nightly-builds
• [x] Searched for an existing similar issue: https://github.com/sqlitebrowser/sqlitebrowser/issues?utf8=%E2%9C%93&q=is%3Aissue%20

[justinclift]

Ouch, that does seem like it'll be a release blocker for 3.10.0. :wink:

@MKleusberg This is one for you. 😀

[MKleusberg]

I'm afraid this will be one for 3.11 😦 We've never really had support for composite keys and it will require some refactoring throughout the code. For 3.10 we already did some preparations for this but unfortunately all the code for the interaction between the UI and our database backend needs to be adjusted for this. That would be too much for 3.10 but I can make that one of the top priorities for 3.11. It's a really annoying bug to have :wink:

[k-j-kleist]

Seems related to #516

[MKleusberg]

This should be solved since a couple of months and is confirmed working in #516 :wink: Sorry for not updating this issue. I seem to forgot about this :frowning: But if you are still interested in this, you can download the nightly build here which doesn't have this problem anymore, @aneroid.