Bug 115081 - Data validity restriction can be violated by copying value into a cell
Summary: Data validity restriction can be violated by copying value into a cell
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.4.4.2 release
Hardware: All All
: high normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Paste Cell-Validity
  Show dependency treegraph
 
Reported: 2018-01-18 06:05 UTC by grofaty
Modified: 2019-05-16 03:13 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
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
Dear grofaty,

To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.

There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.

If you have time, please do the following:

Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the information from Help - About LibreOffice.
 
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice.

Please DO NOT

Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not 
appropriate in this case)


If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from http://downloadarchive.documentfoundation.org/libreoffice/old/

2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to 'inherited from OOo';
4b. If the bug was not present in 3.3 - add 'regression' to keyword


Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa

Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team

MassPing-UntouchedBug