Max row length is exceeded when trying to store a string of 197 chars into a temp col

Virtuoso Universal Server
,
1

I am getting a new error with a derived table that never occurred before.

When left outer joining Table A (page information) with Stored Procedure View B (link information from on demand analysis of an XML file), I get this error:

Max row length is exceeded when trying to store a string of 197 chars into a temp col

I isolated the problematic column ‘keywords’.

I’ve been using this query for over a decade, this never happened before.

Both sides of the query execute successfully, and removing the keywords column runs perfectly. It’s just that keywords column!

No amount of casting, or migrating the field into a nested select in the outermost query seems to solve this. Changing the output field from Stored Procedure Table B to a varchar(500) does not help.

The only way to avoid the error is to eliminate outputting the keywords entirely, or trim it to 100 characters: left(keywords, 100).

Neither will work for us. Is there anyway to influence the temp col in the derived table? Or does my problem stem from the Procedure View itself?

Here is the entire query:

select
	P.seoRound as "SEO Round",
	P.url as "URL",
	P.pageName as "PageID",
	P.pageTargetKeyphrase as "Target Keyphrase",
	P.pageShortTitle as  "Recommended H1/Inbound Link Text",
	P.pageHeading2 as  "Recommended H2",
	P.pageBreadcrumb as  "Recommended Breadcrumb Text",
	(case when P.pagetitle<>'' then P.pagetitle else '' end) as "Recommended Title",
	(case when P.pagedescription<>'' then P.pagedescription else '' end) as "Recommended Description",
	(case when P.pagekeywords<>'' then P.pagekeywords else '' end) as "Recommended Keywords",
	L.h1 as  "Current H1",
	L.h2 as  "Current H2",
	(case when L.title<>'' then L.title else '' end) as "Current Title",
	(case when L.description<>'' then L.description else '' end) as "Current Description",
	L.keywords as "Current Keywords"
from
	( select
	EP.projectshortname,
	KP.seoRound,
	KP.pageName,
	KP.pageShortTitle,
	KP.pageHeading2,
	KP.pagetitle,
	KP.pagedescription,
	KP.pagekeywords,
	KP.pageBreadCrumb,
	KP.pageTargetKeyphrase,
	lower(KP.pageURL) as url
from
	extranet..projects EP,
	keyphrases..pages KP
where
	EP.projectshortname='acme' and
	KP.projectid=EP.projectID and
	KP.pageinclude=1 and
	KP.pageiscode=0 and
	KP.seoRound>0 ) P

left outer join (

select lower(url) as url,
	title,
	description,
	keywords,
	h1,
	h2
from
	LINKANALYSIS..LINK_ANALYSIS_INDEPTH
where
	extranetname='acme' and
	siteName='acme' ) L

on L.url=P.url
order by
	1,
	2

… and here is the definition of Stored Procedure View B:

CREATE PROCEDURE LINKANALYSIS.EXTRANET.PARSE_LINK_ANALYSIS_INDEPTH (
	in extranetName varchar,
	in siteName varchar:=null,
	in reportDate varchar:=null,
	in querymode varchar:='sql',
	in urlFilter varchar:=null
	) {

	declare _keywords varchar(500);

<snip>

		result(
			_url,
			_sampled,

			_layer,
			_status,

			_title,
			_description,
			_keywords,
			_h1,
			_h2,

			_linksOutTotal,
			_linksOutLocal,
			_linksOutOffsite,

			_pagesIn,
			_linksIn,
			_linkStrength,

			_targetURL,
			_bodyText
			);
		};

	return _resultCount;
	};
grant execute on LINKANALYSIS.EXTRANET.PARSE_LINK_ANALYSIS_INDEPTH to EXTRANET;

create procedure view LINKANALYSIS.EXTRANET.LINK_ANALYSIS_INDEPTH as
	LINKANALYSIS.EXTRANET.PARSE_LINK_ANALYSIS_INDEPTH
		(
		extranetName,
		siteName,
		reportDate,
		querymode,
		urlFilter
		)

		(
		url varchar,
		sampledate varchar,

		layer varchar,
		status varchar,

		title varchar,
		description varchar,
		keywords varchar (500),
		h1 varchar,
		h2 varchar,

		linksOutTotal varchar,
		linksOutLocal varchar,
		linksOutOffsite varchar,

		pagesIn varchar,
		linksIn varchar,
		linkStrength varchar,

		targetURL varchar,
		bodyText long varchar

		);
2

The way to address this issue is by creating one or more procedure view for the keywords and then sort in a subquery and join with the keywords procedure view ie take out the keywords from the sorting hash.

3

Interesting. I did try moving the keywords into the outermost select list of fields, but that didn’t help.

In the end, I managed to find a workaround that gave me my results, and I think it led me to the solution.

My initial right-hand procedure View was based on a set of records that didn’t match the left-hand table 1-to-1. I suspect that my initial attempts did not return a column on the right-hand side, i.e. was NULL. My theory is that, without an initial value, the column was cast as varchar(100).

Recreating the XML file in the right-hand procedure view to match the left-hand table 1-to-1 ensured a value in the first row, and let me use any size varchar.

Am I getting warm here?