SQL generation from EJBQLQuery incorrect and assumed ID type incorrect
----------------------------------------------------------------------
Key: CAY-1208
URL: https://issues.apache.org/jira/browse/CAY-1208
Project: Cayenne
Issue Type: Bug
Components: Cayenne Core Library
Affects Versions: 3.0M5
Environment: Windows XP
Reporter: Tony Weddle
Two problems with code generation:
Problem 1.
EJBQLQuery: "select iosbu.toIosb.toIos, sum(iosbu.curQty)" +
" from Iosbu as iosbu, Ios as ios" +
" where iosbu.toIosb.toIosbs.id = 'A'" +
" and (iosbu.toIosb.useByDt is null" +
" or iosbu.toIosb.useByDt > CURRENT_DATE)" +
" group by iosbu.toIosb.toIos"
Generated SQL: SELECT t1.PART_CODE, SUM(t0.CUR_QTY) AS sc0 FROM OPS$PAINT.IOSBU
t0, INNER JOIN OPS$PAINT.IOSB t1 ON (t0.IOSB_ID = t1.IOSB_ID) INNER JOIN
OPS$PAINT.IOSBS t3 ON (t1.IOSB_STAT = t3.IOSB_STAT) OPS$PAINT.IOS t2 WHERE
t3.IOSB_STAT = ? AND t1.USE_BY_DT IS NULL OR t1.USE_BY_DT > {fn CURDATE()}
GROUP BY t1.PART_CODE
Note the lack of comma before the final table spec in the from clause.
Problem 2.
EJBQLQuery: "select iosbu.toIosb.toIos, sum(iosbu.curQty)" +
" from Iosbu as iosbu" +
" where iosbu.toIosb.toIosbs.id = 'A'" +
" and (iosbu.toIosb.useByDt is null" +
" or iosbu.toIosb.useByDt > CURRENT_DATE)" +
" group by iosbu.toIosb.toIos"
When processing the results set, the following exception occurs:
java.sql.SQLException: Fail to convert to internal representation
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:190)
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:286)
at
oracle.jdbc.driver.CharCommonAccessor.getLong(CharCommonAccessor.java:239)
at
oracle.jdbc.driver.OracleResultSetImpl.getLong(OracleResultSetImpl.java:767)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at
org.apache.cayenne.access.types.DefaultType.materializeObject(DefaultType.java:194)
at
org.apache.cayenne.access.jdbc.JDBCResultIterator.readDataRow(JDBCResultIterator.java:289)
at
org.apache.cayenne.access.jdbc.JDBCResultIterator.nextDataRow(JDBCResultIterator.java:138)
at
org.apache.cayenne.access.jdbc.LimitResultIterator.readDataRow(LimitResultIterator.java:78)
at
org.apache.cayenne.access.jdbc.LimitResultIterator.nextDataRow(LimitResultIterator.java:115)
at
org.apache.cayenne.access.jdbc.LimitResultIterator.dataRows(LimitResultIterator.java:91)
at
org.apache.cayenne.access.jdbc.SQLTemplateAction.processSelectResult(SQLTemplateAction.java:231)
at
org.apache.cayenne.dba.oracle.OracleSQLTemplateAction.processSelectResult(OracleSQLTemplateAction.java:85)
at
org.apache.cayenne.access.jdbc.SQLTemplateAction.execute(SQLTemplateAction.java:170)
at
org.apache.cayenne.access.jdbc.SQLTemplateAction.performAction(SQLTemplateAction.java:126)
at
org.apache.cayenne.access.jdbc.EJBQLAction.performAction(EJBQLAction.java:100)
at
org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:57)
at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:236)
at
org.apache.cayenne.access.DataDomainQueryAction.runQuery(DataDomainQueryAction.java:423)
at
org.apache.cayenne.access.DataDomainQueryAction.access$000(DataDomainQueryAction.java:67)
at
org.apache.cayenne.access.DataDomainQueryAction$2.transform(DataDomainQueryAction.java:396)
at
org.apache.cayenne.access.DataDomain.runInTransaction(DataDomain.java:846)
at
org.apache.cayenne.access.DataDomainQueryAction.runQueryInTransaction(DataDomainQueryAction.java:393)
at
org.apache.cayenne.access.DataDomainQueryAction.execute(DataDomainQueryAction.java:119)
at org.apache.cayenne.access.DataDomain.onQuery(DataDomain.java:739)
at
org.apache.cayenne.util.ObjectContextQueryAction.runQuery(ObjectContextQueryAction.java:316)
at
org.apache.cayenne.util.ObjectContextQueryAction.execute(ObjectContextQueryAction.java:95)
at org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1343)
at
org.apache.cayenne.access.DataContext.performQuery(DataContext.java:1332)
at nzs.common.client.Client.test2(Client.java:75)
at nzs.common.client.Client.main(Client.java:43)
It is trying to retrieve a Long from the results set, when the ID for the IOS
table is a String.
Cayenne XML files follow:
cayenne,xml:
<?xml version="1.0" encoding="utf-8"?>
<domains project-version="3.0">
<domain name="PaintCayenne">
<map name="PaintCayenneMap" location="PaintCayenneMap.map.xml"/>
<node name="PaintCayenneNode"
datasource="PaintCayenneNode.driver.xml"
factory="org.apache.cayenne.conf.DriverDataSourceFactory">
<map-ref name="PaintCayenneMap"/>
</node>
</domain>
</domains>
PaintCayenneMap.map.xml:
<?xml version="1.0" encoding="utf-8"?>
<data-map project-version="3.0">
<property name="defaultLockType" value="optimistic"/>
<property name="defaultPackage"
value="nzs.paint.server.entitySupport.entities"/>
<property name="defaultSchema" value="OPS$PAINT"/>
<property name="defaultSuperclass"
value="nzs.common.server.entitySupport.BaseEntity"/>
<db-entity name="IOS" schema="OPS$PAINT">
<db-attribute name="ACTIVE" type="VARCHAR" length="1"/>
<db-attribute name="DEF_BATCH_GRP" type="INTEGER" length="3"/>
<db-attribute name="DEF_IOSBAY" type="VARCHAR" length="2"/>
<db-attribute name="DEF_IOSB_STAT" type="VARCHAR" length="1"/>
<db-attribute name="DEF_IOSLVL" type="VARCHAR" length="2"/>
<db-attribute name="DEF_IOSROW" type="VARCHAR" length="2"/>
<db-attribute name="DEF_IOSYRD" type="VARCHAR" length="2"/>
<db-attribute name="GLOST_CD" type="VARCHAR" length="1"/>
<db-attribute name="INFSUP_DAY" type="INTEGER" length="3"/>
<db-attribute name="ITEM_DESC" type="VARCHAR" length="60"/>
<db-attribute name="ITEM_SHDES" type="VARCHAR" length="10"/>
<db-attribute name="MAX_LEVEL" type="INTEGER" length="6"/>
<db-attribute name="MIN_LEVEL" type="INTEGER" length="6"/>
<db-attribute name="PART_CODE" type="VARCHAR"
isPrimaryKey="true" isMandatory="true" length="8"/>
<db-attribute name="PCTGTP_CD" type="VARCHAR" length="2"/>
<db-attribute name="PREF_SUPLR" type="VARCHAR" length="7"/>
<db-attribute name="PTCOL_CD" type="INTEGER" length="3"/>
<db-attribute name="PT_MIXABLE" type="VARCHAR" length="1"/>
<db-attribute name="ROLL_WIDTH" type="INTEGER" length="5"/>
<db-attribute name="RO_LEVEL" type="INTEGER" length="6"/>
<db-attribute name="SCN" type="VARCHAR" length="8"/>
<db-attribute name="STD_COST" type="DECIMAL" length="11"
scale="2"/>
<db-attribute name="STD_LEADTM" type="INTEGER" length="4"/>
<db-attribute name="SU_QTY" type="INTEGER" length="5"/>
<db-attribute name="UOM_CD" type="VARCHAR" length="3"/>
<db-attribute name="WT_SQM" type="DECIMAL" length="10"
scale="4"/>
</db-entity>
<db-entity name="IOSB" schema="OPS$PAINT">
<db-attribute name="BATCH_GRP" type="INTEGER" length="3"/>
<db-attribute name="BATCH_NO" type="INTEGER" isMandatory="true"
length="5"/>
<db-attribute name="IOSBG_ID" type="INTEGER" length="12"/>
<db-attribute name="IOSB_ID" type="INTEGER" isPrimaryKey="true"
isMandatory="true" length="12"/>
<db-attribute name="IOSB_STAT" type="VARCHAR" length="1"/>
<db-attribute name="MIX_BATCH" type="INTEGER" length="5"/>
<db-attribute name="MIX_SUPLR" type="VARCHAR" length="7"/>
<db-attribute name="PART_CODE" type="VARCHAR"
isMandatory="true" length="8"/>
<db-attribute name="PB_MIXABLE" type="VARCHAR" length="1"/>
<db-attribute name="SUPLR_NO" type="VARCHAR" isMandatory="true"
length="7"/>
<db-attribute name="USE_BY_DT" type="TIMESTAMP" length="7"/>
</db-entity>
<db-entity name="IOSBG" schema="OPS$PAINT">
<db-attribute name="BATCH_GRP" type="INTEGER"
isMandatory="true" length="3"/>
<db-attribute name="IOSBG_DES" type="VARCHAR" length="10"/>
<db-attribute name="IOSBG_ID" type="INTEGER"
isPrimaryKey="true" isMandatory="true" length="12"/>
<db-attribute name="IOSBG_STAT" type="VARCHAR" length="1"/>
<db-attribute name="NOTE" type="VARCHAR" length="80"/>
<db-attribute name="PART_CODE" type="VARCHAR"
isMandatory="true" length="8"/>
<db-attribute name="PRIORITY" type="INTEGER" length="3"/>
</db-entity>
<db-entity name="IOSBS" schema="OPS$PAINT">
<db-attribute name="IOSB_STAT" type="VARCHAR"
isPrimaryKey="true" isMandatory="true" length="2"/>
<db-attribute name="NOTE" type="VARCHAR" length="20"/>
</db-entity>
<db-entity name="IOSBU" schema="OPS$PAINT">
<db-attribute name="BATCH_NO" type="INTEGER" isMandatory="true"
length="5"/>
<db-attribute name="BU_NO" type="INTEGER" isMandatory="true"
length="4"/>
<db-attribute name="CUR_BAY" type="VARCHAR" length="2"/>
<db-attribute name="CUR_LEVEL" type="VARCHAR" length="2"/>
<db-attribute name="CUR_QTY" type="INTEGER" length="10"/>
<db-attribute name="CUR_ROW" type="VARCHAR" length="2"/>
<db-attribute name="CUR_YARD" type="VARCHAR" length="2"/>
<db-attribute name="IOSBU_ID" type="INTEGER"
isPrimaryKey="true" isMandatory="true" length="12"/>
<db-attribute name="IOSB_ID" type="INTEGER" length="12"/>
<db-attribute name="PACK_SLIP" type="VARCHAR" length="10"/>
<db-attribute name="PART_CODE" type="VARCHAR"
isMandatory="true" length="8"/>
<db-attribute name="PURCH_ORD" type="VARCHAR" length="7"/>
<db-attribute name="RECEIVE_DT" type="TIMESTAMP" length="7"/>
<db-attribute name="SUPLR_NO" type="VARCHAR" isMandatory="true"
length="7"/>
</db-entity>
<obj-entity name="Ios"
className="nzs.paint.server.entitySupport.entities.Ios" lock-type="optimistic"
dbEntityName="IOS" superClassName="nzs.common.server.entitySupport.BaseEntity">
<obj-attribute name="active" type="java.lang.String"
lock="true" db-attribute-path="ACTIVE"/>
<obj-attribute name="defBatchGrp" type="java.lang.Integer"
lock="true" db-attribute-path="DEF_BATCH_GRP"/>
<obj-attribute name="defIosbay" type="java.lang.String"
lock="true" db-attribute-path="DEF_IOSBAY"/>
<obj-attribute name="defIoslvl" type="java.lang.String"
lock="true" db-attribute-path="DEF_IOSLVL"/>
<obj-attribute name="defIosrow" type="java.lang.String"
lock="true" db-attribute-path="DEF_IOSROW"/>
<obj-attribute name="defIosyrd" type="java.lang.String"
lock="true" db-attribute-path="DEF_IOSYRD"/>
<obj-attribute name="id" type="java.lang.String" lock="true"
db-attribute-path="PART_CODE"/>
<obj-attribute name="infsupDay" type="java.lang.Integer"
lock="true" db-attribute-path="INFSUP_DAY"/>
<obj-attribute name="itemDesc" type="java.lang.String"
lock="true" db-attribute-path="ITEM_DESC"/>
<obj-attribute name="itemShdes" type="java.lang.String"
lock="true" db-attribute-path="ITEM_SHDES"/>
<obj-attribute name="maxLevel" type="java.lang.Integer"
lock="true" db-attribute-path="MAX_LEVEL"/>
<obj-attribute name="minLevel" type="java.lang.Integer"
lock="true" db-attribute-path="MIN_LEVEL"/>
<obj-attribute name="ptMixable" type="java.lang.String"
lock="true" db-attribute-path="PT_MIXABLE"/>
<obj-attribute name="roLevel" type="java.lang.Integer"
lock="true" db-attribute-path="RO_LEVEL"/>
<obj-attribute name="rollWidth" type="java.lang.Integer"
lock="true" db-attribute-path="ROLL_WIDTH"/>
<obj-attribute name="scn" type="java.lang.String" lock="true"
db-attribute-path="SCN"/>
<obj-attribute name="stdCost" type="java.math.BigDecimal"
lock="true" db-attribute-path="STD_COST"/>
<obj-attribute name="stdLeadtm" type="java.lang.Integer"
lock="true" db-attribute-path="STD_LEADTM"/>
<obj-attribute name="suQty" type="java.lang.Integer"
lock="true" db-attribute-path="SU_QTY"/>
<obj-attribute name="uomCd" type="java.lang.String" lock="true"
db-attribute-path="UOM_CD"/>
<obj-attribute name="wtSqm" type="java.math.BigDecimal"
lock="true" db-attribute-path="WT_SQM"/>
</obj-entity>
<obj-entity name="Iosb"
className="nzs.paint.server.entitySupport.entities.Iosb" lock-type="optimistic"
dbEntityName="IOSB" superClassName="nzs.common.server.entitySupport.BaseEntity">
<obj-attribute name="batchGrp" type="java.lang.Integer"
lock="true" db-attribute-path="BATCH_GRP"/>
<obj-attribute name="batchNo" type="java.lang.Integer"
lock="true" db-attribute-path="BATCH_NO"/>
<obj-attribute name="mixBatch" type="java.lang.Integer"
lock="true" db-attribute-path="MIX_BATCH"/>
<obj-attribute name="mixSuplr" type="java.lang.String"
lock="true" db-attribute-path="MIX_SUPLR"/>
<obj-attribute name="pbMixable" type="java.lang.String"
lock="true" db-attribute-path="PB_MIXABLE"/>
<obj-attribute name="suplrNo" type="java.lang.String"
lock="true" db-attribute-path="SUPLR_NO"/>
<obj-attribute name="useByDt" type="java.util.Date" lock="true"
db-attribute-path="USE_BY_DT"/>
</obj-entity>
<obj-entity name="Iosbg"
className="nzs.paint.server.entitySupport.entities.Iosbg"
lock-type="optimistic" dbEntityName="IOSBG"
superClassName="nzs.common.server.entitySupport.BaseEntity">
<obj-attribute name="batchGrp" type="java.lang.Integer"
lock="true" db-attribute-path="BATCH_GRP"/>
<obj-attribute name="iosbgDes" type="java.lang.String"
lock="true" db-attribute-path="IOSBG_DES"/>
<obj-attribute name="note" type="java.lang.String" lock="true"
db-attribute-path="NOTE"/>
<obj-attribute name="priority" type="java.lang.Integer"
lock="true" db-attribute-path="PRIORITY"/>
</obj-entity>
<obj-entity name="Iosbs"
className="nzs.paint.server.entitySupport.entities.Iosbs"
lock-type="optimistic" dbEntityName="IOSBS"
superClassName="nzs.common.server.entitySupport.BaseEntity">
<obj-attribute name="id" type="java.lang.String" lock="true"
db-attribute-path="IOSB_STAT"/>
<obj-attribute name="note" type="java.lang.String" lock="true"
db-attribute-path="NOTE"/>
</obj-entity>
<obj-entity name="Iosbu"
className="nzs.paint.server.entitySupport.entities.Iosbu"
lock-type="optimistic" dbEntityName="IOSBU"
superClassName="nzs.common.server.entitySupport.BaseEntity">
<obj-attribute name="batchNo" type="java.lang.Integer"
lock="true" db-attribute-path="BATCH_NO"/>
<obj-attribute name="buNo" type="java.lang.Integer" lock="true"
db-attribute-path="BU_NO"/>
<obj-attribute name="curBay" type="java.lang.String"
lock="true" db-attribute-path="CUR_BAY"/>
<obj-attribute name="curLevel" type="java.lang.String"
lock="true" db-attribute-path="CUR_LEVEL"/>
<obj-attribute name="curQty" type="java.lang.Integer"
lock="true" db-attribute-path="CUR_QTY"/>
<obj-attribute name="curRow" type="java.lang.String"
lock="true" db-attribute-path="CUR_ROW"/>
<obj-attribute name="curYard" type="java.lang.String"
lock="true" db-attribute-path="CUR_YARD"/>
<obj-attribute name="packSlip" type="java.lang.String"
lock="true" db-attribute-path="PACK_SLIP"/>
<obj-attribute name="partCode" type="java.lang.String"
lock="true" db-attribute-path="PART_CODE"/>
<obj-attribute name="purchOrd" type="java.lang.String"
lock="true" db-attribute-path="PURCH_ORD"/>
<obj-attribute name="receiveDt" type="java.util.Date"
lock="true" db-attribute-path="RECEIVE_DT"/>
<obj-attribute name="suplrNo" type="java.lang.String"
lock="true" db-attribute-path="SUPLR_NO"/>
</obj-entity>
<db-relationship name="iosbArray" source="IOS" target="IOSB"
toMany="true">
<db-attribute-pair source="PART_CODE" target="PART_CODE"/>
</db-relationship>
<db-relationship name="iosbgArray" source="IOS" target="IOSBG"
toMany="true">
<db-attribute-pair source="PART_CODE" target="PART_CODE"/>
</db-relationship>
<db-relationship name="toIosbs" source="IOS" target="IOSBS"
toMany="false">
<db-attribute-pair source="DEF_IOSB_STAT" target="IOSB_STAT"/>
</db-relationship>
<db-relationship name="iosbuArray" source="IOSB" target="IOSBU"
toMany="true">
<db-attribute-pair source="IOSB_ID" target="IOSB_ID"/>
</db-relationship>
<db-relationship name="toIos" source="IOSB" target="IOS" toMany="false">
<db-attribute-pair source="PART_CODE" target="PART_CODE"/>
</db-relationship>
<db-relationship name="toIosbg" source="IOSB" target="IOSBG"
toMany="false">
<db-attribute-pair source="IOSBG_ID" target="IOSBG_ID"/>
</db-relationship>
<db-relationship name="toIosbs" source="IOSB" target="IOSBS"
toMany="false">
<db-attribute-pair source="IOSB_STAT" target="IOSB_STAT"/>
</db-relationship>
<db-relationship name="iosbArray" source="IOSBG" target="IOSB"
toMany="true">
<db-attribute-pair source="IOSBG_ID" target="IOSBG_ID"/>
</db-relationship>
<db-relationship name="toIos" source="IOSBG" target="IOS"
toMany="false">
<db-attribute-pair source="PART_CODE" target="PART_CODE"/>
</db-relationship>
<db-relationship name="toIosbs" source="IOSBG" target="IOSBS"
toMany="false">
<db-attribute-pair source="IOSBG_STAT" target="IOSB_STAT"/>
</db-relationship>
<db-relationship name="iosArray" source="IOSBS" target="IOS"
toMany="true">
<db-attribute-pair source="IOSB_STAT" target="DEF_IOSB_STAT"/>
</db-relationship>
<db-relationship name="iosbArray" source="IOSBS" target="IOSB"
toMany="true">
<db-attribute-pair source="IOSB_STAT" target="IOSB_STAT"/>
</db-relationship>
<db-relationship name="iosbgArray" source="IOSBS" target="IOSBG"
toMany="true">
<db-attribute-pair source="IOSB_STAT" target="IOSBG_STAT"/>
</db-relationship>
<db-relationship name="toIosb" source="IOSBU" target="IOSB"
toMany="false">
<db-attribute-pair source="IOSB_ID" target="IOSB_ID"/>
</db-relationship>
<obj-relationship name="iosbArray" source="Ios" target="Iosb"
lock="true" deleteRule="Deny" db-relationship-path="iosbArray"/>
<obj-relationship name="iosbgArray" source="Ios" target="Iosbg"
lock="true" deleteRule="Deny" db-relationship-path="iosbgArray"/>
<obj-relationship name="toIosbs" source="Ios" target="Iosbs"
lock="true" deleteRule="Nullify" db-relationship-path="toIosbs"/>
<obj-relationship name="iosbuArray" source="Iosb" target="Iosbu"
lock="true" deleteRule="Deny" db-relationship-path="iosbuArray"/>
<obj-relationship name="toIos" source="Iosb" target="Ios" lock="true"
deleteRule="Nullify" db-relationship-path="toIos"/>
<obj-relationship name="toIosbg" source="Iosb" target="Iosbg"
lock="true" deleteRule="Nullify" db-relationship-path="toIosbg"/>
<obj-relationship name="toIosbs" source="Iosb" target="Iosbs"
lock="true" deleteRule="Nullify" db-relationship-path="toIosbs"/>
<obj-relationship name="iosbArray" source="Iosbg" target="Iosb"
lock="true" deleteRule="Deny" db-relationship-path="iosbArray"/>
<obj-relationship name="toIos" source="Iosbg" target="Ios" lock="true"
deleteRule="Nullify" db-relationship-path="toIos"/>
<obj-relationship name="toIosbs" source="Iosbg" target="Iosbs"
lock="true" deleteRule="Nullify" db-relationship-path="toIosbs"/>
<obj-relationship name="iosArray" source="Iosbs" target="Ios"
lock="true" deleteRule="Deny" db-relationship-path="iosArray"/>
<obj-relationship name="iosbArray" source="Iosbs" target="Iosb"
lock="true" deleteRule="Deny" db-relationship-path="iosbArray"/>
<obj-relationship name="iosbgArray" source="Iosbs" target="Iosbg"
lock="true" deleteRule="Deny" db-relationship-path="iosbgArray"/>
<obj-relationship name="toIosb" source="Iosbu" target="Iosb"
lock="true" deleteRule="Nullify" db-relationship-path="toIosb"/>
</data-map>
PaintCayenneNode.driver.xml:
?xml version="1.0" encoding="utf-8"?>
<driver project-version="3.0" class="oracle.jdbc.OracleDriver">
<url value="jdbc:oracle:thin:@bspdev2:1521:haint"/>
<connectionPool min="1" max="1"/>
<login userName="********" password="********"/>
</driver>
(User name and password hidden)
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
