Apache OpenOffice (AOO) Bugzilla – Issue 64370
Obsolete dependancy of regex-matching
Last modified: 2013-08-07 15:12:27 UTC
Analysing how regexes work with formulae, filters and search/replace I found some errors related to formulas while stumbling into a pitfall with option "Match Whole Cells". See http://www.openoffice.org/issues/show_bug.cgi?id=63849 I think regex-matching in Calc should not depend on that calc-specific option since you just have to use "^" and "$" in order to get the effect of the option for each regex individually. If you consider this as an enhancement you may also consider to disable the corresponding option in the search/replace-dialog when "Regex" is used. Notice: I already filed some related issue 63755 to the documentation project, because the documentation on regexes is very misleading in some respect of the current implementaion. Among other things, I proposed to point out that option "Match Whole Cells" has an effect on regex-matching.
Posted an evaluation-spreadsheet at issue 64368 demonstrating all my regex-issues.
Please let me add another question. Is this "MatchWhole"-property of a sheet-doc nessecary at all? Any spreadsheet-formula, supporting regexes can match whole cells even when the option is turned off [=MATCH("^"&stringVal&"$";ref;0) vs. =MATCH(stringVal;ref;0)]. In numeric context it's pointless anyway. Same with a sheet-filter. StarBasic-speaking: If NOT FilterDescriptor.IsNumeric then isRegex = True MatchWhole = (left(stringValue,1) = "^") AND (right(stringValue,1) ="$") This would result in the same behaviour as a formula shows when MatchWhole = Off (I saved that setting with my default-template). So I believe "UseRegularExpression" as property of a filterDescriptor is obsolete as well. Any regex-unaware user should find a short list of rules in the online-help *before* he/she gets confronted with the famous "List of Regular Expressions": 1. Use .* instead of * 2. Use . instead of ? 3. Use ^ for start, $ for end and both for matching whole string. 4. Escape all kinds of brackets and literal ^$.*?\& with \ (in case of doubt escape anything but <, > and alphanumerics)
Hi Eike, please have a look on this one and give us a comment. Re-assign or close as needed. Frank
@villeroy: > If you consider this as an enhancement you may also consider to disable the > corresponding option in the search/replace-dialog when "Regex" is used. Note that in the Find&Replace dialog the "Entire cells" checkbox _is_ already disabled when "Regular expressions" is enabled. So that point is pointless ;-) Regarding the general behavior of Calc's "whole cell" option in formula calculation, it should not silently act different for different search expression types. Doing so would just confuse the user, IMHO. Additionally we'll have to take another aspect into account: the option will also have an effect as soon as we implement the simple Excel wildcards search, see issue 32344. This again, like the "no wildcard / regex at all" mode, needs the "whole cell" enabled to be compatible with Excel. Last but not least, changing the option to "automatically disabled when regex is on" would break existing documents in case they rely on a search not being successful if it doesn't match a cell. > Is this "MatchWhole"-property of a sheet-doc nessecary at all? Probably not, it even hinders compatibility if disabled. Reassigning to Requirements / User Experience team for evaluation whether removing the option altogether on the risk of breaking legacy documents would be saleable. I doubt so. If at all, it should only be done in a major release.