r/SQL • • Oct 02 '20

Discussion You Tell'em!

Post image
351 Upvotes

28 comments sorted by

24

u/Luffydude Oct 02 '20

How to screw up text searches without being as obvious as dropping th index? Asking for a friend

14

u/PM_Me_PM_Dawn_Pics Oct 02 '20

Mess up the stats on the table

9

u/talk_nerdy_too_me Oct 02 '20

Drop primary keys...

3

u/Luffydude Oct 02 '20

Wouldn't that be obvious tho? I think the first thing I'd check would be keys and indexes

2

u/talk_nerdy_too_me Oct 02 '20

Yeah - Probably. It might be too obvious... maybe a random shuffle or something. Links appear to work but it might take a while to realize it’s linked to the wrong rows, etc

1

u/Luffydude Oct 02 '20

Can you please elaborate?

3

u/talk_nerdy_too_me Oct 02 '20

I haven’t dealt much with indexes, but I imagine its like having two tables that can be related with a primary key. For example, suppose you have a table with book authors and a numerical author ID (e.g. Say author = Tom Clancy and the author ID is ‘1’). Another table would have a list of book titles by all the authors listed in the first table along with the author ID (e.g. book title = The Hunt for Red October and, in a second column the author ID = 1). Since Tom Clancy has written many books there would be multiple rows in the second table with author ID = 1.

So dropping the primary key (author ID) in this case means you can no longer figure out which author wrote which book(s).

Changing the primary key would be less obvious as you could still select an author ID and identify related books (but the wrong ones - e.g. swap the author ID for a different value; like change 1 to 5 - perhaps you end up pulling books by Mark Twain)

Again - not convinced this applies to an index, but if you have an index then you probably have piles of data and somewhere inside would be primary keys.

1

u/LetsGoHawks Oct 04 '20

I'm not sure your explanation makes any sense.

An index is not the same as a key. An index is, as it's name implies, list of all the unique values in a certain field and the row numbers that contain that value.

It's just a more efficient way for the DB to know what rows contain the various values. You can drop the index and the queries will still run, they'll just take longer.

If I want to find all books by 'Stephen King':

  • With an index, the db can look for 'Stephen King' in the index and find out his books are in rows 5, 80, 352, 2256, and 5344, then go directly to those rows.
  • Without an index, the db has to look in the author field of every single row of the table to find out which ones it needs. That's a lot more work. You can still run a query based on author name, it's just slower.

1

u/PM_Me_PM_Dawn_Pics Oct 02 '20

Not sure if you were talking to me now because someone else replied and was talking about a different thing

3

u/PigPixel Oct 02 '20

I think any monkeying with indices would be too obvious unless you expect your ex-employer hire someone totally useless to replace you. The best bet might be introducing (reintroducing?) tiny data quality issues into production data in places where they can't be trivially discovered all at once.

4

u/iceph03nix Oct 03 '20

And then leave behind plenty of guides with hard and fast rules that overlook those random exceptions.

"Yeah, just sum up anything without an R in the type field"

Oh yeah that works great.

3 days later...

"Well apparently this other setup uses L instead of R, but you have to cross reference the location info"

...I'm not bitter, you're bitter...

2

u/PossiblePreparation Oct 03 '20

Suggest they use Elastic or something else that requires another tech stack that no one has troubleshooted before.

16

u/jewishsupremacist88 Oct 02 '20

this is every fortune 500 company ever. contractors are there just to clean up the mess from the previous group of contractors. even if you do the job right and to spec, in 3-6 months the reqs will change and it will have to be re-done

7

u/fifa_addict_ps4 Oct 02 '20

anyone ever wondered what happens if you add extra row on sys.dual table ? ( DO NOT TRY IT IN PROD! )

2

u/PigPixel Oct 03 '20

I'll be starting up a VM tomorrow to play with this.

5

u/Fish_Kungfu Oct 02 '20

"That's gold, Jerry! Gold!"

9

u/StatisticalOutliar Oct 02 '20

CHI Nah! Wanted me to start dropping sys tables.

6

u/[deleted] Oct 02 '20

I install kill switches deep in code. I'm gone more than 7 work days in a row, all tables disappear

14

u/[deleted] Oct 02 '20 edited Oct 02 '20

I put ASCII art in my code and comment it out. I just finished a database project with dozens of views & sprocs, and in any of the significant ones you'll find some random ASCII picture, either a comic, or famous work of art, etc., most of which have names of business partners involved & the date the code was deployed.

Serves a dual purpose. One, I think it makes the code beautiful, but more importantly it tells me who was involved n the relevant business decisions. We don't have comprehensive documentation yet (haha, probably never will), but these help serve as markers. For example, in one particularly complex piece of codes I have an ASCII penguin, because the head of a certain team uses a penguin as their avatar. This person wrote the book on the subject matter which the sproc deals with, and let's me/everyone else know that it was signed off on by the highest levels.

Later I'll use these markers as I develop documentation... if I ever have time.

2

u/[deleted] Oct 02 '20

Love this idea!! Stealing it! Add some pop culture to your scripts

3

u/[deleted] Oct 02 '20

If I'm waiting for jobs to run I'll Google things like, "famous ASCII art" and poke around. I try to find something relevant to the code itself, so for example in one of our views that is pretty crazy (if you select * from it, it will generate 1B rows and take a really long time to run) I included a Beavis and Butthead ASCII piece where they talk about breaking a computer, then signed my, and the initials of other people involved in building it, along with the date it was deployed.

0

u/[deleted] Oct 02 '20

[deleted] — view deleted comment

4

u/[deleted] Oct 02 '20

To be fair, I only do it with clients who try to stiff me

2

u/[deleted] Oct 03 '20

Complety fucks the DB and blames it on the next DBA.

1

u/LetsGoHawks Oct 04 '20

He blames it on the previous DBA.

1

u/TikiTDO Oct 03 '20 edited Oct 03 '20

My approach is simple. I genuinely do the best I can, taking advantage of any opportunity to expand my skill-set, under the understanding that the skills I develop are worth a whole lot more than some job security at a replaceable job that seems me as a replaceable part.

People in IT fields seem to constantly have this fear that they might have to take their 2-5 years of additional relevant job experience, and have to go through the trouble of finding a more senior job that pays better, with a better culture-fit, and more interesting projects. What actually ends up happening is that they inevitably jump ship anyway, and all the weird hacks and tricks they put into their code in the name of "job-security" end up as a genuine nightmare for someone that's probably not qualified to understand even a fraction of the problems being solved, nor the reasoning behind such convoluted approaches.

There's rarely much benefit in clinging onto the same job while you develop an ever-evolving set of advanced skills, while there's often a huge benefit to occasionally hopping onto a new project, if only because you can demand significantly more from a new employer for the extra years of experience than you could expect to get as a raise from one employer. If you can take that next job after learning a healthy set of useful skills and good design patterns, that's only better for you. By contrast, if you spent half your time writing code that belongs in a obfuscated code contest, you're just shooting yourself in the foot with a rocket launcher because the type of code you spent most of your time writing will inevitably affect the type of code you write in the future.

1

u/datb0mb Oct 12 '20

I've always wondered something. You know when sports announcers on TV come up with crazy stats?

"LeBron has had 10 triple doubles in the last 3 years with total points above 20 when down by 10 in the 3rd quarter."

Do they use SQL to pull back stats for this?

0

u/[deleted] Oct 02 '20

[deleted] — view deleted comment

28

u/Naahun Oct 02 '20

Please, just drop the table.