SQLITE Return Exponential value in the latest version(3.28.0) rather than integer number .

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

SQLITE Return Exponential value in the latest version(3.28.0) rather than integer number .

Bhavesh Patel
Hello SQLite Community Members,

I am facing an issue with SQLite latest version(3.28.0).

SQLITE  Return Exponential  value in the latest version(3.28.0) rather than integer number .

The old version (3.6.2) return the same integer  value which is inserted .

Below is the screenshots of my sample application which is shown the difference beween the sqlite 3.6.2 and sqlite 3.28.0.

Sqlite version: 3.28.0


Sqlite version: 3.6.2

The Return Value is different  in the both SQLite versions.

- I have downloaded latest SQLite version (3.28.0) from the below link:

                https://www.sqlite.org/download.html

- I have downloaded the SQLite version (3.6.2) from the below link:

https://www.sqlite.org/src/info/88c51b9f1579707a

Below is the step how I build the Latest  SQlite(3.28.0) Lib:-

-I downloaded the Source code “sqlite-amalgamation-3280000.zip” from the  link: https://www.sqlite.org/download.html

- After that, I build the Visual studio 2012 project name “SQLitelib” from the downloaded source.(the downloaded source placed in the Project directory.)

- Build the Libarary.

Below is the step how I build the Old  SQlite (3.6.2) Lib:-

-I downloaded the Zip file “SQLite-88c51b9f.zip” from the  link: https://www.sqlite.org/src/info/88c51b9f1579707a

- After that,I  make “sqlite3.c” from the downloaded source.

-I build the Visual studio 2012 project name “sqlite362” from the source.(the downloaded Zip file and source placed in the Project directory.)

- Build the Libarary.

Below is the step to Sqlite Test Application:-

-I create sample application in visual studio 2012 app name is “SQLiteCheck”.

-I placed the Both SQLite Lib (Sqlite 3.28.0 abd SQLite 3.6.2) placed in (ProjectDir)/ Externals folder.

- when  the Application Executes.

- Application creates database named  “Mydb.db”.

- it will create the table with one column by below SQL Query:

“CREATE TABLE customer(Rate NUMERIC(4) COLLATE BINARY);”

-The it will insert value '.0000439857' by below Query:

“INSERT into customer (Rate) VALUES ('.0000439857')”

-Then it will get the value by below select Query:

“Select Rate from customer”

-Now, Check for “Return value:” it is different for both SQLite versions.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] SQLITE Return Exponential value in the latest version(3.28.0) rather than integer number .

Hick Gunter
You are doing strange things. And screenshots are not supported on this mailing list.

1) There is no numeric data type in SQLite. Your declaration numeric(4) gives the column numeric affinity. SQLite will attempt to convert the value you give into an integer or a real, if such a conversion is possible and lossless.
2) You are inserting a string into a numeric column, so SQlite applies numeric affinity and stores the value as a real number.
3) SQLite shell correctly formats this real value as 4.39857e-05

Which integer number were you expecting, and why?

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Bhavesh Patel
Gesendet: Donnerstag, 06. Juni 2019 13:49
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] SQLITE Return Exponential value in the latest version(3.28.0) rather than integer number .

Hello SQLite Community Members,

I am facing an issue with SQLite latest version(3.28.0).

SQLITE  Return Exponential  value in the latest version(3.28.0) rather than integer number .

The old version (3.6.2) return the same integer  value which is inserted .

Below is the screenshots of my sample application which is shown the difference beween the sqlite 3.6.2 and sqlite 3.28.0.

Sqlite version: 3.28.0


Sqlite version: 3.6.2

The Return Value is different  in the both SQLite versions.

- I have downloaded latest SQLite version (3.28.0) from the below link:

                https://www.sqlite.org/download.html

- I have downloaded the SQLite version (3.6.2) from the below link:

https://www.sqlite.org/src/info/88c51b9f1579707a

Below is the step how I build the Latest  SQlite(3.28.0) Lib:-

-I downloaded the Source code “sqlite-amalgamation-3280000.zip” from the  link: https://www.sqlite.org/download.html

- After that, I build the Visual studio 2012 project name “SQLitelib” from the downloaded source.(the downloaded source placed in the Project directory.)

- Build the Libarary.

Below is the step how I build the Old  SQlite (3.6.2) Lib:-

-I downloaded the Zip file “SQLite-88c51b9f.zip” from the  link: https://www.sqlite.org/src/info/88c51b9f1579707a

- After that,I  make “sqlite3.c” from the downloaded source.

-I build the Visual studio 2012 project name “sqlite362” from the source.(the downloaded Zip file and source placed in the Project directory.)

- Build the Libarary.

Below is the step to Sqlite Test Application:-

-I create sample application in visual studio 2012 app name is “SQLiteCheck”.

-I placed the Both SQLite Lib (Sqlite 3.28.0 abd SQLite 3.6.2) placed in (ProjectDir)/ Externals folder.

- when  the Application Executes.

- Application creates database named  “Mydb.db”.

- it will create the table with one column by below SQL Query:

“CREATE TABLE customer(Rate NUMERIC(4) COLLATE BINARY);”

-The it will insert value '.0000439857' by below Query:

“INSERT into customer (Rate) VALUES ('.0000439857')”

-Then it will get the value by below select Query:

“Select Rate from customer”

-Now, Check for “Return value:” it is different for both SQLite versions.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] SQLITE Return Exponential value in the latest version(3.28.0) rather than integer number .

Jose Isaias Cabrera-4

I don't think he means integer, but more "the original value entered."


________________________________
From: sqlite-users <[hidden email]> on behalf of Hick Gunter <[hidden email]>
Sent: Thursday, June 6, 2019 08:43 AM
To: 'SQLite mailing list'
Subject: Re: [sqlite] [EXTERNAL] SQLITE Return Exponential value in the latest version(3.28.0) rather than integer number .

You are doing strange things. And screenshots are not supported on this mailing list.

1) There is no numeric data type in SQLite. Your declaration numeric(4) gives the column numeric affinity. SQLite will attempt to convert the value you give into an integer or a real, if such a conversion is possible and lossless.
2) You are inserting a string into a numeric column, so SQlite applies numeric affinity and stores the value as a real number.
3) SQLite shell correctly formats this real value as 4.39857e-05

Which integer number were you expecting, and why?

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Bhavesh Patel
Gesendet: Donnerstag, 06. Juni 2019 13:49
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] SQLITE Return Exponential value in the latest version(3.28.0) rather than integer number .

Hello SQLite Community Members,

I am facing an issue with SQLite latest version(3.28.0).

SQLITE  Return Exponential  value in the latest version(3.28.0) rather than integer number .

The old version (3.6.2) return the same integer  value which is inserted .

Below is the screenshots of my sample application which is shown the difference beween the sqlite 3.6.2 and sqlite 3.28.0.

Sqlite version: 3.28.0


Sqlite version: 3.6.2

The Return Value is different  in the both SQLite versions.

- I have downloaded latest SQLite version (3.28.0) from the below link:

                https://www.sqlite.org/download.html

- I have downloaded the SQLite version (3.6.2) from the below link:

https://www.sqlite.org/src/info/88c51b9f1579707a

Below is the step how I build the Latest  SQlite(3.28.0) Lib:-

-I downloaded the Source code “sqlite-amalgamation-3280000.zip” from the  link: https://www.sqlite.org/download.html

- After that, I build the Visual studio 2012 project name “SQLitelib” from the downloaded source.(the downloaded source placed in the Project directory.)

- Build the Libarary.

Below is the step how I build the Old  SQlite (3.6.2) Lib:-

-I downloaded the Zip file “SQLite-88c51b9f.zip” from the  link: https://www.sqlite.org/src/info/88c51b9f1579707a

- After that,I  make “sqlite3.c” from the downloaded source.

-I build the Visual studio 2012 project name “sqlite362” from the source.(the downloaded Zip file and source placed in the Project directory.)

- Build the Libarary.

Below is the step to Sqlite Test Application:-

-I create sample application in visual studio 2012 app name is “SQLiteCheck”.

-I placed the Both SQLite Lib (Sqlite 3.28.0 abd SQLite 3.6.2) placed in (ProjectDir)/ Externals folder.

- when  the Application Executes.

- Application creates database named  “Mydb.db”.

- it will create the table with one column by below SQL Query:

“CREATE TABLE customer(Rate NUMERIC(4) COLLATE BINARY);”

-The it will insert value '.0000439857' by below Query:

“INSERT into customer (Rate) VALUES ('.0000439857')”

-Then it will get the value by below select Query:

“Select Rate from customer”

-Now, Check for “Return value:” it is different for both SQLite versions.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE Return Exponential value in the latest version(3.28.0) rather than integer number .

R Smith-2
In reply to this post by Bhavesh Patel
Hi Bhavesh,

This is a common thing with 3rd party systems and engines (like SQlite) etc.

One day, you made a simple query which did not specify the output
specifically (or the format, or the order, or such), BUT you liked the
output as it was on that day, and then possibly made your program expect
such output, depending on the form.

Later one day, the Engine changed, still giving exactly mathematically
correct output, but perhaps changing the default format or some other
thing the documentation either explicitly warned may change, or
implicitly did not guarantee not to change, and your program now is no
longer working.

The best advice here would be to tell you how to ask for exactly what
you want, in a way that the system DOES promise to adhere to.

In your case, you could do:
SELECT printf('%0.12f', Rate) AS Rate FROM Customer;

Or whatever similar output format suits you best. You can then make your
program depend on it, because you asked for it to be specifically so,
and the Engine will always give it exactly so, no matter any other
future changes to default formats.

See also:
https://sqlite.org/lang_corefunc.html#printf



Hope that helps,
Ryan


On 2019/06/06 1:48 PM, Bhavesh Patel wrote:

> Hello SQLite Community Members,
>
> I am facing an issue with SQLite latest version(3.28.0).
>
> SQLITE  Return Exponential  value in the latest version(3.28.0) rather than integer number .
>
> The old version (3.6.2) return the same integer  value which is inserted .
>
> Below is the screenshots of my sample application which is shown the difference beween the sqlite 3.6.2 and sqlite 3.28.0.
>
> Sqlite version: 3.28.0
>
>
> Sqlite version: 3.6.2
>
> The Return Value is different  in the both SQLite versions.
>
> - I have downloaded latest SQLite version (3.28.0) from the below link:
>
>                  https://www.sqlite.org/download.html
>
> - I have downloaded the SQLite version (3.6.2) from the below link:
>
> https://www.sqlite.org/src/info/88c51b9f1579707a
>
> Below is the step how I build the Latest  SQlite(3.28.0) Lib:-
>
> -I downloaded the Source code “sqlite-amalgamation-3280000.zip” from the  link: https://www.sqlite.org/download.html
>
> - After that, I build the Visual studio 2012 project name “SQLitelib” from the downloaded source.(the downloaded source placed in the Project directory.)
>
> - Build the Libarary.
>
> Below is the step how I build the Old  SQlite (3.6.2) Lib:-
>
> -I downloaded the Zip file “SQLite-88c51b9f.zip” from the  link: https://www.sqlite.org/src/info/88c51b9f1579707a
>
> - After that,I  make “sqlite3.c” from the downloaded source.
>
> -I build the Visual studio 2012 project name “sqlite362” from the source.(the downloaded Zip file and source placed in the Project directory.)
>
> - Build the Libarary.
>
> Below is the step to Sqlite Test Application:-
>
> -I create sample application in visual studio 2012 app name is “SQLiteCheck”.
>
> -I placed the Both SQLite Lib (Sqlite 3.28.0 abd SQLite 3.6.2) placed in (ProjectDir)/ Externals folder.
>
> - when  the Application Executes.
>
> - Application creates database named  “Mydb.db”.
>
> - it will create the table with one column by below SQL Query:
>
> “CREATE TABLE customer(Rate NUMERIC(4) COLLATE BINARY);”
>
> -The it will insert value '.0000439857' by below Query:
>
> “INSERT into customer (Rate) VALUES ('.0000439857')”
>
> -Then it will get the value by below select Query:
>
> “Select Rate from customer”
>
> -Now, Check for “Return value:” it is different for both SQLite versions.
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users