Skip to main content

Current Version: 2024.0.3.0 (20240603 - Build 24220)
 

Since upgrading from 2023 to 2024, some weird behaviours have happened.
Some critical workflows have not been outputting the correct information on our public facing website.
In this case, I’m referring to the Building Control (BC) header on that page after you type in an address.
Instead of stating that there was a BC information against a property, it would say no information found.

Whilst investigating I found the following:
 

 


The result of SQL Query was outputting NULL values as a string value called {NULL}.

Never seen that before. Therefore, the Conditional Value was returning the wrong information. i.e. If xxx IS NULL and xyz HAS A VALUE….

Subsequently I had to add a StringReplacer to fix this anomaly for now. 

SQL Statement

WITH bc_html_agg
AS
(
SELECT blpu_uprn,  ARRAY_AGG(bc_html) AS bc_html 
FROM myproperty.buildc_html_temp 
    GROUP BY blpu_uprn ORDER BY blpu_uprn
)

Select 
    Distinct(bc_html_agg.blpu_uprn), b.address_string, bc_html_agg.bc_html, b.geom
FROM bc_html_agg 
LEFT Join myproperty.buildc_html_temp b on bc_html_agg.blpu_uprn = b.blpu_uprn 
ORDER BY bc_html_agg.blpu_uprn ASC

 

The table being queried does not contain such values of course as shown below.
 

 

This workflow is one many used to output results on that website. They have been created some time ago.

 

Do you think it’s a bug? I’m kind of puzzled here.
Or maybe it’s the CTE itself... 
No Transformers were upgraded prior by the way.

 

That would be your ARRAY_AGG(), since in Postgres, curly brackets are how arrays are represented. Your source table does not contain any arrays, but your SQL query creates some, which will always at least contain one element due to how your query is structured. I’m surprised that never came up before in your flows. Maybe FME 2023 auto-loaded those as list attributes, with the first element as a non-list attribute of the same name?

At any rate, if you want to ignore any elements besides the first, you can do ARRAY_AGG(bc_html)[1] (Postgres has no FIRST() or LAST() aggregate functions), or just have your CTE read SELECT DISTINCT ON (blpu_uprn) blpu_uprn, bc_html  FROM myproperty.buildc_html_temp (which would be equivalent to FIRST() on other databases).

If you still want to have arrays but you don’t want any of the NULL elements to be included, you can do ARRAY_AGG(bc_html) FILTER (WHERE bc_html IS NOT NULL) as bc_html_list.

@vlroyrenn Thanks for taking the time to look into it.
I’ve taken your observation on board and can confirm it’s the Aggregate function causing this.

I upgraded the below products a couple of weeks ago and I believe that’s when it happened.

FME Form / Flow 2023 to v2024 (latest)

Upgraded Postgres to v14.12.2 & Postgis to v3.4.2 a day later

It could be a combination of things however, as you suggested, the output of the NULL array should be {NULL} in the first instance as shown in PgAdmin (Curly Brackets) & Azure Data Studio [Square Brackets]. I created this workflow sometime ago.

Maybe FME 2023 and prior changed it to NULL as you suggested. Best way to find out would be for me to re-install FME 2023 but I don’t think it’s worth the exercise in any case. Let’s just say that the  SQlExecutor is now behaving normally for this case scenario.

Creating a Postgres array directly from its text representation. Note that the quotes I was using in the query are gone in the result, meaning Pg recognized the array and returned it as such, but FME doesn’t know what to do with it.
Creating an array by aggregation, here on FME 2023. I’m getting the same results as the original post.

I ran some tests on FME 2023.0 (build 23283) and no, it seems like it was the case on FME before: Postgres arrays are not recognized and left as their textual representation. I even have an older 2022.1.1 copy around, which gives me the same results. I doubt the Postgres update is what caused this either, since arrays aren’t exactly new for them.

I definitely agree that it’s not Postgres as the output is the same. But at this point, I’m not sure as to why suddenly it behaved that way. I can only go by what changes I recently made. {NULL} did not show up before however if it did, my workflow would not have worked as intended in the first place.

Community Stats

32,041
Posts
121,677
Replies
39,662
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.