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.
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...
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.
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.
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.
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.
>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 :)
Is the math on that sheet even correct? E.g., rounding 3.47826 to 4, when the directions seem to indicate that it should round to 3?
Edit: So, if you believe a Medium post with lots of animated GIFs[1], in the case that the total is short, the extra delegate is awarded to the closest to round up. So, given that additional rule, the math checks out, but it's oddly not stated on the worksheet.
The multiplication at the top, likewise, is incorrect. Again, wrong inputs (the total present seems to be 69, not 70); the answer is right given corrected inputs.
The votes? at the bottom also appear to have had someone vote twice. Not clear which candidate got the extra, but I don't think it matters.
Now that I've got a better source, while it looks like there are some cases for a coin-toss, I don't think that's the case here. (Though I did hear that one happened, so perhaps it's in a different location?)
>I should point out that I do not think the terrible incompetent app in my screenshot is being used in Iowa. I doubt it, since the developer’s other apps contain Utah counties and specifically mention the REPUBLICAN caucus.
Bobby Tables
4 Tweets
Joined January 2007
259 Following
279 Followers
These Tweets are protected
Only approved followers can see @farmerchris’s Tweets. To request access, click Follow.
Presuming that the Democratic Party of Nevada paid for the app (given none of the other payments to Shadow seem reasonably related), I suppose this is what you get for $58k. No actual load testing, a buggy app, and authentication tokens being posted online.
Clearly, the company picked to implement this app was not picked for their known ability to deliver enterprise-grade software, but rather, a who's who among the party.
This was 2014. You might think with newer technologies this isn't a problem anymore. He also made "Why Electronic Voting Is Still A Bad Idea" last month: https://www.youtube.com/watch?v=LkH2r-sNjQs
I'd like to head off conspiracy theorizing by reminding everyone that unlike a secret ballot, caucus results are public (everyone at the event sees the count, and remembers it after). This is a flaw with the app that was supposed to aggregate these results, but it does not affect the integrity of the election.
The results are being tabulated by phone, and can be verified by an auditable paper trail. Even without the paper trail, they could be reconstituted (or verified) by asking the people who attended the ~1700 caucus events. This was a disaster, but the caucus results are not suspect because of it.
Are caucus and primaries even legally obliged to have any strict formal standards? It's just party-interal business, right? They could toss a coin, or let anyone vote via mobile app from home...
Sure, the party could opt to not even run a primary process I think (like most parties do when they have an incumbent in office). But they do have to at least appear in these run-offs to be fair and following the process they establish and be transparent to the degree possible.
While true, I think there's still a good 'conspiracy' case to be made when you consider that a clear win in the Iowa Caucasus is big news, moreso when the winner is someone like Sanders.
Fucking this up takes some wind out of the sails of, say, a potentially non-establishment candidate who is expected to win.
This does have the unfortunate consequence of removing the steam from whomever has won (still unknown). But Biden is old school; he'd want to win this fair and square. If the Democratic party wants the best shot at winning 2020, they may be more open to the people's choice this time around. (In 2016, many superdelegates publicly declared support before the caucuses and primaries -- it could have as well swayed voters). All of the reporting in 2016, showed Clinton up by ~800 delegates compared to all candidates, without much explanation into how those delegates are not democratically elected. Even in light of what happened this evening, the party learned from 2016 and is running a more fair process.
> If the Democratic party wants the best shot at winning 2020, they may be more open to the people's choice this time around
I'll admit that I'm unusually cynical of the mainstream Democrats, so far from objective. That said, I truly believe that it's quite possible they would rather have Trump for another term than Sanders, and from that perspective a lot of what's happened makes a perverse kind of sense.
Ummm.. yes, but that doesn't mean he'd like someone else to win by cheating or skulduggery explained as "simple human incompetence". That latter happens all-to-often in local/regional inside-party politics ("whoops, we lost all the ballots from your precinct in a boating accident").
> the party learned from 2016 and is running a more fair process
Yeah, involving a company called "Shadow" staffed by ex-Clintonites to represent final tallies in the first primary and changing the debate rules upon the sudden appearance of a guy worth $60 billion, and they're off to a great start!
For what it's worth, I strongly subscribe to the maxim of not ascribing to malicious intent that which can be better explained by incompetence.
> For what it's worth, I strongly subscribe to the maxim of not ascribing to malicious intent that which can be better explained by incompetence.
Me too, but it's really difficult to not suspect at least some malicious intent in this case.
It's not just Buttigieg's links to Shadow, on top of the ex-Clintonites. It's also things like:
- cancelling the Des Moines Register poll (afaik first time this happened)
- 'mistakes' in the poll results that somehow benefit Buttigieg at the expense of Bernie (others too, but Bernie moreso)
- spending a ridiculous amount of time on reporting the Bernie-favoring results, which basically robs Bernie from declaring victory, and which arguably is the main importance of the whole thing (and iirc this has a big effect on how the rest of the elections go, because Iowa is the first).
- MSM focusing on state delegates when the popular vote clearly favors Bernie, and IMO should be the most important statistic.
There's a bunch more, but even just the above makes it difficult for me to assume that there isn't at least some malice towards a candidate who is generally not favored by the DNC and the DNC-aligned MSM (and corporate interests).
EDIT: I'll add that I'm very open to being wrong. I want to be wrong about all this, and as a non-American I have relatively little actual skin in the game.
53 comments
[ 3.7 ms ] story [ 153 ms ] threadNot 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...
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.
https://twitter.com/elainelayabout/status/122458125953343897...
https://www.zerohedge.com/political/thousands-furious-suppor...
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 "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.
If you have subqueries the story is a bit different but for this one, it's really anyone's guess.
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.
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.
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.
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.
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.
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 :)
In one case, the person posting picture is Ben Halle, the Iowa Comms director for Pete Buttigieg: https://mobile.twitter.com/bhalle87/status/12245501117259161...
Edit: So, if you believe a Medium post with lots of animated GIFs[1], in the case that the total is short, the extra delegate is awarded to the closest to round up. So, given that additional rule, the math checks out, but it's oddly not stated on the worksheet.
The multiplication at the top, likewise, is incorrect. Again, wrong inputs (the total present seems to be 69, not 70); the answer is right given corrected inputs.
The votes? at the bottom also appear to have had someone vote twice. Not clear which candidate got the extra, but I don't think it matters.
[1]: https://medium.com/@IowaDems/caucus-math-in-3-minutes-1b9a49...
Edit edit: Ah, here's a better source, maybe? Four years out of date, and the filename looks to be a letter short: https://www.iowaaflcio.org/system/files/cauus_handbook_dems....
https://twitter.com/farmerchris/status/1224572430066311168
The Twitter user said "the app" but later clarified that it's the Republican Utah app (or something?).
https://twitter.com/farmerchris/status/1224572430066311168
Apologies for any misinformation. Hopefully if it is the Republican app they have time to fix it.
https://mobile.twitter.com/wycats/status/1224558210222186496 https://mobile.twitter.com/WalkerBragman/status/122457313173... https://mobile.twitter.com/leondeba/status/12245736489395077...
Clearly, the company picked to implement this app was not picked for their known ability to deliver enterprise-grade software, but rather, a who's who among the party.
Cite: "Votes in transit" (Scott 2019).
The results are being tabulated by phone, and can be verified by an auditable paper trail. Even without the paper trail, they could be reconstituted (or verified) by asking the people who attended the ~1700 caucus events. This was a disaster, but the caucus results are not suspect because of it.
Funny you should mention that...
https://theweek.com/speedreads/893489/iowa-officially-first-...
Fucking this up takes some wind out of the sails of, say, a potentially non-establishment candidate who is expected to win.
I'll admit that I'm unusually cynical of the mainstream Democrats, so far from objective. That said, I truly believe that it's quite possible they would rather have Trump for another term than Sanders, and from that perspective a lot of what's happened makes a perverse kind of sense.
Ummm.. yes, but that doesn't mean he'd like someone else to win by cheating or skulduggery explained as "simple human incompetence". That latter happens all-to-often in local/regional inside-party politics ("whoops, we lost all the ballots from your precinct in a boating accident").
> the party learned from 2016 and is running a more fair process
Yeah, involving a company called "Shadow" staffed by ex-Clintonites to represent final tallies in the first primary and changing the debate rules upon the sudden appearance of a guy worth $60 billion, and they're off to a great start!
For what it's worth, I strongly subscribe to the maxim of not ascribing to malicious intent that which can be better explained by incompetence.
Me too, but it's really difficult to not suspect at least some malicious intent in this case.
It's not just Buttigieg's links to Shadow, on top of the ex-Clintonites. It's also things like:
- cancelling the Des Moines Register poll (afaik first time this happened)
- 'mistakes' in the poll results that somehow benefit Buttigieg at the expense of Bernie (others too, but Bernie moreso)
- spending a ridiculous amount of time on reporting the Bernie-favoring results, which basically robs Bernie from declaring victory, and which arguably is the main importance of the whole thing (and iirc this has a big effect on how the rest of the elections go, because Iowa is the first).
- MSM focusing on state delegates when the popular vote clearly favors Bernie, and IMO should be the most important statistic.
There's a bunch more, but even just the above makes it difficult for me to assume that there isn't at least some malice towards a candidate who is generally not favored by the DNC and the DNC-aligned MSM (and corporate interests).
EDIT: I'll add that I'm very open to being wrong. I want to be wrong about all this, and as a non-American I have relatively little actual skin in the game.
https://docs.google.com/spreadsheets/d/1VpPjgfoH-n7Ie8OmIa2e...
https://www.kcci.com/article/kcci-iowa-caucus-results/305997...