dynamobi/dynamobi — Issue #540: [FRG-332] Intermittent failure in farrago/unitsql/optimizer/hashexe.sql
Author: dynamobi-build
Created: 2011-12-30 03:34:13+00:00
State: closed
Labels: FRG

[reporter="jhyde", created="Fri, 15 Aug 2008 12:22:54 -0500 (GMT-05:00)"]
The hashexe test occasionally produces a different plan. Suspect that there is some non-determinacy in the planner.

Here's the diff. Full log files attached.

*** hashexe.log	Fri Aug 15 11:51:37 2008

--- hashexe.ref	Fri Aug 15 11:51:49 2008

***************

*** 287,316 ****

>

> -- four joins

> explain plan for

> select * from lhxemps, lhxdepts, lhxdepts2, lhxdepts3, lhxdepts4

> where lhxemps.deptno = lhxdepts.deptnoB and lhxemps.deptno = lhxdepts2.deptnoC

> and lhxemps.deptno = lhxdepts3.deptnoF and lhxemps.deptno = lhxdepts4.deptnoG

> order by empno, ename;

'column0'

'FennelToIteratorConverter'

' FennelSortRel(key=[[0, 1]], discardDuplicates=[false])'

! ' FennelReshapeRel(projection=[[0, 1, 2, 5, 6, 7, 8, 3, 4, 9, 10]], outputRowType=[RecordType(INTEGER NOT NULL EMPNO, VARCHAR(40) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" ENAME, INTEGER DEPTNO, INTEGER DEPTNOA, INTEGER DEPTNOB, INTEGER DEPTNOC, INTEGER DEPTNOD, INTEGER DEPTNOE, INTEGER DEPTNOF, INTEGER DEPTNOG, INTEGER DEPTNOH) NOT NULL])'

! ' LhxJoinRel(leftKeys=[[2]], rightKeys=[[0]], joinType=[INNER])'

'LhxJoinRel(leftKeys=[[2]], rightKeys=[[0]], joinType=[INNER])'

' LhxJoinRel(leftKeys=[[2]], rightKeys=[[1]], joinType=[INNER])'

! ' LhxJoinRel(leftKeys=[[2]], rightKeys=[[1]], joinType=[INNER])'

! ' LcsRowScanRel(table=[[LOCALDB, LHX, LHXEMPS]], projection=[*], clustered indexes=[[SYS$CLUSTERED_INDEX$LHXEMPS$DEPTNO, SYS$CLUSTERED_INDEX$LHXEMPS$EMPNO, SYS$CLUSTERED_INDEX$LHXEMPS$ENAME]])'

! ' LcsRowScanRel(table=[[LOCALDB, LHX, LHXDEPTS3]], projection=[*], clustered indexes=[[SYS$CLUSTERED_INDEX$LHXDEPTS3$DEPTNOE, SYS$CLUSTERED_INDEX$LHXDEPTS3$DEPTNOF]])'

' LcsRowScanRel(table=[[LOCALDB, LHX, LHXDEPTS]], projection=[*], clustered indexes=[[SYS$CLUSTERED_INDEX$LHXDEPTS$DEPTNOA, SYS$CLUSTERED_INDEX$LHXDEPTS$DEPTNOB]])'

' LcsRowScanRel(table=[[LOCALDB, LHX, LHXDEPTS2]], projection=[*], clustered indexes=[[SYS$CLUSTERED_INDEX$LHXDEPTS2$DEPTNOC, SYS$CLUSTERED_INDEX$LHXDEPTS2$DEPTNOD]])'

! 'LcsRowScanRel(table=[[LOCALDB, LHX, LHXDEPTS4]], projection=[*], clustered indexes=[[SYS$CLUSTERED_INDEX$LHXDEPTS4$DEPTNOG, SYS$CLUSTERED_INDEX$LHXDEPTS4$DEPTNOH]])'

>

> select * from lhxemps, lhxdepts, lhxdepts2, lhxdepts3, lhxdepts4

> where lhxemps.deptno = lhxdepts.deptnoB and lhxemps.deptno = lhxdepts2.deptnoC

> and lhxemps.deptno = lhxdepts3.deptnoF and lhxemps.deptno = lhxdepts4.deptnoG

> order by empno, ename;

'EMPNO','ENAME','DEPTNO','DEPTNOA','DEPTNOB','DEPTNOC','DEPTNOD','DEPTNOE','DEPTNOF','DEPTNOG','DEPTNOH'

'100','Fred','10','10','10','10','10','10','10','10','10'

'100','Fred','10','10','10','10','10','10','10','10','10'

'110','Eric','20','20','20','20','20','20','20','20','20'

'110','Eric','20','20','20','20','20','20','20','20','20'

--- 287,315 ----

>

> -- four joins

> explain plan for

> select * from lhxemps, lhxdepts, lhxdepts2, lhxdepts3, lhxdepts4

> where lhxemps.deptno = lhxdepts.deptnoB and lhxemps.deptno = lhxdepts2.deptnoC

> and lhxemps.deptno = lhxdepts3.deptnoF and lhxemps.deptno = lhxdepts4.deptnoG

> order by empno, ename;

'column0'

'FennelToIteratorConverter'

' FennelSortRel(key=[[0, 1]], discardDuplicates=[false])'

! ' LhxJoinRel(leftKeys=[[2]], rightKeys=[[0]], joinType=[INNER])'

! ' LhxJoinRel(leftKeys=[[2]], rightKeys=[[1]], joinType=[INNER])'

'LhxJoinRel(leftKeys=[[2]], rightKeys=[[0]], joinType=[INNER])'

' LhxJoinRel(leftKeys=[[2]], rightKeys=[[1]], joinType=[INNER])'

! ' LcsRowScanRel(table=[[LOCALDB, LHX, LHXEMPS]], projection=[*], clustered indexes=[[SYS$CLUSTERED_INDEX$LHXEMPS$DEPTNO, SYS$CLUSTERED_INDEX$LHXEMPS$EMPNO, SYS$CLUSTERED_INDEX$LHXEMPS$ENAME]])'

' LcsRowScanRel(table=[[LOCALDB, LHX, LHXDEPTS]], projection=[*], clustered indexes=[[SYS$CLUSTERED_INDEX$LHXDEPTS$DEPTNOA, SYS$CLUSTERED_INDEX$LHXDEPTS$DEPTNOB]])'

' LcsRowScanRel(table=[[LOCALDB, LHX, LHXDEPTS2]], projection=[*], clustered indexes=[[SYS$CLUSTERED_INDEX$LHXDEPTS2$DEPTNOC, SYS$CLUSTERED_INDEX$LHXDEPTS2$DEPTNOD]])'

! 'LcsRowScanRel(table=[[LOCALDB, LHX, LHXDEPTS3]], projection=[*], clustered indexes=[[SYS$CLUSTERED_INDEX$LHXDEPTS3$DEPTNOE, SYS$CLUSTERED_INDEX$LHXDEPTS3$DEPTNOF]])'

! ' LcsRowScanRel(table=[[LOCALDB, LHX, LHXDEPTS4]], projection=[*], clustered indexes=[[SYS$CLUSTERED_INDEX$LHXDEPTS4$DEPTNOG, SYS$CLUSTERED_INDEX$LHXDEPTS4$DEPTNOH]])'

>

> select * from lhxemps, lhxdepts, lhxdepts2, lhxdepts3, lhxdepts4

> where lhxemps.deptno = lhxdepts.deptnoB and lhxemps.deptno = lhxdepts2.deptnoC

> and lhxemps.deptno = lhxdepts3.deptnoF and lhxemps.deptno = lhxdepts4.deptnoG

> order by empno, ename;

'EMPNO','ENAME','DEPTNO','DEPTNOA','DEPTNOB','DEPTNOC','DEPTNOD','DEPTNOE','DEPTNOF','DEPTNOG','DEPTNOH'

'100','Fred','10','10','10','10','10','10','10','10','10'

'100','Fred','10','10','10','10','10','10','10','10','10'

'110','Eric','20','20','20','20','20','20','20','20','20'

'110','Eric','20','20','20','20','20','20','20','20','20'