You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-dev@db.apache.org by "Robert S. Sfeir" <ro...@codepuccino.com> on 2004/06/20 14:17:23 UTC
Postgres escaping issues
Been digging some more this AM. I think this is an issue in Postgres, not
our tests.
The spot of failure is as you now know:
crit.addLike("firstname", "h%\\%");
Which is perfectly valid according to Postgresql documentation, and does not
return an error when used in pgsql. But... See below.
Here is why I think that it's something funky with Postgresql:
The data I added:
id | lastname | firstname
----+----------+--------------
1 | sfeir | robert
2 | sfeir | robert%dude
3 | sfeir | robert%dude
4 | sfeir | robert%\dude
5 | sfeir | robert\dude
>From pgsql, the following commands were run with results:
Replicated test should have selected records 4 & 5
mytest=# select * from person where firstname like 'r%\\%';
id | lastname | firstname
----+----------+-----------
(0 rows)
Begins with r
mytest=# select * from person where firstname like 'r%';
id | lastname | firstname
----+----------+--------------
1 | sfeir | robert
2 | sfeir | robert%dude
3 | sfeir | robert%dude
4 | sfeir | robert%\dude
5 | sfeir | robert\dude
(5 rows)
Contains r
mytest=# select * from person where firstname like '%r%';
id | lastname | firstname
----+----------+--------------
1 | sfeir | robert
2 | sfeir | robert%dude
3 | sfeir | robert%dude
4 | sfeir | robert%\dude
5 | sfeir | robert\dude
(5 rows)
Begins with r contains t
mytest=# select * from person where firstname like 'r%t%';
id | lastname | firstname
----+----------+--------------
1 | sfeir | robert
2 | sfeir | robert%dude
3 | sfeir | robert%dude
4 | sfeir | robert%\dude
5 | sfeir | robert\dude
(5 rows)
Begins with r contains \ with the escaped \
mytest=# select * from person where firstname like 'r%\\%';
id | lastname | firstname
----+----------+-----------
(0 rows)
Now the failures... No wonder test fails on Postgres.
Begins with r contains % then \
mytest=# select * from person where firstname like 'r%\%%\\%';
id | lastname | firstname
----+----------+-----------
(0 rows)
mytest=# select * from person where firstname like 'r%\%%';
id | lastname | firstname
----+----------+-----------
(0 rows)
No go on any of them. I've followed their docs, doesn't work. I can't say
for 100% certainty that this is not a psql problem, but certainly I would
say it's not a problem with our tests and there is something wrong in
Postgresql.
I'm using Postgresql 7.3.2 on OS X. When testing using the JDBC 3.0 driver.
I don't think this is reason to hold the release, we should document it
though.
R
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: Postgres escaping issues
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi,
mysql has the same problem because it also uses '\' as the default escape character
SELECT A0.LASTNAME,A0.FIRSTNAME,A0.ID FROM PERSON A0 WHERE A0.FIRSTNAME LIKE
'h%\%' escape '\'
results in
Syntax error or access violation, message from server: "You have an error in
your SQL syntax. Check the manual that corresponds to your MySQL server version
for the right syntax to use near ''\'' at line 1"
ojb also uses '\' as default. but for mysql and postgresql it should be '\\' for
sapdb and hsqldb '\' is ok . hsql also works with '\\'.
jakob
Jakob Braeuchi wrote:
> hi,
>
> i think we'd better change the escape character in the example to make
> it work with postgresql.
>
> jakob
>
> Robert S. Sfeir wrote:
>
>>
>> Tried adding the code to the test, but now the error looks like this:
>>
>> Changed like test to this:
>> '%h\\\\%'
>>
>> I think this is a bug in OJB now since the above string works in psql
>> (then
>> again psql is kinda whacked).
>>
>> org.apache.ojb.broker.PersistenceBrokerSQLException:
>> org.postgresql.util.PSQLException: ERROR: unterminated quoted string
>> at or
>> near "'\'"
>> org.apache.ojb.broker.PersistenceBrokerException:
>> org.apache.ojb.broker.PersistenceBrokerSQLException:
>> org.postgresql.util.PSQLException: ERROR: unterminated quoted string
>> at or
>> near "'\'" at
>> org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryRe
>>
>> ferenceBroker.java:240) at
>> org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryRe
>>
>> ferenceBroker.java:252) at
>> org.apache.ojb.broker.core.PersistenceBrokerImpl.getCollectionByQuery(Persis
>>
>> tenceBrokerImpl.java:1238) at
>> org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCollectionByQuery(
>>
>> DelegatingPersistenceBroker.java:332) at
>> org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCollectionByQuery(
>>
>> DelegatingPersistenceBroker.java:332) at
>> org.apache.ojb.broker.QueryTest.testLikeEscapedCriteria1(QueryTest.java:285)
>>
>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at
>> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39
>>
>> ) at
>> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl
>>
>> .java:25)Caused by: org.apache.ojb.broker.PersistenceBrokerSQLException:
>> org.postgresql.util.PSQLException: ERROR: unterminated quoted string
>> at or
>> near "'\'" at
>> org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(JdbcAccessImpl
>>
>> .java:318) at
>> org.apache.ojb.broker.accesslayer.RsQueryObject.performQuery(RsQueryObject.j
>>
>> ava:74) at
>> org.apache.ojb.broker.accesslayer.RsIterator.<init>(RsIterator.java:183)
>> at
>> org.apache.ojb.broker.core.RsIteratorFactoryImpl.createRsIterator(RsIterator
>>
>> FactoryImpl.java:58) at
>> org.apache.ojb.broker.core.PersistenceBrokerImpl.getRsIteratorFromQuery(Pers
>>
>> istenceBrokerImpl.java:1824) at
>> org.apache.ojb.broker.core.PersistenceBrokerImpl.getIteratorFromQuery(Persis
>>
>> tenceBrokerImpl.java:1354) at
>> org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryRe
>>
>> ferenceBroker.java:110) at
>> org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryRe
>>
>> ferenceBroker.java:232) ... 21 moreCaused by:
>> org.postgresql.util.PSQLException: ERROR: unterminated quoted string
>> at or
>> near "'\'" at
>> org.postgresql.util.PSQLException.parseServerError(PSQLException.java:139)
>>
>> at org.postgresql.core.QueryExecutor.executeV3(QueryExecutor.java:152) at
>> org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:100) at
>> org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:43) at
>> org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.j
>>
>> ava:517) at
>> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.j
>>
>> ava:50) at
>> org.postgresql.jdbc1.AbstractJdbc1Statement.executeQuery(AbstractJdbc1Statem
>>
>> ent.java:233) at
>> org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(JdbcAccessImpl
>>
>> .java:283) ... 28 more
>>
>>
>> On 6/20/04 8:38 AM, "Robert S. Sfeir" <ro...@codepuccino.com> wrote:
>>
>>
>>> Huh... Check this out...
>>>
>>> mytest=# select * from person where firstname LIKE 'r%\\\\%';
>>> id | lastname | firstname
>>> ----+----------+--------------
>>> 4 | sfeir | robert%\dude
>>> 5 | sfeir | robert\dude
>>> (2 rows)
>>>
>>> Seems you need to double escape???
>>>
>>> R
>>>
>>>
>>> On 6/20/04 8:32 AM, "Thomas Dudziak" <to...@first.fhg.de> wrote:
>>>
>>>
>>>> Robert, could you also run test with a different escape character ?
>>>> Also, have you checked the postgresql mailing list ? Perhaps this is a
>>>> known behavior with a workaround that we can use. I'd prefer we delay
>>>> the release for a day or two if we can fix this in an easy way (eg.
>>>> without changing OJB too much)
>>>>
>>>> Tom
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
>>>> For additional commands, e-mail: ojb-dev-help@db.apache.org
>>>
>>>
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
>>> For additional commands, e-mail: ojb-dev-help@db.apache.org
>>
>>
>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-dev-help@db.apache.org
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-dev-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: Postgres escaping issues
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi,
i think we'd better change the escape character in the example to make it work
with postgresql.
jakob
Robert S. Sfeir wrote:
>
> Tried adding the code to the test, but now the error looks like this:
>
> Changed like test to this:
> '%h\\\\%'
>
> I think this is a bug in OJB now since the above string works in psql (then
> again psql is kinda whacked).
>
> org.apache.ojb.broker.PersistenceBrokerSQLException:
> org.postgresql.util.PSQLException: ERROR: unterminated quoted string at or
> near "'\'"
>
> org.apache.ojb.broker.PersistenceBrokerException:
> org.apache.ojb.broker.PersistenceBrokerSQLException:
> org.postgresql.util.PSQLException: ERROR: unterminated quoted string at or
> near "'\'" at
> org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryRe
> ferenceBroker.java:240) at
> org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryRe
> ferenceBroker.java:252) at
> org.apache.ojb.broker.core.PersistenceBrokerImpl.getCollectionByQuery(Persis
> tenceBrokerImpl.java:1238) at
> org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCollectionByQuery(
> DelegatingPersistenceBroker.java:332) at
> org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCollectionByQuery(
> DelegatingPersistenceBroker.java:332) at
> org.apache.ojb.broker.QueryTest.testLikeEscapedCriteria1(QueryTest.java:285)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39
> ) at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl
> .java:25)Caused by: org.apache.ojb.broker.PersistenceBrokerSQLException:
> org.postgresql.util.PSQLException: ERROR: unterminated quoted string at or
> near "'\'" at
> org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(JdbcAccessImpl
> .java:318) at
> org.apache.ojb.broker.accesslayer.RsQueryObject.performQuery(RsQueryObject.j
> ava:74) at
> org.apache.ojb.broker.accesslayer.RsIterator.<init>(RsIterator.java:183) at
> org.apache.ojb.broker.core.RsIteratorFactoryImpl.createRsIterator(RsIterator
> FactoryImpl.java:58) at
> org.apache.ojb.broker.core.PersistenceBrokerImpl.getRsIteratorFromQuery(Pers
> istenceBrokerImpl.java:1824) at
> org.apache.ojb.broker.core.PersistenceBrokerImpl.getIteratorFromQuery(Persis
> tenceBrokerImpl.java:1354) at
> org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryRe
> ferenceBroker.java:110) at
> org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryRe
> ferenceBroker.java:232) ... 21 moreCaused by:
> org.postgresql.util.PSQLException: ERROR: unterminated quoted string at or
> near "'\'" at
> org.postgresql.util.PSQLException.parseServerError(PSQLException.java:139)
> at org.postgresql.core.QueryExecutor.executeV3(QueryExecutor.java:152) at
> org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:100) at
> org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:43) at
> org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.j
> ava:517) at
> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.j
> ava:50) at
> org.postgresql.jdbc1.AbstractJdbc1Statement.executeQuery(AbstractJdbc1Statem
> ent.java:233) at
> org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(JdbcAccessImpl
> .java:283) ... 28 more
>
>
> On 6/20/04 8:38 AM, "Robert S. Sfeir" <ro...@codepuccino.com> wrote:
>
>
>>Huh... Check this out...
>>
>>mytest=# select * from person where firstname LIKE 'r%\\\\%';
>> id | lastname | firstname
>>----+----------+--------------
>> 4 | sfeir | robert%\dude
>> 5 | sfeir | robert\dude
>>(2 rows)
>>
>>Seems you need to double escape???
>>
>>R
>>
>>
>>On 6/20/04 8:32 AM, "Thomas Dudziak" <to...@first.fhg.de> wrote:
>>
>>
>>>Robert, could you also run test with a different escape character ?
>>>Also, have you checked the postgresql mailing list ? Perhaps this is a
>>>known behavior with a workaround that we can use. I'd prefer we delay
>>>the release for a day or two if we can fix this in an easy way (eg.
>>>without changing OJB too much)
>>>
>>>Tom
>>>
>>>---------------------------------------------------------------------
>>>To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
>>>For additional commands, e-mail: ojb-dev-help@db.apache.org
>>
>>
>>
>>---------------------------------------------------------------------
>>To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
>>For additional commands, e-mail: ojb-dev-help@db.apache.org
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-dev-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: Postgres escaping issues
Posted by "Robert S. Sfeir" <ro...@codepuccino.com>.
Tried adding the code to the test, but now the error looks like this:
Changed like test to this:
'%h\\\\%'
I think this is a bug in OJB now since the above string works in psql (then
again psql is kinda whacked).
org.apache.ojb.broker.PersistenceBrokerSQLException:
org.postgresql.util.PSQLException: ERROR: unterminated quoted string at or
near "'\'"
org.apache.ojb.broker.PersistenceBrokerException:
org.apache.ojb.broker.PersistenceBrokerSQLException:
org.postgresql.util.PSQLException: ERROR: unterminated quoted string at or
near "'\'" at
org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryRe
ferenceBroker.java:240) at
org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryRe
ferenceBroker.java:252) at
org.apache.ojb.broker.core.PersistenceBrokerImpl.getCollectionByQuery(Persis
tenceBrokerImpl.java:1238) at
org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCollectionByQuery(
DelegatingPersistenceBroker.java:332) at
org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCollectionByQuery(
DelegatingPersistenceBroker.java:332) at
org.apache.ojb.broker.QueryTest.testLikeEscapedCriteria1(QueryTest.java:285)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39
) at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl
.java:25)Caused by: org.apache.ojb.broker.PersistenceBrokerSQLException:
org.postgresql.util.PSQLException: ERROR: unterminated quoted string at or
near "'\'" at
org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(JdbcAccessImpl
.java:318) at
org.apache.ojb.broker.accesslayer.RsQueryObject.performQuery(RsQueryObject.j
ava:74) at
org.apache.ojb.broker.accesslayer.RsIterator.<init>(RsIterator.java:183) at
org.apache.ojb.broker.core.RsIteratorFactoryImpl.createRsIterator(RsIterator
FactoryImpl.java:58) at
org.apache.ojb.broker.core.PersistenceBrokerImpl.getRsIteratorFromQuery(Pers
istenceBrokerImpl.java:1824) at
org.apache.ojb.broker.core.PersistenceBrokerImpl.getIteratorFromQuery(Persis
tenceBrokerImpl.java:1354) at
org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryRe
ferenceBroker.java:110) at
org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryRe
ferenceBroker.java:232) ... 21 moreCaused by:
org.postgresql.util.PSQLException: ERROR: unterminated quoted string at or
near "'\'" at
org.postgresql.util.PSQLException.parseServerError(PSQLException.java:139)
at org.postgresql.core.QueryExecutor.executeV3(QueryExecutor.java:152) at
org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:100) at
org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:43) at
org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.j
ava:517) at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.j
ava:50) at
org.postgresql.jdbc1.AbstractJdbc1Statement.executeQuery(AbstractJdbc1Statem
ent.java:233) at
org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(JdbcAccessImpl
.java:283) ... 28 more
On 6/20/04 8:38 AM, "Robert S. Sfeir" <ro...@codepuccino.com> wrote:
> Huh... Check this out...
>
> mytest=# select * from person where firstname LIKE 'r%\\\\%';
> id | lastname | firstname
> ----+----------+--------------
> 4 | sfeir | robert%\dude
> 5 | sfeir | robert\dude
> (2 rows)
>
> Seems you need to double escape???
>
> R
>
>
> On 6/20/04 8:32 AM, "Thomas Dudziak" <to...@first.fhg.de> wrote:
>
>> Robert, could you also run test with a different escape character ?
>> Also, have you checked the postgresql mailing list ? Perhaps this is a
>> known behavior with a workaround that we can use. I'd prefer we delay
>> the release for a day or two if we can fix this in an easy way (eg.
>> without changing OJB too much)
>>
>> Tom
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-dev-help@db.apache.org
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-dev-help@db.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: Postgres escaping issues
Posted by "Robert S. Sfeir" <ro...@codepuccino.com>.
Huh... Check this out...
mytest=# select * from person where firstname LIKE 'r%\\\\%';
id | lastname | firstname
----+----------+--------------
4 | sfeir | robert%\dude
5 | sfeir | robert\dude
(2 rows)
Seems you need to double escape???
R
On 6/20/04 8:32 AM, "Thomas Dudziak" <to...@first.fhg.de> wrote:
> Robert, could you also run test with a different escape character ?
> Also, have you checked the postgresql mailing list ? Perhaps this is a
> known behavior with a workaround that we can use. I'd prefer we delay
> the release for a day or two if we can fix this in an easy way (eg.
> without changing OJB too much)
>
> Tom
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-dev-help@db.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: Postgres escaping issues
Posted by "Robert S. Sfeir" <ro...@codepuccino.com>.
I've been searching the mailing list, and by all accounts on there this
should work, but it does, even in psql.
I am checking with different characters, the select behavior is just not
consistent. See my second post. The escaping works, it just doesn't work
consistently... Still looking. This is very bizarre.
R
On 6/20/04 8:32 AM, "Thomas Dudziak" <to...@first.fhg.de> wrote:
> Robert, could you also run test with a different escape character ?
> Also, have you checked the postgresql mailing list ? Perhaps this is a
> known behavior with a workaround that we can use. I'd prefer we delay
> the release for a day or two if we can fix this in an easy way (eg.
> without changing OJB too much)
>
> Tom
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-dev-help@db.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: Postgres escaping issues
Posted by Thomas Dudziak <to...@first.fhg.de>.
Robert, could you also run test with a different escape character ?
Also, have you checked the postgresql mailing list ? Perhaps this is a
known behavior with a workaround that we can use. I'd prefer we delay
the release for a day or two if we can fix this in an easy way (eg.
without changing OJB too much)
Tom
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: Postgres escaping issues
Posted by "Robert S. Sfeir" <ro...@codepuccino.com>.
More...
mytest=# select * from person where firstname = '\\'
mytest-# ;
id | lastname | firstname
----+----------+-----------
7 | sfeir | \
mytest=# select * from person where firstname = '\\\%';
id | lastname | firstname
----+----------+-----------
8 | sfeir | \%
(1 row)
But there is a new anomaly, looks like my original tests were defective?
mytest=# select * from person where firstname LIKE '%\\\%%';
id | lastname | firstname
----+----------+--------------
2 | sfeir | robert%dude
3 | sfeir | robert%dude
4 | sfeir | robert%\dude
8 | sfeir | \%
(4 rows)
mytest=# select * from person where firstname LIKE 'r%\\\%%';
id | lastname | firstname
----+----------+--------------
2 | sfeir | robert%dude
3 | sfeir | robert%dude
4 | sfeir | robert%\dude
(3 rows)
mytest=# select * from person where firstname LIKE 'r%\%%';
id | lastname | firstname
----+----------+------------------
1 | sfeir | robert
2 | sfeir | robert%dude
3 | sfeir | robert%dude
4 | sfeir | robert%\dude
5 | sfeir | robert\dude
6 | sfeir
| robert@sfeir.net
(6 rows)
On 6/20/04 8:17 AM, "Robert S. Sfeir" <ro...@codepuccino.com> wrote:
> Been digging some more this AM. I think this is an issue in Postgres, not
> our tests.
>
> The spot of failure is as you now know:
>
> crit.addLike("firstname", "h%\\%");
>
> Which is perfectly valid according to Postgresql documentation, and does not
> return an error when used in pgsql. But... See below.
>
> Here is why I think that it's something funky with Postgresql:
>
> The data I added:
> id | lastname | firstname
> ----+----------+--------------
> 1 | sfeir | robert
> 2 | sfeir | robert%dude
> 3 | sfeir | robert%dude
> 4 | sfeir | robert%\dude
> 5 | sfeir | robert\dude
>
>> From pgsql, the following commands were run with results:
>
> Replicated test should have selected records 4 & 5
> mytest=# select * from person where firstname like 'r%\\%';
> id | lastname | firstname
> ----+----------+-----------
> (0 rows)
>
> Begins with r
> mytest=# select * from person where firstname like 'r%';
> id | lastname | firstname
> ----+----------+--------------
> 1 | sfeir | robert
> 2 | sfeir | robert%dude
> 3 | sfeir | robert%dude
> 4 | sfeir | robert%\dude
> 5 | sfeir | robert\dude
> (5 rows)
>
> Contains r
> mytest=# select * from person where firstname like '%r%';
> id | lastname | firstname
> ----+----------+--------------
> 1 | sfeir | robert
> 2 | sfeir | robert%dude
> 3 | sfeir | robert%dude
> 4 | sfeir | robert%\dude
> 5 | sfeir | robert\dude
> (5 rows)
>
> Begins with r contains t
> mytest=# select * from person where firstname like 'r%t%';
> id | lastname | firstname
> ----+----------+--------------
> 1 | sfeir | robert
> 2 | sfeir | robert%dude
> 3 | sfeir | robert%dude
> 4 | sfeir | robert%\dude
> 5 | sfeir | robert\dude
> (5 rows)
>
> Begins with r contains \ with the escaped \
> mytest=# select * from person where firstname like 'r%\\%';
> id | lastname | firstname
> ----+----------+-----------
> (0 rows)
>
> Now the failures... No wonder test fails on Postgres.
>
> Begins with r contains % then \
> mytest=# select * from person where firstname like 'r%\%%\\%';
> id | lastname | firstname
> ----+----------+-----------
> (0 rows)
>
>
> mytest=# select * from person where firstname like 'r%\%%';
> id | lastname | firstname
> ----+----------+-----------
> (0 rows)
>
> No go on any of them. I've followed their docs, doesn't work. I can't say
> for 100% certainty that this is not a psql problem, but certainly I would
> say it's not a problem with our tests and there is something wrong in
> Postgresql.
>
> I'm using Postgresql 7.3.2 on OS X. When testing using the JDBC 3.0 driver.
>
> I don't think this is reason to hold the release, we should document it
> though.
>
> R
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-dev-help@db.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org