| Summary: | EDITING: Query design view generates SQL syntax error on NOT LIKE | ||
|---|---|---|---|
| Product: | LibreOffice | Reporter: | Graham Horner <graham> |
| Component: | Base | Assignee: | Lionel Elie Mamane <lionel> |
| Status: | RESOLVED FIXED | ||
| Severity: | normal | CC: | johnnak12, lionel, lo_bugs |
| Priority: | medium | Keywords: | bibisected, regression |
| Version: | 4.2.6.2 release | ||
| Hardware: | x86-64 (AMD64) | ||
| OS: | All | ||
| See Also: | https://bugs.freedesktop.org/show_bug.cgi?id=55703 | ||
| Whiteboard: | target:4.4.0 target:4.3.1 target:4.2.7 | ||
| Crash report or crash signature: | Regression By: | ||
| Attachments: | Query editor screenshot before editing criterion | ||
Works as expected in 3.5.7.2 on Ubuntu git bisect tells me:
eef21bdacb96f181580d7ce3abf97ae24ebaccdb is the first bad commit
...
source-hash-6768d2c7f2cf75c507ec2108cbbb5a8a6cf7fae9
...
and git bisect log:
# bad: [793dbf6f80f497dfe587d560d6257f42a24273f6] source-hash-1581b1fc3ac82a7bd62df968226e98604a4ca52d
# good: [65fd30f5cb4cdd37995a33420ed8273c0a29bf00] source-hash-d6cde02dbce8c28c6af836e2dc1120f8a6ef9932
git bisect start 'latest' 'oldest'
# good: [8092559c5013969ebda017d79200463b9b975038] source-hash-fd84daf696a368c2c7561b5253b32a63ecdeca4a
git bisect good 8092559c5013969ebda017d79200463b9b975038
# good: [0270ef1b76a6de423b30f7927362cc01c1a0fc38] source-hash-b1f7dd66b898b03cb4bd8d434b6370310ea95946
git bisect good 0270ef1b76a6de423b30f7927362cc01c1a0fc38
# skip: [ddb123cad22440994cd332d9985dd9558fd07e07] source-hash-647fb29f528b891a1c92846640f7865f5c1fbe7f
git bisect skip ddb123cad22440994cd332d9985dd9558fd07e07
# skip: [9d357dc6201f7cd91448595e0a3f89dfdae81946] source-hash-2304beaca33c63b94df99cb827716f00ce259f9a
git bisect skip 9d357dc6201f7cd91448595e0a3f89dfdae81946
# bad: [ef72aa34cf4ee6399b192de28708d621c9680a50] source-hash-7e07a45500dcbb891a85f0bc9b7049cf4d50bba1
git bisect bad ef72aa34cf4ee6399b192de28708d621c9680a50
# bad: [38c5492124877285c647fa6816f86b8473dbdeb8] source-hash-f4546b72702dbe30505594a8307dd402e81a0303
git bisect bad 38c5492124877285c647fa6816f86b8473dbdeb8
# bad: [57739010c0189a5fdf14ba4cae1612ef572df3dd] source-hash-420aa16af0bbab4bdef80ceeb8d44cabe65840e0
git bisect bad 57739010c0189a5fdf14ba4cae1612ef572df3dd
# bad: [f810e3bc11e4caa6955e050cb0d1847899fa0f1d] source-hash-1c7e0ba6cd9b1ce6a3e1c65460a3a0849bcb3189
git bisect bad f810e3bc11e4caa6955e050cb0d1847899fa0f1d
# bad: [eef21bdacb96f181580d7ce3abf97ae24ebaccdb] source-hash-6768d2c7f2cf75c507ec2108cbbb5a8a6cf7fae9
git bisect bad eef21bdacb96f181580d7ce3abf97ae24ebaccdb
# first bad commit: [eef21bdacb96f181580d7ce3abf97ae24ebaccdb] source-hash-6768d2c7f2cf75c507ec2108cbbb5a8a6cf7fae9
Confirming this bug report. The most similar report I have found is bug 55703 "Query Design SELECT condition: should automatically use "=" or "IS" operator depending on constant value". It is in the same area, but pertains to a different selection criterion. If someone is looking at the way the parser handles reserved words, I have added my take on bug 55703. *** Bug 80916 has been marked as a duplicate of this bug. *** (In reply to comment #5) > *** Bug 80916 has been marked as a duplicate of this bug. *** FWIW, I don't get an error going from SQL view to GUI as reported there, only when going from GUI (looking like my screenshot) to anything else. Lionel Elie Mamane committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=3dd12b4a5c476eac74b4de15f97e0177cde81e1f fdo#82427 disambiguate "NOT LIKE 'foo'" The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback. Lionel Elie Mamane committed a patch related to this issue. It has been pushed to "libreoffice-4-3": http://cgit.freedesktop.org/libreoffice/core/commit/?id=9a0d9d4f404eb49bbe23aa9b70a9ebd7bafdb7c0&h=libreoffice-4-3 fdo#82427 disambiguate "NOT LIKE 'foo'" It will be available in LibreOffice 4.3.2. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback. Lionel Elie Mamane committed a patch related to this issue. It has been pushed to "libreoffice-4-2": http://cgit.freedesktop.org/libreoffice/core/commit/?id=967039703c73a9efc225d0d4d9c1f3a168977a46&h=libreoffice-4-2 fdo#82427 disambiguate "NOT LIKE 'foo'" It will be available in LibreOffice 4.2.7. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback. Lionel Elie Mamane committed a patch related to this issue. It has been pushed to "libreoffice-4-3-1": http://cgit.freedesktop.org/libreoffice/core/commit/?id=c4e400e4ef8523b3bd587f5d7a6160af8d44df94&h=libreoffice-4-3-1 fdo#82427 disambiguate "NOT LIKE 'foo'" It will be available already in LibreOffice 4.3.1. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback. Migrating Whiteboard tags to Keywords: (bibisected) [NinjaEdit] |
Created attachment 104389 [details] Query editor screenshot before editing criterion I have a simple query on one (MySQL 5.1) table: SELECT `District`, `AppNumber`, `Added`, `Parish`, `Address`, `Postcode`, `Proposal`, `KeyWords`, `Validated` FROM `planningApps`.`applications` AS `applications` WHERE `KeyWords` LIKE '%L%' AND NOT `KeyWords` LIKE '%X%' It runs as expected from the query list and from SQL view (from which the above text is copied). Also works from SQL view with the more normal ..... AND `Keywords` NOT LIKE '%X%' After viewing in GUI Design View, I cannot run it, save it or view in SQL view without a SQL syntax error message "Error code: 1000 SQL syntax error". The problem is around the NOT LIKE - if I edit that criterion by deleting NOT and pasting it back, base surrounds the whole thing with quotes - 'NOT LIKE ''*X*''' and generates SQL ...AND `Keywords` = 'NOT LIKE ''*X*''' in SQL view. Works as expected in ooBase.