I have a j2EE application with JPA, hibernate 5.2.1.Final, WildFly 10, MSSQLServer 2012

I can't select a user with this select:
SELECT u FROM UserVO u WHERE u.email = :email AND u.password = CONVERT(VARCHAR(50), HashBytes('MD5', :password), 2)

The real SQL looks right:
2017-11-13 18:17:19 INFO [stdout:71] Hibernate:
2017-11-13 18:17:19 INFO [stdout:71] /* UserVO.findByEmailAndPassword */ select
2017-11-13 18:17:19 INFO [stdout:71] uservo0_.IdUser as IdUser1_12_,
2017-11-13 18:17:19 INFO [stdout:71] uservo0_.IdCompany as IdCompan6_12_,
2017-11-13 18:17:19 INFO [stdout:71] uservo0_.Email as Email2_12_,
2017-11-13 18:17:19 INFO [stdout:71] uservo0_.FirstName as FirstNam3_12_,
2017-11-13 18:17:19 INFO [stdout:71] uservo0_.LastName as LastName4_12_,
2017-11-13 18:17:19 INFO [stdout:71] uservo0_.Password as Password5_12_,
2017-11-13 18:17:19 INFO [stdout:71] uservo0_.IdUserType as IdUserTy7_12_
2017-11-13 18:17:19 INFO [stdout:71] from
2017-11-13 18:17:19 INFO [stdout:71] Users uservo0_
2017-11-13 18:17:19 INFO [stdout:71] where
2017-11-13 18:17:19 INFO [stdout:71] uservo0_.Email=?
2017-11-13 18:17:19 INFO [stdout:71] and uservo0_.Password=CONVERT(VARCHAR(50), HashBytes('MD5', ?), 2)

I can execute on database and work, but on application I don't get any results.

When a I do the same query without the password part, I get result.
SELECT u FROM UserVO u WHERE u.email = :email AND u.password = :password

Looks like something doesn't like the CONVERT part.

Regards,
Felipe Leite

Try running the native SQL and see if it works.

If it doesn't, then you have an issue with that WHERE clause.

This doesn't work:
em.createNativeQuery("SELECT * FROM Users WHERE email = ? and Password = CONVERT(VARCHAR(50), HashBytes('MD5', ?), 2)").setParameter(1, userVO.getEmail()).setParameter(2, userVO.getPassword()).getResultList();

This work:
em.createNativeQuery("SELECT * FROM Users WHERE email = ? and Password = CONVERT(VARCHAR(50), HashBytes('MD5', '"+userVO.getPassword()+"'), 2)").setParameter(1, userVO.getEmail()).getResultList();

Looks like something at CONVERT part. I don't know, is not changing the value.

What could be?
=(

Try with named parameters.

I tried already:

<named-query name="UserVO.findByEmailAndPassword">
<query>SELECT u FROM UserVO u WHERE u.email = :email AND u.password = CONVERT(VARCHAR(50), HashBytes('MD5', :password ), 2)</query>
</named-query>

This produces the query at the first post.

What can be?

Thanks!