Set Function on non-numeric data is not allowed

dev support
1

I am executing the following query, the source query has two columns with the correct names and all of the data in those columns are numeric, mostly zeros in both columns but a few actual numbers.

Any idea what is going on here?

What is being set the qryGetIdLengthSum variable?

SELECT SUM(item1_length) AS total_length, SUM(item2_end) AS total_end FROM retCustomer_st.qryGetCustomers
2

is that a sql server error message? (I assume you’re using sql server from the naming conventions)

3

Actually mysql.
Sorry I missed some of the code due to the html codes.

cfquery name=“qryGetTaxIdLengthSum” dbtype=“query”
SELECT SUM(item1_length) AS total_length, SUM(item2_end) AS total_end FROM retCustomer_st.qryGetCustomers
cfquery

4

try <cfdump var=#qryGetTaxIdLengthSum#>

it will be a query object with two columns, total_length and total_end

5

The query is where I get the error.
Nothing is being set since the query never completes.
If I cfabort before the query closing tag then I get the query info which looks good bu if I try to execute the query I get the error above.

Here is the actual query now that I learned how to escape the code.

The dump before looks good, the dump after never executes if I remove the cfabort I get the error.

<cfdump var="#retCustomer_st.qryGetCustomers#">
	<cfquery name="qryGetTaxIdLengthSum" dbtype="query">
		SELECT SUM(tax_id_length) AS total_tax_length, SUM(use_period_end) AS total_period_end
		FROM retCustomer_st.qryGetCustomers		
		<cfabort>
	</cfquery>
	<cfdump var="#qryGetTaxIdLengthSum#" abort="true">
6

Here is a snap shot of the source query.

	address	address_2	business_name	city	customer_id	customer_status	customer_tax_id	tax_id_length	drivers_license	first_name	last_name	period_end_date	state	thumbnail	use_period_end	zip	customer_type_name
1	10101 South Main			Winsor	290086	1		0		Jonny	Blue	{ts '2013-04-25 00:00:00'}	CO	default_th	0	99909	Normal
2	6266 Skinny Back Lane			Broke Back Mountain	112205	1		0		Bob	Bobby	{ts '2011-07-01 00:00:00'}	MT	default_th	0	61154	Normal

It is a little hard to read but tax_id_length is 0,0 and use_period_end is also 0,0.
All of the values in both columns are either 0 or an integer value.

7

By the way, this code works perfectly in coldfusion server vs lucee.

8

interesting, can you post the stack trace and also mention the versions of lucee and mysql you are using?

9

I do not get a stack trace from lucee, I see the previous query in the debug screen and the one afterward that logs the error to the database.

Lucee (Velvet) Os 5.2.9.31 (CFML Version 2016,0,03,300357)
My MYSQL version is 5.7.25.

It is pretty clear as I play around with it that the first query is adding a column like this:

LENGTH(tax_id) as tax_id_length

I believe it is storing meta data for that column as text, then when it tries to sum it is recognizing that it cannot sum text even though it is a text representation of a number.

Is there a way to examine the metadata associated with the source query?

10

I just pulled the meta data for the source query.

The tax_id_length is a BIGINT but the use_period_end is a BIT.

Struct
isCaseSensitive	
boolean	false
name	
string	use_period_end
typeName	
string	BIT

Struct
isCaseSensitive	
boolean	false
name	
string	tax_id_length
typeName	
string	BIGINT
11 
`this.datasources["datasrc"] = {
	  class: 'com.mysql.jdbc.Driver'
	, bundleName: 'com.mysql.jdbc'
	, bundleVersion: '5.1.40'
	, connectionString: 'jdbc:mysql://db:3306/adilasmini?useUnicode=true&characterEncoding=UTF-8&useLegacyDatetimeCode=true'
		
	// optional settings
	, blob:true // default: false
	, clob:true // default: false
	, connectionLimit:100 // default:-1
	, connectionTimeout: // default: 1; unit: seconds
};`
12

Can you try updating the MySQL extension to the latest version, which is 8.0.15

13

Error is the same with 8.0.15.

I have revised my code so that I can move on with development, but I have saved the bad code for testing.
It would be nice to understand what is going on here.

The query works in coldfusion 10,11, and 2018.
I also pulled the queries them selves and executed them in MySQL Workbench as a subquery of a query and they execute fine there.

14

Can you file a bug in jira with a CFM example, plus create tables and insert SQL statements?

15

Sure, it might take a couple days but I will get it done.
Thank you for the help.

1 Like
16

I have the same problem. is it fixed yet ? if it’s not, could you please give me some advice to solve the problem ? thanks

17

how can a problem be fixed when nobody has provided a working test case?

if you are using dbtype=“query” i.e query of queries, do you have any empty (null) values in the source query?

18

Thanks for the reply @Zackster
here’s my dump https://ibb.co/ZhmGcdm
and here’s my code https://ibb.co/8KqxW71

any help would be appreciate

19

Use getMetaData(qryMTH2) and see what data types are getting returned.

20

which database are you using for the source query?

if you convert the src query to json, are the tax column values quoted strings or numbers?

PS: you can just paste images directly into posts here, no need to use external links