TRIM function is removing characters instead of just spaces

[Luciano Rodrigues Nunes Mendes]

Hi Everyone,

I noticed that the TRIM function is removing characters from the end of fields “CHARACTER SET UTF8 NOT NULL COLLATE UNICODE_CI_AI” instead of just removing whitespace (see attached image).
Could you tell if this is really a bug and if there is already an open ticket tracking this issue?

Thanks in advanced,

Luciano

Here are the table structure for reproduce the issue:

SET SQL DIALECT 3;
/* CREATE DATABASE 'LOCALHOST:BOTSERVICE_CIN' PAGE_SIZE 8192 DEFAULT CHARACTER SET UTF8; */
COMMIT WORK;
/* Table: FEEDITEMS, Owner: SYSDBA */
CREATE TABLE FEEDITEMS (ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
        FEED_ID BIGINT NOT NULL,
        TITLE VARCHAR(4096) CHARACTER SET UTF8 NOT NULL COLLATE UNICODE_CI_AI,
        LINK VARCHAR(1024) CHARACTER SET WIN1252 NOT NULL COLLATE WIN_PTBR,
        PUBLISH_DATE TIMESTAMP DEFAULT LOCALTIMESTAMP NOT NULL,
        VISIBLE BOOLEAN DEFAULT TRUE NOT NULL,
CONSTRAINT FEEDITEMS_PK PRIMARY KEY (ID),
CONSTRAINT FEEDITEMS_UK_1 UNIQUE (FEED_ID, LINK));

[Karol Bieniaszewski]

Have you tested it outside of Flamerobin?

 

Regards,

Karol Bieniaszewski

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/fe71114e-b185-4b5c-b40d-be00ca2947f4n%40googlegroups.com.

 

[Luciano Rodrigues Nunes Mendes]

Hi Karol,

Thanks for your prompt reply!

Yes, I have tested using ADO.NET provider connection and the issue is also reproducible.

Would you like to have the database to see this issue from your side?

thanks in advance,

Luciano

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/4L58yZ6cP1zlg2MN%40smtp.poczta.onet.pl.

[Luciano Rodrigues Nunes Mendes]

Hi Karol,

I have attached the database: BOTSERVICE_CIN.FDB.

To reproduce the issue just run the following query (Ses the issue on picture attached: Firebird_Trim_Issue_2.png)

SELECT

  *

FROM

(

  SELECT

    FEEDITEMS.TITLE,

    TRIM(FEEDITEMS.TITLE) AS TRIM_TITLE

  FROM

    FEEDITEMS

)

WHERE

  TITLE<>TRIM_TITLE

Best Regards,

Luciano

[Karol Bieniaszewski]

It looks like issue with UNICODE_CI_AI as if you change it to TRIM(FEEDITEMS.TITLE COLLATE UTF8) AS TRIM_TITLE

it work ok.

 

Report it on https://github.com/FirebirdSQL/firebird/issues then FB developers can look at it.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/CAK1TzWD5jvo5K61oJd-tqR3saUzMCYH%3D9DDt2g9odHHkhCMb6Q%40mail.gmail.com.

 

[Luciano Rodrigues Nunes Mendes]

Hi Karol,

You are right! Using UTF8 instead UNICODE_CI_AI collate it works fine!

I opened this TRIM function is wrongly working in collate UNICODE_CI_AI · Issue #7190 bug to track this issue!

Thank you very much for your support!

Best Regards,

Luciano

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/4L5Fh91Wwvzlg8GC%40smtp.poczta.onet.pl.