[odb-users] How to change the join type of relations in a view

[علی دانش]

Hi Boris,

Could you give me any answers.
Am I right?
Do you have any plan to support it?
Is there any workaround?

Thanks,
Ali
________________________________________
From: علی دانش
Sent: Tuesday, July 5, 2016 4:32 PM
To: odb-users at codesynthesis.com
Subject: Re: [odb-users] How to change the join type of relations in a view

Hi Boris,

The join type has effects on the opimization of queries.
---
https://www.sqlite.org/optoverview.html
Section "6.1 Order of tables in a join" says:
      Inner joins can be freely reordered. However a left outer join is neither commutative nor associative and hence will not be reordered. Inner joins to the left and right of the outer join might be reordered if the optimizer thinks that is advantageous but the outer joins are always evaluated in the order in which they occur.
---
In my case, there is a big difference in perforamnce when I use inner join instead of left join. Using inner join has one limitation, because we can't have null pointers in the list of related objects; but in my case, this feature isn't needed.

Best regrards,
Ali
________________________________________
From: Boris Kolpackov <boris at codesynthesis.com>
Sent: Tuesday, July 5, 2016 3:19 PM
To: علی دانش
Cc: odb-users at codesynthesis.com
Subject: Re: [odb-users] How to change the join type of relations in a view

Hi Ali,

adanesh at noornet.net writes:

>     r += "FROM \"TagIndex\"";
>
>     r += " INNER JOIN \"SubjectIndex_tags\" ON";
>     r += "\"SubjectIndex_tags\".\"value\"=\"TagIndex\".\"id\"";
>
>     r += " LEFT JOIN \"SubjectIndex\" ON";
>     r += "\"SubjectIndex_tags\".\"object_id\"=\"SubjectIndex\".\"id\"";

Object relationships are always translated to LEFT JOIN, there is no
way to change that and I believe there should be no need either. Can
you show a situation where changing it to INNER JOIN will make a
difference?

Boris