Searching SQL with no index.

Query for the clients and some people

I worked at a place where the help desk guys did the good ol' "I'll send an email from your laptop if you walk away without locking it and tell everyone lunch is on you" routine. After it happened to me about 3 times I was like, "I gotta get this help desk prick back!" So after several failed attempts at walking by his pc when he walked away it instantly hit me how I can punk him back.....SO, I logged onto SQL Server, clicked open a new query window and typed up a dbmail command and on the @from parameter I set it to the help desk guy's email address. His face was PRICELESS when I was shooting off emails to the entire IT dept on behalf of him WHILE he was sitting in front of his PC. Lesson is: don't fuck with dev help desk dude! 😎😜

I've seen native SQL query in JavaScript sent to the backend to execute

Attended one of the best meetups ever. To give you an idea how awesome it was..

Speaker took the first ~20 minutes introducing himself.
His intro card deck kept referring to himself in the third person (he is the only employee in consulting 'company'). Ex. "Mr. Smith began his humble career .."

The powerpoint presentation began with him clicking each page, not executing the slideshow (ex. pressing F5).
Finally someone asked "Can you make slide bigger?"

S:"You can't read that?..um..sure...I guess .."
Starts fumbling around the zoom ...
Dev: "No, can you start the slideshow?"
S: "I don't know what you mean...there...I zoomed it, is that better? Now I can't see my notes..just sec.."
<fumbles again with the zoom>
Dev: "No, not zoom, start the slide show, press F5"
S: "Oh...you want me to F5 it...OK..."
<he *clicks* the slide show button>

Finally getting into code, trying to get out of powerpoint ...
S: "How do I get out of this fullscreen?.."
Dev: "Hit escape"
S:"No..um.."
<keeps trying to click on 'something'>
S:"I see visual studio, but its not on the big screen... "
<keeps click on 'something', no one is sure whats going on>
Dev: "Hit Escape to stop the slideshow"
<finally hits escape, then able to put Visual Studio on the big screen>
S: "Ahh...there, I figured it out."

Speaker had no end of making wild/random statements like:
".Net Core is the future of Microsoft, if you're using .Net 4.5...forget it, its not even supported anymore."
"When I was at Microsoft Build, I asked them why not put all the required .Net assemblies in one directory. Looks like with .Net Core, they listened to me" (he was serious)
"I don't use SQL Server Mgmt Studio. Its free and it sucks. I use <insert a very expensive SSMS clone>, its great, you guys should check it out", then proceeds to struggle to open a query window to write some SQL.
"When you use .Net Core and EntityFramework, you have to write your own stored procedures. If a developer can't write stored procedures, he shouldn't be in this business."

I was on the edge of my seat, hungry for the next crazy bat-shit thing to come out of his mouth. He did not disappoint. BEST MEETUP EVER!

When you see a web service API accepting a SQL query in one of its JSON fields and the evil starts growing within you..

DROP ALL DATABASES

Just because you can!

Time for an actual rant:

During an internship I heard from my PM that my assignment for the week after was going to be working on a specific sql query to add some features and fix some bugs.

When talking with colleagues about that assignment later, they laughed and referred to the query as the "query of doom" (QoD), naive as I was back then, I thought that one of my colleagues had the QoD displayed on his screen because the query he was working on looked rather large (about 20 lines). They all laughed and told me I was in for a treat.

Starting my assignment the week after I was horrified to find out the QoD was huge, and by huge I mean, printing that specific query resulted in 8 A4 pages font size 10, front and back.
There were over a 100 union statements, no proper aliases, no documentation, not a single foreign key in the entire database, naming that makes no sense. And everything written manually by 10 different developers over the past years, who all fell of the face of the earth.

And this was only the query of doom. The entire product was a complete clusterfuck of forms with a queries directly behind action buttons, because we weren't allowed to make classes (yes you read that correctly. We couldn't make classes, unless we had a very compelling reason). Everything was created by over 30 different devs who only managed to stay just long enough to get some work done.

And all of this was the result of a PM who didn't believe in frameworks, ORM's, OOP, classes, ... because that made the software slow. To this day he still manages that product, but I'm glad that I quickly decided to move on.

Every hour or so someone shows up at my desk requesting a query to be executed.

I feel like I'm just a human-sql interface.

23 joins in one SQL query, aliased from a to t kill me now.

Why did the SQL query stop working?
.
..
...
....
.....
It was part of a UNION.

When I get home, my wife will probably tell me about all the fun things she did with the kids today. She'll tell me about all the frustrating things they did too and stories about how they made her almost pull her own hair out.

Then 20 minutes later she'll ask me how my day was and I'll say, "Oh, I dunno. I worked on a really hard SQL query today..."

Customer : c
Me : m

*Few weeks ago*
C: the server is slow, it sometimes takes 7 seconds before I see our data

(the project is 7+ years old and wasn't written by someone who is very good in SQL)

M: yeah I see that, our servers are busy with this one "process" (SQL query)
C: make it faster
M: well that's possible but it will take a few days (massive SQL spaghetti that I first have to untangle)
C: 😡 nvm then

*Yesterday*
C: server is down !
M: 🤔 *loads data from server and waits ~ 7 seconds*
M: Well what's the problem?
C: I need the data but it's so slow

WELL YOU MINDLESS IMBECILE... If something is slow it doesn't mean our god damn production server is down !
That just means that you have to give us a day or two so we can optimise the (ALSO BY YOUR REQUEST) rushed project... And save you YOUR money that YOU waste on the processing time on our server...

Fuck fucking fuck just spent hours and hours looking why I was getting a fatal error. I overlooked a missing bracket in an SQL query. MOTHERFUCKING FUCK FUCKING FUCK FUCK FUCK FUCK

My boss is a grumpy 25 year oldish "Mr. I know it all". We all hate him for that attitude.
Just joining recently, the code base which I got introduced to was totally new and I was overwhelmed.
Boss told me to write an Sql query to wipe the table data. I being reckless wrote a query to wipe the table only and submitted it to my boss.
Few hourse later we were informed by our peers that a certain url was not working. On further investigating we found out that my boss carelessly copy and pasted my query and executed it which wiped an important table clean.
Now he doesn't talk to me straight and I can't look him in the eye because obviously I burst into laughter.
Job well done☺️

Guy I work with: Hey can I borrow you for a minute

Me: sure. What do you need?

Him: so this is a project me an the other dev worked on

Me thinking: Well I know he did it all and sent you the project so don't tell me you worked on it

Him: so we use it to do this and this and send an email to this new account I made because (2 minute explanation)

Me thinking: I don't care. Just tell me what your issue is! I already know what it is and does from what you told me the last time when you showed me. Which took an hour of my time.

Him: so he sent me this code which is called <Descriptive name> and in the method we have variables call <descriptive name> and it returns a <variable name>

Me thinking: You mother fucker! I don't give a shit what your method is named, what it the variable names are, and you don't need to read through every line of code to me! Just from the descriptive name you just said I know what it does! What the fuck is your issue!?

Him: we also have these other methods. This one is called <Descriptive name> which does...

Me: are you fucking seriously going to read me your code line by line and tell me what you named your variables AGAIN!?

Him: and we named this one <descriptive name>

Me: you mother fucker...

Him: and it calls this stored procedure. (Literally opens the stored procedure and shows me) and it is called...which has parameters called... And it is a select query that inserts

45 minutes later after he finishes explaining all 3 pages of his code and his 5 stored procedures that the other dev wrote...

Him: So anyway, back to this method. I need to know where to put this method. The other dev said to put it in this file, but where do you think I should put it in here? Should I place it after this last one or before it?

Me thinking: You fucking wasted my fucking time just to ask where to place your mother fucking method that the other dev sent to you in a project with only 3 files, all less than 500 lines of code with comments and regions that actually tell you what you should put there and 5 small stored procedures that were not even relevant to your issue! Why the fuck did you need to treat me as a rubber ducky which would fly away if you did have one because you didn't have an issue, you just didn't know where to put your fucking code! FUCK YOUR METHOD!

Me: Where ever you want

Him: Well I think it won't work if I placed it before this method.

I walked away after that. What a waste of time and an insult to my skills and really unchallenging. He's been coding for years and still can't understand anything code related. I'm tired if helping him. Every time he needs something he always has to read through and explain his shit just to ask me things like this. One time he asked me what to name his variable and another his project. More recently he asked why he couldn't get his project he found online to work. The error clearly stated he needed to use c# 7. His initial solution was to change his sql connection string. 😑

He should just go back to setting up computers and fixing printers. At least then he would never be in the office to bug me or the other dev with things like this.

The gift that keeps on giving... the Custom CMS Of Doom™
I've finally seen enough evidence why PHP has such a bad reputation to the point where even recruiters recommended me to remove my years of PHP experience from the CV.

The completely custom CMS written by company <redacted>'s CEO and his slaves features the following:
- Open for SQL injection attacks
- Remote shell command execution through URL query params
- Page-specific strings in most core PHP files
- Constructors containing hundreds of lines of code (mostly used to initialize the hundreds of properties
- Class methods containing more than 1000 lines of code
- Completely free of namespaces or package managers (uber elite programmers use only the root namespace)
- Random includes in any place imaginable
- Methods containing 1 line: the include of the file which contains the method body
- SQL queries in literally every source file
- The entrypoint script is in the webroot folder where all the code resides
- Access to sensitive folders is "restricted" by robots.txt 🤣🤣🤣🤣
- The CMS has its own crawler which runs by CRONjob and requests ALL HTML links (yes, full content, including videos!) to fill a database of keywords (I found out because the server traffic was >500 GB/month for this small website)
- Hundreds of config settings are literally defined by "define(...)"
- LESS is transpiled into CSS by PHP on requests
- .......

I could go on, but yes, I've seen it all now.

Today, for fun, I wrote prime number generation upto 1000 using pure single MySQL query.

No already created tables, no procedures, no variables. Just pure SQL using derived tables.

So does this mean that pure SQL statements do not have the halting problem?

Putting an EXPLAIN over the query I could see how MySQL guessed that the total number of calculations would be 1000*1000 even before executing the query in itself and this is amazing ♥️

I have attached a screenshot of the query and if you are curious, I have also left below the plain text.

PS this was a SQL problem in Hackerrank.

MySQL query:

select group_concat(primeNumber SEPARATOR '&') from
(select numberTable.number as primeNumber from

(select cast((concat(tens, units, hundreds)+1) as UNSIGNED) as number from

(select 0 as units union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) unitsTable,
(select 0 as tens union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) tensTable,
(select 0 as hundreds union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) hundredsTable order by number) numberTable

inner join

(select cast((concat(tens, units, hundreds)+1) as UNSIGNED) as divisor from

(select 0 as units union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) unitsTable,
(select 0 as tens union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) tensTable,
(select 0 as hundreds union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) hundredsTable order by divisor) divisorTable

on (divisorTable.divisor<=numberTable.number and divisorTable.divisor!=1)
where numberTable.number%divisorTable.divisor=0
group by numberTable.number having count(*)<=1 order by numberTable.number) resultTable;

I recalled a seemingly simple task I took on.

We were building a booking system, and I had to figure out how to retrieve bookings by a certain date range.

Upfront, the tasked seemed simple until I realised I had to both figure out the logic and the SQL statements needed to retrieve all bookings within a certain date range in one query.

I ended up drawing a model to help me visualise the various date-range criteria to be satisfied. And used unit tests to help me think through each date range criterion and make sure they were accurate. Some were obviously from paranoia, but better to be safe than sorry...

After that, I had wrote down raw SQL directly into Sequel Pro first to make sure my query logic was accurate too, before translating into something the ORM equivalent. This was when I learned how to define and use variables in SQL. The variables were throw-away code; I just didn't want to have to hard-code the test date-ranges over and over again; minimise chances of spelling errors.

Needless to say, felt my problem-solving skills went up one level after this task. Saw my coding style and unit tests improve. And also the thought processes that go into how to maintain code quality...

Forgive me father, for I have sinned.  Alot actually, but I'm here for technical sins.  Okay, a particular series of technical sins.  Sit your ass back down padre, you signed up for this shit.  Where was I?  Right, it has been 11429 days since my last confession.  May this serve as equal parts rant, confession, and record for the poor SOB who comes after me.

Ended up in a job where everything was done manually or controlled by rickety Access "apps".  Many manhours were wasted on sitting and waiting for the main system to spit out a query download so it could be parsed by hand or loaded into one of the aforementioned apps that had a nasty habit of locking up the aged hardware that we were allowed.  Updates to the system were done through and awful utility that tended to cut out silently, fail loudly and randomly, or post data horrifically wrong.

Fuck that noise.  Floated the idea of automating downloads and uploads to bossman.  This is where I learned that the main system had no SQL socket by default, but the vendor managing the system could provide one for an obscene amount of money.  There was no buy in from above, not worth the price.

Automated it anyway.  Main system had a free form entry field, ostensibly for handwriting SELECT queries.  Using Python, AutoHotkey, and glorified copy-pasting, it worked after a fashion.  Showed the time saved by not having to do downloads manually.  Got us the buy in we needed, bigwigs get negotiating with the vendor, told to start developing something based on some docs from the vendor.  Keep the hacky solution running as team loves not having to waste time on downloads.

Found SQLi vulnerability in the above free form query system, brought it up to bossman to bring up the chain.  Vulnerability still there months later.  Test using it for automated updates.  Works and is magnitudes more stable than update utility.  Bring it up again and show the time we can save exploiting it.  Decision made to use it while it exists, saves more time.  Team happier, able to actual develop solutions uninterrupted now.  Using Python, AutoHotkey, glorified copy-pasting, and SQLi in the course of day to day business critical work.  Ugliest hacky thing I've ever caused to exist.

Flash forward 6 years.  Automation system now in heavy use acrossed two companies.  Handles all automatic downloads for several departments, 1 million+ discrete updates daily with alot of room for expansion, stuff runs 24/7 on schedule, most former Access apps now gone and written sanely and managed by the automation system.  Its on real hardware with real databases and security behind it.

It is still using AutoHotkey, copy-paste, and SQLi to interface with the main system.  There never was and never will be a SQL socket.  Keep this hellbeast I've spawned chugging along.

I've pointed out how many ways this can all go pearshaped.  I've pointed out that one day the vendor will get their shit together they'll come in post system update and nothing will work anymore.  I've pointed out the danger in continuing to use the system with such a glaring SQLi vulnerability.

Noone cares.  Won't be my problem soon enough.

In no particular order:
Fuck management for not fighting for a good system interface
Fuck the vendor for A) not having a SQL socket and B) leaving the SQLi vulnerability there this long
Fuck me for bringing this thing into existence

So just about to head to the pub and I got the dreaded call from my boss.

The support team had developed some fixes. They "tested" and deployed without letting us know... And you guessed it there was failures all over the shop!

So it turned out their testing was running on a local base install with no integration compared to the live system with 15 years of customisation and complex integration. My they thought this was acceptable I don't know...

And the best part was the developers who made the changes didn't understand their own code (I found the tutorial they copied online) they just blindly copied it without understanding how it worked!

So 4 hours later we found the bug, nothing like having a query and s SQL connection but not executing the query....

There goes my Saturday evening. Now we're was my beer!

> TeamLeader1: I just discovered SQL is actually super fast! The low responsiveness I've experienced comes from our ORM!
> IHateForALiving: well of course SQL is blazingly fast. SQL has been refined by the best engineers in the world for the past 50 years, its performances are unparalleled for everything you could possibly need, unless you want to scale REALLY big. Sequelize, instead, is an Active Record ORM, so it's bound to struggle with huge amount of data, because every single row will get attached a significant amount of black magic to make sure everything syncs correctly. Why is that?
> TeamLeader1: I have a problem with this frontend component, it doesn't allow pagination. I tried downloading the whole DB to bypass that, but the ORM is slow... so I will bypass the ORM and download the whole table with a raw query. Look at that! It works like a charm, it's super duper fast!'

This mf is downloading some 35 thousand rows every time some user loads a page because he doesn't know how to paginate the fucking table with Angular, there's no way these people are real.

Today my boss told me that i missed the '*' in the delete from x SQL query..

SQL gives me a hard on right now.

Two tables, 954 rows and 9414, connected via foreign keys and shit.

SELECT a_id,name,shit FROM table1 a JOIN table2 b ON a.id = b.a_id WHERE ((lower(name) LIKE '%lorem%') OR MATCH(name) AGAINST('lorem' WITH QUERY EXPANSION) OR name SOUNDS LIKE 'lorem')

and you got fuzzy search with resolved keys to another table in 0.047 sec, fuck me dude.

Not a rant about anything in particular. Just a summary of some feelings stored in the hateful part of my heart.

Developing for Android: Add this third-party library to your Gradle build. Use (this) built-in Android class to make the thing work.

*Clicks link

Deprecated since API version SUCKMYDICK-7. Use (this) instead

*Clicks link

Deprecated since API version LICKMYBALLS-32. Use...

Developing for Windows: Please use (this) API call. It was literally already available before Bill Gates was born. Carbon dating has placed this item to older than the universe itself and it is likely the entry point for the big bang. It is also still the best way to accomplish (task).

Developing for Linux: "Hmm, I wonder how to use this"

> > > Some shitty mailing list in small blue monospace font tells you to reference a man page that is three versions behind but the only version available.

What? Those three sentences didn't explain it enough? Well, maybe you aren't cut out for this type of thing.

JavaScript: you know how it is.

SQL: You expect a decent-quality answer from stack overflow but you always get an outdated and hacky response and it's using syntax from Microsoft SQL. You need MySQL.

C#: A surprising number of Microsoft forum results ranking high on Google. You click on one in hopes that it will be of any sort of quality. You quickly close the tab and wonder why you ever even had hope.

Literally any REST API: Is it "query" or "q"? "UserID" or "user_id"? Oh, fuck, where's the docs again?

You thought you escaped JavaScript, but it was a trick!: Some bullshit library you downloaded to make your other library work redefined one of the global variables in the project you inherited. Now you get 347 "<x> is not a function" errors in your console. Good luck, asshole.

FontAwesome/ Material fonts/ Any icon font pack: You search "Close" for a close button icon. No results. You search "Simplified railroad crossing sign without the railroad". You get a close icon.

I think that's all of my pent up rage. Each of them were too small for an individual rant so I had to do this essay.

Arts and crafts: developer version.

Context: spent all day making an ER diagram from an awful SQL query I was handed. Got sick of asking for it from the contractor who made the DB.

Yes, that's one query.

YAY a JOIN query (MySQL) just ran successfully at the first try for the first time of my life!! I never understood joins but slowly getting there I guess :)

A SQL query goes into a bar, walks up to two tables and asks, "Can I join you?"

Such a classic joke. Apt for a Friday! :D

Senior showing fellow intern what SQL injection is on the app the intern created :
Senior : "then I hit enter and the query get executed and...
Intern : "don't you dare hitting enter!!!"

I've optimised so many things in my time I can't remember most of them.

Most recently, something had to be the equivalent off `"literal" LIKE column` with a million rows to compare. It would take around a second average each literal to lookup for a service that needs to be high load and low latency. This isn't an easy case to optimise, many people would consider it impossible.

It took my a couple of hours to reverse engineer the data and implement a few hundred line implementation that would look it up in 1ms average with the worst possible case being very rare and not too distant from this.

In another case there was a lookup of arbitrary time spans that most people would not bother to cache because the input parameters are too short lived and variable to make a difference. I replaced the 50000+ line application acting as a middle man between the application and database with 500 lines of code that did the look up faster and was able to implement a reasonable caching strategy. This dropped resource consumption by a minimum of factor of ten at least. Misses were cheaper and it was able to cache most cases. It also involved modifying the client library in C to stop it unnecessarily wrapping primitives in objects to the high level language which was causing it to consume excessive amounts of memory when processing huge data streams.

Another system would download a huge data set for every point of sale constantly, then parse and apply it. It had to reflect changes quickly but would download the whole dataset each time containing hundreds of thousands of rows. I whipped up a system so that a single server (barring redundancy) would download it in a loop, parse it using C which was much faster than the traditional interpreted language, then use a custom data differential format, TCP data streaming protocol, binary serialisation and LZMA compression to pipe it down to points of sale. This protocol also used versioning for catchup and differential combination for additional reduction in size. It went from being 30 seconds to a few minutes behind to using able to keep up to with in a second of changes. It was also using so much bandwidth that it would reach the limit on ADSL connections then get throttled. I looked at the traffic stats after and it dropped from dozens of terabytes a month to around a gigabyte or so a month for several hundred machines. The drop in the graphs you'd think all the machines had been turned off as that's what it looked like. It could now happily run over GPRS or 56K.

I was working on a project with a lot of data and noticed these huge tables and horrible queries. The tables were all the results of queries. Someone wrote terrible SQL then to optimise it ran it in the background with all possible variable values then store the results of joins and aggregates into new tables. On top of those tables they wrote more SQL. I wrote some new queries and query generation that wiped out thousands of lines of code immediately and operated on the original tables taking things down from 30GB and rapidly climbing to a couple GB.

Another time a piece of mathematics had to generate all possible permutations and the existing solution was factorial. I worked out how to optimise it to run n*n which believe it or not made the world of difference. Went from hardly handling anything to handling anything thrown at it. It was nice trying to get people to "freeze the system now".

I build my own frontend systems (admittedly rushed) that do what angular/react/vue aim for but with higher (maximum) performance including an in memory data base to back the UI that had layered event driven indexes and could handle referential integrity (overlay on the database only revealing items with valid integrity) or reordering and reposition events very rapidly using a custom AVL tree. You could layer indexes over it (data inheritance) that could be partial and dynamic.

So many times have I optimised things on automatic just cleaning up code normally. Hundreds, thousands of optimisations. It's what makes my clock tick.

Awkward recruiting process? Sit the fuck back!

So about a year ago I got laid off. I got some help setting up LinkedIn and realising I'm not trash and offers to talk started flowing in.

So this consultancy firm asks me to come in for a talk and having nothing better to do I oblige - they're working on big, exciting Greenfield stuff and I'm amazed they want me.
Fast forward the most nervous week in my life and the HR assistant brings me into the meeting room, I get some water and a nice first impression - also my last. I wait in the room for five minutes.

In walks madam HR, madam Team lead and miss assistant from before, all carrying big ass laptops. We shake hands and they sit down and all open up their laptops between me and them - I just sit there feeling naked with my block of paper and pencil I brought.

So we wait for their machines to start up and madam HR just starts throwing questions at me and seemingly noting my answers into a sheet. Meanwhile madam Teamlead is busy on her phone most of the time and my most human interaction remains smalltalk and questions between me and miss assistant.

I did manage to get madam Teamlead to look up from her phone when I asked how they felt about the fact that I have no formal training and would need to pick up a lot of skills as we go, to which she said something along 'well this ain't a candy shop, we expect you to work' and looked back down at her phone.

A bit shaken, I agreed to stay for the technical test (apparently I passed the interview...)
Now this test was designed by their CTO since he didn't feel like any of the available tests on the market could properly judge applicants' skilllevels. Yes, alarms went off already at that point.

What I'm presented with is a word document with questions, and another for answers and... It's just string gymnastics and reference/value difference knowledge - shit it takes you a split second to look up or test if you ever get into these insane cases where you need to know. And then there was a likewise one with sql statements that was also just convoluted query gymnastics and trying to hide changes in the seemingly same statement through various questions. No questions on design, no problem solving, just... Attention span testing with a dash of coding?

Anyway, it turned out they had evening and weekend shifts and round the clock support tournus which on top of the ridiculous recruitment process and way lower than average salary offer had me turn them down.

Don't enable bullshit people, run away!

According to my predecessor, nothing showcases your SQL skills quite like generating the entire page (markup, JavaScript and all) from a single 2500 line query.

I messed up carelessly in production. Learnt how SQL queries bite you in the ass when it knows you are under pressure.

Was hosting an online quiz kinda thing during my college techfest. Tens of thousands of people participating.

Using MySQL as database and thousands of queries were being executed. Everyone were pretty excited as the event just opened up.

None of the teams could solve one particular level. Turns out the solution was wrong and was asked by the organisers to change the solution for that particular level. Usual stuff, right?

Was too lazy to open up the web UI for the back office and so, straight ahead logged in to the MySQL server and ran the UPDATE query on the table consisting of the solutions.

It had been a couple of hours and the organisers came to me with a weird problem. There were no changes in the scoreboard for the last two hours. Everyone were stuck wherever they were. Weird, right?

I then realized.

Fk.

In that dreaded query, I had only run

UPDATE 'qa' SET answer = 'something'

leaving out the where clause, specifying the question to update, like

WHERE qno=13

As a result, solutions to all the questions were updated to the same answer. After hastily fixing everything back, I had the dreaded conversation.

Org: What was the problem?

Me: It was the cache.

Org: Damn thing. Always messes up.

Me: *sheepishly* yeah

Probably the most embarrassing moment in my life, wrt coding 😑

Every once in a while, execute a SQL update query on a production DB without a where clause.

Three months into a new job, as a senior developer (12+ years experience) and updated an import application.

With one small update query that didn't account for a possible NULL value for a parameter, so it updated all 65 million records instead of the 15 that belonged to that user.

Took 3 people and 4 days to put all the data back to it's original state.

Went right back to using the old version of the apllication, still running 2 years later. It's spaghetti code from hell with sql jobs and multiple stored procedures creating dynamic SQL, but I'm never touching it again.

Today I come across something interresting in SQL Server.

I was optimizing a report query and in the SSMS windows runned in 10 seconds for 3000 rows.

Put it to a stored procedure took me 5 minutes for getting 100 rows.

I was like WTF?

After some research I found out that the problem was that I was using the Stored Procedure parameters in the query.

Created local variables for the parameters and poof... 10 seconds again.

So if you are creating Stored Procedures in SQL Server DO NOT USE THE PARAMETERS FROM THE PROCEDURE. CREATE LOCAL VARIABLES.

Grr the feeling when one of your interviewers has a hard-on for trying to find ways to sink your boat.

Went to a job interview yesterday during my lunch break for a mid level dev job in central London , i have been trying to transition from a junior role.

First were two senior devs , that went quiet well...

Next up was the tech lead and a team lead, lets call the latter Mc-douche for some problem

The tech lead was fine, very relaxed and clam guy more interested in seeing the logic of my answers and questions as to why i did certain things in this or that manner....

Mc-douche, he would always try to find something wrong then smile smugly and do that sideways head waggle thing

His tech lead is like " yup that's correct"
But he would be like " yeeess but you didn't think about bla bla bla" then talk about shit not even present in the context of the question

Ah also he would ask a question then cut me off as soon as I begin to say that i didnt mention or take into account x or y even though literally my next sentence is about address those details he wanted.
let me fucking finish you dickbag 😡

Had a js question, simple stuff about dom manipulation, told not to bother with code... yet McD starts asking me to write the code for it....managed it , quite easy stuff

Then a sql and db test , again technlead was happy with the answers and the logic am approaching the question when writing my query, yet mc d Is bitching about SQL syntax....
Ok fine, i made a simple mistake, I forgot and used WHERE instead of HAVING in a group by but really?! Thats his focus ?!

Most devs I know look up syntax to do stuff , they focus on their logic first the do the impl.

Then a general question on some math and how i would code to impl a solution on paper
That was a 20 mins one, the question said they didn't expect me to finish it totally so
I approached it like an exam question.

First
I focussed on my general flow of my process, listing out each step.

Then elaborated each step with pseudo code showing my logic for each of the key steps.

Then went deeper and started on some of the classes and methods , was about to finish before it was time up.

Mc douch went through my solution
And grudgingly admitted my logic was "robust enough" it was like he really had to yank that deep out of his colon.

I didn't really respond to any of his rudeness throughout the whole interview,i either smiled politely or put on a keen looking poker face.

Really felt awful the rest of the day, skipped the gym and went home after work, really sucks to have a hostile interviewer.

Pretty sure i wont be hearing anything good from them even though the three other interviewers were happy with me I felt.

Is prompt engineering going to be the next "big thing"?

Very related fun fact: SQL was meant for business people so they can quickly generate reports w/out needing to rely on programmers. (When was the last time your CEO did a DB query on prod I wonder?!)

At a Magic: the Gathering prerelease tournament (yeah, yeah, stereotypes), on my phone, with a pen and paper copy of the SQL query, as the phone screen was too small to read it properly in full. Managed to fix the bug in the query about 30 seconds before the next game started.

The debugging went well, but the tournament did not; I think I was a bit distracted!!

SQL query walk into a bar and sees 2 tables;

approaches them and says, can I JOIN you?

I find a poor tester copy/pasting data from the test environment to the live one, as he accidentally broke it. I ask the DBA, " why isn't syncing SQL records part of the deployment pipeline?"

"You're front end. This is my job. Go do your job."

"... but it's an easy query, and you're exposing us to human error."

"You need to go sit down."

Dear Microsoft Kusto Query Language (KQL)

Screw you. You suck like more than a sudden depressurization event in an airplane. Creating your own freaking query language is bad, the people who invented SQL based it on a the principles of mathematical relational algebra, which although confusing, and not suited for all use cases is at least consistent.

You were invented by a bunch of oxygen deprived halfwits based on the principles of sadism and incompetence.

The only situation in which I would voluntarily use KQL as my tool of choice is if my purpose was to extract a Dantesque style revenge on someone who had committed grievous harm to myself and my family members. In that case forcing them to work with you day in and day out would still border on cruel and unusual punishment.

Sincerely, A developer who has spent the past 2 hours dealing with your Lovecraftian madness.

P.S. I hope you choke on a raw chicken bone and no one gives you CPR.

I was to optimise a SQL query (7 min to execute,yes) with around 20 joins (I did not write this). Checked for missing indexes,etc.. but nothing worked. Stared outside the window, and back to desk reordered the joins ,executed in 10 secs.

So these motherfuckers... they have stored the queries to generate the reports... fucking guess where. Just guess.

They stored the queries AS FUCKING STRING DATA IN THE TABLE. And you know how they get the parameters? A FUCKING JOIN WHERE THEY HAVE STORED THE PARAMS AS DATA IN ANOTHER TABLE.

So you query set the params to query to get the query to get that is joined with a query to get the reports.

If God is a programmer after all y’all are fuuuuuuuuuuuuuucked

I had spent the last year working on a online store power by woocommerce with over 100k products from various suppliers. This online store utilized a custom API that would take the various formats that suppliers offer their inventory in and made them consistent. Now everything was going swimmingly initially, but then I began adding more and more products using a plug-in called WP all import. I reached around 100k products and the site would take up to an entire minute to load sometimes timing out. I got desperate so I installed several caching plugins, but to no avail this did not help me. The site was originally only supposed to take three to four months but ended up taking an entire year. Then, just yesterday I found out what went wrong and why this woocommerce website with all of these optimizations was still taking anywhere from 60 to 90 seconds to load, or just timing out entirely. I had initially thought that I needed a beefier server so I moved it to a high CPU digitalocean VM. While this did help a little bit, the site was still very slow and now I had very high CPU usage RAM usage and high disk IO. I was seriously stumped the Apache process was using a high amount of CPU and IO along with MYSQL as well. It wasn't until I started digging deeper into the database that I actually found out what the issue was. As I was loading the site I would run 'show process list' in the SQL terminal, I began to notice a very significant load time for one of the tables, so I went to go and check it out. What I did was I ran a select all query on that particular table just to see how full it was and SQL returned a error saying that I had exceeded the maximum packet size. So I was like okay what the fuck...
So I exited my SQL and re-entered it this time with a higher packet size. I ran a query that would count how many rows were in this particular table and the number came out to being in the millions. I was surprised, and what's worse is that this table belong to a plugin that I had attempted to use early in the development process to cache the site. The plugin was deactivated but apparently it had left PHP files within the wp content directory outside of the actual plugin directory, so it's still executing scripts even though the plugin itself was disabled. Basically every time I would change anything on the site, it would recache the whole thing, and it didn't delete any old records. So 100k+ products caching on saves with no garbage collection... You do the math, it's gonna be a heavy ass database. Not only that but it was serialized data, so when it did pull this metric shit ton of spaghetti from the database, PHP then had to deserialize it. Hence the high ass CPU load. I had caching enabled on the MySQL end of things so that ate the ram. I was really desperate to get this thing running.
Honest to God the main reason why this website took so long was because the load times made it miserable to work on. I just thought that the hardware that I had the site on was inadequate. I had initially started the development on a small Linux VM which apparently wasn't enough, which is why I moved it to digitalocean which also seemed to not be enough, so from there I moved to a dedicated server which still didn't seem to be enough. I was probably a few more 60-second wait times or timeouts from recommending a server cluster to my client who I know would not be willing to purchase it. The client who I promised this site to have completed in 3 months and has waited a year. Seriously, I would tell people the struggles that I would go through with this particular site and they would just tell me to just drop the site; just take the money, just take the loss. I refused to, this was really the only thing that was kicking my ass. I present myself as this high-and-mighty developer like I'm just really good at what I do but then I have this WordPress site that's just beating the shit out of me for a year. It was a very big learning experience and it was also very humbling as well, it made me realize that I really don't know as much as I think I might. It was evidence that there is still so much more to learn out there, I did learn a lot from that experience especially about optimizing websites the different types of methods to do that particular lonely on the server side and I'll be able to utilize this knowledge in the future.
I guess the moral of the story is, never really give up. Ultimately things might get so bad that you're running on hopes and dreams. Those experiences are generally the most humbling. Now I can finally present the site that I am basically a year late on to the client who will be so happy that I did not give up on the project entirely. I'll have experienced this feeling of pure euphoria, and help the small business significantly grow their revenue. Helping others is very fulfilling for me, even at my own expense.

Anyways, gonna stop ranting. Running out of characters. If you're still here... Ty for reading :')

Using Oracle 10g for our distributed databases practical lab session, and typed many SQL queries in one sheet.
Suddenly this guy came and told shortcut 'ctrl + r' to quickly run selected query.
And the page fucking reloaded and boom, all queries were gone! His evil laugh was more disturbing. 😡😡
Fuck him.

I don't know if I'm being pranked or not, but I work with my boss and he has the strangest way of doing things.
- Only use PHP
- Keep error_reporting off (for development), Site cannot function if they are on.
- 20,000 lines of functions in a single file, 50% of which was unused, mostly repeated code that could have been reduced massively.
- Zero Code Comments
- Inconsistent variable names, function names, file names -- I was literally project searching for months to find things.
- There is nothing close to a normalized SQL Database, column ID names can't even stay consistent.
- Every query is done with a mysqli wrapper to use legacy mysql functions.
- Most used function is to escape stirngs
- Type-hinting is too strict for the code.
- Most files packed with Inline CSS, JavaScript and PHP - we don't want to use an external file otherwise we'd have to open two of them.
- Do not use a package manger composer because he doesn't have it installed.. Though I told him it's easy on any platform and I'll explain it.
- He downloads a few composer packages he likes and drag/drop them into random folder.
- Uses $_GET to set values and pass them around like a message contianer.
- One file is 6000 lines which is a giant if statement with somewhere close to 7 levels deep of recursion.
- Never removes his old code that bloats things.
- Has functions from a decade ago he would like to save to use some day. Just regular, plain old, PHP functions.
- Always wants to build things from scratch, and re-using a lot of his code that is honestly a weird way of doing almost everything.
- Using CodeIntel, Mess Detectors, Error Detectors is not good or useful.
- Would not deploy to production through any tool I setup, though I was told to. Instead he wrote bash scripts that still make me nervous.
- Often tells me to make something modern/great (reinventing a wheel) and then ends up saying, "I think I'd do it this way... Referes to his code 5 years ago".
- Using isset() breaks things.
- Tens of thousands of undefined variables exist because arrays are creates like $this[][][] = 5;
- Understanding the naming of functions required me to write several documents.
- I had to use #region tags to find places in the code quicker since a router was about 2000 lines of if else statements.
- I used Todo Bookmark extensions in VSCode to mark and flag everything that's a bug.
- Gets upset if I add anything to .gitignore; I tried to tell him it ignores files we don't want, he is though it deleted them for a while.
- He would rather explain every line of code in a mammoth project that follows no human known patterns, includes files that overwrite global scope variables and wants has me do the documentation.
- Open to ideas but when I bring them up such as - This is what most standards suggest, here's a literal example of exactly what you want but easier - He will passively decide against it and end up working on tedious things not very necessary for project release dates.
- On another project I try to write code but he wants to go over every single nook and cranny and stay on the phone the entire day as I watch his screen and Im trying to code.

I would like us all to do well but I do not consider him a programmer but a script-whippersnapper. I find myself trying to to debate the most basic of things (you shouldnt 777 every file), and I need all kinds of evidence before he will do something about it. We need "security" and all kinds of buzz words but I'm scared to death of this code. After several months its a nice place to work but I am convinced I'm being pranked or my boss has very little idea what he's doing. I've worked in a lot of disasters but nothing like this.

We are building an API, I could use something open source to help with anything from validations, routing, ACL but he ends up reinventing the wheel. I have never worked so slow, hindered and baffled at how I am supposed to build anything - nothing is stable, tested, and rarely logical. I suggested many things but he would rather have small talk and reason his way into using things he made.

I could fhave this project 50% done i a Node API i two weeks, pretty fast in a PHP or Python one, but we for reasons I have no idea would rather go slow and literally "build a framework". Two knuckleheads are going to build a PHP REST framework and compete with tested, tried and true open source tools by tens of millions?

I just wanted to rant because this drives me crazy. I have so much stress my neck and shoulder seems like a nerve is pinched. I don't understand what any of this means. I've never met someone who was wrong about so many things but believed they were right. I just don't know what to say so often on call I just say, 'uhh..'. It's like nothing anyone or any authority says matters, I don't know why he asks anything he's going to do things one way, a hard way, only that he can decipher. He's an owner, he's not worried about job security.

Hired a new BI developer. She tested reasonably ok in SQL, and certainly showed good strengths in visualising data, plus had a good attitude in the interview. We hired her. She broke her laptop the first day. We got her another then she complained the camera didn't work but didn't realise the lever in front of the camera was to move the privacy shutter off and on.

Assigned her some work of taking queries that are used in a BI tool that targets the transactional database directly, and re-jigging them for Snowflake which we're using as a data warehouse now, aggregating all our data into one place. Yet, she's struggling to understand why the SQL query she's pasted in doesn't work as-is.

I go over it again; the source schemas and tables are this, but in Snowflake we've named them this. She then bemoans how much work that is to change them all - I say use find and replace. She then struggles with Snowflake syntax errors and asks for a guide on T-SQL to Snowflake. I show her Google and say "this is what I did when I hit these problems - search for 'Snowflake equivalent to T-SQL getdate()' or 'how to get current date in Snowflake' but she still doesn't understand. I ask if she's every had to work between T-SQL and MySQL or MySQL and PostgreSQL or Oracle and so on and she says yes. I say the syntax isn't the same, is it? And she goes oh, now I understand.

She scored reasonably in her SQL test but I'm now concerned there's something fundamental missing in her grasp of SQL. I gave her a detailed demo of the tools, I explained in the interview and on her start about our move to a data warehouse for all our apps, and put her through some training plus gave her time to work through our Confluence pages - not expecting she'll remember everything, but more to ensure she recalls they exist and what the general contents are.

Anyhow, that's my rant.

BI dev: Hey, can you help me with my SQL query?
Me: Sure, let me see it.
BI dev: sends screenshot - not even the whole query, literally a screenshot with a segment of text in it. No errors showing either.
Me: ...

Want to make someone's life a misery? Here's how.

Don't base your tech stack on any prior knowledge or what's relevant to the problem.

Instead design it around all the latest trends and badges you want to put on your resume because they're frequent key words on job postings.

Once your data goes in, you'll never get it out again. At best you'll be teased with little crumbs of data but never the whole.

I know, here's a genius idea, instead of putting data into a normal data base then using a cache, lets put it all into the cache and by the way it's a volatile cache.

Here's an idea. For something as simple as a single log lets make it use a queue that goes into a queue that goes into another queue that goes into another queue all of which are black boxes. No rhyme of reason, queues are all the rage.

Have you tried: Lets use a new fangled tangle, trust me it's safe, INSERT BIG NAME HERE uses it.

Finally it all gets flushed down into this subterranean cunt of a sewerage system and good luck getting it all out again. It's like hell except it's all shitty instead of all fiery.

All I want is to export one table, a simple log table with a few GB to CSV or heck whatever generic format it supports, that's it.

So I run the export table to file command and off it goes only less than a minute later for timeout commands to start piling up until it aborts. WTF. So then I set the most obvious timeout setting in the client, no change, then another timeout setting on the client, no change, then i try to put it in the client configuration file, no change, then I set the timeout on the export query, no change, then finally I bump the timeouts in the server config, no change, then I find someone has downloaded it from both tucows and apt, but they're using the tucows version so its real config is in /dev/database.xml (don't even ask). I increase that from seconds to a minute, it's still timing out after a minute.

In the end I have to make my own and this involves working out how to parse non-standard binary formatted data structures. It's the umpteenth time I have had to do this.

These aren't some no name solutions and it really terrifies me. All this is doing is taking some access logs, store them in one place then index by timestamp. These things are all meant to be blazing fast but grep is often faster. How the hell is such a trivial thing turned into a series of one nightmare after another? Things that should take a few minutes take days of screwing around. I don't have access logs any more because I can't access them anymore.

The terror of this isn't that it's so awful, it's that all the little kiddies doing all this jazz for the first time and using all these shit wipe buzzword driven approaches have no fucking clue it's not meant to be this difficult. I'm replacing entire tens of thousands to million line enterprise systems with a few hundred lines of code that's faster, more reliable and better in virtually every measurable way time and time again.

This is constant. It's not one offender, it's not one project, it's not one company, it's not one developer, it's the industry standard. It's all over open source software and all over dev shops. Everything is exponentially becoming more bloated and difficult than it needs to be. I'm seeing people pull up a hundred cloud instances for things that'll be happy at home with a few minutes to a week's optimisation efforts. Queries that are N*N and only take a few minutes to turn to LOG(N) but instead people renting out a fucking off huge ass SQL cluster instead that not only costs gobs of money but takes a ton of time maintaining and configuring which isn't going to be done right either.

I think most people are bullshitting when they say they have impostor syndrome but when the trend in technology is to make every fucking little trivial thing a thousand times more complex than it has to be I can see how they'd feel that way. There's so bloody much you need to do that you don't need to do these days that you either can't get anything done right or the smallest thing takes an age.

I have no idea why some people put up with some of these appliances. If you bought a dish washer that made washing dishes even harder than it was before you'd return it to the store.

Every time I see the terms enterprise, fast, big data, scalable, cloud or anything of the like I bang my head on the table. One of these days I'm going to lose my fucking tits.

Had an internet/network outage and the web site started logging thousands of errors and I see they purposely created a custom exception class just to avoid/get around our standard logging+data gathering (on SqlExceptions, we gather+log all the necessary details to Splunk so our DBAs can troubleshoot the problem).

If we didn't already know what the problem was, WTF would anyone do with 'There was a SQL exception, Query'? OK, what was the exception? A timeout? A syntax error? Value out of range? What was the target server? Which database? Our web developers live in a different world. I don't understand em.

A SQL query goes into a bar, walks up to two tables and asks, "Can I join you?"

Wrote a long SQL query yesterday for some data I was exporting. It was just about finished and figured I would wrap it up in the morning.

PC reboots overnight. Never saved the query.

// Rookie mistake

Getting real tired of having to reteach the basics of relational databases to the same 2 people. You were brought in as the expert in databases and SQL Server, I shouldn’t have to teach you about effing primary keys, secondary keys, many-to-many relationships, and how to join the damn tables in a basic query. Your 5 years of experience are obviously a waste if all you did was select * from bullshit. This is the 2nd week and 22nd you’ve asked the same damn questions. Get your crap together and study your ass off if you don’t know. Google the error messages if you don’t remember how to solve it before coming to me with the same question a 23rd and 24th time. I’m not going to get any work done if all you do is ninja up behind me with your laptop in tow and just spout off the question that could be done over IM or a quick duckduckgo/google search. Headphones in = do not disturb ya rude mother duckers 🦆.

I promised a friend to have a look over his dads website to add a small blog. No big deal, I've got it on my drive, can reuse it just need to adapt it to the environment.

I take a look at what I'm working with and I see the most terrifying piece of "Please, take my data" code I could possibly imagine (And I've seen passwords, in plain text in a script tag). I quote "function queryDB(mode, val) {
var query=" ";
if(mode==="findProd")
query="Select * from Products where ProdNam=" +val;
... (same shit for different cases)
sendQuery(query) ;
}

He literally built the query on the client side sent it to a php script (without validation) and inserted it into the database.
You could literally call window.sendQuery with any sql query and get the result printed into the console.
And other than the plain text passwords guy that wasn't some kid someone knew, this was a "Webdesign" Agency.
Now I took the entire thing offline, called my friends dad, explained it to him and try to sort this out. I would not charge a good friends father but that hack will get a quite hefty bill since my hourly rate just tripled.

And the worst thing : If I publicly name that asshole or warn the people in his portfolio I can, according to Google, be sued. (But, and I assume thats vague enough not to count as bad mouthing, if anyone of you has a customer from Rheinland-Pfalz, Germany with a preexisting page, please have a look at the database interface)
I will call that agency tomorrow, ask for a detailed explanation for why they apparently let trained monkeys write their code and anonymously warn everyone in their portfolio about those flaws...

I don't know if I'm cursed or if there are just that many bad devs but it seems that once a year I have to stumble over some "mistakes" that make me question my sanity.

Data Disinformation: the Next Big Problem

Automatic code generation LLMs like ChatGPT are capable of producing SQL snippets. Regardless of quality, those are capable of retrieving data (from prepared datasets) based on user prompts.
That data may, however, be garbage. This will lead to garbage decisions by lowly literate stakeholders.
Like with network neutrality and pii/psi ownership, we must act now to avoid yet another calamity.

Imagine a scenario where a middle-manager level illiterate barks some prompts to the corporate AI and it writes and runs an SQL query in company databases.
The AI outputs some interactive charts that show that the average worker spends 92.4 minutes on lunch daily.
The middle manager gets furious and enacts an Orwellian policy of facial recognition punch clock in the office.
Two months and millions of dollars in contractors later, and the middle manager checks the same prompt again... and the average lunch time is now 107.2 minutes!
Finally the middle manager gets a literate person to check the data... and the piece of shit SQL behind the number is sourcing from the "off-site scheduled meetings" database.
Why? because the dataset that does have the data for lunch breaks is labeled "labour board compliance 3", and the LLM thought that the metadata for the wrong dataset better matched the user's prompt.

This, given the very real world scenario of mislabeled data and LLMs' inability to understand what they are saying or accessing, and the average manager's complete data illiteracy, we might have to wrangle some actions to prepare for this type of tomfoolery.

I don't think that access restriction will save our souls here, decision-flumberers usually have the authority to overrule RACI/ACL restrictions anyway.
Making "data analysis" an AI-GMO-Free zone is laughable, that is simply not how the tech market works. Auto tools are coming to make our jobs harder and less productive, tech people!
I thought about detecting new automation-enhanced data access and visualization, and enacting awareness policies. But it would be of poor help, after a shithead middle manager gets hooked on a surreal indicator value it is nigh impossible to yank them out of it.

Gotta get this snowball rolling, we must have some idea of future AI housetraining best practices if we are to avoid a complete social-media style meltdown of data-driven processes.
Someone cares to pitch in?

During a design meeting, our boss tells me that Vertx's MySQL drivers don't have prepared statements, and that in the past, he's used a library or his own functions to do all the escaping.

"Are you kidding me? Are you insane?"

I insisted that surely he must be wrong; that no one would release a database library without built in support for query arguments. Escaping things by hand is just asinine and a security risk. You should always use the tools in the database drivers, as new security vulnerabilities in SQL drivers can be found and fixed so long as you keep your dependencies up to date.

He told me escaping wasn't as tricky as I made it out to be, that there were some good libraries for it, and insisted Vertx didn't have any built in support for "prepared statements." He also tried to tell us that prepared statements had performance issues.

He searched specifically for "prepared statements" and I was like, "You know they don't have to be called that. They have different names in different frameworks."

Sure enough, a short search and we discovered a function in the Vertx base database classes to allow SQL queries with parameters.

When you want only 10 rows of query result.
Mysql: Select top 10 * from foo.... 😁
Sql server: select top 10 * from foo.. 😁
PostgreSQL: select * from foo limit 10.. 😁

Oracle: select * from foo FETCH NEXT/FIRST 10 ROWS ONLY. 🌚

Oracle, are you trying to be more expressive/verbose because if that's the case then your understanding of verbosity is fucked up just like your understanding of clean-coding, user experience, open source, productivity...
Etc.

I feel ridiculous. While learning SQL databases I ran a query that was supposed to fill up the database with test data for me to play with. The actual result?

8 GB and 330,000+ rows.

Keep in mind this database is on a remote server, so trying to delete it times out after thirty minutes. I’m submitting a ticket right now.

What is it about SQL that invites people to do things in utterly ridiculous ways? The amount of pointless repetition and complete lack of clarity in the query scripts I get handed is irritating at the best of times - without any documentation (or even a bare comment sometimes) how am I supposed to work out what you were doing?

Now this looks stupid already, but here is the kicker: by "partially hydrated cursor" i mean that once every page size an sql query is ran to get the next page content. This code is put in an event handler, executed once every time a file is uploaded in a dms where files get uploaded by the thousand.

To sum things up, this simple snippet achieves triple dipping:
* waste time on useless sql queries
* waste cpu on useless iterations
* waste disk space on useless logs

Icing on the cake, the author of this piece of shit was complaining about the overall slowness of the process.
Needless to say that when I stumbled on this, both internal *and* external screaming ensued...

When you find an extremely cool SQL query online but instead of returning the high-five your wife turns around and walks away, shaking her head mumbling

Made a SQL query wich perfectly worked. Than added one collumn to be selected and everything is shit.

And the worst is I still don't know why! Ò_Ó

My react code in production broke because one of my senior renamed a field in sql query to 'id' instead of invoiceid.

It’s been so long since I posted but this time it’s juicy again.

I got a coworker, no prio experience but already a year and few months into the job. He’s bad.
Magnitudes of bad!

We’re trying to teach him but to no avail. Everything about him sucks, major ballsack to be exact.

His attitude is to avoid every task, finishes nothing and then starts something new.

„Did you do X like we told you to?“
„No I started on Y, because I thought it [looks better, seems more interesting, thought that X is useless…]“

When you ask him much is done he is always „almost“ finished and needs your help on the „last 5-10%“. Yeah fuck that!

But that guy has a talent, his talent is to always give you technically correct answers which actually are complete bullshit.

„What are you doing at your job?“
„Staring at a screen and typing things.“ dude what?

That guy used the excuse „I can’t do maths“ on everything.

For an exam he had to calculate how long it would take to reach a certain amount if you would get some interest in that every year.
He asked the teacher for the formula. During the exam! And when the teacher didn’t want to give it to him he wrote plainly „can’t do maths“ on the paper and left

His code is of a quality as if he would write his first line in a week and then has the audacity to blame me and the colleagues for not explaining it right.

Ok you might think now we’re teaching him bad, or are too impatient. But honestly if you have to explain how to do a for loop for over about 15 months and get that attitude I think you get the right to be angry. I don’t mind explaining on how things work, even for the hundredth time, but then don’t tell me you understood, go behind my back, complain at a colleague how bad I explained, get explained by him and then do it again until you whored yourself through the whole staff!

It’s like he got the mind swiper from Men in black at home. Every day he hits the reset button.

He had a week of just changing indentation on a html file. Why? Because he wanted to find his style.

Yeah his style

if(a==b){
console.log(a);
}
else {
console.log(b)
}

And to produce code like that it takes him atleast 4 hours of trial and error.

And at the same time he goes arround and boasts what a super good programmer he his and that he can do some project work for them.
How we found out? Because he started working in those projects during work time at the office and asked us how to do things.
And he does so like a complete bastard!
Broken sql query? “No that query is perfect as it is, it’s supposed to show no results! But, just in theory, if I wanted to show some results, what would I need to change?”

I’m so mad about it and pissed on a personal level because he goes around blames everyone and the world for his short comings

I never knew that I was a good mentor at SQL , specially at PL/SQL.

I gave a task to a new member of my team, to fill 5 tables with data from other 15 tables.

I informed him well about data table info and structure. He spended about 3 days to create 25 different queries in order to fill 5 tables.

After I saw the 25 queries, I told him, that he could do it with 1 main query and 5 insert statements.

So I spended 1 hour of training, in order to build,run and explain how to create the best sql statements for this task.

(First 5 minutes)
It was looking so simple at the beginning from starting with 1 simple join, after some steps he lost my actions.

(Rest 55 minutes)
I was explained the sql statements I 've created and how Oracle works.

Now , every time he meets me, he feels so thankful for learning him all those Oracle sql tips in 1 hour.

Now he is working only with big data and he loves the sql.

Running SQL Activity Monitor to find inefficient queries. According to legecy team this is how they think they should query SQL 2014 for a customer.

Wrote a SQL stored procedure today to do a complicated query. Decided to make it so that I could pass multiple records into the stored procedure in comma separated format, but the damned thing would only pull the first record. The query worked fine outside the procedure but it wouldn't pull anything more than the first record. After deleting and recreating and spending 30 minutes trying to figure out what was wrong I realized I changed the length of the wrong parameter. Set the correct one to varchar max and it was all good. 30 minutes of my life I will never get back.🐘💨

me to dba: do you have any recommendations of sql or query improvements? dba: no, just let us know once you're done.

after sending them explain plans, new queries and asking for reviews with no response from them, i applied the changes in dev.

after applying changes.

dba: you should involve us in any development. we need to collaborate.

me: please check your emails over the past 3 weeks.

wtactualfuck.

a friend of mine and me developed a mmorpg years ago on friday nights and we were learning a lot of sql on the way. for a tidy nested query that sped everything massively up i got a sudden kiss on the cheek at 5am. best reward ever.

A government website that I wanted to try and scrape data from to make a better app, I've actually found to be the pinnacle of a demonstration of what NOT to do...

Containing a JavaScript file that not only had got code copied 3 times (changed the tiniest bit on each) for what environment it's on, but has ALSO got the API keys for all 3 environments, AND the APIs they've made it call from there pass FULL SQL right in the query string...

What. The. Actual. Fuck?!

Ok so I was fetching some JSON data from a SQL database server and loading it on the front-end. Every single data is being loaded onto the table except for a single data column, which is empty.

Hmmm... So I go and check my code... everything looks fine.
Then I console.log the JSON (using .stringify() of course), all the values from the table are present in the printed out JSON.

Ok, now I am really pissed.

Long story short...
I had misplaced a single 'i' in the SQL statement, I had included the 'í' (the i-acute) character instead. And since I was using an alias in the query statement, no error was shown.

# Retrospective as Backend engineer

Once upon a time, I was rejected by a startup who tries to snag me from another company that I was working with.
They are looking for Senior / Supervisor level backend engineer and my profile looks like a fit for them.
So they contacted me, arranged a technical test, system design test, and interview with their lead backend engineer who also happens to be co-founder of the startup.

## The Interview

As usual, they asked me what are my contribution to previous workplace.
I answered them with achievements that I think are the best for each company that I worked with, and how to technologically achieve them.

One of it includes designing and implementing a `CQRS+ES` system in the backend.
With complete capability of what I `brag` as `Time Machine` through replaying event.

## The Rejection

And of course I was rejected by the startup, maybe specifically by the co-founder. As I asked around on the reason of rejection from an insider.

They insisted I am a guy who overengineer thing that are not needed, by doing `CQRS+ES`, and only suitable for RND, non-production stuffs.

Nobody needs that kind of `Time Machine`.

## Ironically

After switching jobs (to another company), becoming fullstack developer, learning about react and redux.
I can reflect back on this past experience and say this:

The same company that says `CQRS+ES` is an over engineering, also uses `React+Redux`.
Never did they realize the concept behind `React+Redux` is very similar to `CQRS+ES`.

- Separation of concern
- CQRS: `Command` is separated from `Query`
- Redux: Side effect / `Action` in `Thunk` separated from the presentation
- Managing State of Application
- ES: Through sequence of `Event` produced by `Command`
- Redux: Through action data produced / dispatched by `Action`
- Replayability
- ES: Through replaying `Event` into the `Applier`
- Redux: Through replay `Action` which trigger dispatch to `Reducer`

---

The same company that says `CQRS` is an over engineering also uses `ElasticSearch+MySQL`.
Never did they realize they are separating `WRITE` database into `MySQL` as their `Single Source Of Truth`, and `READ` database into `ElasticSearch` is also inline with `CQRS` principle.

## Value as Backend Engineer

It's a sad days as Backend Engineer these days. At least in the country I live in.
Seems like being a backend engineer is often under-appreciated.
Company (or people) seems to think of backend engineer is the guy who ONLY makes `CRUD` API endpoint to database.

- I've heard from Fullstack engineer who comes from React background complains about Backend engineers have it easy by only doing CRUD without having to worry about application.
- The same guy fails when given task in Backend to make a simple round-robin ticketing system.
- I've seen company who only hires Fullstack engineer with strong Frontend experience, fails to have basic understanding of how SQL Transaction and Connection Pool works.
- I've seen company Fullstack engineer relies on ORM to do super complex query instead of writing proper SQL, and prefer to translate SQL into ORM query language.
- I've seen company Fullstack engineer with strong React background brags about Uncle Bob clean code but fail to know on how to do basic dependency injection.
- I've heard company who made webapp criticize my way of handling `session` through http secure cookie. Saying it's a bad practice and better to use local storage. Despite my argument of `secure` in the cookie and ability to control cookie via backend.

Today I wrote a SQL query with 7 joins. What's your record?

Welp, this made my night and sorta ruined my night at the same time.

He decided to work on a new gaming community but has limited programming knowledge, but has enough to patch and repair minor issues. He's waiting for an old friend of his to come back to start helping him again, so this leads to me. He needed a custom backend made for his server, which required pulling data from an SQL/API and syncing with the server, and he was falling behind pace and asked for my help. He's a good friend that I've known for a while, and I knew it wouldn't take to long to create this, so I decided to help him. Which lead to an interesting find, and sorta made my night.

It wasn't really difficult, got it done within an hour, took some time to test and fix any bugs with his SQL database. But this is where it get's interesting, at least for me. He had roughly a few hundred people that did beta testing of the server, anyways, once the new backend was hooked in and working, I realized that the other developer he works with had created a 'custom' script to make sure there are no leaks of the database. Well, that 'custom' script actually begins wiping rows/tables (Depends on the sub-table, some get wiped row by row, some just get completely dropped), I just couldn't comprehend what had happened, as rows/tables just slowly started disappearing. It took me a while of checking, before checking his SQL query logs (At least the custom script did that properly and logged every query), to realize it just basically wiped the database.

Welp, after that, it began to restrict the API I was using, and due to this it identified the server as foreign access (Since it wasn't using the same key as his plugin, even though I had an API key created just so it could only access ranks and such, to prevent abuse) and begin responding not with denied, but with a lovely "Fuck you hacker!" This really made my night, I don't know why, but I was genuinely laughing pretty hard at this response.

God, I love his developer. Luckily, I had created a backup earlier, so I patched it and just worked around the plugin/API to get it working. (Hopefully, it's not a clusterfuck to read, writing this at 2 am with less than an hour of sleep, bedtime! Goodnight everyone.)

Going through some pretty fucked up shit right now : a string containing an SQL query goes though a 100+ lines function full of if{} else{} with up to 5 levels of indentation, where it replaces some parts of the query with other words... 😱

Please who the fuck wrote sql query with 6 nested select queries as 1 giant view. Literally 6 paranteses. Garbage 30 year old legacy codebase Please fuck off i now understand why nobody wants to work on this bullshit

Colleague asked me to look at his eCommerce search filtering system as the customer was complaining it was slow taking 5-6 seconds to find results.

Delving into the code deeper, I discovered he was querying the results, sticking them in an array and then sub querying the results looking at all the combinations.

On top of that each sub query looked at the database fields using "DESCRIBE" to then search them each time it found a pair!

The total query count for one page search was 14,512!!!

Why oh Why? One SQL query could of done all that in one go.

I look at other code bits he's done and he's very good in other areas. I just don't get how sometimes a good developer can make such a weird decision? It's almost as if he wanted to make it as complex as possible.

Working in a organization that hire people that don't know what they doing and can't ask a question correctly...

HELP WE'RE GETTING AN ERROR IN OUR CODE WHILE TRYING TO GET DATA FROM YOUR DB... PLZ FIX IT

WTF IS NOT WORKING, WHAT ARE YOU QUERYING, WHAT IS THE ERROR?

**Sends a SQL query but with ? for all the parameters**

WTF..... U PPL ARE IDIOTS.... CAN'T EVEN ASK A QUESTION CORRECTLY OR PROVIDE NECESSARY INFORMATION... CLEARLY YOU HAVE NO IDEA WTF UR DOING..

EVEN GOD CAN'T HELP YOU...

I was working yesterday, writing a calculus with sql.
My very great user explained to me the math in Excel. I first though to myself, piece of cake, i got it.
Then I started typing and at the end of the day i had 6 temp tables which at some point need to join with themselves. It was just hilarious. each table had at least 4 millions rows.

Then I started a new query just for validating the output of me very ugly previous queries.
And I fucking found a easier way to get the same output with 3 joins of 3 different tables and a count at the end.

When you love yourself. but hate yourself at the same time.

xD it was a very productive Friday night

!rant
the most popular ecommerce solution in php is a massive (cosmological scale) pile of corporate crap (magento) and the next most popular is an abomination (opencart)
after fucking around with both for a month (the client asked for the project to be using only one of the two) I'm still barely reaching any results, and most of my time is wasted with the stupid bloated spaghetti that is opencart FUCK THIS,
like seriously. who the fuck writes a single line three left joins sql querry with four or five aliases a couple concacts and a bunch sorting fuckeries just to query the categories list, then just query the details of the specific category from a different function,
also why the fuck map each language string manually. or the fucking hardcoded seo urls, or the use of myisam for all tables, and no fucking foreign keys, let that settle for a minute, no foreign keys, the delete method in the model has at least a twenty lines, and then he came with the genius idea of duplicating models, in the front and the backend, accessing the same data, as the same user, but different naming conventions

I'm going to convince him to use something sane like codeigniter/laravel/fuelphp or I'll deny the project

Who has replaced a dot with a semicolon while composing a SQL query in php? Me. 😤😤😤😤😤
Two hours spent in fixing everything and a sleepless night.

Holly shit, they will never understand....

If you come to dev, minding his buissness, writing some code, beeing focused, with headphones on, etc. You absolutely destroy his cache of things in mind, you stay next to him gazing at his screen and throwing away madly words that you need some data extracted from database NOW and you will stay standing next to him gazing when he quickly types few lines of ultra-wide screen of SQL querry with all the fucking joins and shit you wanted with exac aliases you understand and makes one typo but query executes and spits out some data. He didn't notice that something is wrong with it becouse he didn't memorize database's data, and he sends it to you.

Now you are coming back to him pissed and in general fighting mood becouse he did one fucking typo when you was actively pressurizing him to make fucking query faster while you absolutely destroyed his flow of work (in meaning he now needs to dive back into code, figure out why he was editing given file and what was idea for further structure)

Now you are standing again next to him and absolutely pissing him off that he fucked up and made one fucking typpo that unfortnately database didn't say "nope" but it instead spit out wrong data.

If you can relate to "you" stated above, sincerly fuck you.
If you can relate to "he" stated above, I feel ya man, its fucking annoying, isn't it?

Eloquent is so fucking slow, my (dead) grandma queries faster. It takes the fucking double of time on a simple query with only one join for maybe ~200 rows... From now on I go fucking back to raw SQL in my Laravel projects. 😤

A SQL query goes into a bar, walks up to two tables and asks, "Can I join you?"

Spent the entire morning updating a SQL query.

Client wanted to have different expiration times for different products. So the full package would be 1 year of access and a module would only be 6 months. Then when you renew your account the renewal is 1 year if you have the full package else it's 6 months.

The query takes 0.7s to run and left joins 3 tables. Only to return about 100 results. Still it's faster than the guy who wrote the original query which just dumped the hole db into memory then looped through it appending valid entries to a new array.

One day at the office at whoever built Laravel

Dev: sir, most of devs use php, we can use good old php expressi...
Boss: no, dump all of them, we'll create our own functions that do same job
Dev: ok... So our devs will use mysql, we can use sql quer...
Boss: dump them all too, again, we will make our own functions that dont look anything like that
Dev: we can also use standard...
Boss: NO STANDARDS! Creare every single php method or sql query in another method that does the same job...

The ticket system blokes - episode 3

So we always had and have very awful performance with our ticket system. You can't get anything to load in under ~4s normally. Now since it has gotten worse over the last weeks i decided to set aside a few hours to closely watch our SQL server.
After i identified a culprit that was hogging the CPU almost every 2 minutes i looked at other long running queries in the server and found out where exactly the 4s come from.

6 tables from various DBs. Sure, no problem.

Left Outer Join. Sure, why not.

Querying every fucking column in every fucking table explicitly adding up to a whopping 160 columns which they need not even 10% of. We're talking about session IDs, passwords, stock count, IBANs and all that stuff to show the work done on a ticket. Absolutely not.

So i extracted the query and reduced it to the stuff we need and the execution time went from 4 seconds to almost instant.

The funny thing is that their idea of performance optimization is throwing LIMIT around everywhere to get these monstrous queries under control.

So in the next few days I'll have an appointment with their lead programmer. I'm looking forwards to it.

So out of curiosity: does anyone know an SQL builder or toolset that does shit like
SELECT X AS [t0_c0],
SELECT Y AS [t0_c1],
SELECT Z AS [t1_c0],
and so on? I'd like to know how they got to this point.

Sometimes lack of confidence in one area reveals oversight cockyness in stronger areas:

Set up a simple login system from Unity engine to php to mysql db, using android device ID as the login id. Set up database column to accept 32 length varchar for MD5 hashed strings, as I knew the method I was getting the android device ID was automatically being hashed that way and more or less was what I wanted anyway.

Spend 2 days wondering why it would insert the logins with 0 issue, but could never retrieve them. Due to lack of web development and PHP skills, I assumed I was screwing up the handling of mysqli_num_rows() (to check whether I was inserting or selecting in the query) or simply screwing up my SQL queries.

Rewrite the code a few times, even went back to a method I had used in the past.

Today it dawned on me that my testing machines deviceID had been getting trimmed to the 32 character limit. Turns out I didn't account for my workstations device ID to be automatically hashed like the android device id is.

For 2 days I was obtaining and sending a 40 character string to a 32 character limit varchar and blaming my lack of PHP skills........

Back to my niche I go!

Spent 3 hours on a simple insert sql query for date. thank you pdo data binding...

I am a Technical Lead in the department in my company that writes code for our clients that have money but doesn't have the technical expertise to handle the complexities of our own software.

Part of my tasks involve taking care of a few projects written by employees that have left after using third-party tools rather than using our own software. No one else in this department knows these third-party tools, they only know our own, and my *still limited* web development experience means I get dumped these things in my lap.

And I'm SO pissed at these projects and their authors and the manager that let these ex-employees write these things. There is this one project that was managed by two different "developers" (I don't know they deserve this title) at two different times, and it is so riddled with different technologies it makes me want to throw up almost daily.

Don't believe me? Here is a complete list of the dependencies listed in the package.json of this project: babel-polyfill, body-parser, cookie-parser, debug, edge, edge-sql, excel-to-json, exceljs, express, html-inline, jade, morgan, mssql, mysql, pug, ramda, request, rotating-file-stream, serve-favicon, webpack, xlsx, xml2js

What this doesn't even show, is that one part of this project (literally one page) is made using react, react-dom, react-redux, and jade. The other part (again literally one page) is made using Angular and Pug. In case you missed it while picking up your jaw, there's also mssql, mysql, edge and edge-sql. excel-to-json, exceljs, xlsx.

Oh you want *more* juicy details? This project takes the entire data object used by the front-end, stringifies it into JSON, and shoves it into the database *as a single field*. And instead of doing WHERE clauses in the SQL queries, it grabs the entire table, loops, parses the json, and does a condition on it. If even one of those JSON entries gets corrupted, the entire solution breaks because these "developers" don't know what try/catch is.

The client asked for a very simple change in their app, which was to add a button that queries the back-end for a URL, shows it in a modal dialog, after which a button is clicked to verify the link by doing a second query to the back-end before modifying a couple of fields in the page.

This. Took. Me. Two. Months*. Save me. Please, save me.

*between constant context switches between this and other projects that were continuously failing because of their mistakes.

Pentesting for undisclosed company. Let's call them X as to not get us into trouble.
We are students and are doing our first pentest at an actual company instead of assignments at school. So we're very anxious. But today was a good day.
We found some servers with open ports so we checked a few of them out. I had a set of them with a bunch of open ports like ftp and... 8080. Time to check this out.
"please install flash player"... Security risk 1 found!

System seemed to be some monitoring system. Trying to log in using admin admin... Fucking works. Group loses it cause the company was being all high and mighty about being secure af. Other shit is pretty tight though.

Able to see logs, change password, add new superuser, do some searches for USERS_LOGGEDIN_TODAY! I shit you not, the system even had SUGGESTIONS for usernames to search for. One of which had something to do with sftp and auth keys. Unfortunatly every search gave a SQL syntax error. Used sniffing tools to maybe intercept message so we could do some queries of our own but nothing. Query is probably not issued from the local machine.

Tried to decompile the flash file but no luck. Only for some weird lines and a few function names I presume. But decompressing it and opening it in a text editor allowed me to see and search text. No GET or POST found. No SQL queries or name checks or anything we could think of.

That's all I could do for today. So we'll have to think of stuff for next week. We've already planned xss so maybe we can do that on this server as well.

We also found some older network printers with open telnet. Servers with a specific SQL variant with a potential exploit to execute terminal commands and some ftp and smb servers we need to check out next week.

Hella excited about this!

If you guys have any suggestions let us know. We are utter noobs when it comes to this.

¡rant|rant

Nice to do some refactoring of the whole data access layer of our core logistics software, let me tell an story.

The project is around 80k lines of code, with a lot of integrations with an ERP system and an sql database.

The ERP system is old, shitty api for it also, only static methods through an wrapper to an c++ library

imagine an order table.

To access an order, you would first need to open the database by calling Api.Open(...file paths) (yes, it's an fucking flat file type database)

Now the database is open, now you would open the orders table with method Api.Table(int tableId) and in return you would get an integer value, the pointer.

Now for the actual order. first you need to search for it by setting the search parameter to the column ID of the order number while checking all calls for some BS error code

Api.SetInt(int pointer, int column, int query Value)
Then call the find method.

Api.Find(int pointer)

Then to top this shitcake of an api of: if it doesn't find your shit it will use the "close enough" method of search.

And now to read a singe string 😑

First you will look in the outdated and incorrect documentation given to you from the devil himself and look for the column ID to find the length of the column.

Then you create a string variable with ALL FUCKING SPACES.

Now you call the Api.GetStr(int pointer, int column, ref string emptyString, int length)

Now you have passed your poor string to the api's demon orgy by reference.

Then some more BS error code checking.

Now you have read an string value 😀

Now keep in mind to repeat these steps for all 300+ columns in the order table.

News from the creators: SQL server? yes, sql is good so everything will be better?

Now imagine the poor developers that got tasked to convert this shitcake to use a MS SQL server, that they did.

Now I can honestly say that I found the best SQL server benchmark tool. This sucker creams out just above ~105K sql statements per second on peak and ~15K per second for 1.5 second to read an order. 1.5 second to read less than 4 fucking kilobytes!

Right at that moment I released that our software would grind to an fucking halt before even thinking about starting it. And that me & myself and I would be tasked to fix it.

4 months later and two weeks until functional beta, here I am. We created our own api with the SQL server 😀

And the outcome of all this...

Fixes bugs older than a year, Forces rewriting part of code base. Forces removal of dirty fixes. allows proper unit and integration testing and even database testing with snapshot feature.

The whole ERP system could be replaced with ~10 lines of code (provided same relational structure) on the application while adding it to our own API library.

Best part is probably the performance improvements 😀. Up to 4500 times faster and 60 times less memory usage also with only managed memory.

TL;DR: TIL for heavy queries use PDO and not some frameworks DB class

ffs I was trying to save 300k+ lines at once with Laravel for weeks. Mind you from a text file. 1gb ram on the vps so while trying to prepare the text to save: Fatal Error: Allowed Memory Size of bla bla Bytes Exhausted

ok so lets put it in a loop: Fatal error: Maximum execution time of 30 seconds exceeded (set_time_limit(0); lol)

optimising, varying the code got me into a situation when the content got saved in the BD but inconsistent (duplicates) and the table had often more than 1,5M rows. That was what told me its not a performance issue, my code is the issue. (dah)

I was starting to think it would be easier to export a prepared query to a sql file and load the file into the db as thats the fastest possible option...I even started to think about switching to python, then it hit me, Laravel has a shitload of routes to the DB so I switched to PDO

benchmark on 1vCPU, 1GB RAM VPS with SSD
379k lines with 11 columns in less than 10 sec with a loop of saving every ~6000 rows (if i tried choking it to save the whole thing at once it went up to 16-17sec)

2 SQL files each one with a SQL query with 200 lines using 2 databases. Life is hard.

Even seniors make mistakes. In case you were ever doubting yourself - just remember that.

I just had a very senior level programmer on my staff add a function to a production system that issues an SQL UPDATE query without a WHERE clause. Fortunately, only the 1st entry succeeded and the rest failed due to "duplicate record" errors. Clearly he had intended to do a SELECT to check if an entry was present. If it was present, do an UPDATE, otherwise do an INSERT (think UPSERT - but done manually). However instead in the insert part they were both UPDATE's. The first update was normal looking but the second UPDATE was just this weird malformed-looking thing where he tried to do an UPDATE but to every field including the key fields. Clearly he was thinking about an insert but actually writing it as an update. Every now and then I need to remind myself that these things happen. The guy's not dumb - just made a mistake.

I'm just happy it "failed unsuccessfully".

Stop teaching T-SQL on paper. If i have to write one more transactionsafe sql query without a debugger i'm going to fucking kill somebody.

My Technical lead always asks one question to new interviewing candidates. "Write a SQL query to find the second highest salary from employee salary table". It's the same question which he faced in one interview and he didn't have answered it.
Sorry for my bad English..

wtf...

Ones of the best bugs I love the "most" are the ones where the fix is counter-intuitive, e.g. making smth seemingly incorrect to rectify the issue.

Like today, I crafter an SQL query to fetch some PG metrics. And postgres-exporter refused to accept it until I added an excessive comma [,] at the end of the SELECT block (right before FROM).

Like.. wtf...

Once I maintained one of the most used and fucked up codebases on the market with almost 1M+ daily users. (cannot say more, sorry).

It's written in PHP and is absolutely terrifying,
the first time I saw some lines of code I was about to scream and cry.

- spaghetti code
- no indentation
- random SQL query unoptimized
- unused vars
- Code is split among several files with no logical reasoning
- Mixed procedural and oop programming
- Unsanitised user input (yes, you got it right)

No test environment, no backup database, every commit goes straight to production.

It's a real disaster but the company prefers to keep it as it is without refactoring or anything else.

Just to make it clear:
It's not hatred against PHP, it's against the code's current status and the older programmers which used to work on it.

Love it when you have a SQL query that takes 6.3 seconds (which includes processing time) to execute, and you managed to convert to a process that takes just 0.072 to complete

I made changes to an SQL view used as the source for a pretty important integration that is built and managed by an external company. The integration failed the morning after and I was immediately blamed by the company and heard "how could you be so careless" and "how long have you worked in this business". I've been a programmer for 20+ years and done integrations for 15+ years. I know I checked the output of the view and it was identical in every way to before my changes.

After finally getting access to the integration code on "the other side" I found that it didn't read from the SQL view - it read the view definition. It also uncommented anything in the view (yes, uncommented) and ran that query.
We now have a year free of charge - which we won't need because my boss is throwing the company out as soon as we have a replacement.

Me : So cool ! My new graphQL APIs are working so good !
Also me : ‘order by <text field> take 50 skip 10000’
Me : Hmmmm.. 2.3 SEDCONDS ?! WTF. Let’s add an index !
SQL : Sorry bro, can’t add index on nvrachar(max).
Me: OK. Here you go, you are nvrachar(128) now. Add my index !
SQL : Ok
GraphQl :<same query > Here : 90 milliseconds
Me : ‘order by <text field> desc take 50 skip 10000’
GraphQL : Sorry bro : 3 seconds. (Yes, slower than without any index)
Me : Do I fu7cking need to manually add ASC and DESC indexes ? WTF IS GOING ON !
I should’ve learnt a bit more about databases. ☹. And now I don’t have time to refactor a prod database as “needed” .
/me needs to buy DB audit. Company is still a bit small to have a DBA full time.

Tip: if you are doing a semi complex or complex query in Django and you have doubts print the SQL statement and analyze it. i.e print(queryset.query)

Just reduced a query to 1 join instead of two by just passing a list of int's instead of a list of objects.

I once had to fix a webservice endpoint another developer added that accepted any file from the public internet and loaded it directly onto an NFS file mount with the rest of the site's image assets and then inserted a record of the file into SQL via a hand-stitched query with parameters from the endpoint.

I was working for a large enterprise company at the time... I was very disappoint.

How hard can it be to sort content stored in a relational database by a custom meta parameter and restrict the results to a certain language using a very popular content management system in 2023?

After wasting several hours trying to get my head around reference documents, 20 years of anecdotal StackExchange + WordPress.org discussion and ACF + Polylang support, and trying to debug my code, I will now either write my own SQL query or put the meta query results in a hashed object to sort it using my own PHP code.

What time is it now? 2003?

... a SQL query walked into a bar. It got onto a table and asked, "Can I join you"

I looked at an SQL server today from a customer, talked with one of their devs and he said that he's unable to understand why the server misbehaves... All (!) queries were optimized, but they have 'big data queries'... Migraine started, I had a very bad feeling. Monitoring? Nooooppeeee. Migraine kicks in. Connected to server. SHOW GLOBAL VARIABLES...
After a bit of scrolling I found a lot of misconfigured variables (e.g. extreme large join buffers, unrealistic buffer sizes), high slow query count (nearly 60 % of COM_SELECT) and a few variables that were unknown to me.

Then came the version line.
5.0.46
Yes. 5.0.46.

Big data? Well... 30 GB of usage data.

I called the company back... The dev told me sternly that this was the production server (I had hope...) and that I lie - neither the version, nor the variables could be the problem.
A coworker had to verify it and our manager had to do the communication... Worst, most traumatic working day I ever had.

4 really basic questions. Things you can't get through 1st year undergrad without knowing. One was testing you understand references, one testing understanding of inheritance, then exception handling... Then a bit of a tricky one: what happens when you query 2 tables in sql without a join. That took me a second because it's just not something I'm used to doing.

So yeah it's pretty basic stuff. At this point I was used to writing fairly long code snippets and quizzes with lots of gotchas that make the interviewers feel really smart. I think "ok they basically want to make sure I'm not totally useless and they're fine with training me". But noooooo. Being able to answer all that correctly is really impressive. That's never happened before. I'm a fucking prodigy.

So I got the job and I alternate between thinking I'm in Idiocracy and thinking the reception I get is some sort of elaborate joke

FUCK rabbit holes real hard. When you spend a week creating a super optimized recursive handler for mapping redundant database row results and you wake up from your coma realizing everything could be handled by a much simpler SQL query done in five minutes. Fuck me, fuck the rabbits creating holes to fall into and fuck Alice for creating the fucking illusion of wonderland.

Are sql joins a bad practice? :o
I recently did some work on a page for a site ive never worked on cause my boss told me to. So they recently added product detail video urls to a table that has a relationship to the products table. The existing code was querying for the products on that said page and then during the loop that was outputting the products ,there was another query for getting the url for the current iteration/product. Told my coworker that this imo was pretty inefficient way to do it and switched it to a join and did 1 query then output that but his words were "The way it is now maybe ineffecient in your opinion but it works. Also combining inner joins with left or right is not a good practice. If the data is changed upstream the entire query would need to be redone to accommodate the change". Mind you that they query views a lot which are all made from queries that use joins and I'm also pretty sure these views were written by someone who used to be here because these guys are not good at sql or at least that's what there queries show. I'm at the point now where I'm realizing that my boss and this other guy don't give a fuck about efficiency or doing things the right way they just want it "to work". So this coworker changed my query back to the way it was because he said it broke the shopping cart even though that was already broken when I started... What is life? Maybe I'm the stupid one?

So I inherited this buggy application my company developed to process state rosters for health care. The daily process fails often and I haven’t been able to figure out why. Then I notice one little thing... it’s essentially using SQL injection as a method of updating records from a file that we receive from outside... there’s no checking for validity of the statements or making sure they’re safe to execute. Just a for in loop and calling a sp to execute the query text under elevated permissions.

Do you feel that good too when you find the perfect (any)SQL query?
I feel sooo satisfied!

Learnt a lesson today:

Never try out new hotkeys in a SQL query editor window.

Or if you do, at least make sure it's not connected to anything important :)

I was trying out new hotkeys and accidentally executed a SQL deploy script to rename something in multiple stored procedures in a large system.

Thankfully - so I saw after my heart stopped - it was only our QA db so not too bad, just a couple of devs set back.

Who woulda thought ctrl-l would execute :O

Is there ever any reason for SINGLE Hibernate SQL query/template to join like 10 different tables, do math, and come out to like 30 lines?

This is not a stored procedure, it's a single SELECT

Interesting...

On Friday, I was playing with the ChatGPT integration in DBeaver. I was using the DBeaver sample SQLite database. This database has a couple of tables, among them Album and Artist, where Album has a foreign key into Artist.

So, I asked it:

"give me a query that lists all albums from artists who's name starts with s"

The query I got back was:

SELECT * FROM Album

Uhh, okay.

But then, I noticed that I wrote "who's" instead of 'whose', which would be proper grammatically. So, I changed that, and then I got this query:

SELECT * FROM Album WHERE ArtistId IN (SELECT ArtistId FROM Artist WHERE Name LIKE 'S%');

Hooray, that works! I'm not sure it's the best way to write the query... I might have written:

SELECT * FROM album a, artist r WHERE a.artistid=r.artistid AND r.name LIKE 'S%'

...I'd have to check to see if one performs better than the other, and consider which syntax I find clearer, but that's a separate issue, it's just nice to see a working, reasonable query generated because that's the point, after all.

But I found it interesting that such a minor error would cause it to not work, that's my main point.

Interestingly, it seems to have learned: I just tried the same thing, and I got the right query either way. So that's pretty cool.

It's a pretty neat feature and I can see some legitimate value in it. I'm pretty good writing SQL myself... I've managed to write some truly hideously complex queries over the years... but there are definitely instances I can recall where the query didn't seem obvious at the start, and having an AI that can MAYBE produce something that is AT LEAST a starting point is definitely something I can get onboard with.

So an update on my last rant(s) about dependencies and wasting time...

Today I finally lost it... With deadline fast approaching, i went to the big boss and he basically got everyone on his team to get their shit together and give me what I had asked for... 2 weeks ago...

It only took them 30mins.... Just the correct SQL query really...

a sql query goes to a restaurant walks up to 2 tables and says , "can i join you"

Why it is such a pain to scan nullable types on Golang database.sql

Now I have to chose between:

1- Using temporaty variable to hold the nullable value just to soon after check and set the zero value if it is null

2- Adding coalesce to my sql query for the zero values.

Did I mention that there are around 20 collumns that are nullable...

One day, being new in the team i was not sure of all the DB column decodes, asked my senior member to send me decode of a column.
He sent me a 300 line SQL query. Took me 30min to understand it.

I want to know the name of the evil mastermind who once conceived the "literal" function in Sequelize.

- You design a method to insert pieces of raw SQL exactly the way they are written, no further processing
- You release this method, you call it LITERAL to make sure people know its intended purpose: it is used to insert LITERALLY everything you write, nothing more and nothing less
- Then make sure this "literal" method changes the fucking case of column names. Because that's what "literal" means in the head of this rabid animal: you arbitrarily change the code written by the developer

WHY
WHY ARE ALL AR ORM DESIGNED BY FUCKING ANIMALS
ELOQUENT IS TRASH, SEQUELIZE IS TRASH, TENS OF DEVELOPERS AT WORK TO ALCHEMICALLY CREATE THE MOST ROTTEN CODE THEY POSSIBLY CAN, BECAUSE YOU MUST NOT BE ALLOWED TO WRITE ANY QUERY MORE ADVANCED THAN "SELECT * FROM users WHERE id =1", NOT A FUCKING SHRED OF DOCUMENTATION AND 16 MILLION LAYERS OF ABSTRACTION TO MAKE SURE EVERY BUG FUCKING STAYS THERE, DON'T YOU DARE TO USE A JOIN, DON'T YOU DARE TO TREAT A DMBS LIKE AN ACTUAL FUCKING DBMS INSTEAD OF A HOT STEAMING PILE OF METHODS IMPLEMENTED BY MONKEYS.

Yesterday I had a user reachout via email because his account became inaccessible when he updated it.

This was something I didn't realize could happen... so it was fun recreating this scenario locally.

Basically he somehow bypassed the lowercase requirement for the updateUsername endpoint. So in the DB, his name was uppercase. And the SQL query was searching for his exact username without doing LIKE (by design).

It was failing on that getProfile endpoint and showing a Whoops screen. 😬 Should be a 404 screen, I know. I'm working on it.

Since then, I added Sentry logging to the backend and frontend.

And this showed me there were endpoints still being triggered nightly to remove stale data. (I forgot to remove). And these endpoints were getting 401's. I'm thinking who TF is triggering these endpoints. I soon realized those specific endpoints needed keys that expired monthly. 🤦‍♂️

Wow...a talented developer created a 3 via SQL execution plan. The query runs like crap but the execution plan art is pretty.

Wrote a 217 line script to import/export email templates.

Slightly less painful than hand crafting the 37,000 line SQL query.

A SQL query goes into a bar, walks up to two tables and asks, "Can I join you?"

Can there be a happy rant?

This is going to be a bit of a rambling semi coherent story here:

So this customer who just doesn't know what their data schema is or how they use it (they're a conglomeration of companies so maybe you get how that works out in a database). For every record there's like a ton of reference number type things mapped all over the DB to fit each companies needs needs.

To each company the data means something different, they use the data differently, and despite their claims otherwise, I think there are some logical conflicts in there regarding things like "This widget is owned by company A, division B, user C.". I'm also pretty sure different companies actually don't agree on who owns what... but when I show them they just sort of dance around what they've said in the past...

So I write a report (just an SQL query that outputs ... somewhere ... I mean what isn't that?) that tells them about all the things that happened given X, Y, Z.

Then every damn morning they'd get all up in arms about how some things are 'missing' but sometimes they don't know what or why because they've no clue what the underlying data actually is / their own people don't enter the data in a consistent way. (garbage in garbage out man...)

So I've struggled with this for a few weeks and been really frustrated. Every morning when I'm trying to do something else ... emails about how something isn't working / missing.

In the meantime I'm also frustrated by inquiries about "hey this is just a simple report right?" (to be clear folks asking that aren't being jerks, and they're not wrong ... it really should be simple)

Anyway my boss being the good guy he is offers to take it over, so I can do some things. Also sometimes it helps just to have someone else own something / not just look it over.

So a few days into this.... yup, emails coming in about things 'missing' or 'wrong' every day.

Like it sucks, but it's nice to see it suck for someone else too as validation.

GraphQL question here!
So i recently noticed (few years after everyone?) That graphql seems popular... I decided to try it out, but after playing with it a bit, the conclusion I came to is, that it's a great idea from FE point of view, but for the backend not so much.. a simple sql to return data to ui turns into a bunch of parts, all independant and with even the simplest relationship to some other entity the whole thing becomes very not optimized and when googling about it, all i found were some very awkward libs for work arounds to force everything into 1 optimized query again... But wait, i already have 1 optimized query in my rest api 😆

I don't understand if I'm missing the brilliance of graphql that everyone saw, or is everyone fell for the hype and use a stupid tool and pretend it's cool? 🫣

I had to optimize a SQL-Query and i was able to cut the amount of time in half.

The business part of the company said it is still too slow, so i researched deeper and looked into the called functions which are used in every part where to claculate value X.

Found 20 similiar selects to get values which are summed up afterwards into three different variables. Thought this can be done in three selects with "sum", but then i found the dumbest thing is this function:

V1, V2 have the values from the selects
V3 is ja given value
X is the return value
the function returns another value

help = functionCall(parameter) - V1 -V2
X= help + V1 + V2 +V3

WTF im mean it does the selects for nothing...
X = functionCall(parameter) + V3

This is more of an essay than a rant. TLDR at the end. I simply can't choose from all the shitty lecturers I've had, so I'm going to have to go through them one by one. But of background. I'm currently in 7th year of college, I did a multimedia degree in 2 years, a intro course to Software Dev and I'm currently in my final year of my Software Dev degree. So let's start.

Intro Software Course
- we had a database module, which was thought by, I shit you not, the head of the psychology course in the college, she attempted to teach us Databases using access. And not even using SQL, using access GUI components and it's query builder. Need I say more?

1st year software dev
- We had a networking module, the guy that taught the labs, he literally didn't say more than 12 words the entire 12 week semester, his answer to any question you asked him was a grunt and "research it"

- We had a psychology module, I have no fucking idea why, but instead of learning something useful we were told to read this and get in touch with your feelings...

- database module. Yes we actually did SQL here, 12 weeks of select statements and normal form, talked about by a guy in a monotone voice, who sounded like he was contemplating bringing in an assault riffle some day. Also instead of using MySQL he decided to use Ingres. Why I will never know.

2nd Year Software Dev
- We had a module called Algorithms and Data Structures. The lecturer gave us problems she couldn't solve. Simple problems. She was also crazy. Absolutely nuts.

- Object Orientated Programming. I had this lecturer for 3 semesters up until 3rd year. This guy did COBOLT in college, graduated in the 70s or something and went straight into teaching, he taught us Java for nearly 2 years. He literally copied and pasted texts from PDFs and read through them in class. He told myself and another guy at one stage he really didn't care, and was just counting down the days to his retirement.

- Databases again, different lecturer from 1st year, taught us for 2 semesters (24 weeks) and somehow managed to teach us nothing.

3rd Year Software Dev
- software engineering.. This is where the biggest cunt I've ever met was introduced. He arrives into class 15 minutes late every time without fail, talks shit about stuff that has no relevancy to the topic at all, tries to turn everything into a rugby metaphor and every time you ask a question he somehow dodges it and swiftly changes topic. This cunts past profession? A Project Manager. Fucking typical. This dickhead has also thought me 2 other modules.

4th yr Software Dev
- El cunto mentioned above for 2 more modules. Need I say more.
- real time systems, this module took the piss, the module was written by the lecturer which is what earns his space here. Assignments given to us, which required more time to do than we had in labs so we had to work at home, the problem we that is we were using an obscure RTOS called OS9 which would only work on the college computers. When brought to the lecturers attention he just said "figure it out"

Internet of Things - There was 2 lecturers, each lecturer seemingly working off a different plan, one week you'd have one lecturer, the next would be the other one going on about something completely different and unrelated to anything else we'd done.

Some lecturers didn't even make this list as I couldn't be bothered trying to think back about how shit other ones were. These were the ones that always stood out in my mind.

My main take away point from this is that you go to college for the paper which says you have a degree. Learning things that are going to benefit you in a career is up to yourself.

TLDR; 90% of my college lectures were shit. You need to learn useful stuff yourself.

Spent 2 days optimizing SQL queries, and then I learned a valuable lesson.

If your database size is bigger than the RAM of the machine it’s running on, every query will take 5+ seconds ☹️

My work product: Or why I learned to get twitchy around Java...

I maintain a Java based test system, that tests a raster image processor. The client is a Java swing project that contains CORBA bindings to the internal API of the raster image processor. It also has custom written UI elements and duplicated functionality that became available in later versions of Java, but because some of the third party tools we use don't work with later versions of Java for some reason, it's not possible to upgrade Java to gain things as simple as recursive directory deletion, yes the version of Java we have to use does not support something as simple as that and custom code had to be written to support it.

Because of the requirement to build the API bindings along with the client the whole application must be built with the raster image processor build chain, which is a heavily customised jam build system. So an ant task calls out to execute a jam task and jam does about 90% of the heavy lifting.

In addition to the Java code there's code for interpreting PostScript files, as these can be used to alter the behaviour of the raster image processor during testing.

As if that weren't enough, there's a beanshell interface to allow users to script the test system, but none of the users know Java well enough to feel confident writing interpreted Java scripts (and that's too close to JavaScript for my comfort). I once tried swapping this out for the Rhino JavaScript interpreter and got all the verbal support in the world but no developer time to design an API that'd work for all the departments.

The server isn't much better though. It's a tomcat based application that was written by someone who had never built a tomcat application before, or any web application for that matter and uses raw SQL strings instead of an orm, it doesn't use MVC in any way, and insane amount of functionality is dumped into the jsp files.

It too interacts with a raster image processor to create difference masks of the output, running PostScript as needed. It spawns off multiple threads and can spend days processing hundreds of gigabytes of image output (depending on the size of the tests).

We're stuck on Tomcat seven because we can't upgrade beyond Java 6, which brings a whole manner of security issues, but that eager little Java updated will break the tool chain if it gets its way.

Between these two components we have the Java RMI server (sometimes) working to help generate image data on the client side before all images are pulled across a UNC network path onto the server that processes test jobs (in PDF format), by reading into the xref table of said PDF, finding the embedded image data (for our server consumed test files are just flate encoded TIFF files wrapped around just enough PDF to make them valid) and uses a tool to create a difference mask of two images.

This tool is very error prone, it can't difference images of different sizes, colour spaces, orientations or pixel depths, but it's the best we have.

The tool is installed in both the client and server if the client can generate images it'll query from the server which ones it needs to and if it can't the server will use the tool itself.

Our shells have custom profiles for linking to a whole manner of third party tools and libraries, including a link to visual studio 2005 (more indirectly related build dependencies), the whole profile has to ensure that absolutely no operating system pollution gets into the shell, most of our apps are installed in our home directories and we have to ensure our paths are correct for every single application we add.

And... Fucking and!

Most of the tools are stored as source bundles in a version control system... Not got or mercurial, not perforce or svn, not even CVS... They use a custom built version control system that is built on top of RCS, it keeps a central database of locked files (using soft and hard locks along with write protecting the files in the file system) to ensure users can't get merge conflicts by preventing other users from writing to the files at all.

Branching is heavy weight and can take the best part of a day to create a new branch and populate the history.

Gathering the tools alone to build the Dev environment to build my project takes the best part of a week.

What should be a joy come hardware refresh year becomes a curse ("Well fuck, now I loose a week spending it setting up the Dev environment on ANOTHER machine").

Needless to say, I enjoy NOT working with Java. A lot of this isn't Javas fault, but there's a lot of things that Java (specifically the Java 6 version we're stuck on) does not make easy.

This is why I prefer to build my web apps in python or node, hell, I'd even take Lua... Just... Compiling web pages into executable Java classes, why? I mean I understand the implementation of how this happens, but why did my predecessor have to choose this? Why?

when you give up when compiling

SSMS 18 just removed my DEBUG button.
...
...
...
...
...
...
Why...

Jira query issues
Gotta love them....

Just notice that google spreadshit can do query like actual sql query.

Wtf each cell is like data as in relational databse.

Wtf wtf wtf.... soooooo cooool

My next db spreadsheet.

I spent 2 hours fixing eclipse.
I spent another hour getting Java to parse my date and time input.
And I still have to get an SQL query running.

and I need to do 5 more problems like this for a team project.

I seriously detest being dead weight on my team, especially when it is a two person team.

This is friggin bullshit. I'm a 2nd year college CS student! I'd think I'd be a quicker programmer by know! I LEARNED TO PROGRAM IN JAVA FOR TORVALD'S SAKE!

Well. Back to work.

Worked the whole day on a pretty complex sql query works perfect. Run it on the production server and the hole thing hangs. It's literally the same version of MySQL en a exact clone of the data but still it managed to hang the hole server.

Pardon the rant; some of it can probably attributed to me, but please indulge me of you could.

I'm tasked with creating a report that pulls data from some sql tables in c#and presents it using javascript. My manager was nice enough to lend me his old sql query, so I run with that using sql connections. Now I find out AFTER I get my sql query string working and retrieving data properly that my manager wanted it done using linq and entity framework, so now I have to start over, a process made only more "fun" by the confusing and unintuitive column names of our sql tables.

Moral of the story: don't take the easy way out.

After I spend some time fixing that up, I have to print out the data using javascript and html, which my manager was kind enough to lend me. Cue me shutting off my brain and thinking that I should have the program open and display this stuff itself. Let me tell you that converting a console application to a Windows form application is not a fun experience, especially when entity framework makes classes named "application" and "form" from your database tables. After finally getting the WebBrowser form to work, I'm hit with a javascript error from the library my manager referenced (he is a programmer himself). I tell him about the error and he just tells me to write the html code to a .html on disk like he did, but never explicitly said he did until just now.

Fixed moral of the story: don't take the easy way out, unless you should.

I should clarify I was given the whole raw sql query and html with some embedded javascript and a reference to chart.js.

We had a course on GUI and Databases as part of my bachelor's degree. It was a basic introductory course (I am a mechanical engineer) where we were expected to design some tables and build a simple front end in VB6.

But the instructor was so bad that he hardly taught any VB code at all. And as far as theory on databases was concerned, about 80 percent of the lectures involved some generic introductory statements followed by an explanation of the terms DDL, DML and DCL. I do not remember him writing even a single SQL query to explain to us how it's done.

Ok, this question is related to mysql and php.,
Let me state the current situation
I have a db, say "gd".
That db has several tables all with same columns.(i.e same fields for different manufacturers such as product name, cost , stock etc.)
Now i want to know how many tables have the product 'a' in them and what's the cost of 'a' in each of those table.(tables are generated dynamically so I'll never now how many tables are there, well ofcourse i can refer information schema, but just wanted to highlight this fact). So is there a way to achieve this?.. excluding "dump the whole data and then search it" solution.
Plz help, .. sorry for my bad English, .

Today I (/ later together with some colleagues) spent almost 4 hours trying to improve a Entity Framework LINQ to SQL query.
The initial problem was, that one of our List API endpoints took longer the more you "page" (besides the long response time it had anyways).
So after
- brainstorming in the team
- brainstorming alone
- hacking around and
- shouting at screens
- googling
we
- got nothing optimized
- got confused about what EF does
- lost the believe in our development skills

So Entity Framework is really a nice thing. But as soon as you look deeper, trying to figure out what it really does between ToList() and "yeah my data arrived" it is just....demonic.

So i have been thinking..
SQL is a lang that runs on a specific software on the server, and helps creating data stores(databases and tables) that can be queried & manipulated.

is there a way to run sql like queries on the client side with no interaction from backend at all?

Say i have 5 inter related data models. in a backend world, they will form nice little tables of a db with all their joins and composite keys. from the server, i shall be querying them like "SELECT name from x where y=z & ..."

but what if i could store them like tables in browser memory and run the same query filters via a query language... is this possible?

i know this poses a certain security risk, but we already use cookies, local storage and a lot of json based shitty client side storages. surely it might be possible to have a lesser optimised sql tables on the frontend with extremely good querying capabilities?

or am i talking something far fetched here?

As a follow-up to my last rant, I figured out the SQL (well, WQL) query that would get me what I wanted: a collection of machines that had an error on a deployment.

I also figured out how to automate fixing the error'd machines and turning all of my possible fixes into one script that would also auto-deploy to the collection that was made with the query.

My senior coworker is impressed. He has been doing it manually for years and I was hired partially to take the load off of him. They're putting me on some more challenging projects and it's nice to be a better part of the team.

Not much of a rant, or even much of a developer thing, but I hope this bit of positivity makes for a lighter read in your Algo.

About 20 hours. We had a major campaign for a product launch back in the days when MSN Messenger was awesome. Hitting F5 in MS sql query analyzer to execute query again would show like 20K+ downloads each time, shit was crazy. Then we discovered a major fuck up. Turned out that someone made a mistake by making a guid static. In a personalized content generator. So, most users ended up with someone else's face inside their personalized MSN Messenger wink. Oops... and no, we didn't do code reviews nor TDD back then so we didn't discover it sooner. It was really awesome to see how much traffic MS could generate by just showing a banner in hotmail. Real crazy. Anyway, we fixed it, discovery of the actual problem did take some time though.

An SQL query goes into a bar, walks up to two tables and asks, "Can I join you?"

When you open a SQL File and you just here Maligaro whisper in your ear: " do you know the true face of nightmare, exile?"

So i was trying to learn php from a udemy course. The guy there mixes a hell lot of php with html, like all the pages are .php with html content and mini <?php ... ?> Scripts in between everywhere: titles, swl queries running and displaying outputs as html with echo php variables, etc..

Now am not much versed with client server data model, but isn't there supposed to be clear distinction between the server side and the client side? He puts a form there using echo "html string" , rrcieves the form input in the string's action , runs an sql query and generates another set of html strings. All in one file.

Is it how major php websites work? On the other hand My web dev friend om who works a lot with js usually runs 2 seperate aws instances for frontend and backend and makes them communicate via apis

SQL is amazing.

I'll toss out some bassakwards query and the optimizer will make sense of it and suddenly I'm searching a amazonillian records in no time.

Then rando one day (today) I fire up what I think is really not the most wonky query I've ever written and ... "Well shit this is surprisingly slow."

So then I go full n00b and add some fields to the query that I know would limit the number of possible records to way low thinking that might help and ... nope no faster...

Guess it's time to bust open some books about SQL....

I used alot of if checks to convert category name to category ids for sql query. For eg.

/Index.php?cat=venue

And I handled it like

If($cat=='venue')
{
$cat =1;
}

And so on.

So I was stupid enough to make the same mistake for 5 times in a row within the same hour, simply the same sql query to get something from a table which doesn't exist because I didn't even run the migration.

Working on a legacy PHP project that every single query inserts user-provided data without any sanitization, aka SQL injection ahoy! Also no framework.

In the last days, my developer was working with different kinds of databases.
He uses a database, named TimeScale DB (Timescale). This is the plugin of a famous SQL database PostgreSQL.

He imports the same data in PostgreSQL and timescale DB.

Size in Timescale DB is = 24KB
Size in Simple PostgreSQL = 166MB

This is the record of 1000000 rows, 12 columns.

Data fetching is the same on both databases, But if we fetch time-based data, (e.g 2 days, 24 hours, From Date to Date) we have a 50% to 200% difference depending on the query. python3 & elixir is also working fine with DB.

Results are also nearly the same with InfluxDB.

For a specific use-case, we have software that is handling Simple and time-series data at the same time. If anyone wants to suggest something new, Also tell us.

I took a long time to use prepared statements in a production php application instead of directly constructing the SQL query with the variables I had...

Like $sql = 'SELECT * FROM foo WHERE y = '.$search;

So i was writing sql query and every time i was getting "data type mismatch error."

In frustrations i slammed the table and went outside for some fresh air.

After 15 minutes i came back and runs the query again. Voila, it is working now.

My god these queries burned half of my blood today. LOL. 😁

I had to start over learning SQL when I faced the JSON functions of PostgreSQL during a try and error period to get nested json_agg in one query.. I'am too old for that low level stuff! 🤨

I got a report of a relatively simple WinForms app created by a senior (!!) developer who left just as it was released taking 3 minutes to load.

Step through it.. Narrow it down to one stored procedure.

Open said query, every join is a left join.

None needed to be a left join.

Change them all to inners, app now loads in 5 seconds.

Left Joins: For when people can't be assed to learn SQL basics.

Building a Web dashboard. Stats a day tiles etc.

Boss wants to write sql stored procedures to populate tiles.

Azure dB returns tile list, that maps to angular directive names, use nested angular directive that auto compiles a directive by name, then that directives controller calls a sql query which returns the name of the query to call to receive data, formats the data into charts or lists, builds the tile, places it into a gridster tile array and redirects to new tile pages by UI router state names.

I... I think I have gone too far. What have I done.

So I am struggling with a SQL Query for my Database lecture.

This is the Table Layout:
Users(id:integer, reputation:integer, display_name:string,
day:integer, month:integer, year:integer, location:string,
up_votes:integer, down_votes:integer, age:integer)

This is the task:
Show the set of users who have the highest reputation and the lowest down_votes
than any other user. HINT: there is no user that is better than all other users on each of the
criterion individually. Thus, you need a query that can eliminate users that are worse on both
criteria than some other user (in Economics your query will return what is known as the Pareto
Set).

I have looked up the Pareto Set but I am not really sure how to implement it into SQL.

So does any one of you know how to implement this or could anyone lead me into the right direction?

Help is very appreciated :)

Rant:
Very very hard sql query..

Grrrrrrr!!!!!!! How you frustrate me SQL SERVER REPORTING SERVICES! Designing a report changed query on dataset to include new field, fields started displaying all sorts of random stuff, booleans in text fields etc. Just spent 20 mins "checking" by rebuilding the first few bits of report and first dataset it's something weird with SSRS. Bye bye Sunday evening!!!

Not a rant, but seeking advice...

Should I abandon 2 years' worth of work on migrating a personal project from SQL (M$) to a Graph database, and just stick to SQL? And only consider migrating when/if I need graph capabilities?

The project is a small social media platform. Has around ~50 monthly active users.

Why I started the migration in the first place:
• When researching databases, I read that for social media, graph is more suitable. It was, at least in terms of query structure. It was more natural, there were no "joins", and queries were much simpler than their SQL counterparts.

• In case the project got big, I didn't want to have to panic-deal with database issues that come with growth. I had some indexing issues with MSSQL, and it got me worried that at 50MAU I'm having these issues, what would happen if I get more?

• It's a personal project, and the Gremlin language and graph databases looked cool and I was motivated to learn something new.

----

Why I'm considering aborting the migration:
• It's taking too damn long. I'm unable to work on other features because this migration is taking up all my free time. Sunk cost fallacy is hitting me hard with this one.

• In local testing within docker, it's extremely slow. I tried various graph engines (janusgraph, official tinkerpop, orientdb), and the fastest one takes 4-6minutes to complete my server tests. SQL finishes the same tests in under 2 minutes, same docker environment. I also tried running my tests on a remote server (AWS neptune) and it was just as slow. Maybe my queries are bad, but can I afford to spend even more time fine tuning all queries?

• I now realise that "graph = no scalability issues" was naïve of me, and 100% wishful thinking. Scalability issues don't care what database I use, but about how well tuned and configured the whole system is.

• I really want to move on. My tech stack is falling behind and becoming outdated. I'm unable to maintain dependencies.

• I'm worried about losing those 50 MAU because they're essential to gaining traction once I release the platform. I keep telling them about the migration but at some point (2 years later) they're going to get bored I feel.

I guess partially it's a rant because I feel like I shouldn't stop now having spent 2 years on this, but at the same time I feel like I'm heading towards a dead end.

If you made it this far, thank you for reading:)

Any descent limit value for a SQL query?

I am performing a search operation and i want to limit the scope

I don't want to search the entire table