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 - 02:36:12 EST