Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Issues

Select view

Select search mode

 
43 of 43

CTE query cycle attribute still evaluated incorrectly on MSSQL using collation "Latin1_General_CI_AS"

Fixed

Description

I’m sorry for warming this up again, but the fix in https://hibernate.atlassian.net/browse/HHH-16945 seems to be insufficient for the problematic use case.

When using the test case we provided in https://hibernate.atlassian.net/browse/HHH-16465 , the following SQL is generated:

with AllParents (child_id, parent_id, groupTreeId, cycleMark, path) as (select gt1_0.CHILD_ID, gt1_0.PARENT_ID, gt1_0.id, 0, (char(0)+coalesce(cast(gt1_0.id as varchar(max)), char(0))+char(0)+char(0)) from GROUP_TREE_TABLE gt1_0 join GROUP_TABLE c1_0 on c1_0.id=gt1_0.CHILD_ID where c1_0.id=? union all select gt2_0.CHILD_ID, gt2_0.PARENT_ID, gt2_0.id, case when cte1_0.path like ('%'+char(0)+coalesce(cast(gt2_0.id as varchar(max)), char(0))+char(0)+char(0)+'%') then 1 else 0 end, (cte1_0.path+char(0)+coalesce(cast(gt2_0.id as varchar(max)), char(0))+char(0)+char(0)) from AllParents cte1_0 join GROUP_TREE_TABLE gt2_0 on cte1_0.parent_id=gt2_0.CHILD_ID where cte1_0.cycleMark=0) select child1_0.id, child1_0.NAME, parent2_0.id, parent2_0.NAME, ap1_0.groupTreeId, ap1_0.cycleMark from AllParents ap1_0 join GROUP_TABLE child1_0 on ap1_0.child_id=child1_0.id join GROUP_TABLE parent2_0 on ap1_0.parent_id=parent2_0.id

As you can see, the char(0) is casted to varchar(max) and thus the problem still occurs. If we run the query manually and switch it to varbinary(max) it works correctly.

We also added breakpoints in the CountFunction class you changed in the commit https://github.com/hibernate/hibernate-orm/commit/e73a1cdccb9de397a6b377f26e8b373883821580, but the CountFunction is never rendered for our query. We run the following query:

WITH AllParents AS( SELECT c child, p parent, gt.id groupTreeId FROM GroupTree gt INNER JOIN gt.parent p INNER JOIN gt.child c WHERE c = :start UNION ALL SELECT c2 child, p2 parent, gt2.id groupTreeId FROM AllParents cte JOIN GroupTree gt2 ON cte.parent = gt2.child INNER JOIN gt2.parent p2 INNER JOIN gt2.child c2 ) cycle groupTreeId set cycleMark SELECT ap.child, ap.parent, ap.groupTreeId, ap.cycleMark FROM AllParents ap

Can you please take another look? Thank you very much in advance and we would of course appreciate a higher priority since we receive incomplete data with it…

Details

Assignee

Reporter

Components

Sprint

Fix versions

Affects versions

Priority

Created January 11, 2024 at 2:56 PM
Updated January 18, 2024 at 1:38 PM
Resolved January 15, 2024 at 5:38 AM

Activity

Show:

MKJanuary 15, 2024 at 11:33 AM

I have tested our use case with a snapshot from today and it finally worked, thank you very much!

MKJanuary 12, 2024 at 9:23 AM

Wow, thank you for taking another look so quickly! In our tests we noticed that it works to either cast the char(0) to varbinary instead of varchar or to collate the char(0) to a collation that is known to work.

Christian BeikovJanuary 12, 2024 at 9:11 AM

I was able to boil this behavior down to the following simple test:

select IIF(t.x collate Latin1_General_CI_AS like ('%11%'), 1, 0), IIF(t.x collate SQL_Latin1_General_CP1_CS_AS like ('%11%'), 1, 0) from (values ('1'+char(0)+'1')) t(x)

Essentially, it seems that SQL Server with Latin1_General_CI_AS simply ignores the NUL character i.e. the first statement returns 1 and the second 0.

I’ll dig a bit deeper to understand what is going on and how I can fix that, but I think it’s fine to change the collation for cycle detection.

Flag notifications