Eclipselink only gives syntaxError on PARTITION when running tests

Question

I'm having trouble with a native SQL query that works fine when I run my code normally, but gives me:

[EL Warning]: 2013-01-15 10:24:04.462--UnitOfWork(2230747)--
Exception [EclipseLink-4002] (Eclipse Persistence Services - 
2.3.0.v20110604-r9504): org.eclipse.persistence.exceptions.DatabaseException

Internal Exception: java.sql.SQLSyntaxErrorException: Syntax error: Encountered
"PARTITION" at line 1, column 109.

Error Code: 30000
Call: SELECT COUNT(*) FROM (SELECT p.PRICE_ID, p.PRODUCT_ID, p.PRICE, 
p.TEXT, row_number() OVER (PARTITION BY p.PRODUCT_ID ORDER BY p.PRICE_DT DESC) rn, 
p.PRICE_DT FROM PRICE_T p LEFT JOIN STORE_T b ON p.STORE_ID = 
b.STORE_ID WHERE (p.CHAIN_ID = ? AND p.PRICE_TYPE = 'NORMAL') OR
(p.PRICE_TYPE = 'SOMETHINGELSE' AND b.OTHERSTORE_ID IN (1,2)) ) WHERE rn = 1
bind => [1 parameter bound]

Query: DataReadQuery(sql="SELECT COUNT(*) FROM (SELECT p.PRICE_ID, p.PRODUCT_ID, p.PRICE, 
p.TEXT, row_number() OVER (PARTITION BY p.PRODUCT_ID ORDER BY p.PRICE_DT DESC) rn, 
p.PRICE_DT FROM PRICE_T p LEFT JOIN STORE_T b ON p.STORE_ID = 
b.STORE_ID WHERE (p.CHAIN_ID = ? AND p.PRICE_TYPE = 'NORMAL') OR
(p.PRICE_TYPE = 'SOMETHINGELSE' AND b.OTHERSTORE_ID IN (1,2)) ) WHERE rn = 1")

The exact same query gives me no trouble at all when running my code normally. Here are my persistence.xmls:

persistence.xml (test)

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.0" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
<persistence-unit name="test-pu" transaction-type="RESOURCE_LOCAL">
            <!-- all entit-classes are here -->
    <exclude-unlisted-classes>false</exclude-unlisted-classes>  
    <properties>
        <property name="javax.persistence.jdbc.driver" value="org.apache.derby.jdbc.EmbeddedDriver" />
        <property name="javax.persistence.jdbc.url" value="jdbc:derby:memory:test;create=true" />
        <property name="javax.persistence.jdbc.user" value="test" />
        <property name="javax.persistence.jdbc.password" value="test" />

         <!--  EclipseLink should create the database schema automatically --> 
        <property name="eclipselink.ddl-generation" value="create-tables" />
        <property name="eclipselink.ddl-generation.output-mode" value="database" />
        <property name="eclipselink.target-database" value="Derby"/>
    </properties>
</persistence-unit>

persistence.xml (normal)

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.0" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
<persistence-unit name="pu" transaction-type="JTA">
    <jta-data-source>mydatasource</jta-data-source>
            <!-- all entit-classes are here --> 
    <exclude-unlisted-classes>false</exclude-unlisted-classes>
    <properties>
        <property name="eclipselink.query-results-cache" value="false"/>
        <!--
        <property name="eclipselink.logging.level" value="FINE"/>
        <property name="eclipselink.logging.parameters" value="true"/>
        <property name="eclipselink.logging.logger" value="DefaultLogger"/>
        -->
    </properties>
</persistence-unit>

PriceRepositoryTest.java - testGetAmountOfPricesForChain1()

@Test
public void testGetAmountOfPricesForChain1() {
    EntityManagerFactory emf = Persistence.createEntityManagerFactory("test-pu");
    EntityManager em = emf.createEntityManager();
    try {
        PriceRepository repository = new PriceRepositoryImpl(em);
        long amount = repository.getAmount(STORES_IN_CHAIN_1);
        assertEquals(PRICES_CHAIN_1.size(), amount);
    } finally {
        em.close();
    }
}

I've tried googling this, but I haven't found anything. Any ideas on what could be wrong?

Answer 1

You are connecting to Derby, not Oracle database. Very likely Derby does not support this syntax.