Description
Andrew Wheeler created an issue — 10th February 2012, 0:37:48:
Creating a query using the QueryOver API which has an exists sub-select generates invalid SQL for Sybase ASE15 dialect.
E.g.
Session.QueryOver(()=>company)
.Where(
c => c.Type == Company.GetTypeCode(type)
&& c.Status == "A"
)
.WithSubquery
.WhereExists(QueryOver.Of()
.Where(ip => ip.Enabled == 1)
.JoinQueryOver(b => b.Branch)
.Where(
b => b.Company == company
)
.Select(Projections.Constant(1))
)
.OrderBy( () => company.Name ).Asc
.List();Generates the following SQL:
SELECT this*.co_id as co1_0_0_, this_.co_name as co2_0_0_, this_.co_type as co3_0_0_, this_.co_status as co4_0_0*
FROM company this_
WHERE (this*.co_type = @p0 and this_.costatus = @p1)
and exists (SELECT @p2 as y0_
FROM branchip_address this_0*
inner join branch branch1* on this_0_.br_id=branch1_.brid
WHERE this0*.enabled = @p3
and branch1*.co_id = this_.coid)
ORDER BY this.co*name ascWhich throws the error "Sybase.Data.AseClient.AseException: Incorrect syntax near the keyword 'AS'". I have changed the projection of the sub-select to various values (e.g. Projections.Id()) but this does not change the SQL generated.
I can write this query using HQL, but I prefer the typesafe QueryOver API. This problem means I have a mixture of QueryOver and HQL queries in my code.
Andrew Wheeler added a comment — 21st February 2012, 23:13:32:
I just noticed that this is a duplicate of NH-1602. It was noted that this change would require a lot of work. However, I believe that it is a fundamental bug that should be fixed. There is absolutely no use-case for aliasing columns in a sub-select. A sub-select can only be used with "exists", "equals" and "in" statements which cannot use a column alias, nor can the alias be used inside the sub-select with regards to a join or restriction. In brief, it is a totaly pointless construct.
Alexander Zaytsev added a comment — 9th May 2013, 14:45:40:
Can you please add desired SQL query?
Andrew Wheeler added a comment — 12th May 2013, 21:54:31:
The problem is that Sybase does not support alias names in sub-selects. There is no point in aliasing a column in a sub-select as it is not visible.
So, the sub-select above which is:
... exists (SELECT @p2 as y0_
FROM branchip_address this_0
inner join branch branch1* on this_0_.br_id=branch1_.brid
WHERE this0*.enabled = @p3
and branch1*.co_id = this_.co*id)becomes:
... exists (SELECT @p2
FROM branchip_address this_0
inner join branch branch1* on this_0_.br_id=branch1_.brid
WHERE this0*.enabled = @p3
and branch1*.co_id = this_.co*id)When hand writing exists queries I typically return a constant hence the use of projection:
... exists (SELECT 1
FROM branchip_address this_0
inner join branch branch1* on this_0_.br_id=branch1_.brid
WHERE this0*.enabled = @p3
and branch1*.co_id = this_.co*id)
Alexander Zaytsev added a comment — 12th May 2013, 22:49:37:
Does following query work?
Session.QueryOver(() => company)
.Where(
c => c.Type == Company.GetTypeCode(type)
&& c.Status == "A"
)
.WithSubquery
.WhereExists(QueryOver.Of()
.Where(ip => ip.Enabled == 1)
.JoinQueryOver(b => b.Branch)
.Where(b => b.Company == company)
.Select(ip => ip.ID)
)
.OrderBy(() => company.Name).Asc
.List();
Andrew Wheeler added a comment — 12th May 2013, 23:05:41:
No, because SQL generator always tries to alias the result column using the "as" keyword. This seems to be the case whether the select is a top-level select or a sub-select. Perhaps other SQL dialects simply ignore this syntax (or it is not generated), but Sybase raises an error. Without looking at the hibernate code I would say that it may be reusing the same code for top-level select as for sub-selects. in this case it needs an additional boolean parameter of "withColumnAlias" set to false.