Bug 88581 - Set 'Enable regular expression' off by default
Summary: Set 'Enable regular expression' off by default
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: ux-advise (show other bugs)
Version: 3.3.2 release
Hardware: All All
: medium enhancement
Assignee: Not Assigned
QA Contact:
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2015-01-19 12:06 UTC by Kevin Suo
Modified: 2015-01-22 09:01 UTC (History)
3 users (show)

See Also:
i915 platform:
i915 features:


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

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


Use of freedesktop.org services, including Bugzilla, is subject to our Code of Conduct. How we collect and use information is described in our Privacy Policy.