Our prices be always low!
-
Customer calls. Calculated ticket prices come out wrong after price
updatehike. Spot theCREATE TABLE `prices` ( `id` int(3) NOT NULL DEFAULT '0', [...] `full_oneway` decimal(4,2) DEFAULT NULL, `full_return` decimal(5,2) DEFAULT NULL, `half_oneway` decimal(4,2) DEFAULT NULL, `half_return` decimal(4,2) DEFAULT NULL, [...] PRIMARY KEY (`id`), KEY [...] ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
TRWTF is MySQL of course.
-
..... your fares now exceed 1k units of currency?!
assuming a relatively stable unit of currency such as the Euro, GBP or USD that's a lot of money for a one way fare....
-
Maybe they're Japanese.
-
You're close. quoting the MySQL manual
In a DECIMAL column declaration, the precision and scale can be (and usually is) specified; for example:
salary DECIMAL(5,2)
In this example, 5 is the precision and 2 is the scale. The precision represents the number of significant digits that are stored for values, and the scale represents the number of digits that can be stored following the decimal point.
Standard SQL requires that DECIMAL(5,2) be able to store any value with five digits and two decimals, so values that can be stored in the salary column range from -999.99 to 999.99.
-
unless the conversion's changed much since i last looked at it, it's not common to deal in fractional JPY... in fact Unless i'm missing something there are no coins in current circulation for sen* or rin*
1/100 JPY and 1/1000 JPY respectively
-
Standard SQL requires that DECIMAL(5,2) be able to store any value with five digits and two decimals
-
So, TRWTF is SQL?
-
Actually any good implementation of SQL would have stood up and refused to accept the value
102.00
for the columnfull_oneway
. Then we would have been informed of this WTF in the table definition sooner.MySQL scurried to update
full_oneway
with99.99
. Unreliable piece of submissive hacks.
-
engine=MyIsam
TRWTF if you don't have some specific reason.
-
five digits and two decimals
What kind of dumbass, after all these years, hasn't revised the documentation so it says
five digits [b]including[/b] two decimals
?Huh. Ok, no other formatting in quotey-span things.
-
:( decimal/numeric(5,2) pretty much always means 999.99 max... at least on every database system i've dealt with, so the (4,2) only holds 99.99
i'm sure his was ed but screw them.
-
-
One of our customers hosted their site on a server where MySQL does not support InnoDB tables. That would be a very specific reason to use MyISAM, wouldn't it?
We advised them to change hosting.
-
a server where MySQL does not support InnoDB tables
/me adopts a hillbillyette accent
Well.. (scratches belly) Thar's yer real
-
CHANGE
full_oneway
full_oneway
decimal(10,2) NULL,Spite you all. We're now prepared for three rounds of serious devaluation.
-
Now, I'll just wait around until I see some WTF where the customer asks why ticket prices are 99999999.99.
Maybe they should switch to sqlite where such restrictions are just not enforced.
-
Well.. (scratches belly) Thar's yer real
right? I didn't know it was possible. Like the hosting that didn't let us change the PHP include path. Apparently PHP can be configured to have a constant include path. I don't know how people come up with this.
-
switch to sqlite
I'm torn between and :toughtful:where such restrictions are just not enforced
That's actually preferable to how MySQL does it.
-
That's actually preferable to how MySQL does it.
I suppose you could enforce the restrictions by writing a trigger. If it really mattered all that much. Most people don't care; the reasons for short lengths are to limit the amount of storage used, and SQLite uses a storage engine which doesn't need to do that in the first place.
-
To me, field length has always been of the form "I guess somebody must care or it would not be there". When I started working with DBMS, storage capacities were already being measured in gigabytes, and the DB I dealt with were seldom over one gigabyte, mostly read-heavy. Working with DB was two lessons on repeat for me:
- Be generous when selecting field length, really generous
- Don't forget to index everything
Then I see byte-pinchers going
first_name
15 chars,last_name
31 chars (gotta allow double surnames). When all they want is a fieldname
with at least 200 chars. When you criticize, you realize they were on the verge of having normalized the names to another table to avoid all this duplication of strings. I exaggerate just a little bit!
-
To me, field length has always been of the form "I guess somebody must care or it would not be there".
I think it comes from the assumption that each row would be mapped to a contiguous byte-range on the storage device. If you're doing that, you have to know how many bytes to allocate per column: having the number of digits/characters makes that easy.
Except we can do better now. We now just have the mistakes of the past, baked in ever deeper…
-
Yeah, punch-card thinking. I mean I do see the advantages of having each record packed at one address. Good for locality if the typical operation is to load the whole record. And having fixed offsets makes things super easy to address. Until such time when you want to change the length of one field... The past is a horrible place to be in, and the future is broken.
At least I found
set sql_mode = 'STRICT_ALL_TABLES';
which will be in the opening credits of ever DB connection soon. I think we'll see a few surprises
-
Until such time when you want to change the length of one field...
That's why
ALTER TABLE
is an expensive operation in some DBs. Other DBs don't try to keep all the columns for a row together — there's no technical reason for keeping all the data in one place — and can do other things.
-
engine=MyIsam
TRWTF if you don't have some specific reason.You're going to call that out but not the much much bigger of using MySQL at all?
File under: Discourse quote formatting strikes again
-
I'm torn between and :toughtful:
Don't be. Sqlite does not have decimal type. So you can choose between storing prices as strings ( ), storing prices as binary floats ( ) and storing prices in 1/100s ( :pain-in-the-arse: ).
You're going to call that out but not the much much bigger of using MySQL at all?
Using MySQL with MyIsam is a bigger than using MySQL with a sane backend. And using MySQL was called out in the OP already.
-
Sqlite does not have decimal type
Maybe I shouldn't be surprised. It's got 'lite' in the name and that part of the market where decimal is important was already tended to by Oracle.
Using MySQL with MyIsam is a bigger than using MySQL with a sane backend.
I almost cut out the MyISAM part when anonymizing but then I thought why not preserve a bonus WTF
-
Maybe I shouldn't be surprised. It's got 'lite' in the name and that part of the market where decimal is important was already tended to by Oracle.
It started out in military applications, specifically naval ships. That's somewhere where having a floating point number for money makes sense! :D
-