A more detailed example:
The query below:
select i.key, c.field, c.created , c.fromvalue , c.tovalue from issues i,
issuechanges c where i.jql='project=DPO' and c.field = 'Sprint' and
c.issueid = i.id
has the following execution plan:
SELECT
I.KEY,
C.FIELD,
C.CREATED,
C.FROMVALUE,
C.TOVALUE
FROM PUBLIC.ISSUES I
/* function: JQL = 'project=DPO' */
/* WHERE I.JQL = 'project=DPO'
*/
INNER JOIN PUBLIC.ISSUECHANGES C
/* function: ISSUEID = I.ID
AND FIELD = 'Sprint'
*/
ON 1=1
WHERE (C.ISSUEID = I.ID)
AND ((I.JQL = 'project=DPO')
AND (C.FIELD = 'Sprint'))
Notice please that the execution order of the tables ISSUES -> ISSUECHANGES
above is reversed if a new condition is added:
select i.key, c.field, c.created , c.fromvalue , c.tovalue from issues i,
issuechanges c where i.jql='project=DPO' and c.field = 'Sprint' and
c.issueid = i.id and c.created < now()
Execution plan:
SELECT
I.KEY,
C.FIELD,
C.CREATED,
C.FROMVALUE,
C.TOVALUE
FROM PUBLIC.ISSUECHANGES C
/* function: CREATED < NOW()
AND FIELD = 'Sprint'
*/
/* WHERE (C.FIELD = 'Sprint')
AND (C.CREATED < NOW())
*/
INNER JOIN PUBLIC.ISSUES I
/* function: ID = C.ISSUEID
AND JQL = 'project=DPO'
*/
ON 1=1
WHERE (C.CREATED < NOW())
AND ((C.ISSUEID = I.ID)
AND ((I.JQL = 'project=DPO')
AND (C.FIELD = 'Sprint')))
How could the initial execution order be preserved?
Thanks in advance!
Pablo.
On Tuesday, June 23, 2015 at 4:06:18 AM UTC+2, Pablo Beltran wrote:
>
> Hi,
>
> Sometimes the execution order of tables can vary a lot by add-in a simple
> condition.
>
> select * from
> tableA a
> INNER JOIN
> tableB b on b.id=a.id
> WHERE
> ..
>
> depending on the where conditions tableA is invoked first or not.
>
> How to force H2 to use tableA prior tableB always?
>
> Thanks!
> Pablo.
>
>
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
