tag:blogger.com,1999:blog-145332831106508186.post4143951973554024514..comments2023-03-19T09:06:24.378-04:00Comments on /var/log/rant: Petdance says if you have SELECT * in your code, you have a bugDave Jacobhttp://www.blogger.com/profile/15052163927020492687noreply@blogger.comBlogger6125tag:blogger.com,1999:blog-145332831106508186.post-13664745827148130712012-07-10T11:49:44.497-04:002012-07-10T11:49:44.497-04:00Andy: I know labels. I use labels. It struck me as...Andy: I know labels. I use labels. It struck me as going into the weeds in context of this conversation. <br /><br />A.: In this work, I handle the application from the SQL to the CSS, so changes in the data structures are all me, so nobody's going to change variable names without me knowing. A generally valid concern, though.Dave Jacobhttps://www.blogger.com/profile/15052163927020492687noreply@blogger.comtag:blogger.com,1999:blog-145332831106508186.post-70284824263231741772012-07-10T11:18:31.266-04:002012-07-10T11:18:31.266-04:00Also consider the situation where you get a ton of...Also consider the situation where you get a ton of data from outside that you have to process periodically. Every once in a while, the person generating the the data sets decides that some variables, even though they the exact same definition as before, should be renamed, say from ClientNo to CLIENT_ID. This change is not documented anywhere or propagated to anyone by any other means. With <b>select *</b>, you'd notice this much later than if you had specified column names.Anonymoushttps://www.blogger.com/profile/07397041324572423579noreply@blogger.comtag:blogger.com,1999:blog-145332831106508186.post-48218764215220435082012-07-10T11:03:10.132-04:002012-07-10T11:03:10.132-04:00Here's my blog post on the topic.
Take a look...Here's <a href="http://petdance.com/2012/07/select-is-a-bug-waiting-to-happen/" rel="nofollow">my blog post</a> on the topic.<br /><br />Take a look at table aliases, Dave. Specifying "a.request_id" isn't nearly as odious as "accession.request_id".Andy Lesterhttps://www.blogger.com/profile/09755104360043101497noreply@blogger.comtag:blogger.com,1999:blog-145332831106508186.post-45310850830683275012012-07-10T09:34:07.985-04:002012-07-10T09:34:07.985-04:00Glad to help, Andy. I mostly mentioned you because...Glad to help, Andy. I mostly mentioned you because of my quest for an interesting title....<br /><br />For me, I'm much more worried about columns getting added, and having a firehose query in my db access function means I won't have to change it when I change the table.<br /><br />And, an increasingly large amount of my code is taking the hashref from the DB, serializing it to JSON and handling it to my Javascript, and many JS libraries might pull from the same source. (Not as many as should. I have reinvented wheels unnecessarily and repent.) <br /><br />My joy is the discovery of the mojo behind joins, which is preferable to my previous method of making an array of hashrefs by repeated queries, and I clearly could have selected accession.accession_id accession_id , accession.request_id request_id and so on without hindering the point of the post.Dave Jacobhttps://www.blogger.com/profile/15052163927020492687noreply@blogger.comtag:blogger.com,1999:blog-145332831106508186.post-45254709803161568832012-07-10T00:17:39.107-04:002012-07-10T00:17:39.107-04:00I just turned all that into a blog post on petdanc...I just turned all that into a blog post on petdance.com. Thanks for the kick in the butt. :-)Andy Lesterhttps://www.blogger.com/profile/09755104360043101497noreply@blogger.comtag:blogger.com,1999:blog-145332831106508186.post-59696951263077391702012-07-09T23:25:22.068-04:002012-07-09T23:25:22.068-04:00My point about "SELECT *" being a bug is...My point about "SELECT *" being a bug is more that it's a bug waiting to happen. It's sloppy. I'm all about being specific in what you're fetching from the database. "SELECT *" feels like leaving off "use strict".<br /><br />For instance, if I expect that I'm always going to have column Foo returned to me in my SELECT *, but column Foo gets dropped from the table, the SQL will still execute, and my client program will probably not notice and just think that $row['Foo'] is always returning NULL.<br /><br />Here's another example: You have a JOIN with two tables and it's just a SELECT *. Table A has Foo, and B has Bar and Bat. So you get back A.Foo, B.Bar and B.Bat as columns Foo, Bar and Bat. But later on, someone adds a Foo to table B. Now your SELECT * returns Foo, Bar, Bat and Foo. And A.Foo might be different from B.Foo. Again, no warnings. However, if you'd specified "SELECT foo, bar, bat FROM A JOIN B", then you'd get a warning about ambiguous column Foo. Or if you'd specified "SELECT A.foo, b.bar, b.bat from A JOIN B", then you can add whatever you want to B and your SELECT won't care.<br /><br />There are speed issues, of course. It is never slower to specify columns. It can ONLY be faster. See http://stackoverflow.com/questions/65512/which-is-faster-best-select-or-select-column1-colum2-column3-etc for much discussion.<br /><br />But for me, my main point is that it's all about making your intentions explicit, now and for the future.Andy Lesterhttps://www.blogger.com/profile/09755104360043101497noreply@blogger.com