Created attachment 187802 [details] Extract the *.zip and start the database file … Download the attached *.zip-file. Extract and start the database file. Hope the connection to the dBase-file will work. Start the query. It will show "ID" starting with 2, ending with 9. Close the query, open the query for editing (will only work opening in SQL-mode). Have a look at the code: SELECT "ID" FROM "table" WHERE "ID" BETWEEN '1' AND '10' BETWEEN should start with '1' and end with '10'. If you execute BETWEEN in internal HSQLDB and Firebird it will do so. For dBase connections it will exclude start value and end value. BETWEEN should give the same result as SELECT "ID" FROM "table" WHERE "ID" >= 1 AND "ID" <= 10 This bug appears on all LO-versions here (oldest here: LO 5.1.5.2). It appears also on AOO 4.1.8. Think it has been inherited from OOo. See also: https://ask.libreoffice.org/t/base-query-not-reading-table-with-between-statement/92299
Repro Arch Linux 64-bit, X11 Version: 24.2.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: 518fa99dd7693d64a53e404a065090aedc0002b1 CPU threads: 8; OS: Linux 6.4; UI render: default; VCL: kf5 (cairo+xcb) Locale: fi-FI (fi_FI.UTF-8); UI: en-US Calc: threaded Built on 14 August 2023
I gave a try with https://gerrit.libreoffice.org/c/core/+/173036
BTW the patch also fixes the case "NOT BETWEEN" which was keeping values 0, 11, 12, etc but also 1 and 10.
Julien Nabet committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/403075b4614d2926a07cb43806c3e9ef311ee779 tdf#155751: dBase: BETWEEN won't include first and last value in queries It will be available in 25.2.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Patch for 24.8 branch waiting here: https://gerrit.libreoffice.org/c/core/+/173015
Julien Nabet committed a patch related to this issue. It has been pushed to "libreoffice-24-8": https://git.libreoffice.org/core/commit/23e9fe3b8f71f7d47b0e4a132252efb91cac9e6d tdf#155751: dBase: BETWEEN won't include first and last value in queries It will be available in 24.8.2. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Julien Nabet committed a patch related to this issue. It has been pushed to "libreoffice-24-2": https://git.libreoffice.org/core/commit/22feec2dd97dd4db9a81b26e248303f9921de6cc tdf#155751: dBase: BETWEEN won't include first and last value in queries It will be available in 24.2.7. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.