Rob’s blog

On IRC the other day someone asked about the order in which set operations are performed. I replied something to the effect of it not being guaranteed (“Single-table UPDATE assignments are generally evaluated from left to right… “) to be left to right, but that using User Defined Variables could guarantee deterministic behavior. The fellow ended a bit confused (my fault) and using two queries… However, he could have done something like the following:

mysql> INSERT INTO t (c1,c2)
    -> VALUES(0,0),(1,1);
Query OK, 2 rows affected (0.17 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> UPDATE t
    -> SET c1=@var+1,
    -> c2=@var
    -> WHERE
    -> IFNULL(
    ->       IF(
    ->              @var :=c1
    ->       ,1,1)
    -> ,1);
Query OK, 2 rows affected (0.22 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> SELECT * FROM t;
+------+------+
| c1   | c2   |
+------+------+
|    1 |    0 |
|    2 |    1 |
+------+------+
2 rows in set (0.00 sec)

One might ask what is with all the ugliness surrounding the udv assignment? Well if the where clause does not return something that can be converted to a non-zero integer for all expressions/whatever you get nota for updates or selects. This is problematic if the assignment is to null, 0, or a fully numeric string. So we really want the assignment to return 1 always.

mysql> SELECT 1 FROM dual WHERE 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> SELECT 1 FROM dual WHERE null;
Empty set (0.00 sec)

mysql> SELECT 1 FROM dual WHERE 0;
Empty set (0.00 sec)

mysql> select 1 from dual where 'asdf1';
Empty set, 1 warning (0.00 sec)

mysql> select 1 from dual where '1asdf';
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set, 1 warning (0.00 sec)

(it’s too bad I have to use dual here, I feel dirty in an expensive Oracleish way)

mysql> SELECT 1 WHERE 1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version
 for the right syntax to use near 'WHERE 1' at line 1

[rob@dev rpm]$ svn s
Unknown command: ’s’
Type ’svn help’ for usage.

[rob@dev rpm]$ svn st
(works like svn status)

[rob@dev rpm]$ svn sta
Unknown command: ’sta’
Type ’svn help’ for usage.

[rob@dev rpm]$ svn stat
(works like svn status)

[rob@dev rpm]$ svn statu
Unknown command: ’statu’
Type ’svn help’ for usage.

[rob@dev rpm]$ svn status
(is svn status…)

Interesting.

This script will find all tables that have a column that has the name of the first argument. If there is a second argument it will query the tables how many times the value exists in the column of the various tables. I imagine sooner or later I will improve the ability to quote…

#!/bin/bash
my="mysql -u YOURUSERNAME -pYOURPASSWORD -e"

if [ -p $1 ]; then
        echo "usage: find.sh field (value)"
        exit;
fi

for i in $($my "SELECT CONCAT(TABLE_SCHEMA,'.',TABLE_NAME) AS ''
        FROM information_schema.COLUMNS
        WHERE column_name='$1';"); do
        if [ -p $2 ]; then
                echo $i
        else
                query="SELECT COUNT(*) AS ''
                          FROM $i
                          WHERE $1=$2;"
                echo $i $($my "$query")
        fi
done