You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Knut Anders Hatlen (JIRA)" <ji...@apache.org> on 2010/11/19 09:53:13 UTC
[jira] Created: (DERBY-4908) Instability in
CheckConstraintTest.testBuiltInFunctions
Instability in CheckConstraintTest.testBuiltInFunctions
-------------------------------------------------------
Key: DERBY-4908
URL: https://issues.apache.org/jira/browse/DERBY-4908
Project: Derby
Issue Type: Bug
Components: Test
Affects Versions: 10.7.1.0
Environment: java version "1.6.0_20"
Java(TM) SE Runtime Environment (build 1.6.0_20-b02)
Oracle JRockit(R) (build R28.0.1-21-133393-1.6.0_20-20100512-2126-linux-ia32, compiled mode)
Reporter: Knut Anders Hatlen
Priority: Minor
Saw this failure once when running suites.All on the 10.7.1.0 release candidate:
1) testBuiltInFunctions(org.apache.derbyTesting.functionTests.tests.lang.CheckConstraintTest)junit.framework.AssertionFailedError: Column value mismatch @ column 'TYPE', row 1:
Expected: >P<
Found: >U<
at org.apache.derbyTesting.junit.JDBC.assertRowInResultSet(JDBC.java:1213)
at org.apache.derbyTesting.junit.JDBC.assertRowInResultSet(JDBC.java:1125)
at org.apache.derbyTesting.junit.JDBC.assertFullResultSetMinion(JDBC.java:1012)
at org.apache.derbyTesting.junit.JDBC.assertFullResultSet(JDBC.java:935)
at org.apache.derbyTesting.junit.JDBC.assertFullResultSet(JDBC.java:892)
at org.apache.derbyTesting.functionTests.tests.lang.CheckConstraintTest.testBuiltInFunctions(CheckConstraintTest.java:752)
The failure didn't show up when I reran the test (reran both standalone and as part of suites.All).
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Closed: (DERBY-4908) Instability in
CheckConstraintTest.testBuiltInFunctions
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4908?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen closed DERBY-4908.
-------------------------------------
Resolution: Fixed
Fix Version/s: 10.8.0.0
10.7.1.1
Assignee: Knut Anders Hatlen
> Instability in CheckConstraintTest.testBuiltInFunctions
> -------------------------------------------------------
>
> Key: DERBY-4908
> URL: https://issues.apache.org/jira/browse/DERBY-4908
> Project: Derby
> Issue Type: Bug
> Components: Test
> Affects Versions: 10.7.1.0
> Environment: java version "1.6.0_20"
> Java(TM) SE Runtime Environment (build 1.6.0_20-b02)
> Oracle JRockit(R) (build R28.0.1-21-133393-1.6.0_20-20100512-2126-linux-ia32, compiled mode)
> Reporter: Knut Anders Hatlen
> Assignee: Knut Anders Hatlen
> Priority: Minor
> Fix For: 10.7.1.1, 10.8.0.0
>
> Attachments: assert-unordered.diff
>
>
> Saw this failure once when running suites.All on the 10.7.1.0 release candidate:
> 1) testBuiltInFunctions(org.apache.derbyTesting.functionTests.tests.lang.CheckConstraintTest)junit.framework.AssertionFailedError: Column value mismatch @ column 'TYPE', row 1:
> Expected: >P<
> Found: >U<
> at org.apache.derbyTesting.junit.JDBC.assertRowInResultSet(JDBC.java:1213)
> at org.apache.derbyTesting.junit.JDBC.assertRowInResultSet(JDBC.java:1125)
> at org.apache.derbyTesting.junit.JDBC.assertFullResultSetMinion(JDBC.java:1012)
> at org.apache.derbyTesting.junit.JDBC.assertFullResultSet(JDBC.java:935)
> at org.apache.derbyTesting.junit.JDBC.assertFullResultSet(JDBC.java:892)
> at org.apache.derbyTesting.functionTests.tests.lang.CheckConstraintTest.testBuiltInFunctions(CheckConstraintTest.java:752)
> The failure didn't show up when I reran the test (reran both standalone and as part of suites.All).
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4908) Instability in
CheckConstraintTest.testBuiltInFunctions
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4908?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-4908:
--------------------------------------
Attachment: assert-unordered.diff
The attached patch switches from assertFullResultSet to assertUnorderedResultSet.
Committed to trunk with revision 1036769.
Committed to 10.7 with revision 1036771.
> Instability in CheckConstraintTest.testBuiltInFunctions
> -------------------------------------------------------
>
> Key: DERBY-4908
> URL: https://issues.apache.org/jira/browse/DERBY-4908
> Project: Derby
> Issue Type: Bug
> Components: Test
> Affects Versions: 10.7.1.0
> Environment: java version "1.6.0_20"
> Java(TM) SE Runtime Environment (build 1.6.0_20-b02)
> Oracle JRockit(R) (build R28.0.1-21-133393-1.6.0_20-20100512-2126-linux-ia32, compiled mode)
> Reporter: Knut Anders Hatlen
> Priority: Minor
> Attachments: assert-unordered.diff
>
>
> Saw this failure once when running suites.All on the 10.7.1.0 release candidate:
> 1) testBuiltInFunctions(org.apache.derbyTesting.functionTests.tests.lang.CheckConstraintTest)junit.framework.AssertionFailedError: Column value mismatch @ column 'TYPE', row 1:
> Expected: >P<
> Found: >U<
> at org.apache.derbyTesting.junit.JDBC.assertRowInResultSet(JDBC.java:1213)
> at org.apache.derbyTesting.junit.JDBC.assertRowInResultSet(JDBC.java:1125)
> at org.apache.derbyTesting.junit.JDBC.assertFullResultSetMinion(JDBC.java:1012)
> at org.apache.derbyTesting.junit.JDBC.assertFullResultSet(JDBC.java:935)
> at org.apache.derbyTesting.junit.JDBC.assertFullResultSet(JDBC.java:892)
> at org.apache.derbyTesting.functionTests.tests.lang.CheckConstraintTest.testBuiltInFunctions(CheckConstraintTest.java:752)
> The failure didn't show up when I reran the test (reran both standalone and as part of suites.All).
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
Re: (DERBY-4908) Instability in CheckConstraintTest.testBuiltInFunctions
Posted by Lily Wei <li...@yahoo.com>.
> Bryan Pendleton <bp...@gmail.com> writes:
>
> <good explanation of optimizer workings>
>
> Doesn't this still leave the question why the test is unstable?: What
> could make the optimizer choice vary from test run to test run? I can
> think of the maximum time the optimizer would spend before it gives up
> looking for further plans, and that this could lead to different choice
> depending on machine performance, load etc. Does this test involve a
> large join?
I don't think the join is large, but the query that gets the unexpected
result is indeed a join:
rs = st.executeQuery(
" select c.type from "
+ "sys.sysconstraints c, sys.systables t where "
+ "c.tableid = t.tableid and tablename='T1'");
I'm not 100% sure the failure was caused by wrong ordering, though,
since assertFullResultSet only reports the first mismatch it
finds. Perhaps it would be a good improvement to get assertFullResultSet
to dump the full result on a mismatch?
> If not, what else could make the optimizer choice non-predictable?
I think the memory footprint and the available memory are also used to
determine the cost of a hash scan (if it doesn't fit in main memory, it
will be considered more expensive).
I would think the full result set will be nice to have. Test optimizer on the
path choosing based on row size, memory size and else will be great as well.
Lily
Re: (DERBY-4908) Instability in CheckConstraintTest.testBuiltInFunctions
Posted by "Dag H. Wanvik" <da...@oracle.com>.
Knut Anders Hatlen <kn...@oracle.com> writes:
> I think the memory footprint and the available memory are also used to
> determine the cost of a hash scan (if it doesn't fit in main memory, it
> will be considered more expensive).
That seems a good condicate culprit here, especially when running the
entire test suite. I guess people run with different heap sizes.. and/or
we have some leakage. Not sure if anyone has tested to leaks recently?
Dag
--
Re: (DERBY-4908) Instability in CheckConstraintTest.testBuiltInFunctions
Posted by Knut Anders Hatlen <kn...@oracle.com>.
dag.wanvik@oracle.com (Dag H. Wanvik) writes:
> Bryan Pendleton <bp...@gmail.com> writes:
>
> <good explanation of optimizer workings>
>
> Doesn't this still leave the question why the test is unstable?: What
> could make the optimizer choice vary from test run to test run? I can
> think of the maximum time the optimizer would spend before it gives up
> looking for further plans, and that this could lead to different choice
> depending on machine performance, load etc. Does this test involve a
> large join?
I don't think the join is large, but the query that gets the unexpected
result is indeed a join:
rs = st.executeQuery(
" select c.type from "
+ "sys.sysconstraints c, sys.systables t where "
+ "c.tableid = t.tableid and tablename='T1'");
I'm not 100% sure the failure was caused by wrong ordering, though,
since assertFullResultSet only reports the first mismatch it
finds. Perhaps it would be a good improvement to get assertFullResultSet
to dump the full result on a mismatch?
> If not, what else could make the optimizer choice non-predictable?
I think the memory footprint and the available memory are also used to
determine the cost of a hash scan (if it doesn't fit in main memory, it
will be considered more expensive).
--
Knut Anders
Re: [jira] Commented: (DERBY-4908) Instability in CheckConstraintTest.testBuiltInFunctions
Posted by "Dag H. Wanvik" <da...@oracle.com>.
Bryan Pendleton <bp...@gmail.com> writes:
<good explanation of optimizer workings>
Doesn't this still leave the question why the test is unstable?: What
could make the optimizer choice vary from test run to test run? I can
think of the maximum time the optimizer would spend before it gives up
looking for further plans, and that this could lead to different choice
depending on machine performance, load etc. Does this test involve a
large join?
If not, what else could make the optimizer choice non-predictable?
Dag
Re: [jira] Commented: (DERBY-4908) Instability in CheckConstraintTest.testBuiltInFunctions
Posted by Bryan Pendleton <bp...@gmail.com>.
> I always puzzle about this kind of change. Does this behavior mean the optimizer somehow change so it choose different path?
> Hence, the different order result set. Or, optimizer can decide to choose different path in general?
Hi Lily,
It's a great question, and I think it would be a great topic for the wiki.
The optimizer didn't change, but it definitely can decide to choose
different paths in general.
The optimizer considers a (large) variety of possible query execution
plans, and uses an estimation function to compute an estimate of the
cost of executing each plan. The optimizer attempts to choose the plan
with the lowest cost, by choosing the plan with the lowest estimated cost.
There are some additional wrinkles, but that's the high-level view.
In terms of ordering, the most common reason for an ordering difference
is the difference in behavior between a nested-loops join driven by an
index scan, versus a hash join.
Plans which scan indexes return the rows in key order, and so the rows
tend to be emitted in key order.
Plans which perform hash joins can jumble the rows up in an apparently-random
order, determined by the hash algorithm.
thanks,
bryan
Re: [jira] Commented: (DERBY-4908) Instability in CheckConstraintTest.testBuiltInFunctions
Posted by Lily Wei <li...@yahoo.com>.
Hi Knut:
Thank you so much for fixing this issue.
I always puzzle about this kind of change. Does this behavior mean the
optimizer somehow change so it choose different path? Hence, the different order
result set. Or, optimizer can decide to choose different path in general?
Thanks,
Lily
________________________________
From: Knut Anders Hatlen (JIRA) <ji...@apache.org>
To: derby-dev@db.apache.org
Sent: Fri, November 19, 2010 1:01:14 AM
Subject: [jira] Commented: (DERBY-4908) Instability in
CheckConstraintTest.testBuiltInFunctions
[
https://issues.apache.org/jira/browse/DERBY-4908?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12933731#action_12933731
]
Knut Anders Hatlen commented on DERBY-4908:
-------------------------------------------
The failing assert looks like this:
rs = st.executeQuery(
" select c.type from "
+ "sys.sysconstraints c, sys.systables t where "
+ "c.tableid = t.tableid and tablename='T1'");
(...)
expRS = new String [][]
{
{"P"},
{"U"},
{"C"},
{"U"},
{"C"}
};
JDBC.assertFullResultSet(rs, expRS, true);
There's no ORDER BY clause in the query, so the failure may be caused by the the
optimizer choosing a different plan for the execution. We should probably use
assertUnorderedResultSet() instead of assertFullResultSet() when we check the
results.
> Instability in CheckConstraintTest.testBuiltInFunctions
> -------------------------------------------------------
>
> Key: DERBY-4908
> URL: https://issues.apache.org/jira/browse/DERBY-4908
> Project: Derby
> Issue Type: Bug
> Components: Test
> Affects Versions: 10.7.1.0
> Environment: java version "1.6.0_20"
> Java(TM) SE Runtime Environment (build 1.6.0_20-b02)
> Oracle JRockit(R) (build R28.0.1-21-133393-1.6.0_20-20100512-2126-linux-ia32,
>compiled mode)
> Reporter: Knut Anders Hatlen
> Priority: Minor
>
> Saw this failure once when running suites.All on the 10.7.1.0 release
>candidate:
> 1)
>testBuiltInFunctions(org.apache.derbyTesting.functionTests.tests.lang.CheckConstraintTest)junit.framework.AssertionFailedError:
> Column value mismatch @ column 'TYPE', row 1:
> Expected: >P<
> Found: >U<
> at
>org.apache.derbyTesting.junit.JDBC.assertRowInResultSet(JDBC.java:1213)
> at
>org.apache.derbyTesting.junit.JDBC.assertRowInResultSet(JDBC.java:1125)
> at
>org.apache.derbyTesting.junit.JDBC.assertFullResultSetMinion(JDBC.java:1012)
> at
>org.apache.derbyTesting.junit.JDBC.assertFullResultSet(JDBC.java:935)
> at
>org.apache.derbyTesting.junit.JDBC.assertFullResultSet(JDBC.java:892)
> at
>org.apache.derbyTesting.functionTests.tests.lang.CheckConstraintTest.testBuiltInFunctions(CheckConstraintTest.java:752)
>
> The failure didn't show up when I reran the test (reran both standalone and as
>part of suites.All).
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4908) Instability in
CheckConstraintTest.testBuiltInFunctions
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4908?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12933731#action_12933731 ]
Knut Anders Hatlen commented on DERBY-4908:
-------------------------------------------
The failing assert looks like this:
rs = st.executeQuery(
" select c.type from "
+ "sys.sysconstraints c, sys.systables t where "
+ "c.tableid = t.tableid and tablename='T1'");
(...)
expRS = new String [][]
{
{"P"},
{"U"},
{"C"},
{"U"},
{"C"}
};
JDBC.assertFullResultSet(rs, expRS, true);
There's no ORDER BY clause in the query, so the failure may be caused by the the optimizer choosing a different plan for the execution. We should probably use assertUnorderedResultSet() instead of assertFullResultSet() when we check the results.
> Instability in CheckConstraintTest.testBuiltInFunctions
> -------------------------------------------------------
>
> Key: DERBY-4908
> URL: https://issues.apache.org/jira/browse/DERBY-4908
> Project: Derby
> Issue Type: Bug
> Components: Test
> Affects Versions: 10.7.1.0
> Environment: java version "1.6.0_20"
> Java(TM) SE Runtime Environment (build 1.6.0_20-b02)
> Oracle JRockit(R) (build R28.0.1-21-133393-1.6.0_20-20100512-2126-linux-ia32, compiled mode)
> Reporter: Knut Anders Hatlen
> Priority: Minor
>
> Saw this failure once when running suites.All on the 10.7.1.0 release candidate:
> 1) testBuiltInFunctions(org.apache.derbyTesting.functionTests.tests.lang.CheckConstraintTest)junit.framework.AssertionFailedError: Column value mismatch @ column 'TYPE', row 1:
> Expected: >P<
> Found: >U<
> at org.apache.derbyTesting.junit.JDBC.assertRowInResultSet(JDBC.java:1213)
> at org.apache.derbyTesting.junit.JDBC.assertRowInResultSet(JDBC.java:1125)
> at org.apache.derbyTesting.junit.JDBC.assertFullResultSetMinion(JDBC.java:1012)
> at org.apache.derbyTesting.junit.JDBC.assertFullResultSet(JDBC.java:935)
> at org.apache.derbyTesting.junit.JDBC.assertFullResultSet(JDBC.java:892)
> at org.apache.derbyTesting.functionTests.tests.lang.CheckConstraintTest.testBuiltInFunctions(CheckConstraintTest.java:752)
> The failure didn't show up when I reran the test (reran both standalone and as part of suites.All).
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.