The SQL is not the problem. It's generated by Hibernate (www.hibernate.org) and I've checked the generated SQL on both machines... It's the results that worry me..
Your other point about the actual data is a good one but also covered. The data is the same for the query set that I request. Maybe this is clearer.... select * from claims_payments where (status>='en' ) order by status For the 7.4.1 psql DB produces results that include status' of 'ENTERED'. For the 7.4.6 psql DB produces no results as the query seems to handle the request as case-sensitive for this DB but not the other one. select * from claims_payments where (status>='EN' ) order by status produces the same data on from both DBs. I can do the same thing on all VARCHAR fields in the DB. On the 7.4.1 DB, I get case insensitive results, on 7.4.6 DB I get case-sensitive results. I'm thinking it's not version related, its the Mandrake-build vs the Debian Build or some other parameter in Postgres itself??? Thanks for your suggestion Stuart >> Here is the SQL: > >> select claimspaym0_.id as id, claimspaym0_.member_id >> as member_id, >> claimspaym0_.policy_id as policy_id, >> claimspaym0_.entry_date as >> entry_date, claimspaym0_.family_name as family_n5_, >> claimspaym0_.status as >> status, claimspaym0_.amount as amount, >> claimspaym0_.coverage as coverage >> from claims_payments claimspaym0_ where >> (claimspaym0_.status>='en' ) order >> by claimspaym0_.status limit ? > > 1) "claimspaym0_" is not a very nice name for a > database table in my opinion, it was better as > claims_payments before you aliased it. dare i suggest > something like > > select id, member_id, policy_id, entry_date, > family_name, status, amount, coverage > from claims_payments > where (status >= 'en') > order by status > limit 10; > > since you're only selecting against the one table, > the aliasing is unneeded and confusing I suspect. > > 2) run > select count(*) from claims_payments; > > on both the debian system and the mandrake system > and check that the numbers are the same. If they're > not, your clients are pointing at different databases > and you're getting results on different data. > > perhaps one of your systems is pointing at > a development database and the other at a production > database, or the databases are out of sync, > or somehow databases which you think are identical > aren't. > >> Is it something I said or is there a CREATEDB switch >> or something? Anyone know? > > It won't have anything to do with CREATEDB. > > Good luck, > Stuart. > > Find local movie times and trailers on Yahoo! Movies. > http://au.movies.yahoo.com > > -- SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/ Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html
