My bad. We should use a trimming query translator instead, like Oracle does.
Andrus
> Andrus Adamchik wrote:
>> Thanks, after upgrading the database (and most importantly the
>> driver), I can run the tests. I guess we should document the fact that
>> Cayenne only works with 7.3 and newer.
>
> Well, with the right driver it should probably also work with earlier
> versions, the adaptor doesn't do anything specific. But 7.3 really is a
> reasonable starting point.
>
>> I ran the query generated by Cayenne from psql tool and it fails too.
>> So
>
> yes, but..
>
>> this is a Postgres thing - LIKE queries do not work with exact
>> matches.
>
> ..not for this reason. artist_name is char, not varchar, and this means
> that you have to trim the query as well:
>
> cayenne=> select artist_id from artist where artist_name like 'artist1';
> artist_id
> -----------
> (0 rows)
>
> cayenne=> select artist_id from artist where trim(artist_name) like
> 'artist1';
> artist_id
> -----------
> 1
> (1 row)
>
> cayenne=>
>
> So PostgreSQL does the right thing! I just read the documentaton about
> this, and it explicitly says that LIKE queries with exact matches behave
> just like the = operator.
>
>> So I "fixed" it by appending "%" at the end of the name pattern in the
>> test case.
>
> My first idea, unfortunately not the right solution. For some reason the
> trimmed character is not put into SQL: if you look at QueryLogger output
> you'll see it still generates "WHERE artist_name LIKE 'artist11'". I
> don't know how the changes you made to CharType relate to the
> QueryTranslator (which does the correct trimming).
>
>> The test still "tests" what it was supposed to, and it works with
>> Postgres. I don't think this is cheating :-)
>
> It tests something that will always work, but not the interesting thing
> about the artist_name: the fact that it's a char type that needs
> trimming, unlike varchar. I'm not surprised anymore that this works with
> MySQL:
>
> mysql> select artist_id from ARTIST where artist_name like 'artist1';
> +-----------+
> | artist_id |
> +-----------+
> | 1 |
> +-----------+
> 1 row in set (0.00 sec)
>
> mysql> select artist_id from ARTIST where trim(artist_name) like
> 'artist1';
> +-----------+
> | artist_id |
> +-----------+
> | 1 |
> +-----------+
> 1 row in set (0.00 sec)
>
> So it treats CHAR just like VARCHAR, which is arguable (IMHO wrong).
> Either CHAR is fixed-size or not; a regular expression without special _
> or % characters should not imply them. But that's probably too difficult
> for most people.
>
> Should I add the QueryTranslator to the PostgreSQL adaptor as well? Or
> should we factor this trimming out (since CharType now knows whether it
> has to trim itselfor not) into the general SQL creation?
>
> Holger
This archive was generated by hypermail 2.0.0 : Tue Mar 04 2003 - 11:30:01 EST