<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0" /> <base href="https://hibernate.atlassian.net" />
<title>Message Title</title>
</head>
<body class="jira" style="color: #333; font-family: Arial, sans-serif; font-size: 14px; line-height: 1.429">
<table id="background-table" cellpadding="0" cellspacing="0" width="100%" style="border-collapse: collapse; mso-table-lspace: 0pt; mso-table-rspace: 0pt; background-color: #f5f5f5; border-collapse: collapse; mso-table-lspace: 0pt; mso-table-rspace: 0pt">
<!-- header here -->
<tr>
<td id="header-pattern-container" style="padding: 0px; border-collapse: collapse; padding: 10px 20px">
<table id="header-pattern" cellspacing="0" cellpadding="0" border="0" style="border-collapse: collapse; mso-table-lspace: 0pt; mso-table-rspace: 0pt">
<tr>
<td id="header-avatar-image-container" valign="top" style="padding: 0px; border-collapse: collapse; vertical-align: top; width: 32px; padding-right: 8px"> <img id="header-avatar-image" class="image_fix" src="https://secure.gravatar.com/avatar/84314f17286fd9cab94e12dd3a608231?d=mm&s=48" height="32" width="32" border="0" style="border-radius: 3px; vertical-align: top" />
</td>
<td id="header-text-container" valign="middle" style="padding: 0px; border-collapse: collapse; vertical-align: middle; font-family: Arial, sans-serif; font-size: 14px; line-height: 20px; mso-line-height-rule: exactly; mso-text-raise: 1px"> <a class="user-hover" rel="marco.perazzo" id="email_marco.perazzo" href="https://hibernate.atlassian.net/secure/ViewProfile.jspa?name=marco.perazzo" style="color:#6c797f;; color: #3b73af; text-decoration: none">Marco Perazzo</a> <strong>created</strong> an issue
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td id="email-content-container" style="padding: 0px; border-collapse: collapse; padding: 0 20px">
<table id="email-content-table" cellspacing="0" cellpadding="0" border="0" width="100%" style="border-collapse: collapse; mso-table-lspace: 0pt; mso-table-rspace: 0pt; border-spacing: 0; border-collapse: separate">
<tr>
<!-- there needs to be content in the cell for it to render in some clients -->
<td class="email-content-rounded-top mobile-expand" style="padding: 0px; border-collapse: collapse; color: #fff; padding: 0 15px 0 16px; height: 15px; background-color: #fff; border-left: 1px solid #ccc; border-top: 1px solid #ccc; border-right: 1px solid #ccc; border-bottom: 0; border-top-right-radius: 5px; border-top-left-radius: 5px; height: 10px; line-height: 10px; padding: 0 15px 0 16px; mso-line-height-rule: exactly">
</td>
</tr>
<tr>
<td class="email-content-main mobile-expand " style="padding: 0px; border-collapse: collapse; border-left: 1px solid #ccc; border-right: 1px solid #ccc; border-top: 0; border-bottom: 0; padding: 0 15px 0 16px; background-color: #fff">
<table class="page-title-pattern" cellspacing="0" cellpadding="0" border="0" width="100%" style="border-collapse: collapse; mso-table-lspace: 0pt; mso-table-rspace: 0pt">
<tr>
<td class="page-title-pattern-first-line " style="padding: 0px; border-collapse: collapse; font-family: Arial, sans-serif; font-size: 14px; padding-top: 10px"> <a href="https://hibernate.atlassian.net/browse/HHH" style="color: #3b73af; text-decoration: none">Hibernate ORM</a> / <a href="https://hibernate.atlassian.net/browse/HHH-9232" style="color: #3b73af; text-decoration: none"><img src="cid:jira-generated-image-static-bug-fd227cb3-e49a-4aa2-a556-f36c4412477e" height="16" width="16" border="0" align="absmiddle" alt="Bug" style="vertical-align: text-bottom" /></a> <a href="https://hibernate.atlassian.net/browse/HHH-9232" style="color: #3b73af; text-decoration: none">HHH-9232</a>
</td>
</tr>
<tr>
<td style="vertical-align: top;; padding: 0px; border-collapse: collapse; padding-right: 5px; font-size: 20px; line-height: 30px; mso-line-height-rule: exactly" class="page-title-pattern-header-container"> <span class="page-title-pattern-header" style="font-family: Arial, sans-serif; padding: 0; font-size: 20px; line-height: 30px; mso-text-raise: 2px; mso-line-height-rule: exactly; vertical-align: middle"> <a href="https://hibernate.atlassian.net/browse/HHH-9232" style="color: #3b73af; text-decoration: none">Hibernate generates invalid Left Outer Join statement (related to single table inheritance)</a> </span>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td class="email-content-main mobile-expand wrapper-special-margin" style="padding: 0px; border-collapse: collapse; border-left: 1px solid #ccc; border-right: 1px solid #ccc; border-top: 0; border-bottom: 0; padding: 0 15px 0 16px; background-color: #fff; padding-top: 10px; padding-bottom: 5px">
<table class="keyvalue-table" style="border-collapse: collapse; mso-table-lspace: 0pt; mso-table-rspace: 0pt">
<tr>
<th style="color: #707070; font: normal 14px/20px Arial, sans-serif; text-align: left; vertical-align: top; padding: 2px 0">
Issue Type:
</th>
<td class="has-icon" style="padding: 0px; border-collapse: collapse; font: normal 14px/20px Arial, sans-serif; padding: 2px 0 2px 5px; vertical-align: top"> <img src="cid:jira-generated-image-static-bug-fd227cb3-e49a-4aa2-a556-f36c4412477e" height="16" width="16" border="0" align="absmiddle" alt="Bug" style="vertical-align: text-bottom" /> Bug
</td>
</tr>
<tr>
<th style="color: #707070; font: normal 14px/20px Arial, sans-serif; text-align: left; vertical-align: top; padding: 2px 0">
Affects Versions:
</th>
<td style="padding: 0px; border-collapse: collapse; font: normal 14px/20px Arial, sans-serif; padding: 2px 0 2px 5px; vertical-align: top">
4.3.5
</td>
</tr>
<tr>
<th style="color: #707070; font: normal 14px/20px Arial, sans-serif; text-align: left; vertical-align: top; padding: 2px 0">
Assignee:
</th>
<td style="padding: 0px; border-collapse: collapse; font: normal 14px/20px Arial, sans-serif; padding: 2px 0 2px 5px; vertical-align: top">
Unassigned
</td>
</tr>
<tr>
<th style="color: #707070; font: normal 14px/20px Arial, sans-serif; text-align: left; vertical-align: top; padding: 2px 0">
Created:
</th>
<td style="padding: 0px; border-collapse: collapse; font: normal 14px/20px Arial, sans-serif; padding: 2px 0 2px 5px; vertical-align: top">
05/Jun/14 6:14 AM
</td>
</tr>
<tr>
<th style="color: #707070; font: normal 14px/20px Arial, sans-serif; text-align: left; vertical-align: top; padding: 2px 0">
Priority:
</th>
<td class="has-icon" style="padding: 0px; border-collapse: collapse; font: normal 14px/20px Arial, sans-serif; padding: 2px 0 2px 5px; vertical-align: top"> <img src="cid:jira-generated-image-static-critical-468977b4-446a-4ed4-8f0d-0ec20333b656" height="16" width="16" border="0" align="absmiddle" alt="Critical" style="vertical-align: text-bottom" /> Critical
</td>
</tr>
<tr>
<th style="color: #707070; font: normal 14px/20px Arial, sans-serif; text-align: left; vertical-align: top; padding: 2px 0">
Reporter:
</th>
<td style="padding: 0px; border-collapse: collapse; font: normal 14px/20px Arial, sans-serif; padding: 2px 0 2px 5px; vertical-align: top"> <a class="user-hover" rel="marco.perazzo" id="email_marco.perazzo" href="https://hibernate.atlassian.net/secure/ViewProfile.jspa?name=marco.perazzo" style="color:#6c797f;; color: #3b73af; text-decoration: none">Marco Perazzo</a>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td class="email-content-main mobile-expand issue-description-container" style="padding: 0px; border-collapse: collapse; border-left: 1px solid #ccc; border-right: 1px solid #ccc; border-top: 0; border-bottom: 0; padding: 0 15px 0 16px; background-color: #fff; padding-top: 5px; padding-bottom: 10px">
<table class="text-paragraph-pattern" cellspacing="0" cellpadding="0" border="0" width="100%" style="border-collapse: collapse; mso-table-lspace: 0pt; mso-table-rspace: 0pt; font-family: Arial, sans-serif; font-size: 14px; line-height: 20px; mso-line-height-rule: exactly; mso-text-raise: 2px">
<tr>
<td class="text-paragraph-pattern-container mobile-resize-text " style="padding: 0px; border-collapse: collapse; padding: 0 0 10px 0">
<p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0 0">In our project Hibernate produces an invalid SQL statement under certain circumstances.</p>
<p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0 0">Let's assume the following classes/tables (very simplified, of course):</p>
<p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0 0">A --</p>
<del>
OneToMany(eager) --> B ---OneToOne(eager)
</del>--> C
<p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0 0">A has a list of objects of type B, linked via a foreign key column in B.<br /> B is actually an abstract class having six subclasses. Here we use Single Table inheritance type, so all six subclasses are put into table B.<br /> Let's say the six classes are called B1...B6.<br /> B1 has an additional attribute of type 'date' while B2 has additionally a foreign key column pointing to table C. The other four subclasses B3...B6 should not be relevant.<br /> Now if we have an object A that has at least one object of type B1 (the one with the date value) in its 'B' list and we query this 'A' object, the error occurs.<br /> If we have a look into the SQL that is generated by Hibernate we see that the join from B to C is incorrect. Instead of joining from the foreign key column of B2 to the id column to C it joines from the Date column of B1 to the ID column of ID. This does of course not work because it cannot compare the 'Date' value with the ID column of C which is a 'BigInt'.<br /> The actual error message we get looks like this:</p>
<p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0 0">Cannot parse "TIMESTAMP" constant "10002303"; SQL statement:</p>
<p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0 0"> select<br /> wizardinst0_.id as id1_106_0_,<br /> wizardinst0_.createdAt as createdA2_106_0_,<br /> wizardinst0_.CurrentWizardStepId as CurrentW7_106_0_,<br /> wizardinst0_.remarks as remarks3_106_0_,<br /> wizardinst0_.tenantId as tenantId4_106_0_,<br /> wizardinst0_.updatedAt as updatedA5_106_0_,<br /> wizardinst0_.userId as userId6_106_0_,<br /> wizardinst0_.WizardId as WizardId8_106_0_,<br /> wizardstep1_.id as id1_108_1_,<br /> wizardstep1_.name as name2_108_1_,<br /> wizardstep1_.position as position3_108_1_,<br /> wizardstep1_.WizardId as WizardId4_108_1_,<br /> wizard2_.id as id1_105_2_,<br /> wizard2_.description as descript2_105_2_,<br /> wizard2_.name as name3_105_2_,<br /> data3_.WizardInstanceId as WizardIn9_106_3_,<br /> data3_.id as id2_107_3_,<br /> data3_.id as id2_107_4_,<br /> data3_.name as name3_107_4_,<br /> data3_.WizardInstanceId as WizardIn9_107_4_,<br /> data3_.ValueDate as ValueDat4_107_4_,<br /> data3_.ValueBoolean as ValueBoo5_107_4_,<br /> data3_.MultiLingualStringId as MultiLi10_107_4_,<br /> data3_.ValueString as ValueStr6_107_4_,<br /> data3_.ValueDecimal as ValueDec7_107_4_,<br /> data3_.ValueInteger as ValueInt8_107_4_,<br /> data3_.discriminator as discrimi1_107_4_,<br /> multilingu4_.id as id1_96_5_,<br /> localizeds5_.MultiLingualStringId as MultiLin4_96_6_,<br /> localizeds5_.id as id1_97_6_,<br /> localizeds5_.language as formula1_6_,<br /> localizeds5_.id as id1_97_7_,<br /> localizeds5_.language as language2_97_7_,<br /> localizeds5_.MultiLingualStringId as MultiLin4_97_7_,<br /> localizeds5_.LocalizedText as Localize3_97_7_,<br /> wizard6_.id as id1_105_8_,<br /> wizard6_.description as descript2_105_8_,<br /> wizard6_.name as name3_105_8_ <br /> from<br /> STFSYS_WizardInstance wizardinst0_ <br /> inner join<br /> STFSYS_WizardStep wizardstep1_ <br /> on wizardinst0_.CurrentWizardStepId=wizardstep1_.id <br /> inner join<br /> STFSYS_Wizard wizard2_ <br /> on wizardstep1_.WizardId=wizard2_.id <br /> left outer join<br /> STFSYS_WizardInstanceData data3_ <br /> on wizardinst0_.id=data3_.WizardInstanceId <br /> left outer join<br /> STFSYS_MultiLingualString multilingu4_ <br /> on data3_.ValueDate=multilingu4_.id <br /> left outer join<br /> STFSYS_MultiLingualStringMap localizeds5_ <br /> on multilingu4_.id=localizeds5_.MultiLingualStringId <br /> inner join<br /> STFSYS_Wizard wizard6_ <br /> on wizardinst0_.WizardId=wizard6_.id <br /> where<br /> wizardinst0_.id=?</p>
<p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0 0">--> "10002303" is an ID in table C<br /> --> STFSYS_WizardInstance is 'A'<br /> --> STFSYS_WizardInstanceData is 'B'<br /> --> STFSYS_MultiLingualString is 'C'<br /> --> STFSYS_MultiLingualString.ValueDate is the 'Date' column of 'B1'<br /> --> STFSYS_MultiLingualString.MultiLingualStringId is the FK belonging to 'B2'</p>
<p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0 0">So instead of ...<br /> <span class="error">[...]</span><br /> left outer join<br /> STFSYS_MultiLingualString multilingu4_ <br /> on data3_.ValueDate=multilingu4_.id <br /> <span class="error">[...]</span><br /> ... it should be:<br /> <span class="error">[...]</span><br /> left outer join<br /> STFSYS_MultiLingualString multilingu4_ <br /> on data3_.MultiLingualStringId=multilingu4_.id <br /> <span class="error">[...]</span></p>
<p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0 0">This how the assocation between B2 and C is defined in the code:</p>
<p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0 0">@OneToOne(cascade = CascadeType.ALL, orphanRemoval=true, fetch = FetchType.EAGER)<br /> @JoinColumn(name="MultiLingualStringId")<br /> public MultiLingualString getValue() </p> { return value; }
<p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0 0">I have no clue why Hibernate uses 'ValueDate' as the join column, because in the code it is clearly defined as 'MultiLingualStringId'.</p>
<p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0 0">I hope all this information is enough to be able to reproduce the bug.</p>
<p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0 0">PS: My workmate told me for finding this bug we should be rewarded with a crate of beer. <img class="emoticon" src="https://hibernate.atlassian.net/images/icons/emoticons/smile.gif" height="16" width="16" align="absmiddle" alt="" border="0" /></p>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td class="email-content-main mobile-expand " style="padding: 0px; border-collapse: collapse; border-left: 1px solid #ccc; border-right: 1px solid #ccc; border-top: 0; border-bottom: 0; padding: 0 15px 0 16px; background-color: #fff">
<table id="actions-pattern" cellspacing="0" cellpadding="0" border="0" width="100%" style="border-collapse: collapse; mso-table-lspace: 0pt; mso-table-rspace: 0pt; font-family: Arial, sans-serif; font-size: 14px; line-height: 20px; mso-line-height-rule: exactly; mso-text-raise: 1px">
<tr>
<td id="actions-pattern-container" valign="middle" style="padding: 0px; border-collapse: collapse; padding: 10px 0 10px 24px; vertical-align: middle; padding-left: 0">
<table align="left" style="border-collapse: collapse; mso-table-lspace: 0pt; mso-table-rspace: 0pt">
<tr>
<td class="actions-pattern-action-icon-container" style="padding: 0px; border-collapse: collapse; font-family: Arial, sans-serif; font-size: 14px; line-height: 20px; mso-line-height-rule: exactly; mso-text-raise: 0px; vertical-align: middle"> <a href="https://hibernate.atlassian.net/browse/HHH-9232#add-comment" target="_blank" title="Add Comment" style="color: #3b73af; text-decoration: none"> <img class="actions-pattern-action-icon-image" src="cid:jira-generated-image-static-comment-icon-c1ee9a52-3cef-4f4b-a3ff-3eb597084751" alt="Add Comment" title="Add Comment" height="16" width="16" border="0" style="vertical-align: middle" /> </a>
</td>
<td class="actions-pattern-action-text-container" style="padding: 0px; border-collapse: collapse; font-family: Arial, sans-serif; font-size: 14px; line-height: 20px; mso-line-height-rule: exactly; mso-text-raise: 4px; padding-left: 5px"> <a href="https://hibernate.atlassian.net/browse/HHH-9232#add-comment" target="_blank" title="Add Comment" style="color: #3b73af; text-decoration: none">Add Comment</a>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
</tr>
<!-- there needs to be content in the cell for it to render in some clients -->
<tr>
<td class="email-content-rounded-bottom mobile-expand" style="padding: 0px; border-collapse: collapse; color: #fff; padding: 0 15px 0 16px; height: 5px; line-height: 5px; background-color: #fff; border-top: 0; border-left: 1px solid #ccc; border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; border-bottom-right-radius: 5px; border-bottom-left-radius: 5px; mso-line-height-rule: exactly">
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td id="footer-pattern" style="padding: 0px; border-collapse: collapse; padding: 12px 20px">
<table id="footer-pattern-container" cellspacing="0" cellpadding="0" border="0" style="border-collapse: collapse; mso-table-lspace: 0pt; mso-table-rspace: 0pt">
<tr>
<td id="footer-pattern-text" class="mobile-resize-text" width="100%" style="padding: 0px; border-collapse: collapse; color: #999; font-size: 12px; line-height: 18px; font-family: Arial, sans-serif; mso-line-height-rule: exactly; mso-text-raise: 2px">
This message was sent by Atlassian JIRA <span id="footer-build-information">(v6.3-OD-06-017#6327-<span title="b47b85e7f4061e9b3bf1c2e4c8c7600edebb8d30" data-commit-id="b47b85e7f4061e9b3bf1c2e4c8c7600edebb8d30}">sha1:b47b85e</span>)</span>
</td>
<td id="footer-pattern-logo-desktop-container" valign="top" style="padding: 0px; border-collapse: collapse; padding-left: 20px; vertical-align: top">
<table style="border-collapse: collapse; mso-table-lspace: 0pt; mso-table-rspace: 0pt">
<tr>
<td id="footer-pattern-logo-desktop-padding" style="padding: 0px; border-collapse: collapse; padding-top: 3px"> <img id="footer-pattern-logo-desktop" src="cid:jira-generated-image-static-footer-desktop-logo-5b7c19d1-7749-4f67-8a92-418c9a822a8e" alt="Atlassian logo" title="Atlassian logo" width="169" height="36" class="image_fix" />
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
</tr>
</table>
</body>
</html>