Date: 2013-03-08 17:17:23 +0100
From: Percy Wegmann <>
To: SQL devs <>
Version: 11.15.11 (Feb2013-SP3)
CC: @njnes

Last updated: 2013-07-03 13:56:40 +0200

Comment 18600

Date: 2013-03-08 17:17:23 +0100
From: Percy Wegmann <>

User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_7_5) AppleWebKit/537.22 (KHTML, like Gecko) Chrome/25.0.1364.155 Safari/537.22
Build Identifier:

Using an OFFSET clause in an INSERT INTO ... SELECT * FROM ... query leaves the destination table in an inconsistent state. Querying that table with an OFFSET returns a result that indicates we got rows, but no rows are actually returned.

Note - this problem is only reproduceable once the source table reaches a certain size. I haven't figured out what size that is, but when I tried it with really small tables it was fine.

Reproducible: Always

Steps to Reproduce:

Transcript from mclient session (with comments inline)

sql>CREATE TABLE TA(CA VARCHAR(9999999), CB VARCHAR(9999999));
operation successful (26.946ms)
sql>CREATE TABLE TB(CA VARCHAR(9999999), CB VARCHAR(9999999));
operation successful (25.819ms)
sql>INSERT INTO TA VALUES('A', 'B');
1 affected row (9.548ms)
sql>INSERT INTO TA SELECT * FROM TA;
1 affected row (13.130ms)
sql>INSERT INTO TA SELECT * FROM TA;
2 affected rows (5.538ms)

... and so on to load up TA

sql>SELECT COUNT(*) FROM TA;
+----------+
| L1 |
+==========+
| 16777216 |
+----------+
1 tuple (1.608ms)
sql>INSERT INTO TB SELECT * FROM TA OFFSET 8000000;
16777216 affected rows (2.7s)

-- Here is the first hint that something is wrong -- I should have gotten 8777216 affected rows, but shell reported 16777216 rows

sql>SELECT * FROM TB LIMIT 2 OFFSET 8000000;
+------+------+
| ca | cb |
+======+======+
+------+------+
2 tuples (682.929ms)

-- Hmm, it says there are 2 tuples, but it doesn't show any ...

sql>DROP TABLE TB;
operation successful (26.977ms)
sql>CREATE TABLE TB(CA VARCHAR(9999999), CB VARCHAR(9999999));
operation successful (69.594ms)
sql>INSERT INTO TB SELECT * FROM TA LIMIT 8777216;
8777216 affected rows (2.0s)

-- LIMIT seems to work fine though

sql>SELECT * FROM TB LIMIT 2 OFFSET 8000000;
+------+------+
| ca | cb |
+======+======+
| A | B |
| A | B |
+------+------+
2 tuples (651.026ms)

-- Yes, LIMIT works

-- Does it work if I use a bind parameter for OFFSET?

sql>DROP TABLE TB;
operation successful (43.317ms)
sql>CREATE TABLE TB(CA VARCHAR(9999999), CB VARCHAR(9999999));
operation successful (93.950ms)
sql>PREPARE INSERT INTO TB SELECT * FROM TA OFFSET ?;
execute prepared statement using: EXEC 14(...)
+------+--------+-------+--------+-------+--------+
| type | digits | scale | schema | table | column |
+======+========+=======+========+=======+========+
| wrd | 64 | 0 | null | null | null |
+------+--------+-------+--------+-------+--------+
1 tuple (1.673ms)
sql>EXEC 14(8000000);
16777216 affected rows (2.7s)
sql>SELECT * FROM TB LIMIT 2 OFFSET 8000000;
+------+------+
| ca | cb |
+======+======+
+------+------+
2 tuples (669.347ms)

-- No, it doesn't work with bind parameters either

sql>

Actual Results:

The table created by using this query is missing data and seems to be in a strange state:

INSERT INTO TB SELECT * FROM TA OFFSET 8000000;

The table created with this query is fine though:

INSERT INTO TB SELECT * FROM TA LIMIT 8777216;

Expected Results:

Both OFFSET and LIMIT should work

If the OFFSET clause isn't supportable in an INSERT query, a minimum step would be to remove support for it from the parser.

Comment 18902

Date: 2013-07-03 13:56:40 +0200
From: @njnes

Could you test the current version