Last (aggregated) field in the query defines the table aggregation for the entire query

[grantfitzsimmons]

Describe the bug
The last (aggregated) field in a query defines the table aggregation separator for all (aggregated) fields in the query.

To Reproduce
Steps to reproduce the behavior:

1. Build a query with multiple (aggregated) fields
2. See that the Table Aggregation for the last query item (last (aggregated) line in the query definition) defines which separator is used for all other aggregated records

You can use this query and change the order of the aggregated query lines for reference:
https://kufish212-v796.test.specifysystems.org/specify/query/185/

Expected behavior
The Table Aggregation should be respected.

Screenshots

When Preparations comes first in the query

When Determinations comes first in the query

Crash Report
v7.9.6.2 on kufish_2_12 on the test panel. spfishadmin is the username.
Arc, macOS

Specify 7 System Information - 2024-07-30T14_15_24.010Z.txt

Reported By
CSIRO on Asana

We just stumbled across this issue which only appears in S7; the query out put in S6 is correct.

We noticed that when there is more than one aggregated view of a table present in a query, that the separator of the last aggregator is forced onto all aggregations.
 
Here is an example. The separators for the two tables preparation and Traits (collectionobjectproperty) are different.

When the query contains the aggregators in the order Preparations > Traits, the resulting aggregation for both fields has the separator from the Traits (|)

When the query contains the aggregators in the order Traits > Preparations, the resulting aggregation for both fields has the separator from the Preparations (;)

When the same query is executed in S6, each field displays with their defined separator.

[realVinayak]

This is a duplicate of #5012 (the reason is the same). I'll close the other issue in favor of this.

[grantfitzsimmons]

From @realVinayak in #5012 (comment)

This bug happened bc the SEP variable name is not unique across the query. Looks like that has been fixed by sqlalchemy in at least version 1.3. That is, we can add a bindparam be unique. sqlalchemy/sqlalchemy#4933. Alternatively, we could do still do without it (by storing a counter for sep label.

Updating to the latest version (2.0) might just be a nice thing to do anyways. Especially since we've some form of backend tests for query builder now

Closing in favor of #5164. I'll resolve this as part of this branch since I updated SQLAlchemy to 1.three

[grantfitzsimmons]

Reported By: New Brunswick Museum

I can now see the gear and choose the table aggregation. However, the Preparations aggregation was set as a semicolon “;” and I went into the Schema config. and changed it to a verticle bar “|”, now both the collector and preparation aggregations are verticle bars “|” in the query results. But when I open the Collector 2 Table aggregation in the schema configuration it shows it is indeed a semicolon. See screen shots below.

Can I not choose different separators for different aggregations that are in different tables?