Bug 120126 - Base error after migrating to Firebird
Summary: Base error after migrating to Firebird
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.1.1.2 release
Hardware: All Mac OS X (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Database-Firebird-Migration
  Show dependency treegraph
 
Reported: 2018-09-26 10:21 UTC by Tim
Modified: 2019-01-31 14:04 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
This Query works ok with "Población" field. (133.38 KB, image/jpeg)
2018-09-26 21:11 UTC, Tim
Details
This one very similar to the previous doesn't work (128.16 KB, image/jpeg)
2018-09-26 21:11 UTC, Tim
Details
Base Database (3.02 MB, application/vnd.oasis.opendocument.database)
2018-10-02 09:56 UTC, Tim
Details
ddbb before migrating (2.75 MB, application/vnd.oasis.opendocument.database)
2018-10-08 16:33 UTC, Tim
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Tim 2018-09-26 10:21:25 UTC
Description:
firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -204
*Ambiguous field name between a field and a field in the select list with name
*Población
caused by
'isc_dsql_prepare'

But if I edit the Query and execute there it works!

Steps to Reproduce:
1.Migrate to Firebird
2.Open a Query
3.

Actual Results:
Error

Expected Results:
List selected files


Reproducible: Always


User Profile Reset: No



Additional Info:
Comment 1 Alex Thurgood 2018-09-26 10:37:19 UTC
@Tim please provide us with a test ODB so that we can try and reproduce the problem.
Comment 2 Tim 2018-09-26 11:09:45 UTC
I think it happens when in the query I have in one column File.* and also in another column the Field "Población" with sort or a criterion, but it only seems to happen with that Field!
Comment 3 Julien Nabet 2018-09-26 15:35:15 UTC
Just for the test, would it be possible you change field name and remove the accent?
The goal is to know if it's an encoding pb.
Comment 4 Tim 2018-09-26 21:11:03 UTC
Created attachment 145191 [details]
This Query works ok with "Población" field.

After this I'll send a similar Query where "Población" gives error.
Comment 5 Tim 2018-09-26 21:11:58 UTC
Created attachment 145192 [details]
This one very similar to the previous doesn't work
Comment 6 Tim 2018-09-26 21:15:12 UTC
AND YES! If I change "Población" field to "Poblacion", without accent, the one that didn't run works...!!!
Comment 7 Tim 2018-09-26 21:19:28 UTC
And now I've changed the field back to "Población", with accent, and the Query works!
Comment 8 Julien Nabet 2018-09-26 21:22:17 UTC
(In reply to Tim from comment #7)
> And now I've changed the field back to "Población", with accent, and the
> Query works!

It shows thqt the only pb is migration part which doesn’t take account encoding here.
Tamas: thought you might be interested in this one
Comment 9 Tim 2018-09-26 21:27:56 UTC
Thanks!

Now in another Query with the same error, even if I delete and afterwards add again the field, keeps on giving the error!

It looks like as if something remains somewhere?
Comment 10 Tim 2018-09-26 21:52:35 UTC
And again! If a change the Field name without accent, the Query works. Then I go back and put the Field with accent and the Query works!

It looks likes there is something behind...? It's not enough to delete the Column in the Query and pin up again the Field in a new column.
Comment 11 Tim 2018-09-26 22:31:48 UTC
If I make a new Query exactly with the same fields, conditions..., it works ok!
Comment 12 Tamas Bunth 2018-09-27 09:48:28 UTC
Could you please attach an example ODB file with which the issue can be reproduced? (following the "Steps to Reproduce" part of the bug description)
Comment 13 Tim 2018-10-02 09:56:22 UTC
Created attachment 145320 [details]
Base Database

Executing the following queries within the data base I get the errors...

AAHH_Ga_Consello

firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -104
*Invalid usage of boolean expression caused by 'isc_dsql_prepare'
------------------------

Ctas_CoAhEs_50_Dat
Ctas_CoAhEs_51-100_Dat
Ctas_CoAhEs_101_Dat
Ctas_CoAhEs_NO_Dat
Iglesias_Coruña_Prov
Iglesias_Galicia

firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -204
*Ambiguous field name between a field and a field in the select list with name
*Población caused by 'isc_dsql_prepare'
----------------------------------------

Iglesias_Extranjero

firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -204
*Ambiguous field name between a field and a field in the select list with name
*País caused by 'isc_dsql_prepare'
Comment 14 Xisco Faulí 2018-10-08 14:44:25 UTC
Putting this to NEW.
@Tim, do you also have the original ddbb before the migration ?
Comment 15 Tim 2018-10-08 16:33:01 UTC
Created attachment 145480 [details]
ddbb before migrating
Comment 16 Tim 2018-10-10 23:52:49 UTC
This is one of the errors after migrating:

SELECT "Iglesias".*, "Iglesias"."País" FROM "Iglesias" "Iglesias" WHERE "País" <> 'España' ORDER BY "País" ASC

It works if a put:
SELECT * FROM "Iglesias" WHERE "País" <> 'España' ORDER BY "País" ASC
Comment 17 Julien Nabet 2018-10-13 16:59:16 UTC
On pc Debian x86-64 with master sources updated today, I tried to reproduce this.

I had this during Firebird migration:
Error code: 1

firebird_sdbc error:
*unsuccessful metadata update
*ALTER TABLE Iglesias failed
*Unknown columns in index SYS_FK_78
caused by
'ALTER TABLE "Iglesias" ADD CONSTRAINT SYS_FK_78 FOREIGN KEY("Denominaci\u00f3n") REFERENCES "Denominaciones"("ID")'

Then indeed I could reproduce the pb by trying "Iglesias_Coruña_Prov" query.
Comment 18 Tim 2018-10-13 18:14:51 UTC
May be; but as "Denominación" was changed to "Denominaci\u00f3n" I had to then rename to have it right, and because in another case the name exceeded length permiten as it puts in more digits!
Comment 19 Alex Thurgood 2018-10-15 07:26:04 UTC
(In reply to Tim from comment #18)
> because in another case the name exceeded
> length permiten as it puts in more digits!

That behaviour is bug 116987 (field, table, or query name more than 30 chars)
Comment 20 Alex Thurgood 2018-10-15 07:38:31 UTC
The conversion of non-ASCII chars to a unicode equivalent and the placing of that unicode string instead of the original character is already known as bug (which I can't find at the moment), so in that regard, this report would be a duplicate of that bug.
Comment 21 Terrence Enger 2018-10-30 20:57:21 UTC
I have created bug 121069, which may be the same as this bug but
showing up differently and earlier on Linux.
Comment 22 Tamas Bunth 2019-01-31 14:01:56 UTC
Hi,

(In reply to Tim from comment #13)
> Created attachment 145320 [details]
> Base Database
> 
> Executing the following queries within the data base I get the errors...
> 
> AAHH_Ga_Consello
> 
> firebird_sdbc error:
> *Dynamic SQL Error
> *SQL error code = -104
> *Invalid usage of boolean expression caused by 'isc_dsql_prepare'
> ------------------------

That is because the query named AAHH_Ga_Consello tries to compare a BOOLEAN type with a number (Firebird uses BYTE for the literal '-1' to be exact).

That is not allowed using Firebird. It seems to be allowed with HSQLDB, but it seems to be undocumented.

I think there is nothing to fix here.

> 
> Ctas_CoAhEs_50_Dat
> Ctas_CoAhEs_51-100_Dat
> Ctas_CoAhEs_101_Dat
> Ctas_CoAhEs_NO_Dat
> Iglesias_Coruña_Prov
> Iglesias_Galicia
> 
> firebird_sdbc error:
> *Dynamic SQL Error
> *SQL error code = -204
> *Ambiguous field name between a field and a field in the select list with
> name
> *Población caused by 'isc_dsql_prepare'
> ----------------------------------------
> 
> Iglesias_Extranjero
> 
> firebird_sdbc error:
> *Dynamic SQL Error
> *SQL error code = -204
> *Ambiguous field name between a field and a field in the select list with
> name
> *País caused by 'isc_dsql_prepare'

You have queried the column named 'País' twice here. Once with 'Iglesias.*' and you also queried it explicitly.

After that the criterion "<> 'España'" results in ambigousity, because the DBMS cannot determine which 'País' field is referenced.

Based on the comments above, I closed the bug as "NOTABUG".
Comment 23 Tamas Bunth 2019-01-31 14:04:02 UTC
(In reply to Tim from comment #6)
> AND YES! If I change "Población" field to "Poblacion", without accent, the
> one that didn't run works...!!!

This issue is already addressed here:
https://bugs.documentfoundation.org/show_bug.cgi?id=121469

and here:
https://bugs.documentfoundation.org/show_bug.cgi?id=121069