• Dec 6, 2019

    Finding the Entry with the Largest Value of Another Column in MySQL

    This is totally and 100% cheating. If someone catches you doing this, the SQL police will come after you and cite you for not using a self-join. Just so you know.

    Let’s say you have a table that keeps track of users, colors, and timestamps, and you want to know the most recent color for each user. It might look something like:

    Table: user_colors

    user color timestamp
    Sophie green 2019-08-12 12:34:56
    Sophie blue 2019-03-28 10:42:23
    Gwen red 2018-12-12 03:23:12
    Sophie purple 2013-09-12 04:52:23
    Gwen fuchsia 2017-07-02 11:12:13

    There are two standard solutions.

    SELECT `user`, `color`
FROM (
	SELECT `user`, MAX(`timestamp`) AS `timestamp`
	FROM `user_colors`
	GROUP BY `user`
) `alias1`
INNER JOIN `user_colors`
USING (`user`, `timestamp`)

    Or you could do a left join and handle this with the criterion for joining.

    SELECT `user`, `color`
FROM `user_colors` AS `uc1`
LEFT JOIN `user_colors` AS `uc2`
ON (`uc1`.`user` = `uc2`.`user` AND `uc1`.`timestamp` < `uc2`.`timestamp`)
WHERE `uc2`.`user` IS NULL

    I can never remember how to do the second one, and full disclosure, it might be wrong. I don’t have anything easy around to test it on. I frequently get the direction wrong on the inequality. Sometimes I forget the WHERE clause until I look at my results and notice that they are totally not what I wanted.

    Don’t read any further if you are worried about the SQL police.

    For whatever reason, there is something about my tables and their size and their structure that makes these self-joins really slow. I have come up with a work-around.

    Behold:

    SELECT `user`, SUBSTRING_INDEX( GROUP_CONCAT( `color` 
	ORDER BY `timestamp` DESC
	SEPARATOR "," ), ",", 1 )
FROM `user_colors`
GROUP BY `user`

    The GROUP_CONCAT makes a comma-separted list of all the colors for the user, in descending order by timestamp. The SUBSTRING_INDEX takes the part of the string before the first comma. To be honest, I have no idea what sort of witchcraft the query planner is doing to make this work, but on my tables it is faster than the self-join.

    Note that a GROUP_CONCAT will fail silently and horrifically if you have more than a thousand characters in a group. There are ways around this, but the finer points tend to depend on the system that you’re using.

    Comments
  • Dec 5, 2019

    December Omens (Money Edition)

    1. Gave in to the car’s whining and brought it in for service. Service Advisor 1 confided that I am 2000 miles ahead of each service, so the car started pleading for the 120,000 mile service as I was nearing 118,000 miles. Not only is the 120,000 mile service expensive, but it also involves taking apart a lot of the car and discovering that various expensive parts of the car are leaking essential fluids. Service Advisor 2 offered me a 10% discount on the work for absolutely no reason whatsoever that I could discern. This car needs to last another four years, give or take, because we just bought a new car a few weeks ago, and I would like to stagger the number of years between car purchases.

    2. My year-end bonus will be included in the next pay period. It is roughly double what I paid in car repairs.

    3. One of my yarn-friends is starting at the company full-time on Monday! Assuming that I filled out the refer-a-friend form correctly, I should also be getting a small bonus for that. I’m going to split the after-tax value of that with her. With this money we’d each be able to knit roughly four sweaters in Cascade 220.

    4. You know how they say that you are supposed to save a certain fraction of your income for retirement yadda yadda? I get emails from my financial institution pleading with me to schedule automatic monthly transfers so that I can save money without even thinking about it. I’m not particularly good with money, so instead I tend to wait to roughly this time of year and then move some money into long-term storage. OK, it is not really long-term storage because it’s not an official retirement account. However, I haven’t yet figured out how to get the money back OUT of this account to where I could spend it.

    5. Based on my luck, now would be a great time to short the entire stock market.

    6. I know, I know, retirement account, tax advantages, blah blah blah. But if I want to buy something, like, I dunno, a house in San Diego, I could probably call someone up who would tell me how to take this money out of long-term storage and send it to the house-buying-paperwork-reconciling people.

    7. The apartment downstairs from me is for rent. You can hear me being loud early in the morning for just $2200/month.

    8. I had always thought that the building across the courtyard from mine was roughly the mirror image of my building and that all of the apartments in their building were roughly the same size as the apartments in my building. However, Apartment 1 in the next-door-building has come up for sale, and it is allegedly a 3br 1100 sqft apartment, while all of the units in my building are 2br 800 sqft. I am kind of wondering where the extra space comes from. This apartment also has two nice patios and a garage. If there were an open house scheduled, I’d be tempted to take a look. Listed for $509,000.

    Comments
  • Dec 1, 2019

    How I Know that the Democrats Have No Interest in Winning the 2020 Election

    On a somewhat regular basis you hear people talk about electability. How when you ask a bunch of people would you rather vote for Candidate X or the president who would win in a head-to-head match. This is silly. People who voted for the current president back in 2016 are typically not the sorts of people who will admit that they would vote for anyone else. People who chant about wall-building are not going to be won over by a comprehensive plan to overhaul the corporate income tax structure.

    You know what’s more likely to work? Gaming voter turnout. Especially in key locations. Get the people who side with the president to stay home, and get everyone else to go and vote. And that is how I know that the Democrats have no interest in winning because they have laid none of the groundwork for this.

    First things first – and they should have started on this over a year ago – there should be an internship program for students at HBCUs where they spend the summer driving around rural areas in the south helping poor people get valid ID. Helping people get copies of their birth certificates and marriage certificates and divorce decrees and whatever else from the courthouse. Driving them to the DMV to get the ID. Getting them signed up with some sort of microgrant program (that, to the best of my knowledge, does not currently exist) to pay for all the copies of the various documents that they need. Replicate as necessary in other communities. Run this as a non-partisan non-profit, so that it can partner with organizations (such as churches) that are supposed to stay out of politics.

    Likewise, a non-partisan non-profit should be working with community organizations to develop plans to arrange rides to polling places.

    These plans should be focused on locations where there have been closures of ID-granting government offices and consolidation of polling places and limited or non-existant public transportation options. Since we are told that the closures of various offices and polling places was most definitely for-sure not imposed in a biased way, any work to make it easier to get ID in those locations can’t possibly be political either.

    But I have seen absolutely nothing about this. No one has asked me for any money to fund such an effort or to to fund a microgrant program for helping people get IDs. And people love asking me for money! I am regularly invited to invest in shady investments that I am not legally allowed to invest in, like funding a Bay Area-based start-up that hopes to disrupt laundry (I am not joking).

    There is the other side of the coin as well. There is not a successful propaganda machine combatting the president. And I know this because I have various Facebook friends who seem weakly aligned with him, and if there is any propaganda out there, it is not compelling enough for them to be sharing it on Facebook.

    The Russians have spent years developing minor influencers on social media who have been sharing the sort of banal but uplifting content that gets a lot of shares. The celebrity who went undercover building houses for Habitat for Humanity. That sort of thing. The picture of the ugly dog with the caption “I’m so ugly that I bet I won’t even get one share.” This is a key piece of the mechanism to depress turn-out. A small fraction of the shared stories need to be subtle digs at the president.

    Here are some examples:

    1. A federal corrections officer (ex-military) who is still suffering after the government shut-down. The prisioners that he guards get to sit around and watch TV all day or whatever, and he had to work without pay, and his truck got repossesed. The government should be looking out for hard-working Americans and not criminals.

    2. The ladies who run one of those Christian adoption agencies that won’t adopt kids to gay couples are sad about the conditions where the immigrant children are being detained. They would be able to take such great care to the surrendered children of drug addicts for $750/day and find them good Christian homes! The government should stop being so wasteful and should help American children first.

    3. Military family with young kids living in substandard military housing. Military dad says that he understands how important it is to protect our country. His pregnant wife has to breathe in black mold in their tiny and terrible home on base because the money that was set aside to fix it was diverted to pay for The Wall. Wasn’t Mexico supposed to pay for the wall? The government needs to support our troops at home so that they can defend us effectively.

    4. Someone in the manufacturing sector whose job went overseas. “We work hard and produce the highest quality widgets in the world.” (Subtext: “those people” produce inferior widgets.) The government sided with The Man and let the jobs go overseas.

    5. Hardworking soybean farmer is watching his crops die in the fields. He is a victim of the trade war with China. He doesn’t want the government handout to “bail out” the farmers. He is not afraid to work hard, and he grows the best soybeans in the world, and he just wants to compete in a fair marketplace.

    6. In October 2020 when lots of people are going through open enrollment for their health insurance plans: Some hard-working person who is faced with terrible yet expensive health insurance plans laments that the government was supposed to fix health insurance. They’ve had four years, and they haven’t done anything. Washington as usual.

    There should have been a steady drip of these over the past few years. Every time something happens to hurt hard-working “real Americans,” there should be a hard-working “real American” complaining about “the government” and “Washington as usual.” The goal is not to get them won over to the charms of the Democratic party. The goal is to get them sufficiently disillusioned that nothing is going to change that they stay home on election day. Especially if it is raining.

    Comments
  • Nov 30, 2019

    Secret Messages

    1. My car continues to send me messages. TIRE PRESSURE LOW. FILL WASHER FLUID. BOOK TIME FOR MAINTENANCE. TIME FOR REGULAR MAINTENANCE. STOP BUYING YARN.

    2. Sophie the Cat conflates loneliness with hunger. We tend to feed her in the morning and when we get home from work. I have been working on training her, and I think that she understands the words “hungry” and “petting.” If I ask, “Sophie, do you want petting?” she will jump on the couch and wait for me to pet her (if that’s what she wants). On the other hand, if I ask, “Sophie, do you want food?” she will run to the food dish (if that’s what she wants). I also think that she rubs the top of her head on my feet when she wants to be petted.

    3. The other day at work we had a “hackathon,” and my group worked on the first steps towards having the computer read aloud the content on the page. This is much harder for mathematical notation than it is for English words. It’s also harder when your product uses weird math notation with a home-grown parser so you can’t rely on MathML’s accessibility functions. We have taught the computer to say things like “hippopotamoose over the quantity two plus the variable x end quantity plus the fourth root of the variable x plus pink lightning end root.”

    "hippopotamoose over the quantity two plus the variable x end quantity plus the fourth root of the variable x plus pink lightning end root"

    We have not yet received a feature request for “demanding parent mode” in which the website would say things like “how could you not know that?” or “you are such a disappointment” or “why can’t you be more like your brother?”

    Comments
  • Nov 20, 2019

    Holiday Gift Guide for Your Infosec Friends

    1. USB data blocker. Some of the charging stations in airports and other public places have both regular electrical outlets and USB ports for power. Are the USB ports just sending power to your device or are they also sending malware from Belarusian hackers? As you might guess from the name, a USB data blocker doesn’t let any data through, just electricity.

    2. Faraday bag. Your phone has a microphone and has radios to connect to all sorts of networks. How do you know that it is not spying on you? How do you know that it is really off when you tell it to turn off? It might still be spying on you but with a dark screen. Or are people driving past your house trying to get your phone to join rogue wifi networks? Protect yourself from these very likely situations with a faraday bag. Conspicuously keeping your personal cell phone in a faraday bag while at work gives you plausible cover for not responding to work calls on your personal cell phone outside of work hours.

    3. Hockey puck. Use your label maker and make a sticker that says “Infosec Alexa” and give it to your friends! For bonus fun, if you know someone with a laser cutter that can etch things into a hockey puck, you can put the Bluetooth logo on the bottom and cover it with a piece of black electrical tape before giving the gift!

    Comments
  • Nov 18, 2019

    Not Like Other People

    Subtitle: So many people want me to do “cute,” but I don’t want to.

    1. That online shopping service that sends you a box of clothes and you send back the ones that you don’t want. I tried them, and I didn’t like the clothes. They want me to try again, and they send me flyers in the mail and email with pictures of clothes. The clothes are all terrible. Rather, the clothes are all cute.

    2. Once upon a time I lived in a colony of feral mathematicians, and it was wonderful.

    3. Even when I got really into shopping and having a whole bunch of nice clothes, they were not cute. I forget how many black skirts I had. I don’t really have to sort my laundry because everything I own is darks.

    4. Just the idea of the cute clothes makes me sort of anxious and panicked.

    5. The dentist keeps trying to talk me into getting braces, but I really and truly like the way that one of my front teeth crosses one in front of the other.

    6. My coworkers criticize my choice of editor. They all favor the sorts of dark backgrounds that remind me of the Apple ][+ or the VT100. I use minimally syntax-colored text on a white background with an editor with very few features.

    7. Sadly, it’s been too long since I used emacs on a regular basis. I may need to brush up on that if people keep complaining about my choice of editor. The combination of emacs with the Dvorak key layout will make it impossible for most people to do anything on my computer.

    Comments
  • Nov 16, 2019

    Homemade Decor from the Cats

    Cat: Did you see that felted wool garlands are all the rage this holiday season?

    Me: Felted wool garlands?

    Cat: Little globs of felt on a string.

    Cat:

    Cat: Because if you were looking for something like that made out of partially digested cat fur, I think I could hook you up.

    Comments

subscribe via RSS