U4-1290 - Document.PathPublished fails on Sql Server 2008 when custom datatypes are used (4.11)

Created by Michael Wulff Nielsen 05 Dec 2012, 11:05:17 Updated by Sebastiaan Janssen 11 Jan 2013, 09:15:02

The new PathPublished property fails on Sql Server 2008 due to the way left joins work.

The problem is that sql server will select custom datatypes in the result because the left join allows null results. The problem does not occur under SqlCe. I have not tested it against mysql.

To trigger this problem the following conditions must be met:

  1. Install umbraco on sql server 2008
  2. Define at least 2 custom datatypes.
  3. Try to publish a page at least 2 levels deep.

There are two solutions to this problem. The first one is a code only solution that is simple to understand but does not perform optimally. The other solution is a more convoluted sql statement that correctly checks the parent tree for published items.

Solution 1 - c# code:

public bool PathPublished { get { int parentid=this.ParentId; while (parentid != -1) { Document d = new Document(parentid);

                if (!d.Published)
                    return false;

                parentid = d.ParentId;

            return true;

Solution 2:

public bool PathPublished { get { int x = SqlHelper.ExecuteScalar(@"select COUNT(node.id) - COUNT(doc.published) from umbracoNode as node left join cmsDocument as doc on (node.id=doc.nodeId and doc.published=1) where nodeObjectType='" + Document._objectType + " and node.id!=" + this.Id + " ' and '" + Path + ",' like " + SqlHelper.Concat("node.path", "'%'")); return (x == 0); // 0 means that all the parents were published, any value > 0 means that value parents are unpublished } }

Obviously solution 2 is the fastest, but it is also the hardest to understand. I changed the sql to only select documents from umbracoNode and also excluded the node being published as it is irrelevant to whether or not it can be published.

This solved the Sql Server left join issues since only documents can be selected. I also see no reason why this would not work on mysql.

This bug has grave consequences as the save and publish functionality actually breaks because of it.


Michael Wulff Nielsen 10 Jan 2013, 12:05:45

This bug can be closed, it was reported by others and fixed in 4.11.2

Sebastiaan Janssen 10 Jan 2013, 12:41:17

Thanks Michael!

Priority: Normal

Type: Bug

State: Fixed


Difficulty: Normal


Backwards Compatible: True

Fix Submitted:

Affected versions:

Due in version: 4.11.2


Story Points: