I have the following query:
select concat(weight, ' ', ids, '\n')
from (
select
tableid,
tupleid,
group_concat(id separator ' ') as ids,
(
select count(*)
from (
select transactionid
from transactionlog
where (tableid, tupleid, querytype) =
(t.tableid, t.tupleid, 'update')
group by transactionid
having count(*) > 0
) v
) weight
from transactionlog t
group by tableid, tupleid
having weight > 0 and count(*) > 1
) u;
However, mysql complains about the reference to t from the innermost query:
ERROR 1054 (42S22): Unknown column 't.tableid' in 'where clause'
Why is this an error? Is this a bug? The MySQL docs on scoping rules
don't say anything about this. I was able to suppress the error with
this hack rewrite:
select concat(weight, ' ', ids, '\n')
from (
select
tableid,
tupleid,
group_concat(id separator ' ') as ids,
(
select count(distinct transactionid)
from transactionlog
where transactionid in (
select transactionid
from transactionlog
where (tableid, tupleid, querytype) =
(t.tableid, t.tupleid, 'update')
group by transactionid
having count(*) > 0
)
) weight
from transactionlog t
group by tableid, tupleid
having weight > 0 and count(*) > 1
) u;
I'm not sure if this creates an additional unnecessary join, though --
trying to make sense of the output of EXPLAIN has been a separate
exercise in frustration all to itself (even with mk-visual-explain).
Thanks in advance for any answers.
--
Yang Zhang
http://www.mit.edu/~y_z/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
