[Hibernate-JIRA] Created: (HHH-2674) Query using Escape syntax in LIKE expression with PostgresSQL 8.2 results in PSQLException: The column index is out of range
2:52 p.m.
Query using Escape syntax in LIKE expression with PostgresSQL 8.2 results in
PSQLException: The column index is out of range
-----------------------------------------------------------------------------------------------------------------------------
Key: HHH-2674
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2674
Project: Hibernate3
Issue Type: Bug
Components: core
Affects Versions: 3.2.4.sp1
Environment: JBEAP RC6 - 3.2.4.SP1_CP01
Reporter: Shelly McGowan
JPQL:
createQuery(
"select distinct Object(c) FROM Customer c, in(c.aliases) a WHERE
a.alias LIKE 'sh\\_ll' escape '\\'")
.setMaxResults(NUMOFCUSTOMERS)
.getResultList();
HIBERNATE: select distinct customer0_.ID as ID3_, customer0_.NAME as NAME3_,
customer0_.country as country3_, customer0_.code as code3_,
customer0_.FK6_FOR_CUSTOMER_TABLE as FK5_3_, customer0_.FK5_FOR_CUSTOMER_TABLE as FK6_3_
from CUSTOMER_TABLE customer0_ inner join FKS_ALIAS_CUSTOMER aliases1_ on
customer0_.ID=aliases1_.FK_FOR_CUSTOMER_TABLE inner join ALIAS_TABLE alias2_ on
aliases1_.FK_FOR_ALIAS_TABLE=alias2_.ID where alias2_.ALIAS like 'sh\_ll' escape
'\' limit ?
ERROR: javax.persistence.PersistenceException: org.hibernate.exception.DataException:
could not execute query
.
.
.
Caused by: org.postgresql.util.PSQLException: The column index is out of range: 1, number
of columns: 0.
at org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:52)
at
org.postgresql.core.v3.SimpleParameterList.setLiteralParameter(SimpleParameterList.java:113)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.bindLiteral(AbstractJdbc2Statement.java:2106)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.setInt(AbstractJdbc2Statement.java:1151)
at org.hibernate.loader.Loader.bindLimitParameters(Loader.java:1646)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1566)
at org.hibernate.loader.Loader.doQuery(Loader.java:673)
at
org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2220)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
7:55 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2674) Query using Escape syntax in LIKE expression with PostgresSQL 8.2 results in PSQLException: The column index is out of range
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2674?page=c...
]
Shelly McGowan commented on HHH-2674:
-------------------------------------
http://www.postgresql.org/docs/8.1/interactive/functions-matching.html
Section 9.7.1
Note that the backslash already has a special meaning in string literals, so to write a
pattern constant that contains a backslash you must write two backslashes in an SQL
statement. Thus, writing a pattern that actually matches a literal backslash means writing
four backslashes in the statement. You can avoid this by selecting a different escape
character with ESCAPE; then a backslash is not special to LIKE anymore. (But it is still
special to the string literal parser, so you still need two of them.)
The above JPQL query should be written:
c = getEntityManager().createQuery(
"select distinct Object(c) FROM Customer c, in(c.aliases) a WHERE
a.alias LIKE 'sh\\\\_ll' escape '\\\\'")
.setMaxResults(NUMOFCUSTOMERS)
.getResultList();
resulting in:
Hibernate: select distinct customer0_.ID as ID3_, customer0_.code as code3_,
customer0_.country as country3_, customer0_.FK6_FOR_CUSTOMER_TABLE as FK6_3_,
customer0_.NAME as NAME3_, customer0_.FK5_FOR_CUSTOMER_TABLE as FK5_3_ from CUSTOMER_TABLE
customer0_ inner join FKS_ALIAS_CUSTOMER aliases1_ on
customer0_.ID=aliases1_.FK_FOR_CUSTOMER_TABLE inner join ALIAS_TABLE alias2_ on
aliases1_.FK_FOR_ALIAS_TABLE=alias2_.ID where alias2_.ALIAS like 'sh\\_ll' escape
'\\' limit ?
Query using Escape syntax in LIKE expression with PostgresSQL 8.2
results in PSQLException: The column index is out of range
-----------------------------------------------------------------------------------------------------------------------------
Key: HHH-2674
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2674
Project: Hibernate3
Issue Type: Bug
Components: core
Affects Versions: 3.2.4.sp1
Environment: JBEAP RC6 - 3.2.4.SP1_CP01
Reporter: Shelly McGowan
JPQL:
createQuery(
"select distinct Object(c) FROM Customer c, in(c.aliases) a WHERE
a.alias LIKE 'sh\\_ll' escape '\\'")
.setMaxResults(NUMOFCUSTOMERS)
.getResultList();
HIBERNATE: select distinct customer0_.ID as ID3_, customer0_.NAME as NAME3_,
customer0_.country as country3_, customer0_.code as code3_,
customer0_.FK6_FOR_CUSTOMER_TABLE as FK5_3_, customer0_.FK5_FOR_CUSTOMER_TABLE as FK6_3_
from CUSTOMER_TABLE customer0_ inner join FKS_ALIAS_CUSTOMER aliases1_ on
customer0_.ID=aliases1_.FK_FOR_CUSTOMER_TABLE inner join ALIAS_TABLE alias2_ on
aliases1_.FK_FOR_ALIAS_TABLE=alias2_.ID where alias2_.ALIAS like 'sh\_ll' escape
'\' limit ?
ERROR: javax.persistence.PersistenceException: org.hibernate.exception.DataException:
could not execute query
.
.
.
Caused by: org.postgresql.util.PSQLException: The column index is out of range: 1, number
of columns: 0.
at org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:52)
at
org.postgresql.core.v3.SimpleParameterList.setLiteralParameter(SimpleParameterList.java:113)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.bindLiteral(AbstractJdbc2Statement.java:2106)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.setInt(AbstractJdbc2Statement.java:1151)
at org.hibernate.loader.Loader.bindLimitParameters(Loader.java:1646)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1566)
at org.hibernate.loader.Loader.doQuery(Loader.java:673)
at
org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2220)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
8:50 p.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2674) Query using Escape syntax in LIKE expression with PostgresSQL 8.2 results in PSQLException: The column index is out of range
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2674?page=c...
]
Shelly McGowan commented on HHH-2674:
-------------------------------------
This issue can be resolved with a PostgreSQL setting standard_conforming_strings to true:
standard_conforming_strings (boolean)
This controls whether ordinary string literals ('...') treat backslashes
literally, as specified in the SQL standard. The default is currently off, causing
PostgreSQL to have its historical behavior of treating backslashes as escape characters.
The default will change to on in a future release to improve compatibility with the
standard. Applications can check this parameter to determine how string literals will be
processed. The presence of this parameter can also be taken as an indication that the
escape string syntax (E'...') is supported. Escape string syntax should be used if
an application desires backslashes to be treated as escape characters.
per database parameter (http://www.postgresql.org/docs/8.2/static/sql-alterdatabase.html)
or per user (http://www.postgresql.org/docs/8.2/static/sql-alteruser.html
postgres=# ALTER USER username SET standard_conforming_strings = true;
postgres=> show standard_conforming_strings;
standard_conforming_strings
-----------------------------
on
(1 row)
Query using Escape syntax in LIKE expression with PostgresSQL 8.2
results in PSQLException: The column index is out of range
-----------------------------------------------------------------------------------------------------------------------------
Key: HHH-2674
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2674
Project: Hibernate Core
Issue Type: Bug
Components: core
Affects Versions: 3.2.4.sp1
Environment: JBEAP RC6 - 3.2.4.SP1_CP01
Reporter: Shelly McGowan
JPQL:
createQuery(
"select distinct Object(c) FROM Customer c, in(c.aliases) a WHERE
a.alias LIKE 'sh\\_ll' escape '\\'")
.setMaxResults(NUMOFCUSTOMERS)
.getResultList();
HIBERNATE: select distinct customer0_.ID as ID3_, customer0_.NAME as NAME3_,
customer0_.country as country3_, customer0_.code as code3_,
customer0_.FK6_FOR_CUSTOMER_TABLE as FK5_3_, customer0_.FK5_FOR_CUSTOMER_TABLE as FK6_3_
from CUSTOMER_TABLE customer0_ inner join FKS_ALIAS_CUSTOMER aliases1_ on
customer0_.ID=aliases1_.FK_FOR_CUSTOMER_TABLE inner join ALIAS_TABLE alias2_ on
aliases1_.FK_FOR_ALIAS_TABLE=alias2_.ID where alias2_.ALIAS like 'sh\_ll' escape
'\' limit ?
ERROR: javax.persistence.PersistenceException: org.hibernate.exception.DataException:
could not execute query
.
.
.
Caused by: org.postgresql.util.PSQLException: The column index is out of range: 1, number
of columns: 0.
at org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:52)
at
org.postgresql.core.v3.SimpleParameterList.setLiteralParameter(SimpleParameterList.java:113)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.bindLiteral(AbstractJdbc2Statement.java:2106)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.setInt(AbstractJdbc2Statement.java:1151)
at org.hibernate.loader.Loader.bindLimitParameters(Loader.java:1646)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1566)
at org.hibernate.loader.Loader.doQuery(Loader.java:673)
at
org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2220)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
5950
days inactive
6405
days old
hibernate-issues@lists.jboss.org
2 comments
1 participants
participants (1)
-
Shelly McGowan (JIRA)