Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 1.0.2.0

Mapping documents:

Mapping entity: Card
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
<class name="Ft.PaymentService.Business.Entity.Card, Ft.PaymentService.Business" table="Card" lazy="false" where="CardExDate > getdate()">
<id name="Id" >
<generator class="native" />
</id>

<many-to-one name="User" column="UserId"/>

<property name="CardNumber" not-null="true"/>
<property name="NameOnCard" not-null="true"/>
<property name="CardExDate" not-null="true"/>
</class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

Code:

note: I have the following record in DB, today is 7 of September, 2006, so WHERE clause sholdn't allow to select this row

Id   UserId   CardNumber   NameOnCard   CardExDate
1   1   4406179990019982   test   01.09.2006

the following code works properly, result is null

String hsql = "select card " +
"from " + typeof (Card) + " as card " +
"where card.CardNumber = :cardNumber ";

IQuery query = session.CreateQuery(hsql);
query.SetString("cardNumber", "4406179990019982");

IList data = query.List();
Card card = (Card) ((data.Count > 0) ? data[0] : null);

if (card != null)
Console.WriteLine(card.CardNumber);

but this code returns this card. It shouldn't be so, cause I have the where clause specified in mapping!

Card card1 = (Card) session.Get(typeof (Card), (long)1);
if (card1 != null)
Console.WriteLine(card1.CardNumber);

And more, some of my classes have "1 to many" and "many to many" relation with Card. When I'm trying to select one of these object, unproper cards are selected, too. May be I shouldn't use this WHERE at all? Answer, please.


Name and version of the database you are using:
MS SQL Server 2000

The generated SQL (show_sql=true):

Example 1

NHibernate: select card0_.Id as Id, card0_.CardNumber as CardNumber, card0_.CardExDate as CardExDate, card0_.NameOnCard as NameOnCard, card0_.UserId as UserId from Card card0_ where card0_.CardExDate > getdate() and ((card0_.CardNumber=@p0))
@p0 = '4406179990019982'

Example 2

NHibernate: SELECT card0_.Id as Id2_, card0_.CardNumber as CardNumber2_, card0_.CardExDate as CardExDate2_, card0_.NameOnCard as NameOnCard2_, card0_.UserId asUserId2_, psuser1_.Id as Id0_, psuser1_.Deleted as Deleted0_, psuser1_.ExternalSystemUserId as External3_0_, psuser1_.ExternalSystemId as External2_0_, externalsy2_.Id as Id1_, externalsy2_.Description as Descript3_1_, externalsy2_.EventPluginFileName as EventPlu4_1_, externalsy2_.Alias as Alias1_ FROM Card card0_ left outer join [User] psuser1_ on card0_.UserId=psuser1_.Id left outer join ExternalSystem externalsy2_ on psuser1_.ExternalSystemId=externalsy2_.Id WHERE card0_.Id=@p0
@p0 = '1'
NHibernate: SELECT usersystem0_.UserId as UserId__, usersystem0_.Id as Id__, usersystem0_.Id as Id2_, usersystem0_.SystemParameterTypeId as SystemPa3_2_, usersystem0_.Value as Value2_, usersystem0_.UserId as UserId2_, systempara1_.Id as Id0_, systempara1_.Name as Name0_, systempara1_.IsRequired as IsRequired0_, systemp
ara1_.Alias as Alias0_, systempara1_.ExternalSystemId as External2_0_, externalsy2_.Id as Id1_, externalsy2_.Description as Descript3_1_, externalsy2_.EventPluginFileName as EventPlu4_1_, externalsy2_.Alias as Alias1_ FROM [UserSystemParameter] usersystem0_ left outer join SystemParameterType systempara1_ on usersystem
0_.SystemParameterTypeId=systempara1_.Id left outer join ExternalSystem externalsy2_ on systempara1_.ExternalSystemId=externalsy2_.Id WHERE usersystem0_.UserId=@p0
@p0 = '1'
4406179990019982 (WriteLine works)

Guys, it's a real bug. I have a great problems concerned with it.
Will you fix it?

This should work more or less correctly in the current SVN (and in the future 1.2.0 as well) but I'm not going to fix it in 1.0.x.