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
Maybe the size of int (type) is 2. :)
Giuseppe: Proxy could do it, but I’m not using it.
cett: It isn’t, and that wouldn’t give this output anyway.
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.
Create a trigger on insert that inserts another row.
Before you issue those commands, create a temporary table t1 (doens’t matter what format) which contains 2 rows.
Trigger?
using a trigger? :)
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.
CREATE TRIGGER myTrigger
AFTER INSERT ON t1
FOR EACH ROW
BEGIN
INSERT INTO t1 VALUES (1);
END;
Having a temporary table ‘t1’ already?
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.
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)
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.
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 :)
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.
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 :-)
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.
Leave a Reply