QoQ HSQLDB: error executing sql statement on query

dev support
1

Hi all,

After updating to 5.4.3.2 we’ve started getting these errors

QoQ HSQLDB: error executing sql statement on query.

data exception: string data, right truncation ; size limit: 32768 table: QCATALOGUEITEMS column: TEXT1

It’s a QoQ joining two other queries.

The same thing is working fine on Lucee 5.3.12.1

I’d be grateful for any suggestions how to deal with it.

OS: Linux 6.2.0-1009-aws
Java Version: 11.0.7
Lucee Version: 5.4.3.2

2

what’s the DDL for the source table?

3

Both tables are from MS SQL Server

4

yeah, that’s not very helpful, what I’m asking is what does the column definition look like in sql server?

or share what you get when you dump a getMetaData for the source query

5

They are quite lengthy, only way I could get them is like this:

qGetStoreProfile
qGetStoreProfile1381×4280 364 KB

6

And the other one

qGetCatalogueItems
qGetCatalogueItems1381×11000 671 KB

And the QoQ that’s erroring out is:

SELECT 
	qCatalogueItems.*, qGetStoreProfile.DisplayPreviousPriceYN, qGetStoreProfile.DetailPageBuyIcon, qGetStoreProfile.DetailPageBuyLabel, 
	qGetStoreProfile.ViewLargerIcon, qGetStoreProfile.ViewLargerText, qGetStoreProfile.ListingBuyIcon, qGetStoreProfile.CatalogueItemCode, 
	qGetStoreProfile.Price, qGetStoreProfile.PreviousPrice, qGetStoreProfile.CostPrice, qGetStoreProfile.Tax, qGetStoreProfile.TaxOption, 
	qGetStoreProfile.TaxNote, qGetStoreProfile.ErrorOutOfStockYN, qGetStoreProfile.ErrorNotOnSaleYN, qGetStoreProfile.PreviousPricingLabel, 
	qGetStoreProfile.CurrentPricingLabel, qGetStoreProfile.TaxDisplayLabel, qGetStoreProfile.CatalogueAttributeProfileCode, 
	qGetStoreProfile.DynamicBasketForModifiersYN, qGetStoreProfile.StockLevel, qGetStoreProfile.ShippingStatus, qGetStoreProfile.EnableSubscriptionYN, 
	qGetStoreProfile.ShowStockLevelYN, qGetStoreProfile.CatalogueID, qGetStoreProfile.CatalogueISBN, qGetStoreProfile.CatalogueASIN, qGetStoreProfile.CatalogueASIN, 
	qGetStoreProfile.CatalogueEAN, qGetStoreProfile.CatalogueUPC, qGetStoreProfile.CatalogueGTIN, qGetStoreProfile.InventoryYN, qGetStoreProfile.StockLevelText, 
	qGetStoreProfile.PriceNote, qGetStoreProfile.eCommerceSetup, qGetStoreProfile.CurrencyCode, qGetStoreProfile.PrimaryCurrencyCode, qGetStoreProfile.SpecialOfferYN, 
	qGetStoreProfile.SpecialOfferImage, qGetStoreProfile.MaxQuantityLimit 
FROM qCatalogueItems, qGetStoreProfile
7

I appreciate you looking into it. Do you need any more information?

8

Hi all, is there any update on this one?

What’s the current thinking?

9

I would download and try the latest version and or come through the change log of the RC and beta versions if this is a concern.

10

please try 5.4.3.10-SNAPSHOT

11

We are getting the same error after updating from 5.3.x to the latest 5.4 (5.4.4.38).

Were you able to resolve this?

We’ve been putting off this update for quite a while because we kept finding problems, but thought we had them all resolved now. But after updating production today we are encountering new issues.

12

Update - we ended up rewriting the QoQ so it did not do a JOIN. Therefore it did not fall back to the HSQLDB engine, there by working around whatever issue was introduced in Lucee 5.4 related to HSQLDB.

FWIW we also had issues with CASTing in QoQ in the 5.4 update (which was revealed and fixed during our testing phase).

13

We ended up rewriting the QoQ too.

I didn’t notice any issues with CAST in QoQ - I don’t think we use it.

1 Like
14

I have been able to make it consistently happen with this code on 5.4.4.38 and on Lucee 6.

<cfquery name="test">
	select 1 as num, '3861268/1' as abc
</cfquery>

<cfset qryData = test.map(function(row) {
	return row.append({"id": 12345});
})>

<cfquery dbtype="query" name="test2">
	select num, id
	from qryData
</cfquery>

If you add the “id” column to the original query it doesn’t error. It is complex because the error has to do with the map() as well - removing that also allows it to work.

If you do getMetaData(qryData) it thinks that the abc column is a TIMESTAMP