Bug 161144

Summary:	Query wizard confuses columns with the same name in different tables
Product:	LibreOffice	Reporter:	Hossein <hossein>
Component:	Base	Assignee:	Not Assigned <libreoffice-bugs>
Status:	NEW ---
Severity:	normal	CC:	mentoring
Priority:	medium	Keywords:	difficultyMedium, easyHack, skillJava, skillSql
Version:	24.8.0.0 alpha0+
Hardware:	All
OS:	All
See Also:	https://bugs.documentfoundation.org/show_bug.cgi?id=67664
Whiteboard:
Crash report or crash signature:		Regression By:

Description Hossein 2024-05-17 08:48:41 UTC

This is a spin-off of the "tdf#67664 Query wizard in need of love" (problem #2).

There are shortcomings in the Query Wizard implementation, and one of them is the confusion between columns that have the same name in different tables.

Steps to Reproduce:
To reproduce this problem, you have to create two tables:

"Table1", with the field name "a" (also add a primary key)
"Table2", with the field name "a" (also add a primary key)

First open LibreOffice, then choose "Base Database" from the "start screen", and then select the defaults, and save the result as your desired name.

Then, choose "Create Table in Design View...", and add a field name "a". Use "close" on the toolbar, save changes, and accept "Table1" as the name. When you get the warning about lack of primary key, accept the solution by pressing "Yes".

Then do the same for creating a table named "Table2" with the same field name "a".

After that, go to the "Queries" icon in the main Base page, and select "Use Wizard to Create Query...". In "1. Field selection", Add two 'a' fields from "Table1" and "Table 2" to the "Fields in the Query" using ">" button. In "2. Sorting order", pick "Table1.a" and "Table2.a" for "Sort by", and "Then by". Accept and move to the last page, and press "Finish".

You will get this error message:

The data content could not be loaded. at /home/buildslave/source/libo-core/connectivity/source/commontools/dbtools.cxx:746

firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -204
*Ambiguous field name between an alias and an alias in the select list with name
*a
caused by
'isc_dsql_prepare'
at /home/buildslave/source/libo-core/connectivity/source/drivers/firebird/Util.cxx:69

If you try again and choose to "Modify Query" in the last page, "8. Overview", you will see overlaps. Also, in the overview page, you can see this, which shows the confusion:

Sorting order: a (ASC), a (ASC)

The goal here is to fix this issue by using the table name when using the query in Query Wizard, and distinguish the similar names from different tables.

The problem is not be limited to "Sorting order". Therefore, any solution should take care of all the possible usages of the similar field names in pages from 1 to 8 of the query wizard, and test extensively to make sure that the issue is resolved by the fix.

Comment 1 Hossein 2024-05-17 15:06:26 UTC

Here is the suggested path to fix the issues:

If you open the created query using "Edit in SQL View...", you will see this:

SELECT "Table1"."a" "a", "Table2"."a" "a" FROM "Table1", "Table2" ORDER BY "a" ASC, "a" ASC

If you choose a and b for the alias of the two fields, it becomes:

SELECT "Table1"."a" "a", "Table2"."a" "b" FROM "Table1" "Table1", "Table2" "Table2" ORDER BY "a", "a"

which is wrong.

If you change it to the below SQL query, it should work correctly:

SELECT "Table1"."a", "Table2"."a" FROM "Table1", "Table2" ORDER BY "Table1"."a", "Table2"."a"

Even in this case, the title of the both columns are "a", and does not show anything about the table name. This is an issue.

Also, this one should work:

SELECT "Table1"."a" "a", "Table2"."a" "b" FROM "Table1" "Table1", "Table2" "Table2" ORDER BY "a", "b"

An implementation path could be making sure that the correct alias is used, and also to avoid using duplicate aliases by prefixing table names when needed. In this way, the column display would be also fixed.

Comment 2 Hossein 2024-05-17 15:19:20 UTC

Code pointers:

The relevant source code is inside wizards/com/sun/star/wizards/db/ and wizards/com/sun/star/wizards/query/ folders, and specifically the Java file wizards/com/sun/star/wizards/db/SQLQueryComposer.java contains most of relevant code.

One should start from getSelectClause() method, and go step by step to see how query is being built:

public String getSelectClause(boolean _baddAliasFieldNames) throws SQLException
{
    ....
}

There are methods for Sortingcriteria, GroupByColumns, FromClause and AliasDisplayName. To fix the alias used in sorting, look into appendSortingcriteria().

The query summary is created in wizards/com/sun/star/wizards/query/QuerySummary.java, and it gives you hints where to look. For example, when getting this in summary:

Sorting order: a (ASC), a (ASC)

The code can be found by searching for "Sorting order:" which leads to RID_QUERY_51, that is found in wizards/com/sun/star/wizards/query/QuerySummary.java:60. The relevant code is directly inside setSummaryString() method:

sSortingFraction = combinePartString("RID_QUERY_51", getSortFieldNames(), "RID_QUERY_52", "RID_QUERY_93", new String[]
{
    "<FIELDNAME>", "<SORTMODE>"
}) + sReturnChar;

On the other hand, combineFieldNameFraction() creates this line, which is more complete:

"Fields in the Query: a (Table1.a), a (Table2.a)"

One can look into the above method, and create a similar method for sorting.