[
https://issues.apache.org/jira/browse/DERBY-3955?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Kathey Marsden updated DERBY-3955:
----------------------------------
Attachment: oldSelectivity.java
I wrote this java program (oldSelectivity.java) so I could do some testing on
old branches and discovered something interesting. Our estimated row count
is different if we make the index before or after the insert, even if we update
statistics. The usage is
usage: java oldSelectivity <indexBeforeInsert |indexAfterInsert>
you have to delete the database wombat each time you run.
On trunk/10.5 where update statistics is run we get:
[C:/kmarsden/repro/derby-3955] java oldSelectivity indexBeforeInsert
FAIL: expected row count:8020012.5 actual:1648057.5
[C:/kmarsden/repro/derby-3955] rm -rf wombat
[C:/kmarsden/repro/derby-3955] java oldSelectivity indexAfterInsert
PASS: got expected row count8020012.5
On 10.4/10.3 we get the same numbers but update statistics is not run.
[C:/kmarsden/repro/derby-3955] java oldSelectivity indexBeforeInsert
Couldn't execute update statistics:'SYSCS_UTIL.SYSCS_UPDATE_STATISTICS' is not
recognized as a function or procedure.
FAIL: expected row count:8020012.5 actual:1648057.5
[C:/kmarsden/repro/derby-3955] rm -rf wombat
[C:/kmarsden/repro/derby-3955] java oldSelectivity indexAfterInsert
Couldn't execute update statistics:'SYSCS_UTIL.SYSCS_UPDATE_STATISTICS' is not
recognized as a function or procedure.
PASS: got expected row count8020012.5
For 10.1 we get a different number if the index is created before the insert
[C:/kmarsden/repro/derby-3955] export CLASSPATH=".;$CLASSPATH"
[C:/kmarsden/repro/derby-3955] java oldSelectivity indexBeforeInsert
Couldn't execute update statistics:'SYSCS_UTIL.SYSCS_UPDATE_STATISTICS' is not
recognized as a function or procedure.
FAIL: expected row count:8020012.5 actual:329611.5
[C:/kmarsden/repro/derby-3955] java oldSelectivity indexAfterInsert
Couldn't execute update statistics:'SYSCS_UTIL.SYSCS_UPDATE_STATISTICS' is not
recognized as a function or procedure.
PASS: got expected row count8020012.5
So summary is that we seem to get what the test expects if we make the index
after the data is inserted, but get sundry values otherwise. Regardless of
what the right value is, I think at least for 10.5 and trunk we should get the
same value if we updated statistics.
> test lang/selectivity.sql can be revived
> ----------------------------------------
>
> Key: DERBY-3955
> URL: https://issues.apache.org/jira/browse/DERBY-3955
> Project: Derby
> Issue Type: Test
> Components: Test
> Reporter: Myrna van Lunteren
> Assignee: Kathey Marsden
> Priority: Minor
> Attachments: DERBY-3955.diff1,
> derby-3955_sanityCheck_dont_commit_diff.txt, oldSelectivity.java
>
>
> One test that was contributed during the IBM contribution of derby code was
> not runnable at the time - lang/selectivity.sql.
> This test is still there, and I think might have some valuable regression
> tests if it gets adapted to the current functionality in Derby.
> Ideally too, it should get converted to junit, or at least be made to run
> through the langScripts junit suite.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
