Named Query reports Invalid Column due to aggregates and the column not being in a GROUP BY clause... except I'm not trying to select the column

Ignition Early Access
1

I'm running a query to generate a histogram from some numeric columns in a table. Here's the query:

SELECT
	FLOOR((stops.stop_duration/:downtimeDivisor)/:bucketWidth)*:bucketWidth AS downtime,
	COUNT(*) AS count
FROM stop_history AS stops
WHERE 	stops.downtime_start BETWEEN :startDate AND :endDate
		AND (stops.first_fault NOT IN {hiddenFaults} OR :excludeHidden=0)
GROUP BY FLOOR((stops.stop_duration/:downtimeDivisor)/:bucketWidth)*:bucketWidth
ORDER BY FLOOR((stops.stop_duration/:downtimeDivisor)/:bucketWidth)*:bucketWidth

And the parameters are defined as such:

  • startDate = 'May 1, 2019'
  • endDate = 'June 20, 2019'
  • bucketWidth = 0.5
  • downtimeDivisor = 3600.0
  • hiddenFaults evaluates to a list of strings
  • excludeHidden = false

I've used these types of parameters many times before in the same way, so I doubt they're the source of the problem.

When I run the query, I get this error in Ignition:

com.microsoft.sqlserver.jdbc.SQLServerException: Column 'stop_history.stop_duration' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I'm not trying to do a select on that column anywhere in the query, so I'm very confused.

However, if I run the exact same query (replacing the parameters with variables and the {hiddenFaults} with a hardcoded list) in SQL Server Management Studio, it runs without any issues.

If I add stop_history.stop_duration to the GROUP BY clause, I get the error:

The multi-part identifier "stop_history.stop_duration" could not be bound.

And if I add stops.stop_duration to the GROUP BY clause, the query runs, but the data is grouped entirely wrong.

Is Ignition doing something I'm not aware of or is it somehow running the query differently than SQL Management Studio does?

2

You are selecting that column:

SELECT
	FLOOR((stops.stop_duration/:downtimeDivisor)/:bucketWidth)*:bucketWidth AS downtime

SQL will first select it, then do the calculation, but you’re grouping on a calculation so it cannot be selected.

The solution here it to make a nested query: first query the calculated values into a temporary table (named “t” in the example), then group on those values and display them.

SELECT COUNT(t.downtime), t.downtime FROM
(
    SELECT
	FLOOR((stops.stop_duration/:downtimeDivisor)/:bucketWidth)*:bucketWidth AS downtime,
    FROM stop_history AS stops
    WHERE 	stops.downtime_start BETWEEN :startDate AND :endDate
		AND (stops.first_fault NOT IN {hiddenFaults} OR :excludeHidden=0)
) t
GROUP BY t.downtime
ORDER BY t.downtime
1 Like
3

Well that’s weird.

But it worked, it returned the exact same results as the query being run in the Management Studio.

I’m still very confused as to why it works there but not in Ignition, but this solution certainly worked.

Thanks!

4

There may be a difference when you pass literal values.
Then the SQL server can check the calculations are equal, and do them in advance.
But when the values are passed as variables, the SQL server cannot assume they’re equal, and has to calculate them 3 times, possibly giving columns that don’t match grouping.

In any case, the subquery is cleaner IMO.

3 Likes