Hi All,
I have a very strange problem, the data return by Hibernate by doing is query.list() is incorrect but when i check the query printed by hibernate in log and run it on database it returns the correct data.

Hibernate version: 3.0

Mapping documents:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

<hibernate-mapping>

<class
name="com.sravi.persistance.SearchSongAlbum"
subselect="select 'Album' as rowType,
id as album_id,
title as title1 ,
description as description,
image_file_name as image_file_name,
directory as directory,
html_file_name as html_file_name,
CASE WHEN asl.album_id is not null THEN 'Audio'
ELSE 'Video'
END AS AlbumType
from jt_albums al LEFT OUTER JOIN jt_video_song_albums avl On(al.id=avl.album_id) LEFT OUTER JOIN jt_audio_song_albums asl ON (al.id=asl.album_id)
union
select 'Song' as rowType,
CASE when jas.album_id is not null Then jas.album_id
else vs.album_id
END as album_id,
media_title as title1,
'' as description,
ja.image_file_name as image_file_name,
ja.directory as directory,
ja.html_file_name as html_file_name,
CASE WHEN jas.media_id is not null THEN 'Audio'
ELSE 'Video'
END AS AlbumType
from jt_media_infos md left outer join jt_audio_songs jas on (jas.media_id=md.id) left outer join jt_video_songs vs on
(vs.media_id=md.id),jt_albums ja where ja.id= jas.album_id or ja.id= vs.album_id
"

>
<id
name="albumId"
type="long"
column="album_id"
>
<generator class="assigned">
</generator>

</id>

<property
name="rowType"
type="java.lang.String"
column="rowType"
/>
<property
name="searchTitle"
type="java.lang.String"
column="title1"
/>
<property
name="description"
type="java.lang.String"
column="description"
/>
<property
name="imageFileName"
type="java.lang.String"
column="image_file_name"
/>
<property
name="directory"
type="java.lang.String"
column="directory"
/>
<property
name="htmlFileName"
type="java.lang.String"
column="html_file_name"
/>
<property
name="albumType"
type="java.lang.String"
column="AlbumType"
/>

</class>
</hibernate-mapping>


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

Configuration conf = new Configuration();
sessionFactory = conf.configure().buildSessionFactory();
Session session = sessionFactory.getCurrentSession();

searchText = "'%" + searchText.toUpperCase()+"%'";

String queryString = "Select SearchSongAlbum from SearchSongAlbum As SearchSongAlbum where upper(SearchSongAlbum.searchTitle) like " + searchText ;
Query query = session.createQuery(queryString);

//page Size coming as 20 and pageNum as 1
if(pageSize > 0 )
{
query.setMaxResults(pageSize);
query.setFetchSize(pageSize);
query.setFirstResult((pageNum-1)*pageSize);
}
List searchResultList = query.list();
System.out.println("Total Records are" + searchResultList.size());
Iterator itr = searchResultList.iterator();
SearchSongAlbum oneResult;
//printing the records
while(itr.hasNext())
{
oneResult = (SearchSongAlbum)itr.next();
System.out.println(oneResult.getSearchTitle() + "," + oneResult.getAlbumType()+"," + oneResult.getRowType());

}

session().close();


Full stack trace of any exception that occurs: no Exception

Name and version of the database you are using:PostegreSQL 8.3

The generated SQL (show_sql=true):
select searchsong0_.album_id as album1_19_, searchsong0_.rowType as rowType19_, searchsong0_.title1 as title3_19_, searchsong0_.description as descript4_19_, searchsong0_.image_file_name as image5_19_, searchsong0_.directory as directory19_, searchsong0_.html_file_name as html7_19_, searchsong0_.AlbumType as AlbumType19_ from ( select 'Song' as rowType, CASE when jas.album_id is not null Then jas.album_id else vs.album_id END as album_id, media_title as title1, '' as description, ja.image_file_name as image_file_name, ja.directory as directory, ja.html_file_name as html_file_name, CASE WHEN jas.media_id is not null THEN 'Audio' ELSE 'Video' END AS AlbumType from jt_media_infos md left outer join jt_audio_songs jas on (jas.media_id=md.id) left outer join jt_video_songs vs on (vs.media_id=md.id),jt_albums ja where ja.id= jas.album_id or ja.id= vs.album_id ) searchsong0_ where upper(searchsong0_.title1) like '%A%'

Debug level Hibernate log excerpt:


Now Output from program is coming as(The first 6 rows are correct and its the output from first select in union, the second part of union is selecting songs, its giving the correct number of songs but the title is coming as album title instead of song title and field rowType is also having the same problem, but when i take the generated query from log and run it, that out put is fine and you can see that out put below after wrong output of java program)

**********
Wrong output from java program
**********
album_id,searchTitle, albumType, rowType
-----------------------------------------------
2389 , Temp Album , Video , Album
2399 , Galur , Audio , Album
2400 , Bhedia_Kaun , Audio , Album
2401 , Aadhi_Botal_Pura_Nasha , Audio , Album
2402 , Billo_Pyaarii , Audio , Album
2403 , Dasgi Re Dasgi , Audio , Album
2400 , Bhedia_Kaun , Audio , Album
2400 , Bhedia_Kaun , Audio , Album
2400 , Bhedia_Kaun , Audio , Album
2400 , Bhedia_Kaun , Audio , Album
2400 , Bhedia_Kaun , Audio , Album
2400 , Bhedia_Kaun , Audio , Album
2401 , Aadhi_Botal_Pura_Nasha , Audio , Album
2401 , Aadhi_Botal_Pura_Nasha , Audio , Album
2401 , Aadhi_Botal_Pura_Nasha , Audio , Album
2401 , Aadhi_Botal_Pura_Nasha , Audio , Album
2401 , Aadhi_Botal_Pura_Nasha , Audio , Album
2401 , Aadhi_Botal_Pura_Nasha , Audio , Album
2401 , Aadhi_Botal_Pura_Nasha , Audio , Album
2401 , Aadhi_Botal_Pura_Nasha , Audio , Album

******
Correct output when run the generated sql directly
*****
album_id,searchTitle,albumType,rowType
-----------------------------------------------
2389 , Temp Album , Video , Album
2399 , Galur , Audio , Album
2400 , Bhedia_Kaun , Audio , Album
2401 , Aadhi_Botal_Pura_Nasha, Audio , Album
2402 , Billo_Pyaarii , Audio , Album
2403 , Dasgi Re Dasgi , Audio , Album
2400 , FISLA DIL MERA FISLA DIL , Audio , Song
2400 , KARDE CHAKNACHUR MUJHE, Audio , Song
2400 , O MERE MAHI , Audio , Song
2400 , SAB PYAR KI BATEIN KARTE HAIN , Audio , Song
2400 , TU MERI BAHU BAN JAYE , Audio , Song
2400 , YE ISHQ BADA BEDARDI HAI,Audio , Song
2401 , BHABHI GIRKANI , Audio , Song
2401 , Bhotal Pili Aadhi , Audio , Song
2401 , CHANDRO HUI JAWAN , Audio , Song
2401 , MERI BITI JA JAWANI , Audio , Song
2401 , PIYA JI JOTA LALE , Audio , Song
2401 , TAPTA JOBAN , Audio , Song
2401 , TOCHAN KARWALE , Audio , Song
2401 , YE CHHORI MARENGI , Audio , Song





I have no idea why its coming wrong in java program through hibernate.
Please help.

Thanks in advance,
Ravi.

Oops my mistake.....
I found the solution.
As the data was coming from SQL query so first i didn't put any PK in mapping file, then it gave me parsing error so i just blindly made the first column album_id as PK which is not unique in output of this query and that why i was getting weird result. Now i have created unique column in my query and now things are fine and correct data is coming.

Cheers.
Enjoy your day.
Ravi.

Hai ravi can you please post the change you have made i am getting the following error.Please post the code or configuration change you made.
thanks