Comment by akersten | Hacker News Reader

Comment by akersten | Hacker News Reader

Holy shit.

Not only is that one of the worst-performing SQL queries I've ever seen, the fact that it's being emitted as part of an error message, and clearly not parameterized, is absolutely shocking.

Did a little digging. This[0] is the website of the author of the app allegedly used in the caucusing. And oh boy:

* HTTP

* Copyright 2014

* Half of the showcased examples are broken links

* Weird obsession with assuring that their development is not done by "non-English speakers"

* I'm pretty sure it's just one guy (the VoterClick training videos[1] are done out of his house?)

* I won't even comment on the quality of everything else I'm seeing, my confidence that a functionally sound app could be produced by this firm is already at zero

Someone tell me the caucus administrators were not actually using this? This feels like a fever dream.

[0] http://cerenimbus.com/

[1] http://cerenimbus.com/voterclick.html

Edit: The author of the Twitter thread replied and said they weren't confident it's the same app. That makes me feel a little(?) better - I'll leave my critique of this website for posterity...

The makers of the Iowa Caucus app are: https://shadowinc.io/ .

They received money from the Iowa State Democratic Party, the Nevada State Democratic Party, and Pete Buttigieg for America: https://mobile.twitter.com/lhfang/status/1224572119549267968 .

I’ve been following along https://www.twitch.tv/hasanabi in getting this news.

Also, apparently the CEO of Shadow Inc. spent a year promoting for Hillary for America in 2016 (aka Bernie's chief opponent in the DNC). The CEO's husband also works for Buttigieg.

https://twitter.com/elainelayabout/status/122458125953343897...

https://www.zerohedge.com/political/thousands-furious-suppor...

What exactly makes that "one of the worst-performing SQL queries you've ever seen"?

It has two left joins on foreign/primary keys, and 4 trivially-indexed where conditions. That's all it has, and none of that is even slightly complex.

Maybe my SQL's a little rusty, but isn't a left (outer) join between voters+precincts and (that)+party going to create a massive table with a bunch of nulls inserted? (And, how do you know they're primary keys, and that any of it is indexed? That's not evident from the query)

Maybe "worst I've ever seen" is hyperbolic. My main point was, it's a sloppy query, could probably be done with a few WHERE clauses and an inner join instead, and is more evidence of shoddy coding practices.

The query planner might rewrite it to exactly what you're proposing. Just from looking at it you usually can't identify a bad query.

If you have subqueries the story is a bit different but for this one, it's really anyone's guess.

No, that's not how a left join works.

You really shouldn't act so condescending when you clearly don't even have a solid understanding of the basics yourself. That's a perfectly reasonable query that would run in less than a millisecond on a large database as long as it's been indexed correctly. There's nothing sloppy about it at all.

> No, that's not how a left join works.

It's how a naive implementation would work. Nobody actually knows "how" this left join works since we don't know what the query planner will do. Manifesting a whole outer join is not totally unreasonable in all cases.

I think you come off as very aggressive and there's no need for that tone.

But I do agree with the sentiment that the query might not be bad.

Everybody who reads the query knows how it will work. You don't need to see the query plan.

The where clause at the end will filter to the desired rows. The left join will do absolutely nothing bad.

The purpose of the left join is to include voters who might not be assigned to a precinct.

It's a perfectly fine and performant query.

Outputting the query also doesn't really make any difference. Despite what the people on Twitter are saying showing the query does not make SQL injection easier or harder, it's irrelevant.

The big problem with the query is the lack of bound parameters. It's possible to do that safely in theory, but in practice it's not a good idea.

> Everybody who reads the query knows how it will work. You don't need to see the query plan.

No, we don't. Is there an index join between any of the tables? Is there an index on the name in some fashion and/or on the zip code. All we know is that this query can _probably_ not be answered by index only. What's the relative sizes of the tables (even that's not entirely clear)? Are any tables clustered by the filter criteria?

You wouldn't know and if you think you do, you should read up on possible access plans in modern database systems.

There are only 1,681 voting precincts. And only a couple of parties.

So an index on the join is completely irrelevant.

There are also under a million voters. Even with no indexes at all this query will perform just fine.

Because of the where and the left join there's pretty much just one possible access plan.

Criticize the lack of bound parameters and other problems if you like, but the query itself is perfectly fine.

>one of the worst-performing SQL queries I've ever seen

voter left join precinct left join party where filter(voter)

would work just fine even if precinct and party aren't primary indexed as the number of precincts is just too low (1700) to cause any issues - an optimizer like Oracle would drive the query from the voter while applying filter to it (indexes on the first/last name and/or address would speed the filtering obviously) and looking up precincts by index (in case of precincts not having the index it would just scan the precincts in case of a small filtered voter set or hash the precincts and do hash lookup in case of a larger filtered voter set). For party it is the same, just for 2 parties it doesn't matter - index, scan or hash, etc.

The main issue is as always at business logic and data quality level - it seems that there are supposedly voters without precincts, and precincts without parties :)

Reply on news.ycombinator.com