You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "Michael McGovern (JIRA)" <ji...@apache.org> on 2010/04/12 12:21:41 UTC
[jira] Created: (OPENJPA-1627) ORderBy with @ElementJoinColumn and
EmbeddedId uses wrong columns in SQL
ORderBy with @ElementJoinColumn and EmbeddedId uses wrong columns in SQL
------------------------------------------------------------------------
Key: OPENJPA-1627
URL: https://issues.apache.org/jira/browse/OPENJPA-1627
Project: OpenJPA
Issue Type: Bug
Components: sql
Affects Versions: 2.0.0-beta3
Environment: Windows 7 32 bit / Oracle XE
Reporter: Michael McGovern
Typical bank example, Account with Transactions. It is a legacy db so Transaction has compound key - represented by TransactionId class.
The problem is that the order by in the generated SQL is for columns mapped in the transaction entity NOT the TransacionId as expected.
So the Account class has the following fragment....
@OneToMany(fetch = FetchType.LAZY, cascade = {CascadeType.PERSIST})
@ElementJoinColumn(name="maccno", referencedColumnName="maccno")
@OrderBy(value = "_id._processDate ASC, _id._tranSequenceNumber ASC")
private LinkedList<Transaction> _transactions;
_processDate and _tranSequenceNumber are defined in the TransactionId class.
Transaction has the following fragment....
@EmbeddedId
private TransactionId _id;
@Column(name = "mtrancde")
private int _transactionCode;
@Column(name = "mamount")
private BigDecimal _amount;
@Column(name = "mdesc")
private String _description;
@Column(name = "mactdate")
private Date _actualDate;
@Column(name = "mbranch")
private int _branch;
And TransactionId defines the primary key columns....
@Embeddable
public class TransactionId {
@Column(name = "maccno")
private String _accountNumber;
@Column(name = "mprocdate")
private Date _processDate;
@Column(name = "mtranseqno")
private int _tranSequenceNumber;
However the generated SQL is doing order by on columns mapped in Transaction:
executing prepstmnt 23188098 SELECT t0.maccno, t0.mprocdate, t0.mtranseqno, t0.mactdate, t0.mamount, t0.mbranch, t0.mchqcash, t0.mdesc,
t0.mtmnlno, t0.mtrancde, t0.mtrnfeed
FROM transaction t0
WHERE t0.maccno = ?
ORDER BY t0.mamount ASC, t0.mbranch ASC [params=(String) 000734123]
(no idea why it chose mamount, mbranch)
The last line should be:
ORDER BY t0.mprocdate ASC, t0.mtranseqno ASC [params=(String) 000734123]
Thanks
Michael
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (OPENJPA-1627) ORderBy with @ElementJoinColumn
and EmbeddedId uses wrong columns in SQL
Posted by "Michael McGovern (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1627?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12858525#action_12858525 ]
Michael McGovern commented on OPENJPA-1627:
-------------------------------------------
Fay - thanks - that works very well.
_________________________________________________________________
Need a new place to live? Find it on Domain.com.au
http://clk.atdmt.com/NMN/go/157631292/direct/01/
> ORderBy with @ElementJoinColumn and EmbeddedId uses wrong columns in SQL
> ------------------------------------------------------------------------
>
> Key: OPENJPA-1627
> URL: https://issues.apache.org/jira/browse/OPENJPA-1627
> Project: OpenJPA
> Issue Type: Bug
> Components: sql
> Affects Versions: 2.0.0-beta3
> Environment: Windows 7 32 bit / Oracle XE
> Reporter: Michael McGovern
> Assignee: Fay Wang
> Attachments: OPENJPA-1627.patch
>
>
> Typical bank example, Account with Transactions. It is a legacy db so Transaction has compound key - represented by TransactionId class.
> The problem is that the order by in the generated SQL is for columns mapped in the transaction entity NOT the TransacionId as expected.
> So the Account class has the following fragment....
> @OneToMany(fetch = FetchType.LAZY, cascade = {CascadeType.PERSIST})
> @ElementJoinColumn(name="maccno", referencedColumnName="maccno")
> @OrderBy(value = "_id._processDate ASC, _id._tranSequenceNumber ASC")
> private LinkedList<Transaction> _transactions;
> _processDate and _tranSequenceNumber are defined in the TransactionId class.
> Transaction has the following fragment....
> @EmbeddedId
> private TransactionId _id;
>
> @Column(name = "mtrancde")
> private int _transactionCode;
>
> @Column(name = "mamount")
> private BigDecimal _amount;
>
> @Column(name = "mdesc")
> private String _description;
>
> @Column(name = "mactdate")
> private Date _actualDate;
>
> @Column(name = "mbranch")
> private int _branch;
> And TransactionId defines the primary key columns....
> @Embeddable
> public class TransactionId {
>
> @Column(name = "maccno")
> private String _accountNumber;
>
> @Column(name = "mprocdate")
> private Date _processDate;
>
> @Column(name = "mtranseqno")
> private int _tranSequenceNumber;
> However the generated SQL is doing order by on columns mapped in Transaction:
> executing prepstmnt 23188098 SELECT t0.maccno, t0.mprocdate, t0.mtranseqno, t0.mactdate, t0.mamount, t0.mbranch, t0.mchqcash, t0.mdesc,
> t0.mtmnlno, t0.mtrancde, t0.mtrnfeed
> FROM transaction t0
> WHERE t0.maccno = ?
> ORDER BY t0.mamount ASC, t0.mbranch ASC [params=(String) 000734123]
> (no idea why it chose mamount, mbranch)
> The last line should be:
> ORDER BY t0.mprocdate ASC, t0.mtranseqno ASC [params=(String) 000734123]
> Thanks
> Michael
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (OPENJPA-1627) ORderBy with @ElementJoinColumn and
EmbeddedId uses wrong columns in SQL
Posted by "Fay Wang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1627?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Fay Wang updated OPENJPA-1627:
------------------------------
Attachment: OPENJPA-1627.patch
Hi Mike, can you test if the attached patch fix your problem? Thanks.
> ORderBy with @ElementJoinColumn and EmbeddedId uses wrong columns in SQL
> ------------------------------------------------------------------------
>
> Key: OPENJPA-1627
> URL: https://issues.apache.org/jira/browse/OPENJPA-1627
> Project: OpenJPA
> Issue Type: Bug
> Components: sql
> Affects Versions: 2.0.0-beta3
> Environment: Windows 7 32 bit / Oracle XE
> Reporter: Michael McGovern
> Assignee: Fay Wang
> Attachments: OPENJPA-1627.patch
>
>
> Typical bank example, Account with Transactions. It is a legacy db so Transaction has compound key - represented by TransactionId class.
> The problem is that the order by in the generated SQL is for columns mapped in the transaction entity NOT the TransacionId as expected.
> So the Account class has the following fragment....
> @OneToMany(fetch = FetchType.LAZY, cascade = {CascadeType.PERSIST})
> @ElementJoinColumn(name="maccno", referencedColumnName="maccno")
> @OrderBy(value = "_id._processDate ASC, _id._tranSequenceNumber ASC")
> private LinkedList<Transaction> _transactions;
> _processDate and _tranSequenceNumber are defined in the TransactionId class.
> Transaction has the following fragment....
> @EmbeddedId
> private TransactionId _id;
>
> @Column(name = "mtrancde")
> private int _transactionCode;
>
> @Column(name = "mamount")
> private BigDecimal _amount;
>
> @Column(name = "mdesc")
> private String _description;
>
> @Column(name = "mactdate")
> private Date _actualDate;
>
> @Column(name = "mbranch")
> private int _branch;
> And TransactionId defines the primary key columns....
> @Embeddable
> public class TransactionId {
>
> @Column(name = "maccno")
> private String _accountNumber;
>
> @Column(name = "mprocdate")
> private Date _processDate;
>
> @Column(name = "mtranseqno")
> private int _tranSequenceNumber;
> However the generated SQL is doing order by on columns mapped in Transaction:
> executing prepstmnt 23188098 SELECT t0.maccno, t0.mprocdate, t0.mtranseqno, t0.mactdate, t0.mamount, t0.mbranch, t0.mchqcash, t0.mdesc,
> t0.mtmnlno, t0.mtrancde, t0.mtrnfeed
> FROM transaction t0
> WHERE t0.maccno = ?
> ORDER BY t0.mamount ASC, t0.mbranch ASC [params=(String) 000734123]
> (no idea why it chose mamount, mbranch)
> The last line should be:
> ORDER BY t0.mprocdate ASC, t0.mtranseqno ASC [params=(String) 000734123]
> Thanks
> Michael
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Assigned: (OPENJPA-1627) ORderBy with @ElementJoinColumn and
EmbeddedId uses wrong columns in SQL
Posted by "Fay Wang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1627?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Fay Wang reassigned OPENJPA-1627:
---------------------------------
Assignee: Fay Wang
> ORderBy with @ElementJoinColumn and EmbeddedId uses wrong columns in SQL
> ------------------------------------------------------------------------
>
> Key: OPENJPA-1627
> URL: https://issues.apache.org/jira/browse/OPENJPA-1627
> Project: OpenJPA
> Issue Type: Bug
> Components: sql
> Affects Versions: 2.0.0-beta3
> Environment: Windows 7 32 bit / Oracle XE
> Reporter: Michael McGovern
> Assignee: Fay Wang
>
> Typical bank example, Account with Transactions. It is a legacy db so Transaction has compound key - represented by TransactionId class.
> The problem is that the order by in the generated SQL is for columns mapped in the transaction entity NOT the TransacionId as expected.
> So the Account class has the following fragment....
> @OneToMany(fetch = FetchType.LAZY, cascade = {CascadeType.PERSIST})
> @ElementJoinColumn(name="maccno", referencedColumnName="maccno")
> @OrderBy(value = "_id._processDate ASC, _id._tranSequenceNumber ASC")
> private LinkedList<Transaction> _transactions;
> _processDate and _tranSequenceNumber are defined in the TransactionId class.
> Transaction has the following fragment....
> @EmbeddedId
> private TransactionId _id;
>
> @Column(name = "mtrancde")
> private int _transactionCode;
>
> @Column(name = "mamount")
> private BigDecimal _amount;
>
> @Column(name = "mdesc")
> private String _description;
>
> @Column(name = "mactdate")
> private Date _actualDate;
>
> @Column(name = "mbranch")
> private int _branch;
> And TransactionId defines the primary key columns....
> @Embeddable
> public class TransactionId {
>
> @Column(name = "maccno")
> private String _accountNumber;
>
> @Column(name = "mprocdate")
> private Date _processDate;
>
> @Column(name = "mtranseqno")
> private int _tranSequenceNumber;
> However the generated SQL is doing order by on columns mapped in Transaction:
> executing prepstmnt 23188098 SELECT t0.maccno, t0.mprocdate, t0.mtranseqno, t0.mactdate, t0.mamount, t0.mbranch, t0.mchqcash, t0.mdesc,
> t0.mtmnlno, t0.mtrancde, t0.mtrnfeed
> FROM transaction t0
> WHERE t0.maccno = ?
> ORDER BY t0.mamount ASC, t0.mbranch ASC [params=(String) 000734123]
> (no idea why it chose mamount, mbranch)
> The last line should be:
> ORDER BY t0.mprocdate ASC, t0.mtranseqno ASC [params=(String) 000734123]
> Thanks
> Michael
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (OPENJPA-1627) ORderBy with @ElementJoinColumn and
EmbeddedId uses wrong columns in SQL
Posted by "Donald Woods (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1627?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Donald Woods updated OPENJPA-1627:
----------------------------------
Fix Version/s: 2.1.0
Patch Info: [Patch Available]
the fix has been checked into trunk (2.1)
will decide later if we want this in 2.0.1
> ORderBy with @ElementJoinColumn and EmbeddedId uses wrong columns in SQL
> ------------------------------------------------------------------------
>
> Key: OPENJPA-1627
> URL: https://issues.apache.org/jira/browse/OPENJPA-1627
> Project: OpenJPA
> Issue Type: Bug
> Components: sql
> Affects Versions: 2.0.0-beta3
> Environment: Windows 7 32 bit / Oracle XE
> Reporter: Michael McGovern
> Assignee: Fay Wang
> Fix For: 2.1.0
>
> Attachments: OPENJPA-1627.patch
>
>
> Typical bank example, Account with Transactions. It is a legacy db so Transaction has compound key - represented by TransactionId class.
> The problem is that the order by in the generated SQL is for columns mapped in the transaction entity NOT the TransacionId as expected.
> So the Account class has the following fragment....
> @OneToMany(fetch = FetchType.LAZY, cascade = {CascadeType.PERSIST})
> @ElementJoinColumn(name="maccno", referencedColumnName="maccno")
> @OrderBy(value = "_id._processDate ASC, _id._tranSequenceNumber ASC")
> private LinkedList<Transaction> _transactions;
> _processDate and _tranSequenceNumber are defined in the TransactionId class.
> Transaction has the following fragment....
> @EmbeddedId
> private TransactionId _id;
>
> @Column(name = "mtrancde")
> private int _transactionCode;
>
> @Column(name = "mamount")
> private BigDecimal _amount;
>
> @Column(name = "mdesc")
> private String _description;
>
> @Column(name = "mactdate")
> private Date _actualDate;
>
> @Column(name = "mbranch")
> private int _branch;
> And TransactionId defines the primary key columns....
> @Embeddable
> public class TransactionId {
>
> @Column(name = "maccno")
> private String _accountNumber;
>
> @Column(name = "mprocdate")
> private Date _processDate;
>
> @Column(name = "mtranseqno")
> private int _tranSequenceNumber;
> However the generated SQL is doing order by on columns mapped in Transaction:
> executing prepstmnt 23188098 SELECT t0.maccno, t0.mprocdate, t0.mtranseqno, t0.mactdate, t0.mamount, t0.mbranch, t0.mchqcash, t0.mdesc,
> t0.mtmnlno, t0.mtrancde, t0.mtrnfeed
> FROM transaction t0
> WHERE t0.maccno = ?
> ORDER BY t0.mamount ASC, t0.mbranch ASC [params=(String) 000734123]
> (no idea why it chose mamount, mbranch)
> The last line should be:
> ORDER BY t0.mprocdate ASC, t0.mtranseqno ASC [params=(String) 000734123]
> Thanks
> Michael
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.