Bug 115081

Summary: Data validity restriction can be violated by copying value into a cell
Product: LibreOffice Reporter: grofaty <grofaty>
Component: CalcAssignee: Not Assigned <libreoffice-bugs>
Status: NEW ---    
Severity: normal CC: erack, heiko.tietze, jag, nkemp1165, thomas.lendo
Priority: high    
Version: 5.4.4.2 release   
Hardware: All   
OS: All   
See Also: https://bugs.documentfoundation.org/show_bug.cgi?id=96709
Whiteboard:
Crash report or crash signature: Regression By:
Bug Depends on:    
Bug Blocks: 102593, 108988    

Description grofaty 2018-01-18 06:05:46 UTC 
Description:
Data | Validity functionality is intended to allow only defined values to be entered into the field. If incorrect value is entered the error message is displayed "Invalid value".

But data validity is not respected if data is copied from some other cell and pasted over the existing data validity definition.


Steps to Reproduce:
1. Click in A1 cell.
2. Data | Validity.
3. On Criteria tab from Allow field select List.
4. In Entries field type in: AAA and click on OK button.
5. Right of A1 cell arrow appears. You can select AAA and you can press <Delete> key to delete value.
6. Still in A1 field try typing some value like BBB and press OK button. Error is returned "Invalid value". This is expected.
7. Click on some other cell and type in BBB. Copy the cell and move to A1 cell and press CTRL+V to paste the value.

Actual Results:  
Value is copied successfully over data validity definition.

Expected Results:
Data-Validity definition should be respected and on paste "Invalid value" error should be returned.


Reproducible: Always


User Profile Reset: Yes



Additional Info:
Help|About:

Version: 5.4.4.2 (x64)
Build ID: 2524958677847fb3bb44820e40380acbe820f960
CPU threads: 3; OS: Windows 6.1; UI render: default; 
Locale: sl-SI (sl_SI); Calc: group


User-Agent: Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:52.0) Gecko/20100101 Firefox/52.0
Comment 1 Thomas Lendo 2018-01-18 20:21:05 UTC 
Thanks for your report, grofaty.

From my point of view, this behavior is intended as you copy a cell including all format and other cell attributes.

You can avoid to destroy the cell validity with Edit > Paste Unformatted Text instead of Edit > Paste.
Comment 2 grofaty 2018-01-19 08:45:05 UTC 
@Tomas, thanks for your reply. What I am doing is preparing document for end-users to fill in some data. I know they are capable of using Copy/Paste and so destroying Data|Validity restriction. If this is working as intended, what is the option to prevent copying data into the Data|Validity protected cell?
Comment 3 Thomas Lendo 2018-01-19 12:10:01 UTC 
Today I don't see any possibility to avoid overwriting a cell but I'm no Calc expert. Protecting a sheet and its cells also protects to do anything with the cells, including inserting valid content.

A quick web search doesn't help. Also I couldn't reproduce with MSO Excel 2013 the assertion [1] that Excel can handle what you want.

What I can imagine in the future is an exception in the "Protect Sheet" dialog [2] for Validity and similar functions so that the user can can insert content with the Validity selection list in protected cells but nothing else. I don't know if this is possible in LibreOffice or in the ODF specification and if this is a wanted feature enhancement.

@Heiko, Eike - may I ask for your opinion?

[1] https://forum.openoffice.org/en/forum/viewtopic.php?p=47780#p47780
[2] https://wiki.documentfoundation.org/File:NewSheetProtectionOptions54.png
Comment 4 Heiko Tietze 2018-01-19 14:02:28 UTC 
I don't see a workaround either.

Use case here is to protect cells while in other situations you may want to copy the validation. My guess of how often both are relevant is 70/30. So I would not copy/paste the validity check by default but provide a checkbox 'Validity check' in paste special. Could also live with the opposite to enable it by default and disable in paste special, which keeps the legacy behavior.
Comment 5 Wolfgang Jäger 2018-05-15 13:47:31 UTC 
1. The described behaviour is as the 'Validity' always behaved. 
2. Since there is no explicit specification (afaik) the behaviour as it always was cannot be proved a bug.
3. To get what the reporter wants would not require a bugfix but an ENHANCEMENT.
4. In fact more than one enhancemnt would be neded. At least:
4.1. Add an option concerning cell protection to an effect as described by 'Inhibit Full Paste'.
4.2. Add an option under 'Validity' to an effect like 'Check Pasted Content for Validity'. 
5. All this would not ensure actual data consistency. As soon as any contents of a validity 'List' or a validity 'Cell Range' are changed, newly invalid contents in cells may occur. 

The 'Validity' tool as is is a makshift at best. In specific it may mislead designers of spreadsheets to believe they can make things fool-proof based on it. If this is needed urgently the task should be moved to a database application with its security features and rights management. 

Either NOTABUG or Importance = "enhancement".

(Discussion about workarounds may better be placed in ask.libreoffice.org or on any forum on open OfficeSoftware.)
Comment 6 QA Administrators 2019-05-16 03:13:15 UTC Comment hidden (obsolete, spam)
Comment 7 Justin L 2020-12-15 10:27:27 UTC 
*** Bug 134311 has been marked as a duplicate of this bug. ***
Comment 8 Justin L 2020-12-15 10:33:49 UTC 
Similar to bug 96709, but this focuses on applying validity to pasted content.
Comment 9 QA Administrators 2022-12-16 03:19:19 UTC Comment hidden (obsolete)
Comment 10 grofaty 2022-12-16 06:20:14 UTC 
Tested in LibreOffice Calc 7.4.3.2 and problem remains.