Bug #16800 wrong "affected rows" if CREATE|INSERT SELECT and SQL_SELECT_LIMIT is used

• Submitted: 26 Jan 2006 8:27
• Modified: 24 Jul 2006 10:10
• Reporter: Guilhem Bichot
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0-bk
• OS: Linux (linux)
• Assigned to: Tomash Brechko
• CPU Architecture: Any

[26 Jan 2006 8:27] Guilhem Bichot

Description:
SLAVE> create table is1(a int);
Query OK, 0 rows affected (0.14 sec)

SLAVE> create table is2(a int);
Query OK, 0 rows affected (0.12 sec)

SLAVE> insert into is1 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

SLAVE> insert into is2 select * from is1 limit 2;
Query OK, 2 rows affected (0.01 sec)               !! ok it says "2" as expected
Records: 2  Duplicates: 0  Warnings: 0

SLAVE> select * from is2;
+------+
| a    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.01 sec)

SLAVE> delete from is2;
Query OK, 2 rows affected (0.00 sec)

SLAVE> set sql_select_limit=2;
Query OK, 0 rows affected (0.01 sec)

SLAVE> insert into is2 select * from is1;
Query OK, 3 rows affected (0.02 sec) !!!!!!!!!!! it says "3"
Records: 3  Duplicates: 0  Warnings: 0

SLAVE> select * from is2;
+------+
| a    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

SLAVE> drop table is2;
Query OK, 0 rows affected (0.00 sec)

SLAVE> create table is2 select * from is1;
Query OK, 3 rows affected (0.11 sec)        !!!!!!!!!!!!! CREATE SELECT has same problem
Records: 3  Duplicates: 0  Warnings: 0

SLAVE> select * from is2;
+------+
| a    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

How to repeat:
see description.

Suggested fix:
make sure the "affected rows" is equal to what was really inserted.

[24 Jul 2006 9:50] Tomash Brechko

According to the section "13.5.3. SET Syntax" of the manual,

"SQL_SELECT_LIMIT does not apply to SELECT statements executed within stored routines. It also does not apply to SELECT statements that do not produce a result set to be returned to the client. These include SELECT statements in subqueries, CREATE TABLE ... SELECT, and INSERT INTO ... SELECT."

So the bug is actually in wrong number of rows _inserted_, not reported.

[24 Jul 2006 10:10] Tomash Brechko

This is not a bug.

As said in the previous comment, SQL_SELECT_LIMIT does not affect number of rows inserted.  However it does affects SELECT * FROM is2;, hence my wrong assumption that only 2 rows were inserted.

CREATE TABLE is1 (i INT);
INSERT INTO is1 VALUES (1), (2), (3);
SET SQL_SELECT_LIMIT=2;
CREATE TABLE is2 SELECT * FROM is1;
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0  // This is correct, all rows inserted,
                                        // SQL_SELECT_LIMIT doesn't matter.

SELECT * FROM is2;
+------+
| i    |
+------+
|    1 |
|    2 |                                // SQL_SELECT_LIMIT is in effect.
+------+

SELECT COUNT(*) FROM is2;
+----------+
| COUNT(*) |
+----------+
|        3 |                            // There are three rows in the table.
+----------+

SET SQL_SELECT_LIMIT=DEFAULT;
SELECT * FROM is2;
+------+
| i    |
+------+
|    1 |
|    2 |
|    3 |                                 // Correct, 3 rows were inserted.
+------+