Virtual Happy Hour this month, Jun 28, we'll be getting a sneak preview at our soon to launch SaaS CMS!

Try our conversational search powered by Generative AI!

Content area with catalog content throws exception 'Subquery returned more than 1 value' in commerce 12.3.1



I wanted to have a block that would list products from preset catagories filtered on one bool property. Preset categories would be dragged and dropped into content area from catalog widget.

Content area was defined like so:

        [Display(GroupName = SystemTabNames.Content,
            Order = 20)]
        public virtual ContentArea CategoriesArea { get; set; }

Everything worked fine for one day, then I started to get exception mentioned in the title (and only while accessing getter of ContentArea.Items or ContentArea.FilteredItems). From that point on I could not edit block any more. Page editing went crazy in terms that even if block got removed from the content area I could not publish unless I navigate to some other page and then back to the page in question. 

I've tried to figure out if it had something to do with the catalog structure so for example if there was category A with its subcategories B and C it wouldn't matter if A was dragged into area or pair subcategories. When block gets created from scratch, again, it works fine for one period of time and then it gets back to the stuborn mode. 

Attached screenshots were made while I was trying various ways to get collection of content area items.

Stack trace screenshot

One thing I have noticed while debugging is that when it happens is when evaluation times out - timeout sceenshot.  I was thinking that it might do something with the Visual Studio but this happens in test and stage environments. Stage environment is hosted on DXC.

If this is not preferred way of preseting categories can anyone point me in the right direction? 

Jun 19, 2018 13:28

I think you have a problem with data. One or more of your content have duplicated ContentGuid. The ContentGuid is supposedly unique across system, but for some reason, it's not. 

You can run this query to find out the duplicated contentguid(s)

select contentguid from catalogentry 
group by contentguid 
having count(contentguid) > 1

select contentguid from catalognode
group by contentguid 
having count(contentguid) > 1

select contentguid from catalog
group by contentguid 
having count(contentguid) > 1
Jun 19, 2018 15:09

Yes, you were right.

There were two entries in CatalogNode table that had just the ContentGuid same. One of them was dirty data and is removed now and it seems to work nice.

One thing that I don't understand is how come it worked for several DB calls and then it stopped. I would anticipate that it should break on all calls not after 10ish successfull ones. Or..?

Jun 19, 2018 16:07
This topic was created over six months ago and has been resolved. If you have a similar question, please create a new topic and refer to this one.
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.