Query results do not return columns when they share a name (even though they are in different tables)

[jajwilson]

I have a db with 3 tables: Authors, Works, and Publishers. Each of these tables contains a field called 'name'. If, using the graphical query designer, I select one name field in one of the tables, and other fields (not called 'name') from the other tables, then everything works. If I select two or more fields called 'name', then none of the 'name' fields appears in the query results - I can only see results from those fields that were not called 'name'.

Given that it's quite common to have a fields called 'name' in multiple tables, I'm rating this as Critical priority.

[scottbw]

OK, I've replicated this as follows.

1. Using Mondial, open VQB
2. Select the {table}name and population fields in all three tables

This will result in the following query:

SELECT "province"."provincename","province"."population","country"."countryname","country"."population","city"."cityname","city"."population" FROM "province","country","city" WHERE "province"."country" = "country"."code" AND "city"."country" = "country"."code"

1. Click "Run Query"

The results page will show only "ProvinceName" and "CountryName".

Repeating with just the population columns results in an empty view.

[scottbw]

OK, this has nothing to do with the VQB, you can get the same effect using the old query builder too - its a problem in the either the results page or the underlying logic.

Tracing:

I run this query:

"'SELECT "country"."countryname","country"."population","city"."cityname","city"."population" FROM "country","city" WHERE "city"."country" = "country"."code"'

TableData reports having only 3 columns (not 4), with indices 1,2,3,4. It then returns only 2 columns (countryname, cityname) for rendering, presumably as its counting 1,2,3 through the indexes. I suspect the problem is further back in the code when generating TableData.

2015-10-30 11:58:58,402 DEBUG ords.database.TableData (TableData.java:35) - getColumnsByIndex 2015-10-30 11:58:58,402 DEBUG ords.database.TableData (TableData.java:36) - Looping through 3 column entries 2015-10-30 11:58:58,402 DEBUG ords.database.TableData (TableData.java:40) - Column: cityname 2015-10-30 11:58:58,402 DEBUG ords.database.TableData (TableData.java:41) - OrdsIndex:3 2015-10-30 11:58:58,402 DEBUG ords.database.TableData (TableData.java:40) - Column: countryname 2015-10-30 11:58:58,403 DEBUG ords.database.TableData (TableData.java:41) - OrdsIndex:1 2015-10-30 11:58:58,403 DEBUG ords.database.TableData (TableData.java:40) - Column: population 2015-10-30 11:58:58,403 DEBUG ords.database.TableData (TableData.java:41) - OrdsIndex:4 2015-10-30 11:58:58,403 DEBUG ords.database.TableData (TableData.java:55) - getColumnsByIndex:return 2015-10-30 11:58:58,403 DEBUG ords.database.TableData (TableData.java:56) - Returning 2 column entries

[scottbw]

Ouch. TableData.columns is a hashmap with column names as the keys. No wonder it doesn't work properly with duplicate table names.

[scottbw]

OK, this is a fundamental data modelling error with lots of side effects; columns are stored as a map of key:value pairs, where the key is the column name. This means that fundamentally ORDS isn't able to represent a table structure (such as results) with duplicate column names as new columns overwrite old ones in the map.

Changing this structure will affect 67 classes and an unknown number of JSP pages.

[jajwilson]

Oh lord. I guess we'll have to work around this for the time being as we don't have time to make a load of fundamental changes to the data modelling - unless this is quicker than it sounds.

[scottbw]

I can do an "emergency fix" for now but I can imagine it will cause more bugs elsewhere as we don't have full test coverage.

[scottbw]

Looking through the code I think fixing this bug will cause about 10 more as I can't be sure to catch the side-effects without taking 2-3 days on it.

[jajwilson]

Let's wait until we have test coverage again. We need to get the recent changes into App before we add more bugs - and it seems that this one has been around for a while without being spotted.

[scottbw]

Sounds like a plan. There is a workaround at least in that you can modify the column name in the schema editor to be different.

[jajwilson]

Yes - we'll just advice people to do that for now I think.