I am currently trying to get some latitude and longitude data to import into a database using the data import wizard in mysql workbench. the problem is that the floating point values that get imported are modified by mysql workbench, or whatever query it's preforming, so they are useless. Example: 40.8152 turns into 40.0000. I cannot figure out why this is happening. I have the columns set to DECIMAL(6,4), but they still get modified when imported. Anybody have any ideas as to what might be causing this and how i can get these to import correctly? Edit: I have also tried FLOAT, FLOAT(6,4), DOUBLE and DOUBLE(6,4) with the same results. It either removes the trailing decimal values completely or sets them to 0.

I'm using MySQL's C api to determine the maximum possible value that can be stored in a specific field. I have the following table: CREATE TABLE test ( f1 double(4,2), f2 double ) When I get the field information for f1 and f2 using the API, here is what I see: f1: { [...], length = 4, decimals = 2 } f2: { [...], length = 22, decimals = 31 } I understand that the maximum value that f1 can store is 99.99 (total length = 4, so 4-2=2 digits before the decimal and 2 digits after the decimal). Is it safe to assume that if a column type is DOUBLE, its length is 22 and its decimal is 31, then there is no (m, d) specified? If so, then can we also assume that the maximum value this column can store is 1.7976931348623157E+308 (as per the docs)? Can this value ever change between different machines/operating systems?

I have a column of data that I need to save in a database. It looks like a series of signed integers, but then I came across "-0" and I had a facepalm moment. After checking with a colleague, it was confirmed as a valid data point that needs to be saved. The obvious choice is to use a VARCHAR type column instead of SMALLINT or similar. But I'd really like to know if negative zero exists in MySQL? I found only one reference to it in the manual that said when '-1e+52' is saved and selected from a FLOAT column, it is returned as either -inf or -0 depending on the platform.

I've encountered what seems like an unexpected precision loss when casting from float4 directly to numeric in PostgreSQL:

select 1079414::float4,
       (1079414::float4)::numeric(15,2) as float4_to_numeric, -- loses precision
       ((1079414::float4)::float8)::numeric(15,2) as float4_to_float8_to_numeric;

This produces:

float4   | float4_to_numeric | float4_to_float8_to_numeric
------------+-------------------+----------------------------
  1,079,414 |          1,079,410 |                  1,079,414

The direct conversion from float4 to numeric unexpectedly yields 1,079,410 instead of the expected 1,079,414. The PostgreSQL docs state : "On all currently supported platforms, the real type has a range of around 1E-37 to 1E+37 with a precision of at least 6 decimal digits." and I realize that this is 7 decimal digits. However, Wikipedia notes : "Any integer with absolute value less than 2^24 can be exactly represented in the single-precision format" Since 1,079,414 is much less than 2^24 (16,777,216), I would expect it to be represented exactly. Interestingly, converting to float8 first and then to numeric preserves the full value, suggesting PostgreSQL is storing the full precision but somehow losing it during direct conversion to numeric. Is this behavior a bug or is there some underlying reason for this behavior? What's happening during the type conversion that causes this precision loss? I see there is a similar question already, but that example uses decimals while this is about an integer less than 2^24.

i got a weird problem using mariadb.

CREATE DATABASE testdb;
USE testdb;
CREATE TABLE testing (afloat FLOAT NULL);
INSERT INTO testing VALUES(123456789.0);
SELECT * FROM testing;

But instead of returning 123456789 it returns 123457000. I'm aware of the precision with floating numbers in IEEE 754 but even considering this the actual value stored is 123456792. I came across this documentation but I am not that experienced with the ISO C standard. [https://dev.mysql.com/doc/internals/en/floating-point-types.html](https://dev.mysql.com/doc/internals/en/floating-point-types.html#:~:text=Since%20MySQL%20uses%20the%20machine,for%20binary%20floating%2Dpoint%20arithmetic.&text=However%2C%20we%20can%20not%20rely,every%20system%20uses%20this%20representation) Can somebody explain why mariadb is not storing the expected value please? *PS: I know the problem could be solved using a double but I want to understand why it is not working with a float.*

I know that numeric is an "exact" data type, representing numbers as accurately as possible so that there are no floating-point errors such with float or real data types. But what about numeric(p,s)? It must approximate a number because of the fixed scale, but does it have floating-point inaccuracies or is it “safe”? I could not find any information about it. Maybe it is obvious, but I would like to understand this aspect better.

I've encountered a perplexing issue while working with floating point values in MySQL. Specifically, I'm attempting to store and retrieve a floating point value with seven significant digits. However, when I fetch the data using a plain SELECT statement, the returned value appears to be rounded off. Conversely, when I utilize the ROUND or FORMAT functions, I receive the exact, unrounded value. I've read some related discussion but most seem to be about suggesting the use of decimal type, but don't mention the reason for the discrepancy. Here's a demonstration of my problem using a basic SQL command sequence: 1. Table Creation:

mysql> CREATE TABLE IF NOT EXISTS demo
  (
     id    INT(10) NOT NULL auto_increment,
     value FLOAT,
     PRIMARY KEY (id)
  );

2. Inserting Value:

mysql> INSERT INTO demo (value) VALUES (2998877.0);

3. Retrieving Value (Returns Rounded Off Value):

mysql> SELECT value FROM demo;
+---------+
| value   |
+---------+
| 2998880 |
+---------+
1 row in set (0.00 sec)

4. Retrieving Value with ROUND Function (Returns Exact Value):

mysql> SELECT round(value) FROM demo;
+--------------+
| round(value) |
+--------------+
|      2998877 |
+--------------+
1 row in set (0.00 sec)

Given that the ROUND function returns the accurate value, it seems to indicate that the value is stored precisely. Therefore, my main question is: why does the SELECT statement, without any accompanying functions, yield a rounded value instead of the precise one? Any insights into this issue would be greatly appreciated.

I have a Laravel application that needs to store minimum and maximum weight. Currently, we are using integer values to store data but I need to modify it to store data values with a floating point. What data type do I have to use for good accuracy?

I have a table in sql server containing a float field. This field displays a different number of decimals from row to row. I know for a fact that a float field CAN NOT contain the exact value 3173.77, so some rounding is going on here below: SELECT Weight ... FROM ... WHERE ... result : 3173,77001953125 3173,77 but somehow sqlserverstudio somehow rounds to different lengths. Is this because a precision is stored ? SELECT CAST(Weight AS DECIMAL(38,30)) ... FROM ... WHERE ... result : 3173.770019531250000000000000000000 3173.769999999999981810105964541435 or is this because the second number in this case can round to 3173.77000000000000

I tried to store decimal number, but couldn't. It's Office 2019. I tried both number types, and different "format". One format had some zero's show up after a comma, but could not enter decimal number, it was rounded. I was thinking about storing gps coordinates.

The cast from double precision (float8) to numeric rounds to 15 significant decimal digits, thereby losing information. Clearly, more precision is possible. The cast to bigint (for values within its range) preserves more precision:

SELECT f8          AS float8
     , f8::bigint  AS to_bigint
     , f8::numeric AS to_numeric
FROM  (
   VALUES
     ('8217316934885843456'::float8)
   , ('8217316934885843457')
   , ('8217316934885844479')
   , ('8217316934885844480')   
   , ('8217316934885845503')
   , ('8217316934885845584')   
   ) t(f8);

        float8         |      to_bigint      |     to_numeric      
-----------------------+---------------------+---------------------
 8.217316934885843e+18 | 8217316934885842944 | 8217316934885840000
 8.217316934885844e+18 | 8217316934885843968 | 8217316934885840000
 8.217316934885844e+18 | 8217316934885843968 | 8217316934885840000
 8.217316934885845e+18 | 8217316934885844992 | 8217316934885840000
 8.217316934885845e+18 | 8217316934885844992 | 8217316934885840000
 8.217316934885846e+18 | 8217316934885846016 | 8217316934885850000
(6 rows)

*dbfiddle [here](https://dbfiddle.uk/?rdbms=postgres_13&fiddle=89538ad467b701f391aae67a8bff3ff1)* I picked values at boundaries where float8 flips a bit - at least in my local installation (Postgres 13, Ubuntu, Intel CPU), and on dbfiddle, and in a hosted DB on AWS, too). Some relevant quotes from the current manual (Postgres 14): In the overview of numeric types > double precision ... **15 decimal digits** precision In the chapter for floating point numbers : > The double precision type has a range of around 1E-307 to 1E+308 with a **precision of at least 15 digits**. And: > By default, floating point values are output in text form in their shortest precise decimal representation; the decimal value produced is closer to the true stored binary value than to any other value representable in the same binary precision. (However, the output value is currently never *exactly* midway between two representable values, in order to avoid a widespread bug where input routines do not properly respect the round-to-nearest-even rule.) This value will use **at most 17 significant decimal digits** for float8 values, and at most 9 digits for float4 values. Bold emphasis mine. So why not preserve *at most 17 significant decimal digits* in the cast to numeric? The cast to bigint does better! This has counter-intuitive (at least for me) effects. A cast to numeric loses precision, while a cast to bigint does not.

test=> SELECT '8217316934885843456'::float8 = '8217316934885843456'::float8::bigint::float8
test->      , '8217316934885843456'::float8 = '8217316934885843456'::float8::numeric::float8;
 ?column? | ?column? 
----------+----------
 t        | f

It's a notoriously tricky matter. So maybe there are good reasons for capping at 15 digits that I fail to see? Or could Postgres do better?

Consider the following. CREATE TABLE IF NOT EXISTS docs ( id int(6) unsigned NOT NULL, qty float NOT NULL, price float NOT NULL, rebate float not null, PRIMARY KEY (id) ) DEFAULT CHARSET=utf8; INSERT INTO docs VALUES (1,1,43616.7,0); SELECT SUM(qty * (price - price * rebate/100)) AS endval FROM docs Instead of returning 43616.7 as expected, this query gives back 43616.69921875 (on some machines 43616.671875). Demo fiddle can be found here . Why is this happening? I would expect it to work like this (apologies for actually typing out something as basic as this): 1 * (43616.7 - 43616.7 * 0 / 100) = 1 * (43616.7 - 0) = 43616.7 I realize that I could overcome the issue by going with decimal (10,2) for my columns, I'm just wondering what's causing this unexpected behaviour.

Consider the following example: DROP TABLE dbo.TEST_FLOAT; CREATE TABLE dbo.TEST_FLOAT ( AMOUNT FLOAT ); INSERT INTO dbo.TEST_FLOAT (AMOUNT) VALUES (11546.25); SELECT AMOUNT, CAST(AMOUNT AS VARCHAR(10)) AS AMOUNT_AS_CHAR FROM dbo.TEST_FLOAT This is what is returned from Management Studio: What I want is the displayed values to be the same, however it seems Azure Synapse is rounding the number when the CAST is performed. I need this to work inline as I am hashing several concatenated values and need these all COALESCED VARCHARS. Any idea how to do this?

I came across some weird behavior: While passing a float value into a varchar column, the values are getting converted from integers into scientific notation, and it's that scientific notation that gets stored as a string. if OBJECT_Id('tempdb..#whydis') is not null begin drop table #whydis end if OBJECT_Id('tempdb..#ImSeriously') is not null begin drop table #ImSeriously end create table #whydis (bigID float) create table #ImSeriously (bigID varchar(255)) insert into #whydis(BigID) values(1495591), (1495289), (1495610), (1495611), (1495609), (1495592), (1495686) INSERT INTO #ImSeriously (bigID) SELECT BigID from #whydis select * from #ImSeriously results look like this: 1.49559e+006 Scientific notation stored as a string. It's easy enough to work around by casting as int: INSERT INTO #ImSeriously (bigID) SELECT cast(BigID as int) from #whydis But the whole thing has me scratching my head. **Question:** What is it about floats that stores them this way?

Reading the Postgresql docs [about the numeric data types](https://www.postgresql.org/docs/12/datatype-numeric.html) leads me to this question: why do I get these unexpected results with the data types Float (SQL standard) and Numeric in Postgresql? For example: CREATE TEMP TABLE testnum (a numeric, b float); INSERT INTO testnum VALUES (100,100); INSERT INTO testnum VALUES (9*9*9,9*9*9); INSERT INTO testnum VALUES (9^9^9,9^9^9); SELECT (a/3)*3 AS numeric, (b/3) * 3 AS float FROM testnum; SELECT (a/5)*5 AS numeric, (b/5) * 5 AS float FROM testnum; Then run SELECT (a/3)*3 AS numeric, (b/3) * 3 AS float FROM testnum; numeric | float 99.9999999999999999 | 100 729.0000000000000000 | 729 In this test Float looks more accurate than Numeric. Which one is the appropriate data type for precise numeric values like currency or inventory quantities?

I have the following table: CREATE TEMPORARY TABLE new_aps ( MinuteStart datetime NOT NULL, ProxyListID bigint(20) NOT NULL, SuccessCount double(22,0) NOT NULL, UnknownCount double(22,0) NOT NULL, FailureCount double(22,0) NOT NULL, PRIMARY KEY (ProxyListID,MinuteStart) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci When I try to run the following query in HeidiSQL: INSERT INTO new_aps (MinuteStart, ProxyListID, SuccessCount, UnknownCount, FailureCount) SELECT NOW(), 1, 0.5, 1.5, 2.5 I get the following entry in new_aps table: SELECT * FROM new_aps "2020-06-11 11:58:19" "1" "0" "1" "2" What am I doing wrong? I need double-precision floating-point values stored in the table. MariaDB version is 10.4.13 on Windows 10 64-bit.

SQL Server 2008 R2 Why when converting a FLOAT to a string (Varchar / Nvarchar) using Cast or Convert, does a rounding or truncation occur? Using CAST DECLARE @floatVal FLOAT = 4.76758527755737 SELECT CAST(@floatVal AS VARCHAR(20)) 'VARCHAR', CAST(@floatVal AS NVARCHAR(20)) 'NVARCHAR' Output VARCHAR NVARCHAR 4.76759 4.76759 Using CONVERT: DECLARE @floatVal FLOAT = 4.76758527755737 SELECT CONVERT(VARCHAR(20), @floatVal) 'VARCHAR', CONVERT(NVARCHAR(20), @floatVal) 'NVARCHAR' Output VARCHAR NVARCHAR 4.76759 4.76759 With STR however, there's no truncation/rounding DECLARE @floatVal FLOAT = 4.76758527755737 SELECT LTRIM(STR(@floatVal, 17, 14)) 'STR' Output STR 4.76758527755737 Thanks in advance

My exact MySQL version is 5.7.29-0ubuntu0.18.04.1. And I'm beginning to think that I'm getting crazy... Just try these queries:

CREATE TABLE float_bug (
  v FLOAT NOT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO float_bug VALUES(92160596);

SELECT * FROM float_bug;

Returns 92160600. Then I tried these:

SELECT * FROM float_bug WHERE v = 92160596;
SELECT * FROM float_bug WHERE v BETWEEN 92160596 AND 92160600;

Both return nothing! However this

SELECT * FROM float_bug WHERE v = 92160592;

Returns 92160600. I'm ready to throw my computer into the wall, but unfortunately that won't help because I have exactly the same behaviour on my remote server and I can't throw it into the wall physically. Any ideas what is going on here?

I know there are different variations of this question asked. But what I am trying to do is that, there is a table with a numeric column (12,0) that has data looking like Units ----- -1222222 0 -19 21123021 What they really mean is this Units ----- -12222.22 0.00 -0.19 211230.21 With two decimal places in the last digits of each number. I am struggling to convert these numbers to a two decimal place float column(Units)

I have many situation where I need to convert big float numbers with many decimal places to a rounded number with 2, or 1 or no decimal places at all. the way I have been doing this is demonstrated below: declare @t table ( [rows] float ) insert into @t select 1.344 union all select 32.3577 union all select 65.345 union all select 12.568 union all select 12.4333 select * from @t Now I will do my convertion and save them all into a temp table and then we are going to look at the structure of the temp table. begin try drop table #radhe end try begin catch end catch select [Rows Formated] = REPLACE(CONVERT(VARCHAR(50),CAST(sp.[Rows] AS MONEY),1), '.00','') into #radhe from @t sp select * from #radhe that is all fine, it worked great, it did what I wanted, however, when I look at the structure of the table I get the following: use tempdb go sp_help '#radhe' you can see on the picture, the varchar(8000). can we avoid that? I would be happy with a varchar(50)