Prevent Automatic Rounding?

(1) By jhersey29 on 2022-03-14 23:25:11 [link] [source]

I have a query (Select ItemId, Quantity, Price from SaleItems Where SaleId = 1234) that has the following data

ItemId	Quantity	Price
12345	1	7
23456	2	4.10
45678	4	5.20
78941	1	6.90

When I query the table the database engine appears to be rounding every item after the first item based on the first item not having decimal places. For example the 4.10 is returned as 4. The 5.20 is returned as 5 and the 6.90 is returned as 7. If the price of the 1st item has decimal places then the database engine returns the decimal places for the remaining items instead of rounding them. Is there a way to prevent this erroneous casting done by the database engine?

I am hoping I don't have to put a round(Price, 2) as Price to work around the issue. The database engine should not be casting the type based on the 1st row especially when it is causing loss of data.

Using System.Data.Sqlite 1.0.115.5. This wasn't a problem until I updated to the new engine and .Net 4.7.2.

(2.1) By Harald Hanche-Olsen (hanche) on 2022-03-15 07:07:26 edited from 2.0 in reply to 1 [source]

(Deleted the original text, where I said that this is not the right place to ask. I may have been wrong about that. Even so, my other point remains, that you need to provide much more detailed information to get any help with this. As I don't use System.Data.SQLite myself, I cannot tell you exactly what, but the database schema might be a good place to start.)

(3) By Larry Brasfield (larrybr) on 2022-03-15 07:16:17 in reply to 1 [link] [source]

... the database engine appears to be rounding ...
If the price of the 1st item has decimal places then the database engine returns the decimal places for the remaining items instead of rounding them.
Is there a way to prevent this erroneous casting done by the database engine?
The database engine should not be casting the type based on the 1st row ...

You are describing behavior which neither the SQLite library or that library wrapped by the System.Data.SQLite [sic] are capable of. If result rows show some interdependence, from the library itself, that will be due to either a result of some complex query logic or something strange the application which loads the database is doing.

You have shown nothing which provides the slightest clue as to what is happening or why it is happening. I suggest you get a copy of the CLI shell from here, then use a simple query from that shell to see what you have managed to insert into your database. If that data has this unwelcome "rounding", you need to debug whatever input and load process has produced that data. If the unwelcome rounding does not appear in that data, you need to debug the (as yet unseen here) queries and result rendering code that has led you to post your vague inquiry here.

Short answer: The problem is in your code, not "erroneous casting done by the database engine". You are misunderstanding or misusing your tool(s), and curing that is where progress will lie, not in "preventing erroneous" anything.

Just to encourage an objective investigation using the CLI, here is some code which would appear to demonstrate a counter-example to the behavior you have attributed to "the database engine":CREATE TABLE NamedNums(spelledName TEXT, nearValue REAL); INSERT INTO NamedNums VALUES ('e', 2.718281828459), ('pi', 3.141592653589793238462643383279) ; SELECT * FROM NamedNums;, which produces this result:e|2.718281828459 pi|3.14159265358979.

If anybody here is going to understand the cause of your^a undesired "rounding", they will need to know how what you are doing is materially different from that simple load and query. Your post does not provide the slightest clue as to what that might be.

a. The rounding is yours, not "done by the database engine". That is a mental adjustment which will greatly aid your debugging effort.

(4) By Larry Brasfield (larrybr) on 2022-03-15 07:18:59 in reply to 2.1 [link] [source]

... the right place to ask.

This forum is a fine place to inquire about issues with the .Net SQLite wrapper published by the SQLite project.

(5) By Warren Young (wyoung) on 2022-03-15 10:10:44 in reply to 3 [link] [source]

You are describing behavior which neither the SQLite library or that library wrapped by the System.Data.SQLite [sic] are capable of

While likely true in this immediate case, I suspect once past this one, the OP will run into "How SQLite Stores Numbers".

(6) By Larry Brasfield (larrybr) on 2022-03-15 10:20:50 in reply to 5 [link] [source]

I imagine a similar fate. However, even though SQLite takes more liberties than might be naively assumed with representation of numbers, it does any conversions between representations at the API and on-disk (or in-storage) only when those are reversible. "Rounding" as the OP used the term is not reversible except when applied to integers.

(7.1) By jhersey29 on 2022-03-15 14:33:23 edited from 7.0 in reply to 3 [link] [source]

Thank you for helping!  Please see below as proof of the issue.

Using VS2019
Create New Console App using ConsoleApp1 as the name
.Net Framework 4.7.2
Add NuGet Package System.Data.SQLite 1.0.115.5.

Paste this code over the default class you are provided and run the code.  You will indeed see that the database engine is erroneously rounding the prices.  This might be the desired behavior of the new database engine but sure is going to corrupt a lot of projects that are updated and expect their data not to be altered when running a query.


using System;
using System.Data.SQLite;
using System.Data;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            string datafilepath = string.Format("{0}\\{1}", "c:", "automaticroundingissue.sqlite");            
            string databaseconnectionstring = string.Format("{0}={1};new=False;DateTimeFormat={2}", "Data Source", datafilepath, SQLiteDateFormats.CurrentCulture);
            string sql = string.Empty;

            SQLiteConnection conn = new SQLiteConnection(databaseconnectionstring);

            if (! System.IO.File.Exists(datafilepath))
            {
                //Create file
                SQLiteConnection.CreateFile(datafilepath);
                conn.Open();

                SQLiteCommand cmd = new SQLiteCommand(conn);
                
                //Create Table
                sql = "CREATE TABLE [SaleItems]([SaleItemId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,[SaleId] [int], [ItemId] [int] NULL, [Quantity] [int] NULL, [Price] [decimal](18, 2) NULL, [CreatedDateTime] [datetime] NOT NULL DEFAULT (datetime('now')))";
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
                
                //Create Rows
                sql = "INSERT INTO [SaleItems](SaleId, ItemId, Quantity, Price) VALUES(1,12345,1,7)";
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO [SaleItems](SaleId, ItemId, Quantity, Price) VALUES(1,54878,2,4.10)";
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO [SaleItems](SaleId, ItemId, Quantity, Price) VALUES(1,41848,2,5.49)";
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO [SaleItems](SaleId, ItemId, Quantity, Price) VALUES(1,12345,1,7.90)";
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                // Make a second set of number with 1st item having decimals
                //Create Rows
                sql = "INSERT INTO [SaleItems](SaleId, ItemId, Quantity, Price) VALUES(2,12345,1,7.49)";
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO [SaleItems](SaleId, ItemId, Quantity, Price) VALUES(2,54878,2,4.10)";
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO [SaleItems](SaleId, ItemId, Quantity, Price) VALUES(2,41848,2,5.49)";
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO [SaleItems](SaleId, ItemId, Quantity, Price) VALUES(2,12345,1,7.90)";
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();


                conn.Close();
            }
            
            conn.Open();

            Console.WriteLine("Automatic rounding due to 1st price item does not have decimals!!! WHY????");
            sql = "SELECT ItemId, Quantity, Price, CreatedDateTime from SaleItems Where SaleId = 1 order by SaleItemId asc";
            SQLiteCommand cmdread = new SQLiteCommand(conn);
            cmdread.CommandText = sql;
            SQLiteDataReader reader = cmdread.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Load(reader);
            reader.Close();
            foreach (DataRow row in dt.Rows)
            {
                Console.WriteLine(row["Price"]);
            }

            Console.WriteLine("Not rounding because 1st item has decimals!!!");
            string sql2 = "SELECT ItemId, Quantity, Price, CreatedDateTime from SaleItems Where SaleId = 2 order by SaleItemId asc";
            SQLiteCommand cmdread2 = new SQLiteCommand(conn);
            cmdread2.CommandText = sql2;
            SQLiteDataReader reader2 = cmdread2.ExecuteReader();
            DataTable dt2 = new DataTable();
            dt2.Load(reader2);
            reader2.Close();
            foreach (DataRow row in dt2.Rows)
            {
                Console.WriteLine(row["Price"]);
            }

            conn.Close();



            Console.WriteLine("Press any key to continue . . .");
            Console.ReadKey();

        }
    }
}

OUTPUT OF APP
Automatic rounding due to 1st price item does not have decimals!!! WHY????
7
4
5
8
Not rounding because 1st item has decimals!!!
7.49
4.1
5.49
7.9
Press any key to continue . . .

(8.1) By jhersey29 on 2022-03-15 14:34:30 edited from 8.0 in reply to 3 [link] [source]

Deleted

(9) By RandomCoder on 2022-03-15 14:55:04 in reply to 7.1 [link] [source]

If you use SQLite methods, you won't see your bug, which is a large hint as to what's going on:

Console.WriteLine("Automatic rounding due to 1st price item does not have decimals!!! WHY????"); // Nope
sql = @"
    SELECT 
        ItemId, Quantity, Price, CreatedDateTime 
    from 
        SaleItems Where SaleId = 1 order by SaleItemId asc
";
SQLiteCommand cmdread = new SQLiteCommand(conn);
cmdread.CommandText = sql;
SQLiteDataReader reader = cmdread.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine(reader.GetFloat(2));
}

The non-SQLite code you've inserted, namely DataTable, has this to say about how it operates:

The Load method infers the schema based on the result set from the imported IDataReader.

In other words, this is by design. The method you're passing data through makes a guess as to the data type for each returned value based off the first value returned. SQlite stores integers as exact values, so when DataTable sees an integer for the first value, it coercers all remaining values into integers.

(10) By anonymous on 2022-03-15 15:02:07 in reply to 7.1 [link] [source]

This is not sqlite problem but could only be with System.Data.SQLite wrapper. Some wrappers don't recognize column declaration so they try to define it when reading data from first row.

If you try your test with sqlite CLI you will find that all is working ok.

I suggest you change Price declaration just to float and see if it works.

(11) By jhersey29 on 2022-03-15 15:22:14 in reply to 9 [link] [source]

Thank you! Anyway for the reader.GetFloat(i) to use a named column instead of a column index. It personally believe it is bad practice to use indexes on columns. Leads to bugs down the road. In this case it works fine due to listing the columns in the select statement but it would not work well with Select *. It also makes the code and project hard to work on since you have to reference the order of the columns instead of just using the column name.

(12) By jhersey29 on 2022-03-15 15:28:44 in reply to 11 [link] [source]

I found this works. Thank you for the help! This is going to be a project to change all this code. Not a small project.

Console.WriteLine(reader.GetFloat(reader.GetOrdinal("Price")));

(13) By jhersey29 on 2022-03-15 15:41:43 in reply to 10 [link] [source]

That does work in this case as well. Thanks for your suggestion. However for financial data it is not good practice to use approximate values such as a float.

(14) By jhersey29 on 2022-03-15 15:45:14 in reply to 12 [link] [source]

I think I am going to go with reader.GetDecimal(reader.GetOrdinal("Price")). Floats pose all sorts of issues when numbers are approximated.

(15) By anonymous on 2022-03-15 16:44:13 in reply to 13 [link] [source]

I have to disappoint you but sqlite internally has only float data type. You must read this https://www.sqlite.org/datatype3.html So declaration is just information for reader what should be stored but it is not necessary true.

(16.14) By cj (sqlitening) on 2022-03-17 02:26:03 edited from 16.13 in reply to 1 [link] [source]

create table t1(Price real)
instead of
create table t1([Price] [decimal] (18,2) NULL)

If you don't like float use INTEGER with pennies where a dollar is 100.
insert into t1 values(700)
select printf('%.2f',price *.01) from t1

Notice the table name is specified with order by if alias name is the same
as the column name with printf so the column sorts numerically.  
select printf('%.2f',price*.01) as price from t1 order by t1.price


If float is used convert price to your languages "currency type"
before insert/update to control the decimal places.

Note: If there is a macro or replacement for this, please post it.
printf('%.2f',price *.01)