Our prices be always low!



  • Customer calls. Calculated ticket prices come out wrong after price updatehike. Spot the :wtf:

    CREATE 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.


  • FoxDev

    ..... 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....

    :wtf:


  • Banned

    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.


  • FoxDev

    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


  • FoxDev

    @gleemonk said:

    Standard SQL requires that DECIMAL(5,2) be able to store any value with five digits and two decimals

    :headdesk:


  • Banned

    So, TRWTF is SQL?



  • Actually any good implementation of SQL would have stood up and refused to accept the value 102.00 for the column full_oneway. Then we would have been informed of this WTF in the table definition sooner.

    MySQL scurried to update full_oneway with 99.99. Unreliable piece of submissive hacks.


  • Fake News

    engine=MyIsam
    TRWTF if you don't have some specific reason.


  • Discourse touched me in a no-no place

    @gleemonk said:

    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.


  • :belt_onion:

    :( 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 :wtf:

    i'm sure his was :hanzo:ed but screw them.


  • Discourse touched me in a no-no place

    @accalia said:

    ..... your fares now exceed 1k units of currency?!

    Getting there...



  • 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.


  • FoxDev

    @gleemonk said:

    a server where MySQL does not support InnoDB tables

    /me adopts a hillbillyette accent

    Well.. (scratches belly) Thar's yer real :wtf:



  • 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.



  • @accalia said:

    Well.. (scratches belly) Thar's yer real :wtf:

    :headdesk: 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.



  • @Nprz said:

    switch to sqlite

    I'm torn between 😆 :wtf: and :toughtful:

    @Nprz said:

    where such restrictions are just not enforced

    That's actually preferable to how MySQL does it.


  • Discourse touched me in a no-no place

    @gleemonk said:

    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:

    1. Be generous when selecting field length, really generous
    2. 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 field name 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!


  • Discourse touched me in a no-no place

    @gleemonk said:

    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 😄


  • Discourse touched me in a no-no place

    @gleemonk said:

    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.



  • @JBert said:

    engine=MyIsam
    TRWTF if you don't have some specific reason.

    You're going to call that out but not the much much bigger :wtf: of using MySQL at all?

    File under: Discourse quote formatting strikes again



  • @gleemonk said:

    I'm torn between and :toughtful:

    Don't be. Sqlite does not have decimal type. So you can choose between storing prices as strings ( :wtf: ), storing prices as binary floats ( :wtf: ) and storing prices in 1/100s ( :pain-in-the-arse: ).

    @another_sam said:

    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 :wtf: than using MySQL with a sane backend. And using MySQL was called out in the OP already.



  • @Bulb said:

    Sqlite does not have decimal type

    :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.

    @Bulb said:

    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 😄


  • Discourse touched me in a no-no place

    @gleemonk said:

    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 🚎


  • BINNED

    @dkf said:

    a floating point number

    because they are ships ?

    :rimshot:


Log in to reply