MySQL Bugs: #105034: ROW_COUNT() with CREATE TABLE SELECT is improperly documented, or incorrect

Bug #105034	ROW_COUNT() with CREATE TABLE SELECT is improperly documented, or incorrect
Submitted:	24 Sep 2021 10:16	Modified:	4 Oct 2021 13:09
Reporter:	Saverio Miroddi	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	8.0.23	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When invoked after a sequence of `CREATE TABLE ... SELECT`, `ROW_COUNT()` returns incorrect results.

Now, it's not clear whether this is a bug and/or it's improper documentation.

According to https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_row-count:

  ROW_COUNT() returns a value as follows:
  - DDL statements: 0. This applies to statements such as CREATE TABLE or DROP TABLE. 
  - SELECT: -1 if the statement returns a result set, or the number of rows “affected” if it does not.

Now, first, it should be clarified in the documentation what `CREATE TABLE ... SELECT` constitutes, since it's not obvious; based on my tests, it appears to return the number of records created, however, the statement itself does not return a result set.

Independently of this, there is what I believe is a bug - the results are inconsistent across multiple runs. See the test case.

How to repeat:
CREATE TEMPORARY TABLE main_table (id int) SELECT 1 `id`;

DROP PROCEDURE IF EXISTS sp_test;

DELIMITER $$

CREATE PROCEDURE sp_test()
BEGIN
  DECLARE v_current_id INT DEFAULT 1;

  select_loop: LOOP
    CREATE TEMPORARY TABLE test_table
    SELECT id
    FROM main_table
    WHERE id = v_current_id;

    -- 1 on both loops
    SELECT ROW_COUNT();

    -- 1 on first loop, 0 on the second
    SELECT COUNT(*) `test_table_count` FROM test_table;

    DROP TEMPORARY TABLE test_table;

    SET v_current_id = v_current_id + 1;

    IF v_current_id = 3 THEN
      LEAVE select_loop;
    END IF;
  END LOOP select_loop;
END$$

DELIMITER ;

CALL sp_test();

-- In the case below, the return value is `2`

CREATE TEMPORARY TABLE other_table (id int)
SELECT *
FROM (
  VALUES
    ROW(1),
    ROW(2)
) v
;

SELECT ROW_COUNT();

Suggested fix:
First, the documentation should clarify the case `CREATE TABLE ... SELECT`.

Second, the results, as executed in the test case, should be consistent.

Hi Mr. Miroddi,

Thank you for your bug report.

However, your statements are not clear.

First of all, CREATE ........ SELECT does return a result set, only that result set is saved into the table and not sent to the client side. Hence, returning a result set does not imply that it is returned to the client side.

Regarding inconsistencies, there is nothing inconsistent. First of all, 

SELECT 1 `id`;

returns one row and `id` is treated as an alias.

SELECTs in the stored routines return correct result, since there is only one row in the temporary table. On second time it is zero, since there is no row with value 2;

In the last case, row_count() is 2, because you inserted two rows.

If you provide us with some further proof that our documentation or row_count() function does not run correctly, we shall examine it again.

Let me simplify the test cases (I've added too much information in the first).

Below you will find two cases, which run substantially the same logic, however, they return different results. At the bottom, you'll find my comment. I'm putting the comment about the documentation last.

-- ---------------------------------------------------------------------
-- Case 1 (unrolled version/no stored procedure)
-- ---------------------------------------------------------------------

CREATE TEMPORARY TABLE main_table (id int) SELECT 1 `id`;

CREATE TEMPORARY TABLE test_table_1
SELECT id
FROM main_table
WHERE id = 1;

-- Returns 1: expected, since 1 row is found
--
SELECT ROW_COUNT();

CREATE TEMPORARY TABLE test_table_2
SELECT id
FROM main_table
WHERE id = 2;

-- Returns 0: expected, since no rows are found
--
SELECT ROW_COUNT();

DROP TEMPORARY TABLE main_table;

-- ---------------------------------------------------------------------
-- Case 2 (loop version/inside stored procedure)
-- ---------------------------------------------------------------------

CREATE TEMPORARY TABLE main_table (id int) SELECT 1 `id`;

DELIMITER $$

CREATE PROCEDURE sp_test()
BEGIN
  DECLARE v_current_id INT DEFAULT 1;

  select_loop: LOOP
    CREATE TEMPORARY TABLE test_table
    SELECT id
    FROM main_table
    WHERE id = v_current_id;

    -- Returns 1 on the first iteration: expected
    -- Returns 1 on the second iterarion: unexpected; should be 0, as no rows are found
    --
    SELECT ROW_COUNT();

    SET v_current_id = v_current_id + 1;

    DROP TEMPORARY TABLE test_table;

    IF v_current_id = 3 THEN
      LEAVE select_loop;
    END IF;
  END LOOP select_loop;
END$$

DELIMITER ;

CALL sp_test(); DROP PROCEDURE sp_test; DROP TEMPORARY TABLE main_table;

-- ---------------------------------------------------------------------
-- Comments
-- ---------------------------------------------------------------------

According to the previous comment:

> SELECTs in the stored routines return correct result, since there is only one row in the temporary table. On second time it is zero, since there is no row with value 2;

when inside the stored procedure, the second ROW_COUNT() invocation the result is 1, which is unexpected, not zero as commented.

-- ---------------------------------------------------------------------
-- Documentation
-- ---------------------------------------------------------------------

My concern about the documentation is that it's not obvious at all which should be the result of ROW_COUNT(). With a better understanding, it's evident what the result of a `CREATE TABLE ... SELECT` should be, however, not by reading at the documentation. The reason is that technically, as far as I understand, `CREATE TABLE ... SELECT` is a DDL; the manual states:

> DDL statements: 0. This applies to statements such as CREATE TABLE or DROP TABLE. 

and by looking at it, it would appear that `CREATE TABLE ... SELECT` should cause ROW_COUNT() to return 0.

I think explicitly stating that this is a special case, that returns the number of rows selected, would make this case unambiguous.

Hi,

The output of ROW_COUNT() is correct, because it does not work like COUNT(*) at all. This is documented in our Reference Manual.

> The output of ROW_COUNT() is correct, because it does not work like
COUNT(*) at all. This is documented in our Reference Manual.

I understand; could you point, exactly, to the text(s) in the reference manual? I can't find any that explain all the four results, in the page https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_row-count.

According to:

>  DDL statements: 0. This applies to statements such as CREATE TABLE or DROP TABLE. 

This doesn't hold true for the cases: 1A (result: 1), 2A (result: 1), and 2B (result: 1).

If we use as reference:

> SELECT: -1 if the statement returns a result set, or the number of rows “affected” if it does not. For example, for SELECT * FROM t1, ROW_COUNT() returns -1. For SELECT * FROM t1 INTO OUTFILE 'file_name', ROW_COUNT() returns the number of rows written to the file. 

This doesn't hold true for the case 2B (result: 1, although 0 rows are found).

I don't see any other documentation (at least in that page) that could potentially refer to this ROW_COUNT logic.

Copy of the cases below:

---------------------------------------------------------------------
-- Cases 1A/1B (unrolled version/no stored procedure)
---------------------------------------------------------------------

CREATE TEMPORARY TABLE main_table (id int) SELECT 1 `id`;

CREATE TEMPORARY TABLE test_table_1
SELECT id
FROM main_table
WHERE id = 1;

-- Case 1A; 1 row found; returns 1
--
SELECT ROW_COUNT();

CREATE TEMPORARY TABLE test_table_2
SELECT id
FROM main_table
WHERE id = 2;

-- Case 1B: 0 rows found; returns 0
--
SELECT ROW_COUNT();

DROP TEMPORARY TABLE main_table;

---------------------------------------------------------------------
-- Cases 2A/2B (loop version/inside stored procedure)
---------------------------------------------------------------------

CREATE TEMPORARY TABLE main_table (id int) SELECT 1 `id`;

DELIMITER $$

CREATE PROCEDURE sp_test()
BEGIN
  DECLARE v_current_id INT DEFAULT 1;

  select_loop: LOOP
    CREATE TEMPORARY TABLE test_table
    SELECT id
    FROM main_table
    WHERE id = v_current_id;

    -- Case 2A (1st iteration); 1 row found; returns 1
    -- Case 2B (2nd iteration): 0 rows found; returns 1
    --
    SELECT ROW_COUNT();

    SET v_current_id = v_current_id + 1;

    DROP TEMPORARY TABLE test_table;

    IF v_current_id = 3 THEN
      LEAVE select_loop;
    END IF;
  END LOOP select_loop;
END$$

DELIMITER ;

CALL sp_test();
DROP PROCEDURE sp_test;
DROP TEMPORARY TABLE main_table;

Hi Mr. Miroddi,

If we understood you well, you are asking for more documentation on this issue.

We must inform you that we maintain only Reference Manual and not User's Manual. What you describe belong to the later, which we do not maintain.

I understand, thanks for the clarification :)