Bug 88581 - Set 'Enable regular expression' off by default
Summary: Set 'Enable regular expression' off by default
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium enhancement
Assignee: Eike Rathke
QA Contact:
URL:
Whiteboard: target:5.3.0
Keywords:
Depends on:
Blocks:
 
Reported: 2015-01-19 12:06 UTC by Kevin Suo
Modified: 2016-10-25 18:38 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:


Attachments
test ODS file (12.98 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-01-19 12:06 UTC, Kevin Suo
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Kevin Suo 2015-01-19 12:06:33 UTC
Created attachment 112459 [details]
test ODS file

VLOOKUP returns #N/A in the attached spreadsheet. The expected return value is "1".

Steps to reproduce:
1. Open the attached ODS file;
2. Observe that cell B2 has formula "=VLOOKUP(A2,E1:F1,2,0)". A2 contains string "26*12-12-8 TL R1".

Expected:
Because Cell E1 contains the string "26*12-12-8 TL R1" to be lookup, so cell B2 should return "1" (cell F1)

Current Behaviour:
Cell B2 returns "#N/A".

Win 7 X86
Version: 4.4.0.2
Build ID: a3603970151a6ae2596acd62b70112f4d376b990
Locale: zh_CN

VLOOKUP is one of the most common used functions in spreadsheet, so this is a critical issue.
Comment 1 Kevin Suo 2015-01-19 12:21:11 UTC
This issue seems the same as described in bug 49711. That bug was closed as NOTABUG per the following comment:
https://bugs.freedesktop.org/show_bug.cgi?id=49711#c2
Which says disable "Allow regular expressions in formulas" solves the proble.

However, in my opinion this is not the case, this is really a bug.
"Allow regular expressions in formulas" means regular expressions in *FORMULAS* are allowed.
But in the formula "=VLOOKUP(A2,E1:F1,2,0)" there are no regular expressions, only reference to cells. (The regular expressions are in cell references, not in *FORMULAS".

If the devs insists that "Allow regular expressions in formulas" = "Allow regular expressions in formulas, including regular expressions in values returned by cell references", then the option "Allow regular expressions in formulas" should be disabled by default in Calc. Enabling this option will cause confuse for users (Who knows what special chars are regular expressions? and how many people know that we can use regular expressions in formulas?)
Comment 2 Kevin Suo 2015-01-19 12:48:56 UTC
Well, let me count the reported regular expression issues which were closed as NOTABUG:

Bug 83791 - SUMIF() not work with a string with parenthesis as criteria
Bug 81395 - Function RICERCA(x;y;z) doesn't work
Bug 72739 - MATCH function returns wrong result
Bug 67633 - EDITING: SEARCH function doesn't find text in cells containing hyperlinks (Closed as FIXED, which is wrong status. hyperlinks contains "." which is a regular expression, this is the real cause of the bug)
Bug 57368 - FORMULA SUMIF with brackets
Bug 65043 - MATCH for text containing brackets returns #N/A
Bug 43919 - "[" breaks vlookup
Bug 42097 - Formula: Search criteria fail with parenthesis and bracket characters
Bug 36740 - Function ZÄHLENWENN does not recognize a text with a '+' character
...
(and there may be even more which didn't show in my bugzilla search results)
(and there are even more issues the ordinary users encounter, don't know why their formula returns wrong value, but don't know where to ask questions / reporting bugs)
Comment 3 Kevin Suo 2015-01-19 12:56:59 UTC
P.S.
I am a 10+ years speadsheet user, and a 8+ years OpenOffice/LibreOffice user, but still didn't know the "Allow regular expressions in formulas" option. What about the other ordinary users?
And what's the advantages of enabling this option by default?

If a user knows what regular expressions are, and want to allow regular expressions in there formulas, then they can enable this option mannully. We should not enable it by default for all users.
Comment 4 raal 2015-01-19 14:41:02 UTC
Do I understand correctly that you propose "Set 'Enable regular expression' off by default" ?
Comment 5 Kevin Suo 2015-01-20 01:07:55 UTC
(In reply to raal from comment #4)
Yes correct. I set this as an enhancement request.
Comment 6 raal 2015-01-20 09:46:20 UTC
(In reply to Kevin Suo from comment #5)
> (In reply to raal from comment #4)
> Yes correct. I set this as an enhancement request.

Changing summary.
Comment 7 Robinson Tryon (qubit) 2015-01-22 09:01:18 UTC
(In reply to Kevin Suo from comment #3)
> P.S.
> I am a 10+ years speadsheet user, and a 8+ years OpenOffice/LibreOffice
> user, but still didn't know the "Allow regular expressions in formulas"
> option. What about the other ordinary users?
> And what's the advantages of enabling this option by default?
> 
> If a user knows what regular expressions are, and want to allow regular
> expressions in there formulas, then they can enable this option mannully. We
> should not enable it by default for all users.

Sounds like a question for the UX Council of Elders (and young'uns).

Status -> NEW
Component -> ux-advise
Comment 8 V Stuart Foote 2015-10-01 17:09:56 UTC
Not really a UI/UX issue, back to the Calc guru's

@Markus, Regina, Eike, *

Is there anything forcing us to retain "enable regular expressions in formulas" as the default in Calc?  Or would it, as Kevin suggests, be more reasonable not to--given the functions that regular expression parsing of Formulas apparently disrupts.
Comment 9 Eike Rathke 2015-10-02 13:54:27 UTC
Might be possible to disable it per default, the actual option setting is stored with the document so existing documents using it should not be harmed. Needs to be verified.


(In reply to Kevin Suo from comment #1)
> However, in my opinion this is not the case, this is really a bug.
> "Allow regular expressions in formulas" means regular expressions in
> *FORMULAS* are allowed.
> But in the formula "=VLOOKUP(A2,E1:F1,2,0)" there are no regular
> expressions, only reference to cells. (The regular expressions are in cell
> references, not in *FORMULAS".

Not true. Affected is the lookup-value (string to be searched), it doesn't matter if the argument appears literally in the expression or is fetched through a reference or is the result of another expression.


(In reply to Kevin Suo from comment #2)
> Bug 67633 - EDITING: SEARCH function doesn't find text in cells containing
> hyperlinks (Closed as FIXED, which is wrong status. hyperlinks contains "."
> which is a regular expression, this is the real cause of the bug)

Not true. Matched is the resulting cell string and that example works in more recent releases.
Comment 10 Eike Rathke 2016-05-30 21:11:33 UTC
Note that upcoming 5.2 also will have wildcards implemented same as in Excel. See https://wiki.documentfoundation.org/ReleaseNotes/5.2#Support_wildcards_to_be_compatible_with_XLS.2FXLSX_and_with_ODF_1.2
Loading an Excel document of course uses wildcards instead of regular expressions.

So, should we disable regular expressions and enable wildcards instead in new installations' user profiles for all new documents? My take is yes.
Comment 11 Commit Notification 2016-07-20 17:20:43 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=4a2d67df5c3c44766adb0d395dffae61387c22a5

Resolves: tdf#88581 default for new installations is Wildcards on

It will be available in 5.3.0.

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.
Comment 12 Commit Notification 2016-07-20 21:15:02 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=d77081946f59127aab4b017f455b0c5b489dcb84

RegularExpressions and Wildcards are mutually exclusive, tdf#88581 follow-up

It will be available in 5.3.0.

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.
Comment 13 Commit Notification 2016-07-20 21:54:52 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=c48c5bad9d371da0e0c47c7ef5611a4afe4fd82e

switch also internal default to Wildcards, tdf#88581 related

It will be available in 5.3.0.

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.