Mysql subquery COUNT in same Table returns NULL

[h2odev]

Hi,
I have this MySQL table and I want to update multiple columns ( children, size) using COUNT() and SUM() on same table.

mytable

id  parentid  name      userid   path    children   privatesize    size
=======================================================================
1   0         Test-1    1        NULL      5        20             125
2   0         Test-2    1        NULL      0        15             15
3   1         Test-3    1        /1/       3        25             75
4   1         Test-4    1        /1/       0        30             30
5   3         Test-5    1        /1/3/     0        10             10
6   3         Test-6    1        /1/3/     1        30             40
7   6         Test-7    1        /1/3/6/   0        10             10
8   0         Test-8    2        NULL      0        20             20

Notes:
size = privatesize + children privatesize
NULL = "", just for demonstration

Let's update for now just one column, children column. Right now I use MySQL stored functions to calculate children:

DELIMITER $$

CREATE DEFINER=`dbname`@`localhost` 
FUNCTION `getchildren`( rowid INT, uid INT ) RETURNS INT(11)

BEGIN

DECLARE children    INT DEFAULT 0;

SELECT COUNT( `mytable`.`id` ) INTO children
FROM `dbname`.`mytable`
WHERE `mytable`.`path` LIKE CONCAT( '%/',rowid ,'/%' ) AND `mytable`.`userid` = uid;

RETURN  children;
END

Test function getchildren:

SELECT dbname.getchildren( 1, 1 );

This returns number 5

To update rows ( Example id 2 and 5 ) I use this query:

UPDATE `dbname`.`mytable`
SET `children` = getchildren( `mytable`.`id` , `mytable`.`userid` )
WHERE `mytable`.`id` IN ( 2, 5 );

And works correctly.

But I don't want to use functions because later I need to update multiple columns (example: size ) and I don't want to call functions for every columns.

For that I have tried this query:

UPDATE `dbname`.`mytable` mt
INNER JOIN  (
        SELECT `mytable`.`path` AS path, COUNT( `mytable`.`id`)  AS countid
        FROM `dbname`.`mytable`
        GROUP BY `mytable`.`userid`
        ) sub ON  `sub`.`path` LIKE CONCAT( "%/", `mt`.`id` , "/%" )
SET `mt`.`children` = `sub`.`countid`
WHERE `mt`.`id` IN ( 2, 5 );

In fact without success, this change the value of children to NULL.

Even the same method (using the same subquery logic) doesn't work in SELECT, returns NULL or returns multiple rows ( with correct count for each group) if I change GROUP BY: id or to path. It looks like COUNT() doesn't work like usual on subquery.

What I'm missing in this query? Can someone explain what really cause this behavior or I'm wrong at all?

Online SELECT Test

Thanks.

[Luuk]

Sorry, i am not explaining what you do wrong,
but maybe this helps:

SELECT 
  mt.*, 
  (select count(*) 
   from mytable mtc 
   where path like concat('%',mt.id,'%')) CNT 
FROM mytable mt ;

this returns:

+----+----------+--------+--------+---------+----------+------+-------------+------+
| id | parentid | name   | userid | path    | children | size | privatesize | CNT  |
+----+----------+--------+--------+---------+----------+------+-------------+------+
|  1 |        0 | Test-1 |      1 |         |        5 |   20 |          95 |    5 |
|  2 |        0 | Test-2 |      1 |         |        0 |   15 |          15 |    0 |
|  3 |        1 | Test-3 |      1 | /1/     |        3 |   25 |          75 |    3 |
|  4 |        1 | Test-4 |      1 | /1/     |        0 |   30 |          30 |    0 |
|  5 |        3 | Test-5 |      1 | /1/3/   |        0 |   10 |          10 |    0 |
|  6 |        3 | Test-6 |      1 | /1/3/   |        1 |   30 |          40 |    1 |
|  7 |        6 | Test-7 |      1 | /1/3/6/ |        0 |   10 |          10 |    0 |
|  8 |        0 | Test-8 |      2 |         |        0 |   20 |          20 |    0 |
+----+----------+--------+--------+---------+----------+------+-------------+------+

[h2odev]

Hi Luuk,
Thanks for reply and your time.

In fact this doesn't resolve my problem. I want to update multiple columns of table `mytable` (children, size) using COUNT() and SUM() on same table `mytable`. Right now I'm using MySQL stored function but i know is possible to update the table without calling functions for each column, but just a JOIN (Equal to virtual/temporary table logic).

Maybe I'm wrong, can you please post a UPDATE query?

Thank You.

[Luuk]

The query with a jOIN, which 'determines' the new value for childeren is:

SELECT  mt.*, count(*) CNT  
from mytable mt, mytable mtc 
where mtc.path like concat('%',mt.id,'%') 
group by mt.id;

Converted into an update:

UPDATE mytable m
LEFT JOIN (
	SELECT  mt.id, count(*) CNT  
	from mytable mt, mytable mtc 
	where mtc.path like concat('%',mt.id,'%') 
	group by mt.id) m2 on m2.id=m.id
SET m.children=ifnull(CNT,0);

[h2odev]

Hi Luuk,

Your query works correctly.

Also I like the idea how you include the WHERE clause and `mytable`.`id` inside subquery, very nice.


I will post here (Just for community wiki) another solution, using User Defined Variables inside User Defined Function to "return" multiple variables.

DELIMITER $$

CREATE DEFINER=`dbuser`@`localhost` 
FUNCTION `getchildren`( rowid INT, uid INT ) RETURNS INT(11)

BEGIN

DECLARE children    INT DEFAULT 0;
SET @childrensize := 0;


SELECT 
    COUNT( `mytable`.`id` ),
    SUM( `mytable`.`privatesize` )
INTO children, @childrensize

FROM `dbname`.`mytable`
WHERE 
    `mytable`.`path` LIKE CONCAT( '%/',rowid ,'/%' )
    AND `mytable`.`userid` = uid;

RETURN  children;
END

Test New Function:

SELECT `dbname`.getchildren( 1, 1 ) AS children, @childrensize AS size;

This returns:

Children   size
===============
5          105

Now let's update rows with id 2 and 5 (both children and size)

UPDATE `dbname`.`mytable`
SET 
    `children` = getchildren( `mytable`.`id`, `mytable`.`userid` ),
    `size` = `privatesize` + IFNULL( @childrensize, 0 )
WHERE `mytable`.`id` IN ( 2, 5 )

The logic is simple, each time the function getchildren is called he updates user defined variable @childrensize. If the row has no children the function set @childrensize to NULL, for that IFNULL( @childrensize, 0 ) is necessary.

In this way, the function will called one time for each row and update multiple columns.


Thank You again Luuk.