• (cs) in reply to jrockway

    Anonymous:
    DON'T PANIC - the system never saw real production use until after it was completely revamped in a proper DBMS
    Ah excellent. It wasn't bad coding that was the problem, it was MySQL. If it's free, it must be bad!

    MySQL has a bad rep these days for some reason, but it's not really a terrible RDMBS. It works for big sites like mixi, livejournal, vox, slashdot, etc., so you shouldn't discount it immediately.

    Captcha: poprocks.

    Don't forget yahoo.  One of the most visited sites on the 'net (if not the most visited site).  They use MySQL for many of their web applications.

  • (cs) in reply to mrprogguy
    mrprogguy:

    More power to all you DBAs out there, and to those of you who have to write queries and generate reports.  It's a legit biz, but face it--you're users, not programmers.

    I beg to differ, you can do a lot more that just query tables in a RDBMS. In fact most of the real data work is down at the database level through stored procedures, fuctions triggers, etc.... all the "programmer" (front end that is) has to do is create a pretty interface and call stored procedures.

    I would say that in most client server apps, the real work is done by programmers of the database. 

  • RJ (unregistered) in reply to Mike
    Anonymous:
    Anonymous:

    NULLs dont compare equal. Whoopdeedoo. Now look at your queries where you join on an index that may be null. Byebye cardinality. SET  VARIABLE myisam_stats_method="nulls_equal";

    err.. NULLs don't compare equal in Oracle either

    And in fact, nulls should never compare equal. Applying any serious logical approach to data, you can't compare null with null because it is not a value. That's why the COALESCE, IS NULL, and IS NOT NULL expressions exist.

  • RJ (unregistered) in reply to Sam Thornton
    Anonymous:

    Actually, foreign key constraints themselves may be a WTF in the case where the fact that data is missing has a specific meaning within the DB design. Read your E.F. Codd. 

    I have read Codd; and Date, and Darwen, among others. What on earth are you talking about?

  • (cs)

    This isn't quite as scary as another internet banking site I worked on recently.

    The system in question used a DHTML/JScript menu on the screen and was configured from the database. Each option on the menu had security settings for each user. The turkey who wrote it did something like:

    - SELECT * FROM menuTable

    - For each item returned (>30), it did a round-trip to the database and checked security and a few other things I forget now.

    And it was doing this on each page load. I did a similar trace and it was hitting the database >120 times per page load. And the original developer was blaming the security protocols for slowing the system down......

     
    Cheers,

    Tim
     

  • Rich (unregistered) in reply to Mike
    Anonymous:
    Anonymous:

    NULLs dont compare equal. Whoopdeedoo. Now look at your queries where you join on an index that may be null. Byebye cardinality. SET  VARIABLE myisam_stats_method="nulls_equal";

    err.. NULLs don't compare equal in Oracle either

    Or mssql or postgresql. I don't have the SQL standard to hand but I'd lay good money...


    Rich

  • (cs) in reply to Sam Thornton

    I can't help feeling that 16 joins sounds like baaaad design.  i'm not exactly a SQL guru, and perhaps I lack imagination, but what can require that much mangling?

  • (cs) in reply to ---

    PostgreSQL sucks about as much as MySQL does. It's just a different kind of suck. 


    Strictly speaking, PostgreSQL doesn't suck - it VACUUMs.

    But it does VACUUM a great deal.

  • (cs) in reply to Rich
    Rich:

    Or mssql or postgresql. I don't have the SQL standard to hand but I'd lay good money...



    SQL-92 section 8.2, general rules 1a. "If XV or YV is the null value, then "X <comp op> Y" is unknown" - so no, there's no way X = Y can be true if X and Y are both null.

    http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

    (On the other hand, two null values are not DISTINCT.)

  • (cs) in reply to mrprogguy
    mrprogguy:

    It's a good think I have no idea what this is about, because otherwise I'd probably be pretty torqued.

     The WTF for me in all these SQL-related WTFs is that, well, it ain't programming.  I thought this was about programming.  You know, C, C++, Fortran, hell, even LISP, but SQL?

    More power to all you DBAs out there, and to those of you who have to write queries and generate reports.  It's a legit biz, but face it--you're users, not programmers.



    The WTF for me is a person admitting cluelessness in the subject they then head off to pass judgement on. Especially when they get that judgement thoroughly wrong... there's a lot more to SQL than SELECT * FROM Employee WHERE salary > $WHATEVER_I_GET, you know.

    But then, presumably you don't believe shell-scripting is "programming" either. Especially given that your verdict on Lisp appears to be that it's only just programming.

    On the other hand, perhaps we should expect that kind of uninformed arrogance from someone who calls themselves MrProgGuy, as though the whole damn forum were not populated by experienced jobbing coders.

  • David (unregistered) in reply to anonymous

    Let's not forget DB2! ;)

  • byrmol (unregistered) in reply to voidy
    voidy:
    I can't help feeling that 16 joins sounds like baaaad design.  i'm not exactly a SQL guru, and perhaps I lack imagination, but what can require that much mangling?


    Just about any decent schema at 3NF or above...
  • AC (unregistered)

    What seas exactly do you have to cross to go from the USA to Argentina again?

  • (cs)

    Obligatory:

    The real WTF is that "Dispatchers_tmp" is obviously meant to be a temporary table, but isn't created as one!

     (On second thought, given this code, I wouldn't be surprised if they need multiple connections to do all this, in which case the temporary table couldn't cut it.) 

     ---

     
    Other comment: "proper DBMS" <- there is any DBMS that someone with this expertise in SQL  *couldn't* muck up? I'm certain a properly written PHP/MySQL-based system would run much more smoothly and securely than anything this person could do with the best DBMS in the world...
     

  • Anonymous Tart (unregistered) in reply to Mike
    Anonymous:
    Anonymous:

    NULLs dont compare equal. Whoopdeedoo. Now look at your queries where you join on an index that may be null. Byebye cardinality. SET  VARIABLE myisam_stats_method="nulls_equal";

    err.. NULLs don't compare equal in Oracle either

     They do in indices. Say you have a table:

        create table foo (id int not null, type int, primary key (id), key type (type)) ;

    and you whack some data into it

        insert into foo values (1,1),(2,1),(3, NULL),(4,NULL),(5,NULL),(6,NULL);

    Whats the cardinality of the 'type' key? A decent RDBMS would have the NULLs equal, giving a cardinality of 2. MySQL gives that a cardinality of  5. In a key, NULLs should compare equal. MySQL doesn't unless you force it..
     

  • Anonymous Tart (unregistered) in reply to Rich
    Anonymous:
    Anonymous:
    Anonymous:

    NULLs dont compare equal. Whoopdeedoo. Now look at your queries where you join on an index that may be null. Byebye cardinality. SET  VARIABLE myisam_stats_method="nulls_equal";

    err.. NULLs don't compare equal in Oracle either

    Or mssql or postgresql. I don't have the SQL standard to hand but I'd lay good money...


    Rich

     

    http://www.postgresql.org/docs/8.1/interactive/indexam.html

    Pay close attention to what an index access method must do in PostgreSQL to claim amindexnulls support. I perhaps should have been more explicit and said "NULLs dont compare equal in indices", but you should have inferred that from the rest of the sentence.

  • (cs) in reply to Sam Thornton
    Anonymous:

    Re mySQL being free, it's only free for commercial use if you violate their license. See http://www.mysql.com/buy-mysql/

    This is not entirely true. Since MySQL is licensed under the GPL, you are free to use it for any purpose, including commerical use.

    But, like with any other GPLed software, you cannot use the GPLed version of MySQL to create a derived work (read: you own proprietary program, linked to the MySQL drivers) and distribute it;

    if you want to do that, you need the commercial license.
     

     

  • (cs) in reply to voidy

    voidy:
    I can't help feeling that 16 joins sounds like baaaad design.  i'm not exactly a SQL guru, and perhaps I lack imagination, but what can require that much mangling?

    Excessive joins are typical for the dreaded generic data models. 

  • Dhericean (unregistered) in reply to Sindri

    Sindri:
    I was a bit disappointed not to find a "DROP TABLE 'Dispatchers_tmp';" anywhere there.  Why delete from temporary tables when you can just drop them! :oP

     

    I suspect that it was originally written to drop the temporary table at some point but this hosed other transactions trying to run in parallel.  All the create temporary table if not existing tests were probably written during this period (as it kept vanishing).

  • Anonymous Tart (unregistered) in reply to ammoQ
    ammoQ:

    voidy:
    I can't help feeling that 16 joins sounds like baaaad design.  i'm not exactly a SQL guru, and perhaps I lack imagination, but what can require that much mangling?

    Excessive joins are typical for the dreaded generic data models. 

    You cant say they are excessive without seeing the data tbfh :D

    Its actually from the opposite of a generic data model. If you correctly denormalise your data, any 1 to many relationship requires two joins, once to link between an item and its lookup, and then into the lookup table itself. With this model, any query looking at 8 attributes or more of an object would have at least 16 joins. If you add in Multiple Table Inheritance, you could add 3-4 joins before you even look at any attributes.

     Objects in this particular architecture have around 30-40 attributes, so we often encounter MySQLs maximum join limit of 31 tables..

    If you optimise the lookups into some static local storage, of course, you can eliminate half the joins..

  • (cs) in reply to Anonymous Tart
    Anonymous:

    Its actually from the opposite of a generic data model. If you correctly denormalise your data, any 1 to many relationship requires two joins, once to link between an item and its lookup, and then into the lookup table itself.

    Why would I need the lookup table itself? (Maybe I misunderstand you, could you provide an example?)

  • (cs) in reply to disaster
    Anonymous:

    I don't understand your remark about SQL injection. Isn't he just logging the queries as they arrive at the DBMS? There's no way to tell if they have been safely escaped in the application.

    The other issue is the way various IDs are scattered around the query indicates to me that something like 'http://website/application?uid=2193' would work. The PHP could be getting all of those values from some non-SQL session database off to the side or something, but I don't consider that likely.

    I would generally expect to see something like:
    select * from sessions where id = 'ce7aac1e116ee477c7f4fc816377718510fd296eea614f23649c54c9e62dfcbc'
    at the beginning.

  • (cs) in reply to mrprogguy
    mrprogguy:

    It's a good think I have no idea what this is about, because otherwise I'd probably be pretty torqued.

     The WTF for me in all these SQL-related WTFs is that, well, it ain't programming.  I thought this was about programming.  You know, C, C++, Fortran, hell, even LISP, but SQL?

    More power to all you DBAs out there, and to those of you who have to write queries and generate reports.  It's a legit biz, but face it--you're users, not programmers.

    I love it when people uncomfortable with their own ignorance then cover for it by trying to devalue the knowledge. That will get you really far in programming.

    SQL with stored procedures is turing complete. That makes writing SQL programming. I suppose the response to that will be "All those effete CSci snobs and their stupid terms. Face it, they aren't real programmers, they're just academics.".

  • Anonymous (unregistered) in reply to Mike

    or in the SQL standard, for that matter...

  • Anonymous Tart (unregistered) in reply to ammoQ
    ammoQ:
    Anonymous:

    Its actually from the opposite of a generic data model. If you correctly denormalise your data, any 1 to many relationship requires two joins, once to link between an item and its lookup, and then into the lookup table itself.

    Why would I need the lookup table itself? (Maybe I misunderstand you, could you provide an example?)

     So you can interpret what that magical foreign key actually means.

    Quick example. Cars come in any colour, and any number of colours. 

    create table Cars (id int not null auto_increment, primary key(id));

    create table Colours (id int not null auto_increment, name char(64), r int not null, g int not null, b int not null, primary key(id));

    create table car_colours(car_id int not null, colour_id int not null, key colours (colour_id)); 

    insert into Cars values (1); 

    insert into Colours values (1, "Red", 255, 0, 0), (2, "Green", 0, 255, 0), (3, "Blue", 0, 0, 255);

    insert into car_colours values (1,1), (1,2);
     

    Show the colours for Car 1?

    select  Colours.name

    from Cars

    join car_colours on Cars.id = car_colours.car_id

    join Colours on car_colours.colour_id = Colours.id

    where Cars.id = 1;

     

    Quite straightforward 3NF .. 

  • (cs) in reply to Anonymous Tart

    (fixed your typos, deleted your post regarding fixes) 

    Anonymous:

     So you can interpret what that magical foreign key actually means.

    Quick example. Cars come in any colour, and any number of colours. 

    This will quickly lead you to the flamew^h^h^h^h^h^hdiscussion about artificial keys. Why use a meaningless integer as the primary key for colors instead of the name?
     

  • Rich (unregistered) in reply to ammoQ
    ammoQ:

    This will quickly lead you to the flamew^h^h^h^h^h^hdiscussion about artificial keys. Why use a meaningless integer as the primary key for colors instead of the name?
     

     

    What shade of red was that you wanted again? Metal flakes?

     You can put in the actual name as the key but then you might still need a separate table to describe the base color, (sunburst==yellow for example) and supplier codes etc.

     

    Rich
     

  • (cs) in reply to Rich
    Anonymous:
    ammoQ:

    This will quickly lead you to the flamew^h^h^h^h^h^hdiscussion about artificial keys. Why use a meaningless integer as the primary key for colors instead of the name?
     

     

    What shade of red was that you wanted again? Metal flakes?

     You can put in the actual name as the key but then you might still need a separate table to describe the base color, (sunburst==yellow for example) and supplier codes etc.

    There is nothing wrong with a "color" table for all those details.

    Anyway, the color name is probably a good primary key candidate in the given context, so you don't have to do a join just to find the name. Of course there can be the problem that some people don't understand color names like "sunburst", but since this is a rather artificial example, there is no sense in asking whether or not people understand "sunburst". The point is: Artificial keys force you to do more joins than natural keys.
     

  • Anonymous Tart (unregistered) in reply to ammoQ

    To avoid redundant data?

    Lets consider a small database of 200 million cars (heh). With a 'meaningless integer' as the key, car_colours has a rough record size of ~16 bytes, with a char(64), we're talking about ~ 72 bytes a record.

     

    Secondly, and more pedanticly, a meaningless integer is sometimes a better representation. For our colours, we may have several different 'Red' colours. We might have a new formulation of 'Red' each year. We could have a multi-index column of name, year, brand, manufacturor...

     

    You'll notice I didnt feel the need to have an artificial primary key on car_colours. Horses for courses.

  • Anonymous Tart (unregistered) in reply to ammoQ
    ammoQ:
    Anonymous:
    ammoQ:

    This will quickly lead you to the flamew^h^h^h^h^h^hdiscussion about artificial keys. Why use a meaningless integer as the primary key for colors instead of the name?
     

     

    What shade of red was that you wanted again? Metal flakes?

     You can put in the actual name as the key but then you might still need a separate table to describe the base color, (sunburst==yellow for example) and supplier codes etc.

    There is nothing wrong with a "color" table for all those details.

    Anyway, the color name is probably a good primary key candidate in the given context, so you don't have to do a join just to find the name. Of course there can be the problem that some people don't understand color names like "sunburst", but since this is a rather artificial example, there is no sense in asking whether or not people understand "sunburst". The point is: Artificial keys force you to do more joins than natural keys.
     

     

    Not using artificial keys forces you to denormalise data. [ To disagree with this statement, please post an example of a one-to-many relationship not using an artifical key and that doesnt repeat the primary key of the 'many' side of the relationship. Sorry, but I'm now working :D ]

  • Richard (unregistered) in reply to RJ
    OK, so I took a cheap shot at MySQL. I must explain that when this system was written, version 3.x of MySQL was in use, which did not have transactions, foreign key constraints (or any constraints, really), and which would allow for a '0000-00-00' date value, as well as silently truncate values without warning, or insert a default value in place of a NULL, etc...

    Was that back at the time that no-one dared use transactions in MS SQL Server because they were just terrible? Even now the locking mechanisms it uses can cause problems when it decides to promote a lock. I note also that Microsoft really strongly discourage long sessions on SQL Server, where in Oracle long sessions, long transactions and decent locking were taken for granted.

    Just to put in my bit of putting-down-the-other-Databases ;-) 

     

  • Robin (unregistered)

    Holy shit Batman!  I hope that bastard got whacked in the balls.

  • Cold LAMPin (unregistered)

    I have used many RDBMS systems.  MySQL is fine when you know its limitations -- JUST LIKE ALL THE OTHERS.  Shelve your bigotry.

     I have had far, far, far more trouble with MSSQL.
     

  • LP (unregistered) in reply to Wolven
    Anonymous:

    Anonymous:
    > DON'T PANIC - the system never saw real production use until after it was completely revamped in a proper DBMS

    Ah excellent. It wasn't bad coding that was the problem, it was MySQL. If it's free, it must be bad!

    MySQL has a bad rep these days for some reason, but it's not really a terrible RDMBS. It works for big sites like mixi, livejournal, vox, slashdot, etc., so you shouldn't discount it immediately.

    Captcha: poprocks.

    Right, thats it: MySQL is a pile of dung, even compared to other free RDMBSs. Don't believe me? Go check out the PosgreSQL manual, far more features (including the basics missing from MYSQL).

    Generally MySQL has a bad reputation because it sucks arse!
     

    I usually discard even thinking about PostgreSQL just because of comments like this one. I stay away from communities filled with fanboys.

    Seriously, MySQL has nothing to prove anymore. The reason why it's installed on so many servers is that it does work just fine. The downside of MySQL is that it's too simple to install and anyone can write an application with it. This WTF is an example of that. With the MySQL bashing comment after the request log, I have the feeling the submitter is a good source of WTF himself.

  • Rick (unregistered) in reply to abdul
    abdul:

    This isn't quite as scary as another internet banking site I worked on recently.

    The system in question used a DHTML/JScript menu on the screen and was configured from the database. Each option on the menu had security settings for each user. The turkey who wrote it did something like:

    - SELECT * FROM menuTable

    - For each item returned (>30), it did a round-trip to the database and checked security and a few other things I forget now.

    And it was doing this on each page load. I did a similar trace and it was hitting the database >120 times per page load. And the original developer was blaming the security protocols for slowing the system down......

     
    Cheers,

    Tim

    That wasn't Virtual Bank was it?  My friends that work there tell me it's a serious WTF.  I warned them that a View Source would show VB coded in their pages, commented out with HTML comments.  Great for reverse engineering SQL injections.  They got rid of that a few years ago.

  • Daniel Colascione (unregistered) in reply to Sam Thornton

    It's licensed under the GPL, and it's perfectly legal to use GPL software commercially. What are you talking about?

  • SLM (unregistered) in reply to Anonymous Tart

    Pretty unbelievable, ain't it? You don't know of an up-to-date MySQL WTF page out there, do you? I'm on a continuing mission to educate all the phonkies (php monkies) out there.

     There's a great page on MySQL 4.x but I can't find one on 5.x.

     

  • Teltariat (unregistered) in reply to ArSing

    Thats what I was wondering.

  • Aaron (unregistered) in reply to Anonymous Tart

    "NULLs dont compare equal. Whoopdeedoo. Now look at your queries where you join on an index that may be null. Byebye cardinality. SET VARIABLE myisam_stats_method="nulls_equal";"

    This is not a bug. NULLs do not evaluate equal in Oracle, SQL server, or any DBMS worth a damn. NULL is the absence of a value, not a value. Also, why would you have an index on a nullable column. Do you mean foreign key?

  • RAB (unregistered) in reply to ammoQ
    ammoQ:
    This is not entirely true. Since MySQL is licensed under the GPL, you are free to use it for any purpose, including commerical use.

    But, like with any other GPLed software, you cannot use the GPLed version of MySQL to create a derived work (read: you own proprietary program, linked to the MySQL drivers) and distribute it;if you want to do that, you need the commercial license.  

    Not quite right: in broad terms you can distribute modified GPLed code for any purpose, provided you offer to distribute the source as well, all under the GPL. GPLv3 further ensures the recipient of this source can make full use of it.

  • Who Cares (unregistered)

    Wasn't that whole cars and colors example actually a many to many, not a one to many?

Leave a comment on “The Seven Levels of SQL”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article