[Cubicweb] SQL generation problem with MSSQL backend

[Alexandre Fayolle]

On Friday 26 November 2010 08:52:15 Alexandre Fayolle wrote:
> Here's the relevant output (reformatted for view using a fixed-width font).
> Any hint at what could cause MSSQL to choke on this is very welcome.

I'm attaching the output provided by SET SHOWPLAN_TEXT ON on the two queries, 
in case someone sees a pattern. 



-- 
Alexandre Fayolle                              LOGILAB, Paris (France)
Formations Python, CubicWeb, Debian :  http://www.logilab.fr/formations
Développement logiciel sur mesure :      http://www.logilab.fr/services
Informatique scientifique:               http://www.logilab.fr/science
-------------- next part --------------
  |--Sort(ORDER BY:([_X].[cw_data_type] ASC, [_AE].[cw_modification_date] DESC))
       |--Nested Loops(Left Outer Join, OUTER REFERENCES:([_X].[cw_rcase_of]))
            |--Nested Loops(Left Semi Join, OUTER REFERENCES:([_X].[cw_eid]))
            |    |--Nested Loops(Inner Join, OUTER REFERENCES:([rel_ts_variant0].[eid_to]))
            |    |    |--Nested Loops(Inner Join)
            |    |    |    |--Clustered Index Seek(OBJECT:([pegase_alf].[dbo].[cw_CWUser].[PK__cw_CWUser__6A669BCA] AS [_B]), SEEK:([_B].[cw_eid]=(2673)) ORDERED FORWARD)
            |    |    |    |--Index Seek(OBJECT:([pegase_alf].[dbo].[ts_variant_relation].[ts_variant_relation_from_idx] AS [rel_ts_variant0]), SEEK:([rel_ts_variant0].[eid_from]=(3227)) ORDERED FORWARD)
            |    |    |--Clustered Index Seek(OBJECT:([pegase_alf].[dbo].[cw_TimeSeries].[PK__cw_TimeSeries__5DCBBABB] AS [_X]), SEEK:([_X].[cw_eid]=[pegase_alf].[dbo].[ts_variant_relation].[eid_to] as [rel_ts_variant0].[eid_to]) ORDERED FORWARD)
            |    |--Nested Loops(Inner Join, OUTER REFERENCES:([rel_has_group_permission2].[eid_to]))
            |         |--Nested Loops(Inner Join, OUTER REFERENCES:([rel_has_group_permission2].[eid_to]))
            |         |    |--Index Seek(OBJECT:([pegase_alf].[dbo].[has_group_permission_relation].[has_group_permission_relation_from_idx] AS [rel_has_group_permission2]), SEEK:([rel_has_group_permission2].[eid_from]=(2673)) ORDERED FORWARD)
            |         |    |--Index Seek(OBJECT:([pegase_alf].[dbo].[cw_CWPermission].[cw_cwpermission_cw_name_idx] AS [_A]), SEEK:([_A].[cw_name]=N'modeller' AND [_A].[cw_eid]=[pegase_alf].[dbo].[has_group_permission_relation].[eid_to] as [rel_has_group_permission2].[eid_to] OR [_A].[cw_name]=N'operator' AND [_A].[cw_eid]=[pegase_alf].[dbo].[has_group_permission_relation].[eid_to] as [rel_has_group_permission2].[eid_to] OR [_A].[cw_name]=N'pfolio_manager' AND [_A].[cw_eid]=[pegase_alf].[dbo].[has_group_permission_relation].[eid_to] as [rel_has_group_permission2].[eid_to]) ORDERED FORWARD)
            |         |--Index Seek(OBJECT:([pegase_alf].[dbo].[require_permission_relation].[require_permission_relation_from_idx] AS [rel_require_permission1]), SEEK:([rel_require_permission1].[eid_from]=[pegase_alf].[dbo].[cw_TimeSeries].[cw_eid] as [_X].[cw_eid] AND [rel_require_permission1].[eid_to]=[pegase_alf].[dbo].[has_group_permission_relation].[eid_to] as [rel_has_group_permission2].[eid_to]) ORDERED FORWARD)
            |--Nested Loops(Left Semi Join, OUTER REFERENCES:([_AE].[cw_eid]))
                 |--Clustered Index Seek(OBJECT:([pegase_alf].[dbo].[cw_Case].[PK__cw_Case__6F2B50E7] AS [_AE]), SEEK:([_AE].[cw_eid]=[pegase_alf].[dbo].[cw_TimeSeries].[cw_rcase_of] as [_X].[cw_rcase_of]) ORDERED FORWARD)
                 |--Nested Loops(Inner Join, OUTER REFERENCES:([rel_has_group_permission1].[eid_to]))
                      |--Nested Loops(Inner Join, OUTER REFERENCES:([rel_has_group_permission1].[eid_to]))
                      |    |--Index Seek(OBJECT:([pegase_alf].[dbo].[has_group_permission_relation].[has_group_permission_relation_from_idx] AS [rel_has_group_permission1]), SEEK:([rel_has_group_permission1].[eid_from]=(2673)) ORDERED FORWARD)
                      |    |--Index Seek(OBJECT:([pegase_alf].[dbo].[cw_CWPermission].[cw_cwpermission_cw_name_idx] AS [_D]), SEEK:([_D].[cw_name]=N'modeller' AND [_D].[cw_eid]=[pegase_alf].[dbo].[has_group_permission_relation].[eid_to] as [rel_has_group_permission1].[eid_to] OR [_D].[cw_name]=N'operator' AND [_D].[cw_eid]=[pegase_alf].[dbo].[has_group_permission_relation].[eid_to] as [rel_has_group_permission1].[eid_to] OR [_D].[cw_name]=N'pfolio_manager' AND [_D].[cw_eid]=[pegase_alf].[dbo].[has_group_permission_relation].[eid_to] as [rel_has_group_permission1].[eid_to]) ORDERED FORWARD)
                      |--Index Seek(OBJECT:([pegase_alf].[dbo].[require_permission_relation].[require_permission_relation_from_idx] AS [rel_require_permission0]), SEEK:([rel_require_permission0].[eid_from]=[pegase_alf].[dbo].[cw_Case].[cw_eid] as [_AE].[cw_eid] AND [rel_require_permission0].[eid_to]=[pegase_alf].[dbo].[has_group_permission_relation].[eid_to] as [rel_has_group_permission1].[eid_to]) ORDERED FORWARD)
-------------- next part --------------
  |--Sort(ORDER BY:([_X].[cw_data_type] ASC))
       |--Nested Loops(Left Semi Join, OUTER REFERENCES:([_X].[cw_eid]))
            |--Nested Loops(Inner Join, OUTER REFERENCES:([rel_ts_variant0].[eid_to]))
            |    |--Index Seek(OBJECT:([pegase_alf].[dbo].[ts_variant_relation].[ts_variant_relation_from_idx] AS [rel_ts_variant0]), SEEK:([rel_ts_variant0].[eid_from]=(3227)) ORDERED FORWARD)
            |    |--Clustered Index Seek(OBJECT:([pegase_alf].[dbo].[cw_TimeSeries].[PK__cw_TimeSeries__5DCBBABB] AS [_X]), SEEK:([_X].[cw_eid]=[pegase_alf].[dbo].[ts_variant_relation].[eid_to] as [rel_ts_variant0].[eid_to]) ORDERED FORWARD)
            |--Nested Loops(Inner Join, OUTER REFERENCES:([rel_has_group_permission2].[eid_to]))
                 |--Nested Loops(Inner Join, OUTER REFERENCES:([rel_has_group_permission2].[eid_to]))
                 |    |--Index Seek(OBJECT:([pegase_alf].[dbo].[has_group_permission_relation].[has_group_permission_relation_from_idx] AS [rel_has_group_permission2]), SEEK:([rel_has_group_permission2].[eid_from]=(2673)) ORDERED FORWARD)
                 |    |--Index Seek(OBJECT:([pegase_alf].[dbo].[cw_CWPermission].[cw_cwpermission_cw_name_idx] AS [_A]), SEEK:([_A].[cw_name]=N'modeller' AND [_A].[cw_eid]=[pegase_alf].[dbo].[has_group_permission_relation].[eid_to] as [rel_has_group_permission2].[eid_to] OR [_A].[cw_name]=N'operator' AND [_A].[cw_eid]=[pegase_alf].[dbo].[has_group_permission_relation].[eid_to] as [rel_has_group_permission2].[eid_to] OR [_A].[cw_name]=N'pfolio_manager' AND [_A].[cw_eid]=[pegase_alf].[dbo].[has_group_permission_relation].[eid_to] as [rel_has_group_permission2].[eid_to]) ORDERED FORWARD)
                 |--Index Seek(OBJECT:([pegase_alf].[dbo].[require_permission_relation].[require_permission_relation_from_idx] AS [rel_require_permission1]), SEEK:([rel_require_permission1].[eid_from]=[pegase_alf].[dbo].[cw_TimeSeries].[cw_eid] as [_X].[cw_eid] AND [rel_require_permission1].[eid_to]=[pegase_alf].[dbo].[has_group_permission_relation].[eid_to] as [rel_has_group_permission2].[eid_to]) ORDERED FORWARD)