kartis: Please provide some more information.

What do you mean when you say "attached by text in ODB" ?

Please give detailed step by step instructions, or else a sample ODB file and instructions so that we can test.

At present, I don't understand : 

1) if the error you are seeing occurs only when you bind/import the CSV data to your first ODB file, or 

2) whether it occurs only when you try and copy/paste table from your first ODB file to a second ODB that has no corresponding table ; or

3) whether it occurs when you try to append table from ODB1 to an existing table in ODB2

  
Setting to needinfo

Created attachment 125562 [details]
sample list of csv

Created attachment 125563 [details]
odb2.odb

Table new is copyed as is
new2 is changed field format #,##0

OK for step by step info. 
it's similar to here 
http://dominoc925.blogspot.jp/2013/05/import-csv-file-into-libreoffice-base.html

0) export twitter block list from twitter>settings>blocked etc
   this csv has one field, id number only.

1) make new odb, Connect to an existing database Text

2) select CSV path, and check *.csv, and Finish

3) save any name odb. (odb1)
   this odb has csv file name talbes, and field type is DECIMAL. no error

4) open another odb, "Create a new database". (odb2)

5) drag and drop table from "odb1". "Definition and data" and finish. yes to make PrimaryKey

6) table has DECIMAL filed type. but view is float type such as " 1.23E+16"
   so change field format  #,##0,  data is zero filled "12,345,678,912,345,700" from "12345678912345678".

 I want "12,345,678,912,345,678" as integer or string.

next 

5-2) drag and drop table from "odb1". "Definition and data" and field type as BIGINT(19).

6-2) table has BIGINT(19) filed type. but view is float type such as " 1.23E+16"
   so change field format  #,##0,  data is zero filled "12,345,678,912,345,700"

last

5-3) drag and drop table from "odb1". "Definition and data" and field type as CHAR(20).

6-3) table has CHAR(20) field type. view is zero filled "12345678912345700   "

@kartis56 : thanks for the very comprehensive explanation. What you describe appears to be a bug in the data import wizard. I will test and report back.

So I did some preliminary testing on the CSV file. When I open this file in my master build of LO Calc, the numbers get truncated and appended with scientific notation. I think the root of the problem lies in this conversion of the integer, as I suspect that the wizard uses Calc's datatype engine in the import wizard. Unfortunately, it would appear that the code responsible for appending the data into to the table doesn't know how to handle numbers represented with this scientific notation correctly.

I have a vague recollection of this issue already having been raised somewhere, but will have to check.

This is probably because it goes through a double (IEEE 64bit double precision floating point number) and 15 decimal digits of precision for the mantissa sounds about right.

During testing, I also noticed multiple incorrect codepage suggestions through different versions of Calc when opening the CSV file, ranging from UTF-8, to Korean, to Vietnamese, which doesn't help when testing. This behaviour appears to be bug 39124

Working with LO 5132 on OSX 10.11.5 :

1) Create new ODB file connecting to folder containing CSV provided by OP.
2) Open table "new" to display data.
3) Data displays as follows :

id
1,23E+16
2,23E+16
3,23E+16

For me the error is already here in the data displayed. The field definition of the table is 

DECIMAL
Length : -1
Decimal places : -1
Default value : N/A
Format example : 0

The value should not be DECIMAL but INT. Confirming

Confirming also that a copy-append of the table data and definition in a first ODB connected to the CSV file into a clean ODB file creates by default a DECIMAL datatype, that even when specified as BIGINT fails to display data correctly. Not really surprised by this as the first table from the first ODB already no longer contains an INT type.

Created attachment 125566 [details]
CALC import wizard

Note that when you create a link to a CSV file on setting up the connection, the user gets asked by the connection setup wizard to specify the value separator characters and decimal point separator character. There seems to be no way to avoid this part of the connection setup wizard.

I'm using locale ja-JP, so at CALC import wizard encoding to ASCII, lang en.
wizard view is correct, but imported data is incorrect such as 1.23E+16

maybe not only murtibyte ploblem...

Ok I make 2 file and check separator

1) " quote csv
2) original null quote csv

1) makes corerct view. data type is VARCHAR and copy makes correct.
2) has same ploblem. DECIMAL data type and incorrect view.

Created attachment 125567 [details]
DB wizard

maybe separator {null}  works as field type DECIMAL?

I see bug 39124   https://bugs.documentfoundation.org//show_bug.cgi?id=39124
and test this

1) " quote csv for test

2) import BASE by wizard as qwuoted ", type VARCHAR

3) view table and, copy all

4) make new CALC file, and paste

5) DATA is float type and zero filled. such as 12,345,678,912,345,700

maybe something wrong at CALC wizard

and test next

1) open " quote csv by CALC, text separator is ", field separtor is conma
   wizard view is correct. 12345678912345678

2) there is float... 1.23E+16

and next test

1) make new table at BASE
   ID as INTEGER primary key
   id as BIGINT(19) 

2) id format change to #,##0

3) open table and enter new record such as 
   1 and 1234567890123456789

4) id is zero filled        1,234,567,890,123,460,000

OK I'll give up using LO .

Have tested it a little bit.
1. Opened the database https://bugs.documentfoundation.org/attachment.cgi?id=125563
2. Executed there in Tools > SQL:
CREATE TEXT TABLE "Csv" ("ID" BIGINT PRIMARY KEY);
(creates a text-table with BIGINT)
SET TABLE "Csv" SOURCE "new.csv;ignore_first=true;encoding=UTF-8";
(creates a connection to the first attached *.csv-file)
SELECT * FROM "Csv";
(shows the correct numbers)
3. Opened the same table in the GUI - couldn't display the right number, shows 12345678912345700 instead of 12345678912345678

Displaying numbers with more than 16 digit seems to be impossible in the GUI. But this numbers were saved in the database.

4. Now I created a query:
SELECT CAST("ID" AS VARCHAR(18)) FROM "Csv"
Shows the right value.

The bug for Base and Calc has nothing to do with DECIMAL or BIGINT, but with displaying the right value inside the GUI.

Thank you Robert, I reproduced exactly what you indicated.

Lionel: any codepointer about table gui management?

(remark: "csv" table can't be seen on table list unless restarting LO but that's another story)

(In reply to Julien Nabet from comment #23)

> (remark: "csv" table can't be seen on table list unless restarting LO but
> that's another story)

You have to chose View > Refresh tables (or something like this - haven't looked in English GUI). This is the normal way to show the GUI tables have been added through direct SQL.

(In reply to Julien Nabet from comment #23)

> Lionel: any codepointer about table gui management?

My guess is that it is because a formatted field use a double (IEEE 64bit double precision floating point number) for the number that is contained in it and/or for the formatting.

See forms/source/component/FormattedField.[ch]xx

The formatting is done by a system that is shared with Calc; I don't remember out of the top of my head where it is, but look for "SvNumberFormatsSupplier" in svl

(In reply to Lionel Elie Mamane from comment #25)
> (In reply to Julien Nabet from comment #23)
> 
> > Lionel: any codepointer about table gui management?
> 
> My guess is that it is because a formatted field use a double (IEEE 64bit
> double precision floating point number) for the number that is contained in
> it and/or for the formatting.
> 
> See forms/source/component/FormattedField.[ch]xx
> 
> The formatting is done by a system that is shared with Calc; I don't
> remember out of the top of my head where it is, but look for
> "SvNumberFormatsSupplier" in svl

maybe here?

core\svl\source\numbers\numfmuno.hxx 

or this?

https://docs.libreoffice.org/svl/html/classSvNumberFormatterServiceObj.html

(In reply to kartis56 from comment #26)
> (In reply to Lionel Elie Mamane from comment #25)
>> (In reply to Julien Nabet from comment #23)

>> The formatting is done by a system that is shared with Calc; I don't
>> remember out of the top of my head where it is, but look for
>> "SvNumberFormatsSupplier" in svl

> maybe here?

> core\svl\source\numbers\numfmuno.hxx 
> https://docs.libreoffice.org/svl/html/classSvNumberFormatterServiceObj.html

Yes, that looks right.

\core\svl\source\numbers\zformat.cxx(57,40)

const double EXP_ABS_UPPER_BOUND = 1.0E15;  // use exponential notation above that absolute value.
                                            // Back in time was E16 that lead
                                            // to display rounding errors, see
                                            // also sal/rtl/math.cxx
                                            // doubleToString()

line 1750

void SvNumberformat::ImpGetOutputStandard(double& fNumber, OUString& rOutString)
{
    sal_uInt16 nStandardPrec = rScan.GetStandardPrec();

    if ( fabs(fNumber) > EXP_ABS_UPPER_BOUND )
    {
        nStandardPrec = ::std::min(nStandardPrec, static_cast<sal_uInt16>(14)); // limits to 14 decimals

but why use this ?

(In reply to kartis56 from comment #28)
> \core\svl\source\numbers\zformat.cxx(57,40)
> 
> const double EXP_ABS_UPPER_BOUND = 1.0E15;  // use exponential notation
> above that absolute value.
>                                             // Back in time was E16 that lead
>                                             // to display rounding errors,
> see
>                                             // also sal/rtl/math.cxx
>                                             // doubleToString()
> 
> line 1750
> 
> void SvNumberformat::ImpGetOutputStandard(double& fNumber, OUString&
> rOutString)
> {
>     sal_uInt16 nStandardPrec = rScan.GetStandardPrec();
> 
>     if ( fabs(fNumber) > EXP_ABS_UPPER_BOUND )
>     {
>         nStandardPrec = ::std::min(nStandardPrec,
> static_cast<sal_uInt16>(14)); // limits to 14 decimals
> 
> but why use this ?

Taking a look at sal/rtl/math.cxx, we got:
    381     switch (eFormat)
    382     {
    383         case rtl_math_StringFormat_Automatic:
    384         {   // E or F depending on exponent magnitude
    385             int nPrec;
    386             if (nExp <= -15 || nExp >= 15)  // was <-16, >16 in ancient versions, which leads to inaccuracies
    387             {
    388                 nPrec = 14;
    389                 eFormat = rtl_math_StringFormat_E;
    390             }
(see https://opengrok.libreoffice.org/xref/core/sal/rtl/math.cxx?r=be8da979#380)

Eike: it seems it might be a WONTFIX, any thoughts?

See also further above https://opengrok.libreoffice.org/xref/core/sal/rtl/math.cxx?r=be8da979#283 for how representable integer values are handled. If it reaches line 381 it means the value is no such.

The error is on user side to import the ID values as numeric instead of text.
Floating point IEEE 754 double precision values are limited in their accuracy, the largest representable unambiguous whole integer number is (2^53)-1 or 9007199254740991.