<!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: #333333; 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: 0; mso-table-rspace: 0; background-color: #f5f5f5; border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0" bgcolor="#f5f5f5">
<!-- header here -->
<tbody>
<tr>
<td id="header-pattern-container" style="padding: 0; border-collapse: collapse; padding: 10px 20px">
<table id="header-pattern" cellspacing="0" cellpadding="0" border="0" style="border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0">
<tbody>
<tr>
<td id="header-avatar-image-container" valign="top" style="padding: 0; border-collapse: collapse; vertical-align: top; width: 32px; padding-right: 8px" width="32"> <img id="header-avatar-image" class="image_fix" src="https://avatar-cdn.atlassian.com/56e841801c22c5d64079f22d892c5a71?s=48&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2F56e841801c22c5d64079f22d892c5a71%3Fd%3Dmm%26s%3D48%26noRedirect%3Dtrue" height="32" width="32" border="0" style="border-radius: 3px; vertical-align: top"> </td>
<td id="header-text-container" valign="middle" style="padding: 0; 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="william.li.shiroi" id="email_william.li.shiroi" href="https://hibernate.atlassian.net/secure/ViewProfile.jspa?name=william.li.shiroi" style="color:#6c797f;; color: #3b73af; text-decoration: none">William Li</a> <strong>created</strong> an issue </td>
</tr>
</tbody>
</table> </td>
</tr>
<tr>
<td id="email-content-container" style="padding: 0; 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: 0; mso-table-rspace: 0; border-spacing: 0; border-collapse: separate">
<tbody>
<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: 0; border-collapse: collapse; color: #ffffff; padding: 0 15px 0 16px; height: 15px; background-color: #ffffff; border-left: 1px solid #cccccc; border-top: 1px solid #cccccc; border-right: 1px solid #cccccc; 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" height="10" bgcolor="#ffffff"> </td>
</tr>
<tr>
<td class="email-content-main mobile-expand " style="padding: 0; border-collapse: collapse; border-left: 1px solid #cccccc; border-right: 1px solid #cccccc; border-top: 0; border-bottom: 0; padding: 0 15px 0 16px; background-color: #ffffff" bgcolor="#ffffff">
<table class="page-title-pattern" cellspacing="0" cellpadding="0" border="0" width="100%" style="border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0">
<tbody>
<tr>
<td class="page-title-pattern-first-line " style="padding: 0; 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-12038" style="color: #3b73af; text-decoration: none"><img src="cid:jira-generated-image-avatar-16298ce7-768e-4047-9717-80c74b03a673" height="16" width="16" border="0" align="absmiddle" alt="Bug" style="vertical-align: text-bottom"></a> <a href="https://hibernate.atlassian.net/browse/HHH-12038" style="color: #3b73af; text-decoration: none">HHH-12038</a> </td>
</tr>
<tr>
<td style="vertical-align: top;; padding: 0; 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-12038" style="color: #3b73af; text-decoration: none">TypedQuery named parameter gets rendered as multiple SQL parameters</a> </span> </td>
</tr>
</tbody>
</table> </td>
</tr>
<tr>
<td class="email-content-main mobile-expand wrapper-special-margin" style="padding: 0; border-collapse: collapse; border-left: 1px solid #cccccc; border-right: 1px solid #cccccc; border-top: 0; border-bottom: 0; padding: 0 15px 0 16px; background-color: #ffffff; padding-top: 10px; padding-bottom: 5px" bgcolor="#ffffff">
<table class="keyvalue-table" style="border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0">
<tbody>
<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: 0; border-collapse: collapse; font: normal 14px/20px Arial, sans-serif; padding: 2px 0 2px 5px; vertical-align: top"> <img src="cid:jira-generated-image-avatar-16298ce7-768e-4047-9717-80c74b03a673" 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: 0; border-collapse: collapse; font: normal 14px/20px Arial, sans-serif; padding: 2px 0 2px 5px; vertical-align: top"> 5.1.10 </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: 0; 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">Components:</th>
<td style="padding: 0; border-collapse: collapse; font: normal 14px/20px Arial, sans-serif; padding: 2px 0 2px 5px; vertical-align: top"> hibernate-entitymanager </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: 0; border-collapse: collapse; font: normal 14px/20px Arial, sans-serif; padding: 2px 0 2px 5px; vertical-align: top"> 17/Oct/2017 13:53 PM </td>
</tr>
<tr>
<th style="color: #707070; font: normal 14px/20px Arial, sans-serif; text-align: left; vertical-align: top; padding: 2px 0">Environment:</th>
<td style="padding: 0; border-collapse: collapse; font: normal 14px/20px Arial, sans-serif; padding: 2px 0 2px 5px; vertical-align: top"> Hibernate 5.2.10.Final <br> SqlServer 2008 R2 + </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: 0; 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-major-53531b6c-0ac6-45ae-8b94-5aed4d8c92cc" height="16" width="16" border="0" align="absmiddle" alt="Major" style="vertical-align: text-bottom"> Major </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: 0; border-collapse: collapse; font: normal 14px/20px Arial, sans-serif; padding: 2px 0 2px 5px; vertical-align: top"> <a class="user-hover" rel="william.li.shiroi" id="email_william.li.shiroi" href="https://hibernate.atlassian.net/secure/ViewProfile.jspa?name=william.li.shiroi" style="color:#6c797f;; color: #3b73af; text-decoration: none">William Li</a> </td>
</tr>
</tbody>
</table> </td>
</tr>
<tr>
<td class="email-content-main mobile-expand issue-description-container" style="padding: 0; border-collapse: collapse; border-left: 1px solid #cccccc; border-right: 1px solid #cccccc; border-top: 0; border-bottom: 0; padding: 0 15px 0 16px; background-color: #ffffff; padding-top: 5px; padding-bottom: 10px" bgcolor="#ffffff">
<table class="text-paragraph-pattern" cellspacing="0" cellpadding="0" border="0" width="100%" style="border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0; font-family: Arial, sans-serif; font-size: 14px; line-height: 20px; mso-line-height-rule: exactly; mso-text-raise: 2px">
<tbody>
<tr>
<td class="text-paragraph-pattern-container mobile-resize-text " style="padding: 0; border-collapse: collapse; padding: 0 0 10px"> <p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0; margin-top: 0">Hello,</p> <p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0">I'm encountering an issue with a EntityManger created TypedQuery object.</p> <p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0">The query below has several named parameters ("otlx", "obrx", "otly", and "obry").</p>
<div class="code panel" style="border-width: 1px;; border: 1px solid #cccccc; background: #f5f5f5; font-size: 12px; line-height: 1.333; font-family: monospace; border: 1px solid #cccccc; -moz-border-radius: 3px; border-radius: 3px; margin: 9px 0">
<div class="codeContent panelContent" style="padding: 9px 12px">
<pre class="code-java" style="margin: 10px 0 0; margin-top: 0; max-height: 30em; overflow: auto; white-space: pre-wrap; word-wrap: normal">
query = entityManager.createQuery(
<span class="code-quote" style="color: #009100">"select <span class="code-keyword" style="color: #000091; color: #009100">new</span> map(COUNT(incident.id) as incidentCount, "</span> +
<span class="code-quote" style="color: #009100">"(round((((stx(incident.point) - :otlx)/(:obrx - :otlx))/5),2)*5) as xPercent, "</span> +
<span class="code-quote" style="color: #009100">"(round((((sty(incident.point) - :otly)/(:obry - :otly))/5),2)*5) as yPercent) "</span> +
<span class="code-quote" style="color: #009100">"from UFRIncident incident "</span> +
<span class="code-quote" style="color: #009100">"where incident.point is not <span class="code-keyword" style="color: #000091; color: #009100">null</span> "</span> +
<span class="code-quote" style="color: #009100">"group by (round((((stx(incident.point) - :otlx)/(:obrx - :otlx))/5),2)*5), "</span> +
<span class="code-quote" style="color: #009100">"(round((((sty(incident.point) - :otly)/(:obry - :otly))/5),2)*5)"</span>, Map.class);
query.setParameter(<span class="code-quote" style="color: #009100">"otlx"</span>, (<span class="code-object" style="color: #910091">float</span>)otl.getCoordinate().x);
query.setParameter(<span class="code-quote" style="color: #009100">"otly"</span>, (<span class="code-object" style="color: #910091">float</span>)otl.getCoordinate().y);
query.setParameter(<span class="code-quote" style="color: #009100">"obrx"</span>, (<span class="code-object" style="color: #910091">float</span>)obr.getCoordinate().x);
query.setParameter(<span class="code-quote" style="color: #009100">"obry"</span>, (<span class="code-object" style="color: #910091">float</span>)obr.getCoordinate().y);
</pre>
</div>
</div> <p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0">The issue is that when this query gets sent down to SQL Server, it gets converted into the following:</p>
<div class="code panel" style="border-width: 1px;; border: 1px solid #cccccc; background: #f5f5f5; font-size: 12px; line-height: 1.333; font-family: monospace; border: 1px solid #cccccc; -moz-border-radius: 3px; border-radius: 3px; margin: 9px 0">
<div class="codeContent panelContent" style="padding: 9px 12px">
<pre class="code-sql" style="margin: 10px 0 0; margin-top: 0; max-height: 30em; overflow: auto; white-space: pre-wrap; word-wrap: normal">
<span class="code-keyword" style="color: #000091">exec</span> sp_executesql N'<span class="code-keyword" style="color: #000091">select</span> <span class="code-keyword" style="color: #000091">count</span>(ufrinciden0_.id) <span class="code-keyword" style="color: #000091">as</span> col_0_0_,
round((ufrinciden0_.point.STX- @P0 )/( @P1 - @P2 )/5, 2)*5 <span class="code-keyword" style="color: #000091">as</span> col_1_0_,
round((ufrinciden0_.point.STY- @P3 )/( @P4 - @P5 )/5, 2)*5 <span class="code-keyword" style="color: #000091">as</span> col_2_0_
<span class="code-keyword" style="color: #000091">from</span>
ufr_forms ufrinciden0_
<span class="code-keyword" style="color: #000091">where</span>
ufrinciden0_.point <span class="code-keyword" style="color: #000091">is</span> <span class="code-keyword" style="color: #000091">not</span> <span class="code-keyword" style="color: #000091">null</span>
<span class="code-keyword" style="color: #000091">group</span> <span class="code-keyword" style="color: #000091">by</span>
round((ufrinciden0_.point.STX- @P6 )/( @P7 - @P8 )/5, 2)*5 ,
round((ufrinciden0_.point.STY- @P9 )/( @P10 - @P11 )/5, 2)*5'
,N<span class="code-quote" style="color: #009100">'@P0 <span class="code-keyword" style="color: #000091; color: #009100">real</span>,@P1 <span class="code-keyword" style="color: #000091; color: #009100">real</span>,@P2 <span class="code-keyword" style="color: #000091; color: #009100">real</span>,@P3 <span class="code-keyword" style="color: #000091; color: #009100">real</span>,@P4 <span class="code-keyword" style="color: #000091; color: #009100">real</span>,@P5 <span class="code-keyword" style="color: #000091; color: #009100">real</span>,@P6 <span class="code-keyword" style="color: #000091; color: #009100">real</span>,@P7 <span class="code-keyword" style="color: #000091; color: #009100">real</span>,@P8 <span class="code-keyword" style="color: #000091; color: #009100">real</span>,@P9 <span class="code-keyword" style="color: #000091; color: #009100">real</span>,@P10 <span class="code-keyword" style="color: #000091; color: #009100">real</span>,@P11 <span class="code-keyword" style="color: #000091; color: #009100">real</span>'</span>,-71,-97.5,-71,26,49,26,-71,-97.5,-71,26,49,26
</pre>
</div>
</div> <p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0">The issue with this is that the group by clause is not recognized as being the same as one of the selected fields and the following error is returned:</p>
<div class="code panel" style="border-width: 1px;; border: 1px solid #cccccc; background: #f5f5f5; font-size: 12px; line-height: 1.333; font-family: monospace; border: 1px solid #cccccc; -moz-border-radius: 3px; border-radius: 3px; margin: 9px 0">
<div class="codeContent panelContent" style="padding: 9px 12px">
<pre class="code-java" style="margin: 10px 0 0; margin-top: 0; max-height: 30em; overflow: auto; white-space: pre-wrap; word-wrap: normal">
Caused by: java.sql.SQLException: Column <span class="code-quote" style="color: #009100">'ufr_forms.POINT'</span> is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
</pre>
</div>
</div> <p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0">If I modify the sp_executesql string to only use @P0 to @P3, it all works fine but you'll notice that right now, even though the named parameters in the original TypedQuery object is the same, the rendered SQL Query has 11 different parameter values!</p> <p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0">How would I go about addressing this issue?</p> <p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0">Thanks!</p> </td>
</tr>
</tbody>
</table> </td>
</tr>
<tr>
<td class="email-content-main mobile-expand " style="padding: 0; border-collapse: collapse; border-left: 1px solid #cccccc; border-right: 1px solid #cccccc; border-top: 0; border-bottom: 0; padding: 0 15px 0 16px; background-color: #ffffff" bgcolor="#ffffff"> <script type="application/ld+json">
{
"@context": "http://schema.org",
"@type": "EmailMessage",
"description": "View Issue",
"potentialAction": {
"@type": "ViewAction",
"target": "https://hibernate.atlassian.net/browse/HHH-12038?inbox=true&",
"name": "View Issue"
},
"publisher": {
"@type": "Organization",
"name": "Atlassian",
"url": "https://www.atlassian.com"
}
}
</script>
<table id="actions-pattern" cellspacing="0" cellpadding="0" border="0" width="100%" style="border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0; font-family: Arial, sans-serif; font-size: 14px; line-height: 20px; mso-line-height-rule: exactly; mso-text-raise: 1px">
<tbody>
<tr>
<td id="actions-pattern-container" valign="middle" style="padding: 0; border-collapse: collapse; padding: 10px 0 10px 24px; vertical-align: middle; padding-left: 0">
<table align="left" style="border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0">
<tbody>
<tr>
<td class="actions-pattern-action-icon-container" style="padding: 0; border-collapse: collapse; font-family: Arial, sans-serif; font-size: 14px; line-height: 20px; mso-line-height-rule: exactly; mso-text-raise: 0; vertical-align: middle"> <a href="https://hibernate.atlassian.net/browse/HHH-12038#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-65134b11-7dfc-4399-a880-57dad1764c34" 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: 0; 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-12038#add-comment" target="_blank" title="Add Comment" style="color: #3b73af; text-decoration: none">Add Comment</a> </td>
</tr>
</tbody>
</table> </td>
</tr>
</tbody>
</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: 0; border-collapse: collapse; color: #ffffff; padding: 0 15px 0 16px; height: 5px; line-height: 5px; background-color: #ffffff; border-top: 0; border-left: 1px solid #cccccc; border-bottom: 1px solid #cccccc; border-right: 1px solid #cccccc; border-bottom-right-radius: 5px; border-bottom-left-radius: 5px; mso-line-height-rule: exactly" height="5" bgcolor="#ffffff"> </td>
</tr>
</tbody>
</table> </td>
</tr>
<tr>
<td id="footer-pattern" style="padding: 0; border-collapse: collapse; padding: 12px 20px">
<table id="footer-pattern-container" cellspacing="0" cellpadding="0" border="0" style="border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0">
<tbody>
<tr>
<td id="footer-pattern-mobile-separated-links" class="mobile-resize-text" width="100%" colspan="2" style="padding: 0; border-collapse: collapse; color: #999999; font-size: 12px; line-height: 18px; font-family: Arial, sans-serif; mso-line-height-rule: exactly; mso-text-raise: 2px"> Get Jira notifications on your phone! Download the Jira Cloud app for <a href="https://play.google.com/store/apps/details?id=com.atlassian.android.jira.core&referrer=utm_source%3DNotificationLink%26utm_medium%3DEmail" style="color: #3b73af; text-decoration: none">Android</a> or <a href="https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailNotificationLink&mt=8" style="color: #3b73af; text-decoration: none">iOS</a>
<hr> </td>
</tr>
<tr>
<td id="footer-pattern-text" class="mobile-resize-text" width="100%" style="padding: 0; border-collapse: collapse; color: #999999; 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">(v1001.0.0-SNAPSHOT#100066-<span title="6f50351d38d27333f7766ad0f968a2a6a5f67feb" data-commit-id="6f50351d38d27333f7766ad0f968a2a6a5f67feb}">sha1:6f50351</span>)</span> </td>
<td id="footer-pattern-logo-desktop-container" valign="top" style="padding: 0; border-collapse: collapse; padding-left: 20px; vertical-align: top">
<table style="border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0">
<tbody>
<tr>
<td id="footer-pattern-logo-desktop-padding" style="padding: 0; border-collapse: collapse; padding-top: 3px; opacity: 0.150"> <img id="footer-pattern-logo-desktop" src="cid:jira-generated-image-static-footer-desktop-logo-3dcf7cf3-5de1-4691-aebb-63dc958e85f3" alt="Atlassian logo" title="Atlassian logo" width="192" height="24" class="image_fix"> </td>
</tr>
</tbody>
</table> </td>
</tr>
</tbody>
</table> </td>
</tr>
</tbody>
</table>
<img src="https://u4043402.ct.sendgrid.net/wf/open?upn=YNXLe5Uzuivj8jGpuXra04BSJ-2BS7raa1puTAtyVUP-2F0vngXMYTK-2FS3Irgj0Y-2FFD4s3ybCF0PJ-2BJBap3z14Xzbl5EU3dwei4WlWEAmvVhI2k0tpFnzWEkDO3OJd-2FDHRKQZF-2BYHVVMEpC2AwtBwvyes7JT9HO6lEV69RuUBPAM46jYl-2FSHvNcq5nHweRVs806QZTV5fitkZzt-2Bj3cmv7ysLU0ur0bhGbeXVkRI0pm77brWbpAHKVsnUOJ-2FdJdDbrdU" alt="" width="1" height="1" border="0" style="height:1px !important;width:1px !important;border-width:0 !important;margin-top:0 !important;margin-bottom:0 !important;margin-right:0 !important;margin-left:0 !important;padding-top:0 !important;padding-bottom:0 !important;padding-right:0 !important;padding-left:0 !important;"/>
</body>
</html>