I would avoid using a struct and doing name lookups on each row due to the overhead in very large result sets. Arrays are smaller in memory than arrays and access faster.

Sorry Brad, I didn’t know or think of that. Your suggestion makes a lot more sense now.

Ok, I updated it based on what I meant. By building a small array of column index mappings first and then using that to loop over, we keep memory down by still only using a native array instead of a struct for each row, and we eliminate the redundant getColumnLabel() calls which will happen numRows x numCols times. And you were still looping over the number of initial columns, not the number of de-duped columns, meaning unnecessary loops when there were dupe cols.

Yes, I knew Adobe’s query object allows dupe col names and I was aware when making ours like Lucee’s that the internal design would not allow for the dupe column names, but I was ok with that. I’ve never actually seen anyone have issues with Lucee’s behavior.

As far as your fix, that’s not at all what I had suggested in Slack I would avoid using a struct and doing name lookups on each row due to the overhead in very large result sets. Arrays are smaller in memory than arrays and access faster. I’ll implement what I was thinking so you can see what I meant.

Here’s how I chose to implement this. Since the JDBC ResultSet API allows for retrieving row values by column name, it was a pretty simple fix. The test passes, though I had to tweak our query.addRow() handling as well when a null value test failed.

Given this test query:

Lucee:

• Uses the value from the first matching column name

• The resulting query column ordering matches the SQL query

• Only a single duplicate column is present in the results

Adobe:

• Uses the value from the first matching column name

• The resulting query column ordering matches the table definition.

• BOTH duplicate column names are present in the results!

• Though the data is duplicated.

I much prefer Lucee’s behavior here, FYI.

• Maintain column order as defined in the SQL

• When duplicate column names are encountered, keep the first column only and drop subsequent duplicate column names.