Bug 87936 - Calc change values when pasting values from clipboard
Summary: Calc change values when pasting values from clipboard
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.3.5.2 release
Hardware: Other All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: needsDocumentationReview
Keywords:
Depends on:
Blocks:
 
Reported: 2015-01-01 15:57 UTC by Ljiljan
Modified: 2015-01-18 22:14 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 Ljiljan 2015-01-01 15:57:50 UTC
I was following these instructions (http://www.comfsm.fm/~dleeling/statistics/text5.html) and tried to calculated MODE. 

Steps to reproduce:
 
1. Copy these values to clipboard (CTRL +C) 18,19,19,20,20,21,21,21,21,22,22,22,22,23,23,24,24,25,25,26
2. Open Calc 
3. Click on any cell
4. Type "=MODE(" and paste values + ")"
5. Press Enter

Current behaviour:

Extra "0" are added.

Expected behaviour:

No "0" is added.
Comment 1 Luuk 2015-01-01 16:24:51 UTC
Indeed, i see:
=MODE(18,190,190,20,20,210,210,210,210,220,220,220,220,230,230,240,240,25,25,26 )
in the cell after doing what you described.
Comment 2 Ljiljan 2015-01-01 16:58:45 UTC
It acctually happens with any other funcions: SUM, AVERAGE...
Comment 3 Ljiljan 2015-01-01 17:01:18 UTC
But if you paste these values:
18;19;19;20;20;21;21;21;21;22;22;22;22;23;23;24;24;25;25;26

it will not happen? 

Since "," is decimal point in my local language settings, could it be that Calc is trying to "format" the number?
Comment 4 GerardF 2015-01-01 17:10:26 UTC
(In reply to Ljiljan from comment #3)
 
> Since "," is decimal point in my local language settings, could it be that
> Calc is trying to "format" the number?

Exactly. This is indeed not a bug.
Calc try to convert this strings to numbers by adding 0 before comma.
Using correct function args separator (;) is the correct way.
Comment 5 Luuk 2015-01-01 17:13:18 UTC
Subject of this error was changed from:
Calc change values when doing replace
to:
Calc change values when pasting values from clipboard

But a better title would be:
Calc change values when it finds a comma

Because manually entering the comma-separated numbers also triggers the error.
Comment 6 Luuk 2015-01-01 17:14:32 UTC
"Calc try to convert", but not succeeding!!

That's why Calc SHOULD NOT CHANGE A THING!!  (Grrr....  ;-)
Comment 7 Ljiljan 2015-01-01 17:29:02 UTC
Well, this is stil a bug? :) final output of this conversion does not make sense... 

If only it does following:
18,19;0,19;0,2;0,2;0,21;0,21;0,21;0,210

"8,190,190,20,20,210,210,210" is not a number either.
Comment 8 GerardF 2015-01-01 17:35:41 UTC
It is exactly the same as when you wrote ,35 in a cell:
Calc add a leading 0 and display 0,35.
Here you have commas not preceded by 0 and Calc adds them.
Comment 9 Luuk 2015-01-01 17:36:55 UTC
When someone copies/pastes text from anywhere to another place,
This input should be checked.

If the receiving application cannot do a correct thing with it, 
it should not change ANYTHING to the copied text.

It is up to the user to make sure that the copied/pasted text is correct.
(and not to an assumption made by some developer!)
Comment 10 Luuk 2015-01-01 17:40:44 UTC
(In reply to GerardF from comment #8)
> It is exactly the same as when you wrote ,35 in a cell:
> Calc add a leading 0 and display 0,35.
> Here you have commas not preceded by 0 and Calc adds them.

This is correct behaviour, but when the text you entered contains 2 (or more) commas it should not change the text, and throw an error

When entering '=1,2,3,4,5,6,7,8,9' in a cell without single quotes)
Calc also 'decides' to change it to : '=1,20,30,40,50,60,7,8,9' which is WRONG
Comment 11 Robinson Tryon (qubit) 2015-01-17 21:55:18 UTC
(In reply to Luuk from comment #9)
> When someone copies/pastes text from anywhere to another place,
> This input should be checked.
> 
> If the receiving application cannot do a correct thing with it, 
> it should not change ANYTHING to the copied text.
> 
> It is up to the user to make sure that the copied/pasted text is correct.
> (and not to an assumption made by some developer!)

Yeah, there's definitely a judgment call going on here; I'm reticent to impulsively pick one interpretation :-)

(In reply to Luuk from comment #10)
> (In reply to GerardF from comment #8)
> > It is exactly the same as when you wrote ,35 in a cell:
> > Calc add a leading 0 and display 0,35.
> > Here you have commas not preceded by 0 and Calc adds them.

that makes a lot of sense

> This is correct behaviour, but when the text you entered contains 2 (or
> more) commas it should not change the text, and throw an error

That sounds like a plausible strategy. I think I'd also be okay with it adding explicit separators such as "18,19; 0,19; 0,5" etc.

> When entering '=1,2,3,4,5,6,7,8,9' in a cell without single quotes)
> Calc also 'decides' to change it to : '=1,20,30,40,50,60,7,8,9' which is
> WRONG

In the absence of any set parsing rules, I don't see it as being wrong. I agree that
1) There should be better documentation about HOW we parse cell input
2) We should be more explicit with the output (i.e. add separators), and
3) In some cases we should give a warning/error
Comment 12 Robinson Tryon (qubit) 2015-01-17 21:56:55 UTC
Markus: thoughts on how we parse incoming values that include several delimiters?
Comment 13 Robinson Tryon (qubit) 2015-01-17 22:21:13 UTC
(In reply to Robinson Tryon (qubit) from comment #11)
> I think I'd also be okay with it
> adding explicit separators such as "18,19; 0,19; 0,5" etc.

I just chatted w/the Calc devs. One problem with adding explicit separators/delimiters here is that they aren't there in the original input. If there is no ocSep token in the token sequence, adding one in might break some other use-cases.

In this particular example, the input goes through the formula compiler which means that it is interpreted as normal formula. Making changes at that level is very tricky, and has a high possibility of inadvertently introducing a number of regressions.

Sticking this one in NEW. It would be great to see the Documentation Team provide some more information on this topic in the Calc manual.
Comment 14 Luuk 2015-01-18 18:14:46 UTC
(In reply to Robinson Tryon (qubit) from comment #13)

> I just chatted w/the Calc devs. One problem with adding explicit
> separators/delimiters here is that they aren't there in the original input.
> If there is no ocSep token in the token sequence, adding one in might break
> some other use-cases.

Nothing should be added because there are multiple 'ocSep' characters in the pasted string.

> 
> In this particular example, the input goes through the formula compiler
> which means that it is interpreted as normal formula. Making changes at that
> level is very tricky, and has a high possibility of inadvertently
> introducing a number of regressions.
> 

The input should not go through the formula compiler because there are multiple 'ocSep' characters in the pasted string.

I think it should be pasted as 'text'. (no changes to the pasted text!)
Comment 15 Markus Mohrhard 2015-01-18 21:43:17 UTC
(In reply to Luuk from comment #14)
> (In reply to Robinson Tryon (qubit) from comment #13)
> 
> > I just chatted w/the Calc devs. One problem with adding explicit
> > separators/delimiters here is that they aren't there in the original input.
> > If there is no ocSep token in the token sequence, adding one in might break
> > some other use-cases.
> 
> Nothing should be added because there are multiple 'ocSep' characters in the
> pasted string.

There is no ocSep. These are decimal separators in your locale. Change the ',' to ';' and it works as they are the separators in your locale.

> 
> > 
> > In this particular example, the input goes through the formula compiler
> > which means that it is interpreted as normal formula. Making changes at that
> > level is very tricky, and has a high possibility of inadvertently
> > introducing a number of regressions.
> > 
> 
> The input should not go through the formula compiler because there are
> multiple 'ocSep' characters in the pasted string.
> 
> I think it should be pasted as 'text'. (no changes to the pasted text!)

Just stop with that "paste" stuff. This has nothing to do with pasting. You get the same result if you type that character sequence. As soon as we start to parse that formula this goes wrong.

After thinking about it even more I think that it is actual the correct behavior so I will close that bug. Don't use the decimal separator in a formula string unless you want the stuff being parsed as decimal number. There is nothing wrong with how we parse the formula.
Comment 16 Robinson Tryon (qubit) 2015-01-18 22:14:58 UTC
(In reply to Markus Mohrhard from comment #15)
> After thinking about it even more I think that it is actual the correct
> behavior so I will close that bug. Don't use the decimal separator in a
> formula string unless you want the stuff being parsed as decimal number.
> There is nothing wrong with how we parse the formula.

The Docs Team should verify that we're clear about this expected behavior in our documentation, so
Whiteboard -> needsDocumentationReview