Issue 123908

Summary: Database formulas show wrong results with wildcard characters if there are multiple conditions
Product: Calc Reporter: fanyuzhen
Component: codeAssignee: Clarence GUO <>
Status: CLOSED NOT_AN_OOO_ISSUE QA Contact: liuping <doneyourself>
Severity: Major    
Priority: P3 CC: elish, issues, rainerbielefeld_ooo_qa
Version: 3.4.0Keywords: needmoreinfo
Target Milestone: ---   
Hardware: All   
OS: All   
See Also:
Issue Type: DEFECT Latest Confirmation in: ---
Developer Difficulty: ---
Description Flags
sample file1
sample file2
AOO&Ms2010.jpg none

Description fanyuzhen 2013-12-23 08:44:32 UTC
This defect is migrated from Symphony defect as below:

Sym2_10737	Database formulas do not work well with wildcard characters when there are multiple conditions
Comment 1 Rainer Bielefeld 2013-12-23 09:25:47 UTC 
Can you please contribute a bug description telling normal users like me what the problem is?
Comment 2 liuping 2013-12-26 06:49:30 UTC
Confirmed also in
AOO410m1(Build:9750)  -  Rev. 1548193

1. Open the sample file with AOO, and then check results of each formula with conditions 2 or 3.
2. Compare the result with the file that open with Excel.

Database formulas do not work well with wildcard characters when there are multiple conditions, and the Boolean logic of them are "OR".
Comment 3 liuping 2013-12-26 06:50:13 UTC
Created attachment 82167 [details]
sample file1
Comment 4 liuping 2013-12-26 06:50:49 UTC
Created attachment 82168 [details]
sample file2
Comment 5 Rainer Bielefeld 2013-12-26 09:28:59 UTC
Seems more or less reproducible with "AOO 4.0.1   – German UI / German locale  [Rev. 1524958 2013-09-20 11:40:29]" on  German WIN7 Home Premium (64bit)", “historic”  4.0  User Profile used for all  predecessor versions, but until now it's unknown for me what exactly the problem is

(a) I compared view of both attachments 2013-12-26 07:50 CET, liuping with 
    MS Excel viewer and AOO. Both show the same results so I only will 
    consider "sample file2" for further tests.
(b) Excel Viewer shows identical values in columns J,K
(c) With menu 'Tools -> Options -> Calc -> Calculate - Regular Expressions in 
    Formulas disabled' results in column J are completely nonsense, only "0"   
     or "#VALUE!"
(d) with "Regular Expressions Enabled" in most relevant cells of column J values
    appears, but except J12, J13 all values are different
(e) Gnumeric Tabellenkalkulation 1.10.16 shows the same values in columns 
    J, K.
(f) With OOo 3.3.0 Checking "'Tools -> Options -> Calc -> Calculate - Search
    criteria must apply to whole cells" increases number of correct results.
    But gambling with that setting has unpredictable result with different 
    OOo / AOO versions.
(g) we definitively have not a new issue here, that never worked correctly 
    with OOo /  AOO
(h) Seems to work with Lotus Symphony Release 3.0.1

(z) there are 2 attachments with more or less the same contents. 
    What one is "THE" sample file? Why do you attach 2 ones?
(y) It would really ease to reproduce your results  if you would read and heed 
    the bug writing guidelines, for example:
 * "Be precise, avoid general rare statements ... "does not work""
 * "Be clear, don't simply describe what your problem is, explain it so that 
   other users can reproduce the bug "
(x) Please contribute information how to find out that "multiple conditions"
    is one reason for the different values
(w) Is it sure that not additionally other bugs are reason for 
    different results between Excel and AOO? It would help to have
    1 single result in the sample document with step by step instruction
    proving that wildcards and multiple conditions cause a bug.
(v) We have to separate "open-import" bugs from calculation bugs. 
    Can you reproduce at least a single wrong result with an original.ods
    created with AOO?
(u) Is it known that AOO supports that multi condition wildcard use?
(t) any Idea why J12, J13 show the same results as Excel?
Comment 6 liuping 2013-12-27 02:48:41 UTC
sample file1 is *.xlsx ; sample file2 is *.xls

I check menu 'Tools -> Options -> Calc -> Calculate - Enabled Regular Expressions in formulas' in column J ,results in column J are still "#VALUE!" 

column J in symphony3.0.1 FP2 same with MS2010

compared result please refer symphony&MS2010.jpg and AOO&MS2010.jpg
Comment 7 liuping 2013-12-27 02:49:50 UTC
Created attachment 82176 [details]
Comment 8 liuping 2013-12-27 02:51:41 UTC
Created attachment 82177 [details]
Comment 9 Rainer Bielefeld 2014-03-20 17:25:28 UTC
Hm, is this really related to "multiple conditions" or simply naive import of MS Wildcards what can not work with AOO, see "Issue 17993 - Add wildcards to the standard autofilter "?

Or "Issue 32344 - Simple wildcards * and ? as opposed to regular expressions"? Currently I can't tell.

See also for now.
Comment 10 Rainer Bielefeld 2014-03-20 17:30:35 UTC
Or also related to "Issue 123995 - Wrong results with wildcard characters if there are multiple conditions when importing *.xls "?

This Wildcard area needs some cleanup.
Comment 11 Edwin Sharp 2014-04-03 10:28:08 UTC
Wildcards not supported.