https://bugs.freedesktop.org/show_bug.cgi?id=57985
Priority: medium
Bug ID: 57985
Assignee: libreoffice-bugs@lists.freedesktop.org
Summary: SQL: left() function causes VARCHAR_IGNORECASE columns
to be compared case-sensitive
Severity: normal
Classification: Unclassified
OS: Windows (All)
Reporter: davidjudysm...@gmail.com
Hardware: Other
Status: UNCONFIRMED
Version: 3.6.3.2 release
Component: Database
Product: LibreOffice
Created attachment 71135
--> https://bugs.freedesktop.org/attachment.cgi?id=71135&action=edit
Database file containing the tables described.
I have found a situation where values in specified columns are compared in a
case-sensitive way, even though the columns are both declared as
VARCHAR_IGNORECASE.
The attached database (Case Sensitive Function.odb) contains the example.
There are two tables:
Text1 has a column Col1 with text values abcde, fghij, klmno, and pqrst;
Text2 has a column Col2 with text values abcde, Fghij, klmnO, and pqRst.
(Note that the first values match, while the other values differ in one capital
letter.)
Both Col1 and Col2 are declared as VARCHAR_IGNORECASE. Text1 has an additional
column called Target.
The SQL statement
update "Text1" "T1" set "Target"=(select "Col2" from "Text2" "T2" where
T1."Col1"=T2."Col2")
works as expected: all of the rows from Col2 in Text2 are copied to Target,
because the strings match in a case-insensitive comparison.
But the SQL statement
update "Text1" "T1" set "Target"=(select "Col2" from "Text2" "T2" where
left(T2."Col2",5)=T1."Col1")
does not work correctly. Only the first row from Col2 is copied to Target,
showing that something - presumably the left() function - caused a
case-sensitive comparison to be done. Interestingly, the statement
update "Text1" "T1" set "Target"=(select "Col2" from "Text2" "T2" where
T1."Col1"=left(T2."Col2",5))
works as expected, copying all rows, so there is something significant about
the order of the comparison.
Perhaps this behavior of the left() function (and the right() function has the
same quirk) is by design. My issue is that I need a way to force comparisons
to be case-insensitive, and apparently specifying the column type isn't
sufficient.
To show the behavior:
1. Open the Case Sensitive Function.odb database.
2. Edit the two tables Text1 and Text2 to verify the column declarations.
3. Open each of the tables to verify the contents. Make sure that the Target
column is empty.
4. From the Tools menu, choose SQL. Copy one of the SQL statements above into
the Command field, then click Execute. When the statement executes correctly,
click Close.
5. Open the Text1 table and inspect the Target column. If all four rows are
filled, then the comparison was case-insensitive; if only the first row is
filled, the comparison was case-sensitive.
6. Be sure to delete all values from Target before closing the table, in
preparation for the next test.
(Note: my installation of LibreOffice uses the default database, presumably
HSQL. I don't know how to verify that that's the case.)
--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
