Same behavior with function "Upper". Will set all upper, but doesn't set special characters.

Seems the internal Firebird has a big UTF8-problem. Nearly no function for text will work as expected with special characters. All functions, which will set characters to lower and upper or which will need to count characters won't work right.

I didn't find some Firebird 3.0 doc about Lower function, only this:
http://firebirdsql.org/refdocs/langrefupd25-intfunc-lower.html

quotation:
"With ASCII or NONE for instance, only ASCII characters are lowercased; with OCTETS, the entire string is returned unchanged"

Lionel/Tamas: thought you might be interested in this one.

What character set is assigned? See e.g. http://www.destructor.de/firebird/charsets.htm

And collation, also. Try:
 SELECT "Name", LOWER ( "Name" COLLATE UNICODE ) FROM "Table2"
 SELECT "Name", LOWER ( "Name" COLLATE DE_DE ) FROM "Table2"
 SELECT "Name", LOWER ( "Name" COLLATE UTF-8 ) FROM "Table2"

(In reply to Lionel Elie Mamane from comment #3)
> What character set is assigned? See e.g.
> http://www.destructor.de/firebird/charsets.htm

Have tried to create a table with direct SQL as defined above. The following error appeared.

COLLATION DE_DE for CHARACTER SET NONE is not defined

I'm only using the internal Firebird. I haven't defined any collation before, because I have never had a database, where no collation hasn't be defined. Don't know if there is any character-set for Firebird installed at all.

(In reply to Lionel Elie Mamane from comment #4)
> And collation, also. Try:
>  SELECT "Name", LOWER ( "Name" COLLATE UNICODE ) FROM "Table2"
>  SELECT "Name", LOWER ( "Name" COLLATE DE_DE ) FROM "Table2"
>  SELECT "Name", LOWER ( "Name" COLLATE UTF-8 ) FROM "Table2"

Changing a query like this gives:
COLLATION UTF for CHARACTER SET NONE is not defined
... for every collation. And a query like this won't work in GUI, only direct SQL, so the query couldn't be used for input or changing data any more.

(In reply to robert from comment #5)

> Have tried to create a table with direct SQL as defined above. The following
> error appeared.

> COLLATION DE_DE for CHARACTER SET NONE is not defined

Looks like you need to set a character set _and_ a character set. You have "NONE" as character set, need "UTF8" or "ISO8859_1". E.g.:

CREATE TABLE users (
  "NAME" VARCHAR(50) CHARACTER SET UTF8 COLLATE UNICODE,
  ...
);


Possibly we should just set:
 * default character set UTF8 at the database level when it is created
 * UNICODE collation by default when the table is created through GUI (unless we can set that at database level, too?)

Let's first finish this through (see if it works...) and see about setting the default then.

(In reply to robert from comment #6)
> (In reply to Lionel Elie Mamane from comment #4)
> > And collation, also. Try:
> >  SELECT "Name", LOWER ( "Name" COLLATE UNICODE ) FROM "Table2"
> >  SELECT "Name", LOWER ( "Name" COLLATE DE_DE ) FROM "Table2"
> >  SELECT "Name", LOWER ( "Name" COLLATE UTF-8 ) FROM "Table2"
> 
> Changing a query like this gives:
> COLLATION UTF for CHARACTER SET NONE is not defined
> ... for every collation. And a query like this won't work in GUI, only
> direct SQL, so the query couldn't be used for input or changing data any
> more.

I think if the character set / collation are set at the table (or database level), the query will work without an explicit collation. I'm just trying to make a test here.

_After_ setting the character set, maybe
SELECT "Name", LOWER ( "Name" COLLATE 'UTF-8' ) FROM "Table2"
will work better (quotes around UTF-8).

(In reply to Lionel Elie Mamane from comment #7)
> (In reply to robert from comment #5)
> 
> Looks like you need to set a character set _and_ a character set. You have
> "NONE" as character set, need "UTF8" or "ISO8859_1". E.g.:
> 
> CREATE TABLE users (
>   "NAME" VARCHAR(50) CHARACTER SET UTF8 COLLATE UNICODE,
>   ...
> );

This one works. It seems it will solve all problems with special characters (Lower, Left, length of characters ...)
Couldn't we set this to database-default? I don't know a possibility to set this for the database after the *.odb-file has been created by LibreOffice.

Setting to new, per comment 8.

Perhaps we should open a new report with regard to having the UI at least specify a minimum collation / character set when the table is created like we do already for embedded hsqldb.

The script file in our embedded hsqldb appears to take collation from the locale information, e.g. :

SET DATABASE COLLATION "French"

I opened bug 105422 for the automatic generation of a collation statement on new FB3 db creation.

Sorry, that should be bug 105142

I pushed a patch to master:

http://cgit.freedesktop.org/libreoffice/core/commit/?id=1db423338899c71ba70e361af339d7b7e4aff61f

Note, that to test it you have to create a brand new database (the attachment is not good), because the default character set is set while creating a database.

Have tested it, works now.

Thanks to Tamas.

Could we get fixed it also in LO 5.3.1?