Created attachment 71135 [details] 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.)
All input in the direct SQL-way (like your update-commands) could not be changed by the GUI of Base. It's the behavior of the HSQLDB. You could wish to change the old HSQLDB to a newer one - or to another database. This is discussed in https://bugs.freedesktop.org/show_bug.cgi?id=53333 https://bugs.freedesktop.org/show_bug.cgi?id=51781 ...
Thanks for your comment, and for links to the discussion of other databases. I understand that the underlying problem is in HSQLDB rather than in LO. But isn't there a channel for LO developers to report bugs to HSQLDB?
(In reply to comment #2) > > I understand that the underlying problem is in HSQLDB rather than in LO. > But isn't there a channel for LO developers to report bugs to HSQLDB? The internal HSQLDB is version 1.8.0; the actual HSQLDB is version 2.2. No developer would change something in the old version, which is included in LO and OpenOffice since OpenOffice 2. The only way could be to change the whole database in LO. But this could be a problem for all users of the old HSQLDB and all users, who want to use databases in LO and in AOO. Also no developer of HSQLDB would change anything in a version, which has been fixed the last time 2010-06-23 (http://sourceforge.net/projects/hsqldb/files/hsqldb/). And this isn't the same version included in LO, so it couldn't be used in LO, if anybody would change something in this database.
Thanks for the extra detail. Certainly it's a difficult situation for LO: the default HSQLDB is imperfect, but any change to it will affect current users. I hope that the LO planning team is thinking about this, because it will only get worse as time progresses. Back before I retired from my IT support job, I often had to deal with software vendors, and I spent much time carefully characterizing and reporting bugs. I found that the developers were happy to take bug reports that related to old versions, because then they could say either "It's still in the current version, so we'll add it to our bug list" or "We fixed that in version x.x, so you should upgrade." That's why I suggested passing this one to HSQLDB: not because I thought they would fix it in the old version for LO, but because it might still be a problem that they should address in their own development plan.
Hello Well, for me it is not a bug. (In reply to comment #0) > My issue is that I need a way to force > comparisons to be case-insensitive, and apparently specifying the column > type isn't sufficient... Yes it is : > The SQL statement > update "Text1" "T1" set "Target"=(select "Col2" from "Text2" "T2" where > T1."Col1"=T2."Col2") > works as expected Now, in a query, as you point out the order of comparison is crucial. With the SQL statement: > update "Text1" "T1" set "Target"=(select "Col2" from "Text2" "T2" where > T1."Col1"=left(T2."Col2",5)) T1."Col1" ignores case and "accepts" so uppercase or lowercase (result of left...) With the SQL statement: > update "Text1" "T1" set "Target"=(select "Col2" from "Text2" "T2" where > left(T2."Col2",5)=T1."Col1") The expression left(T2."Col2",5) is evaluated and compared with T1."Col1" T1."Col1" is defined as to ignore case, but not the result of expression left... IMHO This does not seem to me to be a bug, I would recommend then to close as NOTABUG (In reply to comment #1) If I understand this response, with all due respect, it seems inappropriate: the request is not here to be able to run update queries from the GUI. The Issue is not either a limitation of the engine version but a different result depending on how to write SQL. Best regards Pierre-Yves
> > update "Text1" "T1" set "Target"=(select "Col2" from "Text2" "T2" where > > T1."Col1"=left(T2."Col2",5)) > > T1."Col1" ignores case and "accepts" so uppercase or lowercase (result of > left...) > > With the SQL statement: > > > update "Text1" "T1" set "Target"=(select "Col2" from "Text2" "T2" where > > left(T2."Col2",5)=T1."Col1") > > The expression left(T2."Col2",5) is evaluated and compared with T1."Col1" > T1."Col1" is defined as to ignore case, but not the result of expression > left... In SQL it has to be the same result when linking a = b or b = a. When this doesn't work it is a bug. Try in the example-database: SELECT "Col2" FROM "Text2" WHERE 'pqrst' = "Col2" Response is Col2 with 'pqRst'. > > > (In reply to comment #1) > If I understand this response, with all due respect, it seems inappropriate: > the request is not here to be able to run update queries from the GUI. The comment has nothing to do with an update-query from the GUI. The problem of this bug is, that nobody from the Libre-Office developers could change something at this position. It's a direct SQL-input. Somebody, who has installed a newer version of HSQLDB, could try the command. Could be, that it works in the way the reporter of the bug thinks it should work. I can confirm the bug, but I would say: it is not our bug.
(In reply to comment #5) > Well, for me it is not a bug. I disagree. > Now, in a query, as you point out the order of comparison is crucial. I'm not aware of any other SQL situation in which a comparison is non-commutative. "Equal" should mean equal, regardless of the order of the comparands, and as far as I know it does mean that in every other flavor of SQL. What you're describing sounds like a workaround for a bug, and not one that I as a developer would be happy to publicize except as a short-term fix.
(In reply to comment #6) > I can confirm the bug, but I would say: it is not our bug. I agree, and I would hope that there is an established channel from the L-O developers to the HSQLDB developers to report bugs, even in old versions, in case the bugs have not yet been fixed. Is that not the case? Is it my responsibility as an L-O user to report this to HSQLDB?
(In reply to comment #8) > (In reply to comment #6) > > > I can confirm the bug, but I would say: it is not our bug. > > I agree, and I would hope that there is an established channel from the L-O > developers to the HSQLDB developers to report bugs, even in old versions, in > case the bugs have not yet been fixed. Is that not the case? Is it my > responsibility as an L-O user to report this to HSQLDB? I 'm only a LO-user, have nothing to do with the developing. But I have just tested the behavior with a external HSQLDB, version 2.2.6. The update runs correct. All rows were updated, not only the fist as in the internal HSQLDB. So we haven't to report this bug, because it didn't exist in the actual HSQLDB any more. I set this bug to "Resolved" and "Notourbug". Could only be solved by changing the internal database.
Hello (In reply to comment #6) > In SQL it has to be the same result when linking a = b or b = a. When this > doesn't work it is a bug. Ok > The comment has nothing to do with an update-query from the GUI. The problem > of this bug is, that nobody from the Libre-Office developers could change > something at this position. Sorry, I knew that I had misunderstood. Text was too implicit for my bad english (or better my English too bad to guess what was implied) Sorry for the spam :) Regards Pierre-Yves