Major Description
With a collated database dml001 fails on statement
– TEST:0004 SELECT with UNION, ORDER BY integer DESC!
SELECT WORKS.EMPNUM
FROM WORKS
WHERE WORKS.PNUM = 'P2'
UNION
SELECT STAFF.EMPNUM
FROM STAFF
WHERE STAFF.GRADE=13
ORDER BY 1 DESC;
EM&
—
E5
E4
E3
E2
E1
ij> – PASS:0004 If 5 rows selected and last EMPNUM = 'E1'?
– END TEST >>> 0004 <<< END TEST
– *********************************************
– TEST:0005 SELECT with UNION ALL!
SELECT WORKS.EMPNUM
FROM WORKS
WHERE WORKS.PNUM = 'P2'
UNION ALL
SELECT STAFF.EMPNUM
FROM STAFF
WHERE STAFF.GRADE = 13;
EM&
—
E1
E2
E3
E4
E3
E5
ij> – PASS:0005 If 6 rows selected?
– END TEST >>> 0005 <<< END TEST
– *********************************************
– TEST:0158 SELECT with UNION and NOT EXISTS subquery!
SELECT EMPNAME,PNUM,HOURS
FROM STAFF,WORKS
WHERE STAFF.EMPNUM = WORKS.EMPNUM
UNION
SELECT EMPNAME,PNUM,HOURS
FROM STAFF,WORKS
WHERE NOT EXISTS
(SELECT HOURS
FROM WORKS
WHERE STAFF.EMPNUM = WORKS.EMPNUM);
EMPNAME |PN&|HOURS
-------------------------------
Alice |P1 |40
Alice |P2 |20
Alice |P3 |80
Alice |P4 |20
Alice |P5 |12
Alice |P6 |12
Betty |P1 |40
Betty |P2 |80
Carmen |P2 |20
Don |P2 |20
Don |P4 |40
Don |P5 |80
Ed |P1 |40
Ed |P2 |20
Ed |P2 |80
Ed |P3 |80
Ed |P4 |20
Ed |P4 |40
Ed |P5 |12
Ed |P5 |80
Ed |P6 |12
ij> – PASS:0158 If 21 rows selected?
– END TEST >>> 0158 <<< END TEST
– *********************************************
– TEST:0159 SELECT with 2 UNIONs, ORDER BY 2 integers!
SELECT PNUM,EMPNUM,HOURS
FROM WORKS
WHERE HOURS=80
UNION
SELECT PNUM,EMPNUM,HOURS
FROM WORKS
WHERE HOURS=40
UNION
SELECT PNUM,EMPNUM,HOURS
FROM WORKS
WHERE HOURS=20
ORDER BY 3,1;
PN&|EM&|HOURS
--------------
P2 |E4 |20
P2 |E3 |20
P2 |E1 |20
P4 |E1 |20
P1 |E2 |40
P1 |E1 |40
P4 |E4 |40
P2 |E2 |80
P3 |E1 |80
P5 |E4 |80
ij> – PASS:0159 If 10 rows selected?
– END TEST >>> 0159 <<< END TEST
– *********************************************
– TEST:0160 SELECT with parenthesized UNION, UNION ALL!
SELECT PNUM,EMPNUM,HOURS
FROM WORKS
WHERE HOURS=12
UNION ALL
(SELECT PNUM,EMPNUM,HOURS
FROM WORKS
UNION
SELECT PNUM,EMPNUM,HOURS
FROM WORKS
WHERE HOURS=80)
ORDER BY 2,1;
With error:
junit.framework.ComparisonFailure: Output at line 82 expected:<...M&> but was:<...RROR XJ001: Java exception: 'ASSERT FAILED col1.getClass() (class org.apache.derby.iapi.types.SQLChar) expected to be the same as col2.getClass() (class org.apache.derby.iapi.types.CollatorSQLChar): org.apache.derby.shared.common.sanity.AssertFailure'.>
at junit.framework.Assert.assertEquals(Assert.java:81)
at org.apache.derbyTesting.functionTests.util.CanonTestCase.compareCanon(CanonTestCase.java:100)
at org.apache.derbyTesting.functionTests.util.ScriptTestCase.runTest(ScriptTestCase.java:124)
at junit.framework.TestCase.runBare(TestCase.java:127)
at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:95)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22)
at junit.extensions.TestSetup$1.protect(TestSetup.java:19)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.extensions.TestSetup.run(TestSetup.java:23)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22)
at junit.extensions.TestSetup$1.protect(TestSetup.java:19)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.extensions.TestSetup.run(TestSetup.java:23)
at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22)
at junit.extensions.TestSetup$1.protect(TestSetup.java:19)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.extensions.TestSetup.run(TestSetup.java:23)
at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22)
at junit.extensions.TestSetup$1.protect(TestSetup.java:19)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.extensions.TestSetup.run(TestSetup.java:23)
at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22)
at junit.extensions.TestSetup$1.protect(TestSetup.java:19)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.extensions.TestSetup.run(TestSetup.java:23)
at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22)
at junit.extensions.TestSetup$1.protect(TestSetup.java:19)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.extensions.TestSetup.run(TestSetup.java:23)
at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22)
at junit.extensions.TestSetup$1.protect(TestSetup.java:19)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.extensions.TestSetup.run(TestSetup.java:23)
at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22)
at junit.extensions.TestSetup$1.protect(TestSetup.java:19)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.extensions.TestSetup.run(TestSetup.java:23)
at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22)
at junit.extensions.TestSetup$1.protect(TestSetup.java:19)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.extensions.TestSetup.run(TestSetup.java:23)
at TimeRunner.main(TimeRunner.java:29)
16 dml001(org.apache.derbyTesting.functionTests.tests.nist.NistScripts)
I have not been able to get a standalone repro yet.