Skip to main content

One more SQL hack (or How I Married a Genius)

Here's my problem.  I'm making the following SQL call from my application:

select a.*, b.* from foo a, bar b where a.[column]= b.[column]

I don't know on the application end what fields are in what table, and I don't know what field the join will be on -- that's all dynamic.

I need to know from looking at my result set which table each column came from.  The problem of course is that I can't (at least in mysql) because if you run a select with a *, you only get back column names as a headers.

After beating my head against it, and not coming up with a solution, I IMed my big brain DB team, Greg and Ellie, but they were both apparently at lunch.  Then I remembered at my new job we have a $50k/yr contract with MySQL for support, so I called them.

It took quite a while to explain to the 1st level of customer support what the problem was.  He kept insisting the answer was to use an "as", but I tried that long before I called, and it doesn't work. I had tried:

select a.* as foostuff, b.* as barstuff from foo a, bar b where a.[column]= b.[column]


in the hopes I would get back foostuff.id, foostuff.name,bar.id etc etc etc..  No joy.  It still gives you the same tableless headers.  Eventually 1st level customer support kicked it upstairs.  2nd level customer support called me within 1 minute of that happening.  Very impressive.

"It can't be done,"  He let me know.  "When you use the * you are telling the DB to not include table names."  Yes yes, I could use the metadata or information_schema to get around this, but I didn't want to do that because of all the extra junk it involves.

So I resigned myself to having to preload the metadata in the app, and write out more detailed queries.

Fortunately I married a genius, who having finally come back from lunch, within 5 minutes of me explaining the problems said

"select a.*, 1 as NEWTABLE, b.* from foo a, bar b where a.[column]= b.[column]"

GENIUS!  Now I know everything in the result set to the left of the NEWTABLE is a foo, and everything to the right is a bar!!!  GENIUS!!!

And considering I spent (much) less than $50k on the ring 8 years ago, is a real bargain in customer support!

Comments

--- said…
That's hilarious and a nice hack ;)
3:08 PM
Post a Comment

Popular posts from this blog

October is Cyber Security Month

The President has declared October as Cybersecurity month.  It's not a bad idea -- just like you change the batteries in your smoke detectors once a year, maybe you should review your electronic vulbnerabilities? My top ten security tips: 1) Change your passwords.  You've had them too long, you use the same password in too many places.  Somewhere someone has hacked a site that has your username and password in plain text.  Now they are getting ready to try that username/password somewhere else.  Beat them to the punch. 2) Use a safe browser.  That means anything that's not Internet Explorer.   I prefer chrome. 3) Use 2 step verification for your email account.  If your email doesn't provide 2 step authentication consider switching. 4) Get a free credit report  and review it.  You are entitled to one free report a year.   BE VERY CAREFUL!  There are man scam sites that offer free credit reports.  Go through the s...
16 comments
Read more

So You're Looking for Work in Tech

So You're Looking for Work in Tech It's a more competitive market than it's ever been—but don't despair! There are still plenty of jobs out there for humans who can demonstrate insight, creativity, and the ability to execute. Here's a practical guide to help you prove you can do just that. 0. File for Unemployment (If Applicable) If you were recently laid off, file for unemployment right now . This won’t help your job search directly, but it will help you financially. Get that support—you earned it. 1. Buy a Domain and Invest in Yourself If you don’t own a domain, buy one today. Get a GSuite (Google Workspace) account and start using a professional email like yourname@yourdomain.com . Avoid using email services like GoDaddy, Zoho, or Office365. Google is the gold standard—invest in the best. 2. Hire Yourself Give yourself a tech project—because this is your job now. Choose a project that will add value to your life while forcing you to learn new ...
4 comments
Read more

Using hosts.deny against bad guys.

Executive Summary: This article details how to minimize hack attempts on open facing ports on a *nix machine, by reading the attackers IP addresses from a log file, and adding the address to hosts.deny Chances are pretty good if you've got a port open to the internet you're seeing unwanted traffic on it. If you've got a service such as telnet or FTP or POP that transmits passwords unencrypted, you're significantly at risk for a breach from a packet being intercepted, but even if you're using a secure protocol such as SSH, you're vulnerable to a brute force attempt to break into your site. As an example, here is a sample of a log file showing a break-in attempt on one of my servers: Oct 8 13:54:18 chapelle sshd(pam_unix)[4554]: authentication failure; logname= uid=0 euid=0 tty=ssh ruser= rhost=59.13.157.12 Oct 8 13:54:22 chapelle sshd(pam_unix)[4557]: authentication failure; logname= uid=0 euid=0 tty=ssh ruser= rhost=59.13.157.12 Oct 8 13:54:27 chapelle ssh...
3 comments
Read more