[MCOL-4453] CREATE TABLE t2 AS SELECT..LIMIT 0 erroneously writes a row into the table Created: 2020-12-16  Updated: 2021-01-25  Resolved: 2021-01-25

Status: Closed
Project: MariaDB ColumnStore
Component/s: None
Affects Version/s: 5.4.3
Fix Version/s: 5.5.2

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MCOL-4455 "SELECT DISTINCT col FROM table LIMIT... Closed
Sprint: 2021-1, 2021-2

 Description   

I run this script with intent to copy the datatype of the column t1.c1 to t2.c2, but without copying the data:

DROP TABLE IF EXISTS t1,t2;
 
CREATE TABLE t1 (c1 INT) ENGINE=ColumnStore;
 
INSERT INTO t1 VALUES (1),(2);
 
CREATE TABLE t2 AS SELECT c1 FROM t1 LIMIT 0;
 
SELECT * FROM t2;


+------+
 
| c1   |
 
+------+
 
|    1 |
 
+------+

Notice, t2 erroneously has one record, while the statement asked to write 0 rows to the destination table t2.

 Comments   
Comment by Alexander Barkov [ 2020-12-17 ]

The problem is repeatable even if a simple SELECT..LIMIT 0.

DROP TABLE IF EXISTS t1,t2;
 
CREATE TABLE t1 (c1 INT) ENGINE=ColumnStore;
 
INSERT INTO t1 VALUES (1),(2);
 
SELECT c1 FROM t1 LIMIT 0;


+------+
 
| c1   |
 
+------+
 
|    1 |
 
+------+

Comment by Alexander Barkov [ 2020-12-17 ]

A related problem: a query with a DISTICT set qualifier ignores the offset in the LIMIT clause:

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (c1 INT) ENGINE=ColumnStore;
 
INSERT INTO t1 VALUES (1),(2),(3);
 
SELECT DISTINCT c1 FROM t1 LIMIT 0,1;


+------+
 
| c1   |
 
+------+
 
|    1 |
 
+------+


SELECT DISTINCT c1 FROM t1 LIMIT 1,1;


+------+
 
| c1   |
 
+------+
 
|    1 |
 
+------+


SELECT DISTINCT c1 FROM t1 LIMIT 2,1;


+------+
 
| c1   |
 
+------+
 
|    1 |
 
+------+


SELECT DISTINCT c1 FROM t1 LIMIT 3,1;


+------+
 
| c1   |
 
+------+
 
|    1 |
 
+------+

Comment by Daniel Lee (Inactive) [ 2021-01-25 ]

Build verified: 5.5.2 (b1552), develop b1546

The fixed also checked into the develop branch

Generated at Thu Feb 08 02:50:27 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.