Haven’t done any sneaky puzzles in a while. How would you accomplish this?
mysql> CREATE TABLE t1 (id int); Query OK, 0 rows affected (0.08 sec) mysql> INSERT INTO t1 VALUES (1); Query OK, 1 row affected (0.02 sec) mysql> SELECT COUNT(*) FROM t1; +----------+ | COUNT(*) | +----------+ | 2 | +----------+ 1 row in set (0.00 sec)
Using Proxy?
Giuseppe
Comment by Giuseppe Maxia — August 19, 2008 @ 12:01 pm
Maybe the size of int (type) is 2. :)
Comment by cett — August 19, 2008 @ 12:07 pm
Giuseppe: Proxy could do it, but I’m not using it.
cett: It isn’t, and that wouldn’t give this output anyway.
Comment by snoyes — August 19, 2008 @ 12:20 pm
Ooh, ooh Stored function or UDF
ala
CREATE FUNCTION count (s CHAR(1)) RETURNS INT
RETURN 2;
which would work for
SELECT COUNT(‘*’) FROM t1;
But I think you might need a real UDF to get it to work without the single quotes.
Comment by Bill Newton — August 19, 2008 @ 12:58 pm
Create a trigger on insert that inserts another row.
Comment by James Bathgate — August 19, 2008 @ 2:02 pm
Before you issue those commands, create a temporary table t1 (doens’t matter what format) which contains 2 rows.
Comment by ryan — August 19, 2008 @ 2:07 pm
Trigger?
Comment by Jeremy — August 19, 2008 @ 2:21 pm
using a trigger? :)
Comment by Tommaso — August 19, 2008 @ 2:50 pm
Damn, this one has me puzzled. Does the default storage engine have anything to do with it? You’re not doing anything with funny combinations of lowecase L and 1 typographical confusion as far as I can see…
I’ve rather missed this series, and this one is really good.
Comment by Xaprb — August 19, 2008 @ 2:55 pm
CREATE TRIGGER myTrigger
AFTER INSERT ON t1
FOR EACH ROW
BEGIN
INSERT INTO t1 VALUES (1);
END;
Comment by bob — August 19, 2008 @ 5:11 pm
Having a temporary table ‘t1’ already?
Comment by Jedy — August 19, 2008 @ 7:03 pm
A temporary table named t1 that already contained one row could do it. It’s not what I used, but it would work, so I’ll give full marks for that answer.
A trigger would not work – you cannot use a trigger to insert into the table that launched it.
Overriding the COUNT function would not work – if you create a function with the same name as a built-in function, you must call it with a space between COUNT and (, and there isn’t one in the test case. I imagine a UDF could handle the unquoted *, but I think you’d still need the space. I guess you could go edit the source, but I think we’ll all agree that’s cheating.
Storage engine doesn’t matter (except it’s not BLACKHOLE). I have not used any typographical tricks.
Comment by snoyes — August 19, 2008 @ 8:03 pm
Something along the lines of this ??
mysql> create table t2(id int) engine=myisam;
Query OK, 0 rows affected (0.24 sec)
mysql> create table t1(id int) engine=merge union(t2) insert_method=last;;
Query OK, 0 rows affected (0.07 sec)
mysql> delimiter //
mysql> create trigger tricky after insert on t1
-> for each row
-> insert into t2 values (1)//
Query OK, 0 rows affected (0.01 sec)
mysql> select count(*) from t1;
-> //
+———-+
| count(*) |
+———-+
| 0 |
+———-+
1 row in set (0.00 sec)
mysql> delimiter ;
mysql> insert into t1 values (1);
Query OK, 1 row affected (0.00 sec)
mysql> select count(*) from t1;
+———-+
| count(*) |
+———-+
| 2 |
+———-+
1 row in set (0.00 sec)
Comment by Tom Hanlon — August 20, 2008 @ 8:16 pm
Tom: Very clever. Not what I did, but it works. In the comments I mentioned that storage engine doesn’t matter, but that wasn’t part of the original puzzle – so full marks to you too. Though your solution makes me wonder if it ought to behave that way – should a trigger on the base table of a MERGE fire when a row is inserted in this fashion?
To repeat what I did, you must become master of your own destiny.
Comment by snoyes — August 20, 2008 @ 8:52 pm
Elementary, my dear, though your clue makes it too easy.
Your database is either set to slave off of itself, or it is slaving off of a slave (with log-slave-updates enabled) of itself (or some combination thereof)… in any case, you set replicate-same-server-id, so your db is re-executing the statement it just wrote to its own binlog :)
Comment by Mike Baehr — August 20, 2008 @ 9:03 pm
Mike: That is what I did. You also have to instruct the slave to skip errors, so you don’t have a problem when creating the table.
Comment by snoyes — August 20, 2008 @ 10:51 pm
Snoyes,
You said.. “it makes me wonder if it should work that way ” in regards to the trigger on a base table in a merge.
In my opinion.. it depends :-)
Somedays I feel like this is a bug, some days I feel like the insert_method feature of merge tables is the bug, sometimes I just don’t worry about it :-)
Comment by Tom Hanlon — August 21, 2008 @ 9:01 pm
While peeling an apple, the answer came to me. So I rushed to my computer and came back to post the answer. And then I saw that someone had already gotten it (slave of self with shoot-my-foot-off settings). Woe is me, I am not the frist psoter. Will I ever recover? If I don’t, it’s your fault.
Back to my apple, now.
Comment by Xaprb — August 27, 2008 @ 12:21 pm