Created attachment 152488 [details] Open the queries - the one with an alias will work, the other not Open the attached database. Open the query "Query_Ambiguous". Query doesn't work, because the are two columns with the same name and the columns should be sorted by the name. Shows only an error. This code has been created by the query-GUI: SELECT "Forename", "Forename" FROM "Table1" ORDER BY "Forename" ASC Same code will work in HSQLDB-queries in the GUI. The query will work in Firebird only when setting an alias to one of the columns: SELECT "Forename" "FName", "Forename" FROM "Table1" ORDER BY "FName" ASC Could be this behaviour is a special Firebird behaviour. So it couldn't be fixed by LO.
If running the request on Firebird on direct SQL gives the error, I'd say not a LO pb. Anyway, even if it is, I can't help here.
Confirming with Version: 6.4.0.0.alpha0+ Build ID: 75d35ef8df1936dd93d5919b688abcaddb58bee8 CPU threads: 4; OS: Mac OS X 10.14.5; UI render: default; VCL: osx; Locale: fr-FR (fr_FR.UTF-8); UI-Language: en-US Calc: threaded
Dear Robert Großkopf, To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
Bug is still the same with LO 7.2.0.1 on OpenSUSE 15.2 64bit rpm Linux.
Dear Robert Großkopf, To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://web.libera.chat/?settings=#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
Bug is still the same: Version: 25.2.2.1 (X86_64) / LibreOffice Community Build ID: 38d746d66d9b82fa248a2e90142b9dd3ddd1d6cd CPU threads: 6; OS: Linux 6.4; UI render: default; VCL: kf5 (cairo+xcb) Locale: de-DE (de_DE.UTF-8); UI: de-DE Calc: threaded
Not is Bug of LO. >> SELECT "Forename", "Forename" FROM "Table1" ORDER BY "Forename" ASC is wrong query statement. Firebird response with error.
(In reply to mc from comment #7) > Not is Bug of LO. > > >> SELECT "Forename", "Forename" FROM "Table1" ORDER BY "Forename" ASC > is wrong query statement. Firebird response with error. And this wrong statement will be set by the GUI for creating a query. So it is a bug of the GUI not to create an alias. 2 solutions: 1. We have to create an alias through GUI automatically 2. We have to create an alias in the background for Firebird, because it works well with internal HSQLDB and also other databases connected to Base like MariaDB. It isn't a solved invalid bug. If we would do this for all, what wouldn't work with Firebird, we should end supporting for Firebird.
Created attachment 200809 [details] Ambiguous Query, MSSQL @robert This query statement is ambiguous in "standard", see MSSQL response
(In reply to mc from comment #9) > Created attachment 200809 [details] > Ambiguous Query, MSSQL > > @robert > This query statement is ambiguous in "standard", see MSSQL response And now try the same with internal HSQLDB and with MariaDB direct connection. Will work, because these databases will take the first field if there are fields with the same name. So what is "standard"? And: Why does the GUI offer this possibility if it doesn't work?
(In reply to Robert Großkopf from comment #10) > (In reply to mc from comment #9) > > Created attachment 200809 [details] > > Ambiguous Query, MSSQL > > > > @robert > > This query statement is ambiguous in "standard", see MSSQL response > > And now try the same with internal HSQLDB > ... > > So what is "standard"? And: Why does the GUI offer this possibility if it > doesn't work? Tried in: MariaDB, PostgreSql, Sqlite, HSQLDB work Firebird, MSSQL, Oracle not work The GUI offer to write all you think, but the DB Engine, check if your statement is valid command. Now without going into too much detail, what is the point of repeating a column twice in a Select statement?
(In reply to mc from comment #11) … > > Now without going into too much detail, what is the point of repeating a > column twice in a Select statement? Its a query like this: SELECT "Table1".*, "Table2".* FROM "Table1", "Table2" WHERE "Table1"."ID" = "Table2"."T1_ID" Both tables could contain fields with the same name. Sorting of the result would be impossible for fields with the same name of different tables.
Created attachment 200815 [details] ORDER BY, two tables with same fields name @robert Not see difficulties. In ORDER BY need to declare the name of Table
(In reply to mc from comment #13) > Created attachment 200815 [details] > ORDER BY, two tables with same fields name > > @robert > Not see difficulties. In ORDER BY need to declare the name of Table And now set code only to connect the tables: SELECT "Table1".*, "Table2".* FROM "Table1", "Table2" WHERE "Table1"."ID" = "Table2"."T1ID" (set a foreignkey to Table2 but there is no difference) Execute the query, try to order by GUI - doesn't work. Its the same reason. Will only work if I set all fields of the second table with alias. The GUI will work with internal HSQLDB with 2 tables the same as with one table and 2 fields with same name. The GUI won't work with internal Firebird with 2 tables, also not with one table and 2 fields and the same name. You could create such a query in GUI by choosing the field a second time and sort this field, because there has been a table added.
@robert Also HSQLDB does not work if you try to sort from the Sort buttons in the toolbar, in fact, the sort only works on the first column. The best solution is to write the SQL statement correctly and, if necessary, any column Aliases. By writing the statement correctly it can be easily used for other DB Engines too.
(In reply to mc from comment #15) > @robert > Also HSQLDB does not work if you try to sort from the Sort buttons in the > toolbar, in fact, the sort only works on the first column. And this is the behavior user expect since Base exists together with internal HSQLDB: Queries could be mixed as you want. If there are duplicate names in the fields the first field will be taken as the field for sorting. This isn't a special behavior of HSQLDB. It is a behavior of the GUI. This has nothing to do with writing a statement correctly. It is something I could create by the GUI offered for creating queries and users expect the GUI will work, special with internal databases. By the way: How would you define "correctly"? As the GUI creates a statement? I would prefer to end this discussion and offer a solution Firebird will work as expected with GUI. We try this since nearly 8 years and had decided some times ago to set Firebird as experimental, because there are too much elements which doesn't work as expected. One thing is sorting: When creating a database in Firebird I only sort after changing columns to UPPER cased, because the kind of sorting Firebird uses at default is a nightmare. You could do this better by setting the collation, but I got external Firebird databases with setting collation, which couldn't be used by peoples with other systems.
(In reply to Robert Großkopf from comment #16) > (In reply to mc from comment #15) > > @robert > > Also HSQLDB does not work if you try to sort from the Sort buttons in the > > toolbar, in fact, the sort only works on the first column. > > And this is the behavior user expect since Base exists together with > internal HSQLDB: > Queries could be mixed as you want. If there are duplicate names in the > fields the first field will be taken as the field for sorting. > > This isn't a special behavior of HSQLDB. It is a behavior of the GUI. > > This has nothing to do with writing a statement correctly. It is something I > could create by the GUI offered for creating queries and users expect the > GUI will work, special with internal databases. > > By the way: How would you define "correctly"? As the GUI creates a statement? > > ... In this way, the sorting with GUI work fine for all DB Engine (HSQLDB, Firebird ...) SELECT "Table1"."Surname" "Surname1", "Table2"."Surname" "Surname2" FROM "Table1", "Table2" WHERE "Table1"."ID" = "Table2"."ID"