20 times around the Wörthersee

ToDo List summer11 Item #1: 20 times biking around the Wörthersee ☑. Last Friday was my last ride and I finished my first ToDo Item 🙂 Spontaneous pics following:

The distance is about 54 km from my home place. It’s about 45 kilometers around the lake. My approximate average speed was between 18 and 21 kmh-1; well, most of the time I was tired after work. 20 times means twice a week. In the middle, I incremented the number to 3 to end earlier and have some space for other projects. 2/3 times I rode my bike in the rain.

One time I saw a free rabbit in the lawn. I have also met some strange chicken next to road 2 times.

I checked out 5 ice cream shops next to the roads, I was riding along. 2 are expensive. One of those did not manage to give me the correct flavor (banana and vanilla have similar, but not equal colors) and it did not taste very good. I have to admit that “Aqua” in Velden had the most delicious cream (not watery and not viscous), the best cone and the price was average (2€ for 2 scoops of ice cream). Actually it’s the ice cream shop where a old school mate served me some ice cream 3 times. Recommendation. 😉

And again: please stop telling me that driving in the dark or in the rain cannot be beautiful.

20 times around the Wörthersee

Gastronomy #fail

A pizza delivery man is handing me in some “Pizza San Daniele” instead of the requested “Pizza Primavera”. He excuses himself by telling me, that I did not have to pay the price of the first pizza (which is a few cents more expensive). Well… but I am vegetarian?!

A ice cream boy handed me in banana instead of vanilla. Well… almost the same color.

In the bakery I was asking for some Poppy Seed Snail (Mohnschnecke [DE]). They gave me some Snail with nuts (Nussschnecke [DE]).

What’s up with the gastronomy industry? 🙁

Gastronomy #fail

When MySQL substr does not work

So let’s start with a simple MySQL setup:

mysql> CREATE TABLE example (prefix VARCHAR(50),
domain VARCHAR(30), PRIMARY KEY(prefix, domain));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO example VALUES
("hello", "world.org"), ("foo", "bar"),  ("Foot","ball");
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

Alright… so we have some sort of “split up” email addresses. It’s not a problem to combine them together on the fly as far as MySQL provides basic string operations.

mysql> SELECT CONCAT(prefix, "@", domain) as email_addr FROM example;
+-----------------+
| email_addr      |
+-----------------+
| foo@bar         | 
| Foot@ball       | 
| hello@world.org | 
+-----------------+
3 rows in set (0.00 sec)

So now let’s say, we want to remove the Top Level Domain from the domain (“world” instead of “world.org”) [1].

mysql> SELECT SUBSTR(domain, 0, LOCATE('.', domain)-1) as tld FROM example;
+-----+
| tld |
+-----+
|     | 
|     | 
|     | 
+-----+
3 rows in set (0.00 sec)

What? Let’s slow down… take a substring of domain starting at position zero and with length of the position of the “.” (dot character) minus 1 (before that dot character). Okay… so something has to be wrong about it.

mysql> SELECT domain as tld FROM example;
+-----------+
| tld       |
+-----------+
| bar       | 
| ball      | 
| world.org | 
+-----------+
3 rows in set (0.00 sec)

A quarter of an hour later I realized the problem with the help of a colleague:

mysql> SELECT SUBSTR(domain, 1, LOCATE('.', domain)-1) as tld FROM example;
+-------+
| tld   |
+-------+
|       | 
|       | 
| world | 
+-------+
3 rows in set (0.00 sec)

SQL (and therefore the substr function) is one-based. So to address the first character you have to specify it using “1”. MySQL uses 0 to tell “no match”.

Stupid world. Why do we have conventions? To break them? I was aware that MATLAB sucks in this regard too, but I was shocked, when I heard about Lua (both languages one-based).

Note [1]. If you really have such an usecase, please refer to SUBSTRING_INDEX.

When MySQL substr does not work