Bug 298197 - Kexi crashes while trying to save query with join on non PK fields
Summary: Kexi crashes while trying to save query with join on non PK fields
Status: CLOSED FIXED
Alias: None
Product: KEXI
Classification: Unclassified
Component: Queries (show other bugs)
Version: 2.4.0
Platform: Archlinux Packages Linux
: NOR major (vote)
Target Milestone: ---
Assignee: Jarosław Staniek
URL:
Keywords:
: 298294 (view as bug list)
Depends on:
Blocks:
 
Reported: 2012-04-15 18:00 UTC by Werner Llacer
Modified: 2012-08-11 11:33 UTC (History)
1 user (show)

See Also:
Latest Commit:
Version Fixed In:


Attachments
Sample database (11.00 KB, application/octet-stream)
2012-04-15 18:01 UTC, Werner Llacer
Details
DrKonqi trace of the error (90.88 KB, application/x-gzip)
2012-04-15 18:06 UTC, Werner Llacer
Details
Corrected database schema (16.00 KB, application/x-kexiproject-sqlite)
2012-04-20 23:12 UTC, Dimitrios T Tanis
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Werner Llacer 2012-04-15 18:00:13 UTC
User-Agent:       Mozilla/5.0 (X11; Linux i686; rv:11.0) Gecko/20100101 Firefox/11.0
Build Identifier: 

Seems a behaviour similar as bug 273151 but as it is now closed ...
I can create and save a query ONCE.
When try to modify it , i can run and get the results of the change, but when i try to save it kexi crashes.
This behaviour is database independent. The databases i try are file databases (an sqlite i suppose) 

Sadly there is no way I can get drkonqi not looping 

Reproducible: Always

Steps to Reproduce:
1. I've prepared a sample database (11 K) with two tables "base" and "codigo"  (btw. how do I send it?)
2 I've created a simple query on base named "consulta1"
3. I open the query in edit mode and add a relationshio of "base.campo2" to "codigos.codigo" (N:1) and include "codigos.descripcion" into the query (a simple decoding operation)
4. I run it perfectly
5 I return to design mode
6 Click save and

Actual Results:  
kexi crashes

Expected Results:  
query should be saved

My Archlinux config today is
Kernel   3.3.1-1-ARCH #1 SMP PREEMPT  i686
Qt  4.8.1-1
Kdelibs 4.8.2-1
kexi  2.4.0-1
sqlite 3.7.11-2

(The severity is how i view the problem in order to use it, and I badly need such a tool ..)
Comment 1 Werner Llacer 2012-04-15 18:01:36 UTC
Created attachment 70407 [details]
Sample database
Comment 2 Werner Llacer 2012-04-15 18:06:45 UTC
Created attachment 70408 [details]
DrKonqi trace of the error
Comment 3 Dimitrios T Tanis 2012-04-20 22:53:25 UTC
*** Bug 298294 has been marked as a duplicate of this bug. ***
Comment 4 Dimitrios T Tanis 2012-04-20 23:12:34 UTC
Created attachment 70545 [details]
Corrected database schema
Comment 5 Dimitrios T Tanis 2012-04-20 23:23:07 UTC
Hi Werner.
First of all thank you for your elaborate report. Providing a good report is essential so that we can solve issues as they come in.

I could reproduce the crash with the samples you have provided. It is caused when you join fields that are not Primary Keys. For some reason this only happens when you Design the query > Switch to design view > Switch back to design View > Hit Save button.

For a temporary workaround you could design your query in Design Mode > Save and then run the query. That is Save the query BEFORE you go to the Data View.

Further more I would like to recommend you to take another look at your database schema. Having PKs and joining on other fields violates normalization rules and makes PKs redundant (With some exceptions). To better explain for the sample database you provided:

Tables named codigos and codigos2 are lookup tables. That is they provide values that fields in other tables look up. These are defined OK and they have a incremental PK and other fields.

The base table is where you have the problem. Fields Campo2 and Campo3 are lookup columns. That is columns that lookup data from other tables. To make your database design efficient in both these columns you would use Primary Key values from tables codigos and codigos2 and NOT values from other cells. That would make them Foreign Keys. Then when you design a query you would make a relationship between a PK (id from codigos) and a FK (Campo2 from Base).

Please study the attached database named "Corrected database schema" and the table base_new to understand how the table should be constructed and then check the consulta2_new query to see how that works nicely now. At the table design please check the fields' comments and note that the real values in the table are PK values from tables codigos and codigos2. The data you want to see is set from the "Lookup column" tab (next to the properties tab).

I hope you get the best out of Kexi.
Comment 6 Dimitrios T Tanis 2012-04-20 23:25:19 UTC
*** bug 298294 has been marked as a duplicate of this bug ***
Comment 7 Werner Llacer 2012-04-21 08:55:20 UTC
Thanks for the reply.
 I'm  at the moment at sick bed and have just the time to check in. So i hope to give you a detailed reply next week.

BTW: I've spent the last 20 something years going back and forth from DBA to sysmgr position on mainframe environments (DB2, Adabas on Z/OS or Oracle under Unix). Don't tell me about DB Design and weird necessities ..
Comment 8 Jarosław Staniek 2012-04-21 09:35:35 UTC
Werner, regardless the normalization rules, Kexi should work OK for the relation you specified, so we want to fix this bit of course.
Comment 9 Werner Llacer 2012-04-22 19:13:33 UTC
You provided my brand new scar its first good laugh. It did hurt. You had to pay for it ;-)

Seriously, I really find it has been a sensible idea to provide the educative bit about lookup tables and foreing keys, virtually lingo-free (most desktop DB users i've met, don't have the slightest exposure to relational design). I might be not the target, but it is really a good idea 

Anyway, (per other bug i opened last week) you can only save a query once, so i will try to stick to the workaround (while very much against my normal usage) till you can fix it


Why  I'm using non keyed lookup tables, then? A bit of background -and self justifiying-is not out of place.

The database i'm testing Kexi with is a third party data warehouse  (detailed Spanish Municipal Elections Results from 2011 if you ever want to know), originally in MDB, and with a star schema, very odd on first sight. 
In a couple of the lookup tables the natural key is a list of fields, defining a fixed length hierachy, and lookup can be done on any level of the hierarchy. In such an scenario, significant primary keys are useless or even impossible:depending on value spread,the best design query-wise could be to nullify all component fields thus making a significant primary key inacceptable for any SQL database.

It is asumed that no self respecting designer would accept a non significative value in a lookup field (exactly this is the difference with Data Model Relationship derived foreign keys). To have a real code in the record has saved many the day ...

An intersting sample is Oracle's smart CG_REF_CODES table for Designer/Forms. A single table designed to hold  lookup tables, including time constrained or intervals. The internal PK has absolutely nothing to do with the field value.
Comment 10 Jarosław Staniek 2012-04-22 19:25:18 UTC
Dear Werner, thanks for the explanation and the background. I propose to move such supervaluable discussion to http://forum.kde.org/viewforum.php?f=220 is that makes sense for you, so others can contribute their use cases.

As an update: I think I am close to fixing this really not obvious bug (it really hurt in many other use cases, not just saving).
Comment 11 Dimitrios T Tanis 2012-04-22 22:32:05 UTC
Hi Werner.
I'd like to apologize if I sounded too prickly. 
Many times users coming here have pretty much non existent knowledge of RDBMS and we try to put their efforts in creating a database to a good start. 

Providing the explanation really gives now a use case that represents a valid scenario and thus exposes a bug otherwise would not be given much attention at first (I bet you've seen bad db schemas yourself).

Thank you for the report and keep them coming. :)
Comment 12 Werner Llacer 2012-04-23 16:25:11 UTC
Jaroslav. Of course you can move what you think interesting to the forum. I will register there ASAP.
Is really good to hear you have the bug cornered so soon.
Dimitrios. I thought it was me who was being too harsh ...

It is really a pleasure to work for you both.
Comment 13 Jarosław Staniek 2012-04-30 22:21:11 UTC
Hi Werner, the bug is almost fixed now. It would appear in many unexpected cases. I expect to have solution this week.
Comment 14 Jarosław Staniek 2012-05-02 22:06:45 UTC
Git commit 8a00c61dbee111f1522d71e84541e5bf7a37221f by Jaroslaw Staniek.
Committed on 03/05/2012 at 00:04.
Pushed by staniek into branch 'kexi-features-staniek'.

KexiDB: fix crashed when saving query design

*put DESC/ASC after COLLATE section, not before (needed at least for sqlite driver)
*fixed crash because of invalid deletion order of query structures
*fixed crash because of invalid deletion relationship structures

M  +7    -0    kexi/doc/dev/CHANGELOG-Kexi-js
M  +22   -18   kexi/kexidb/queryschema.cpp
M  +1    -1    kexi/kexidb/relationship.h
M  +6    -1    kexi/plugins/queries/kexiquerydesignerguieditor.cpp

http://commits.kde.org/calligra/8a00c61dbee111f1522d71e84541e5bf7a37221f
Comment 15 Jarosław Staniek 2012-05-02 22:40:46 UTC
Please test...
Comment 16 Jarosław Staniek 2012-05-06 19:21:22 UTC
Git commit 0fefd8dbbe333000ab27dcf91c345863a39751d9 by Jaroslaw Staniek.
Committed on 03/05/2012 at 00:04.
Pushed by staniek into branch 'master'.

KexiDB: fix crashed when saving query design

*put DESC/ASC after COLLATE section, not before (needed at least for sqlite driver)
*fixed crash because of invalid deletion order of query structures
*fixed crash because of invalid deletion relationship structures

REVIEW:104833

M  +7    -0    kexi/doc/dev/CHANGELOG-Kexi-js
M  +22   -18   kexi/kexidb/queryschema.cpp
M  +1    -1    kexi/kexidb/relationship.h
M  +6    -1    kexi/plugins/queries/kexiquerydesignerguieditor.cpp

http://commits.kde.org/calligra/0fefd8dbbe333000ab27dcf91c345863a39751d9
Comment 17 Jarosław Staniek 2012-05-07 06:36:18 UTC
Git commit 85790700a0ab6cc15cea2dc9a09d23594f2f4ee4 by Jaroslaw Staniek.
Committed on 03/05/2012 at 00:04.
Pushed by staniek into branch 'calligra/2.4'.

KexiDB: fix crashes when saving query design

*put DESC/ASC after COLLATE section, not before (needed at least for sqlite driver)
*fixed crash because of invalid deletion order of query structures
*fixed crash because of invalid deletion relationship structures

REVIEW:104833

M  +7    -0    kexi/doc/dev/CHANGELOG-Kexi-js
M  +22   -18   kexi/kexidb/queryschema.cpp
M  +1    -1    kexi/kexidb/relationship.h
M  +6    -1    kexi/plugins/queries/kexiquerydesignerguieditor.cpp

http://commits.kde.org/calligra/85790700a0ab6cc15cea2dc9a09d23594f2f4ee4
Comment 18 Jarosław Staniek 2012-06-27 11:28:42 UTC
Git commit 981708e44f67b64da98ca86e4982d2a2545a69b2 by Jaroslaw Staniek.
Committed on 27/06/2012 at 13:05.
Pushed by staniek into branch 'master'.

Fix crashes when saving query design

*(2012-05-03 calligra master commit 0fefd8dbbe33)

M  +19   -18   Predicate/QuerySchema.cpp
M  +1    -1    Predicate/Relationship.h

http://commits.kde.org/predicate/981708e44f67b64da98ca86e4982d2a2545a69b2