Sybase ASE dialect generates invalid sub-select using QueryOver
Description
Environment
Activity
Andrew Wheeler May 12, 2013 at 11:05 PM
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.
Alex Zaytsev May 12, 2013 at 10:49 PM
Does following query work?
Session.QueryOver<Company>(() => company)
.Where(
c => c.Type == Company.GetTypeCode(type)
&& c.Status == "A"
)
.WithSubquery
.WhereExists(QueryOver.Of<BranchIpAddress>()
.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 May 12, 2013 at 9:54 PM
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 branch_ip_address this_0_
inner join branch branch1_ on this_0_.br_id=branch1_.br_id
WHERE this_0_.enabled = @p3
and branch1_.co_id = this_.co_id)
becomes:
... exists (SELECT @p2
FROM branch_ip_address this_0_
inner join branch branch1_ on this_0_.br_id=branch1_.br_id
WHERE this_0_.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 branch_ip_address this_0_
inner join branch branch1_ on this_0_.br_id=branch1_.br_id
WHERE this_0_.enabled = @p3
and branch1_.co_id = this_.co_id)
Alex Zaytsev May 9, 2013 at 2:45 PM
Can you please add desired SQL query?
Andrew Wheeler February 21, 2012 at 11:13 PM
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.
Details
Details
Assignee
Reporter
Labels
Components
Affects versions
Priority
Major
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>(()=>company)
.Where(
c => c.Type == Company.GetTypeCode(type)
&& c.Status == "A"
)
.WithSubquery
.WhereExists(QueryOver.Of<BranchIpAddress>()
.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_.co_status = @p1)
and exists (SELECT @p2 as y0_
FROM branch_ip_address this_0_
inner join branch branch1_ on this_0_.br_id=branch1_.br_id
WHERE this_0_.enabled = @p3
and branch1_.co_id = this_.co_id)
ORDER BY this_.co_name asc
Which 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.