ctas pk table fail

[chengqianli-git]

Steps to reproduce the behavior (Required)

1. CREATE TABLE '...'
2. INSERT INTO '....'
3. SELECT '....'

CREATE TABLE duplicate_table_demo_datatype_not_replicated_all_varchar ( AAA DATETIME not NULL COMMENT "", BBB VARCHAR(200) not NULL COMMENT "", CCC VARCHAR(200) not NULL COMMENT "", DDD VARCHAR(20000) COMMENT "", EEE LARGEINT  NULL COMMENT "", FFF DECIMAL(20,10) NULL COMMENT "", GGG VARCHAR(200)  NULL COMMENT "", HHH FLOAT  NULL COMMENT "", III BOOLEAN  NULL COMMENT "", KKK CHAR(20)   NULL COMMENT "", LLL STRING   NULL COMMENT "", MMM VARCHAR(20)   NULL COMMENT "", NNN BINARY  NULL COMMENT "", OOO TINYINT NULL COMMENT "", PPP DATETIME NULL COMMENT "", QQQ ARRAY<INT> NULL COMMENT "", RRR JSON NULL COMMENT "", SSS MAP<INT,INT> NULL COMMENT "", TTT STRUCT<a INT, b INT> NULL COMMENT "", INDEX init_bitmap_index (KKK) USING BITMAP ) duplicate KEY(AAA, BBB, CCC) PARTITION BY RANGE (`AAA`) ( START ("1970-01-01") END ("2030-01-01") EVERY (INTERVAL 30 YEAR) ) DISTRIBUTED BY HASH(`AAA`, `BBB`) BUCKETS 3 ORDER BY(`AAA`,`BBB`,`CCC`,`DDD`) PROPERTIES ( "replicated_storage"="false", "replication_num" = "3", "storage_format" = "v2", "enable_persistent_index" = "true", "bloom_filter_columns" = "MMM", "unique_constraints" = "GGG" );

CREATE TABLE new_table1 (AAA,DDD) PRIMARY KEY (AAA) ORDER BY (AAA,DDD) AS SELECT  AAA,DDD FROM duplicate_table_demo_datatype_not_replicated_all_varchar;
[success]

CREATE INDEX idx ON duplicate_table_demo_datatype_not_replicated_all_varchar(DDD) USING GIN('parser' = 'english');

SHOW ALTER TABLE COLUMN where tablename='duplicate_table_demo_datatype_not_replicated_all_varchar' ORDER BY JobId DESC LIMIT 1;

CREATE TABLE new_table2 (AAA,DDD) PRIMARY KEY (AAA) ORDER BY (AAA,DDD) AS SELECT  AAA,DDD FROM duplicate_table_demo_datatype_not_replicated_all_varchar;
[success]


insert into duplicate_table_demo_datatype_not_replicated_all_varchar values ('1996-05-02 16:31:13', 'eKTzvAebxnIhhuwKVcOo', 'tXLPSSMmkHvYIPUtqpta', 'ARjknTrGwvhuCZBAHlUK', 2981, 43246.53760, 'bdHLAKyTFWVjvwbzOzUQ', -46115053940163.6, True, 'NEKLfKXdJgKIfxjikgTW', 'ZfEVNuWfAkRaDZEvPeSf', 'gYJszRoXGAAZjfGPcWTv', '', 2, '1974-06-09 20:01:40', [4957, 8846, 14442], '{"job": "Ceramics designer", "company": "Williams, Guerra and Harris", "ssn": "293-16-8186", "residence": "293 Michelle Knolls Apt. 258\nWilliamhaven, MA 36785", "current_location": ["37.6862115", "-111.268613"], "blood_group": "AB+", "website": ["https://www.evans-burke.org/", "https://johnston-johnson.org/", "http://www.page.com/", "https://www.romero.com/"], "username": "tkline", "name": "Sally Gomez", "sex": "F", "address": "829 Alex Trail\nSouth Catherine, MN 16006", "mail": "[email protected]", "birthdate": "1953-02-10"}', null, null);

CREATE TABLE new_table3 (AAA,DDD) PRIMARY KEY (AAA) ORDER BY (AAA,DDD) AS SELECT  AAA,DDD FROM duplicate_table_demo_datatype_not_replicated_all_varchar;


insert into duplicate_table_demo_datatype_not_replicated_all_varchar values ('2009-05-29 06:48:13', 'qBeSKnqTiQcMYVdBOilC', 'VKMwVckzCVCaDZvDbhTW', 'yNXXrjcqEpNjdrrLhWCw', 8296, -33828.66286, 'OGUPTTBoKpSbNUOnXsgu', -1205.51767338334, True, 'dBWTHpqCURUwEQQpbpML', 'VoEHWPPmETthNehsnVYQ', 'DjDNfiFrnvaUsPZDiGtP', '', 2, '2022-01-23 08:50:20', [1300, 5904, 15809], '{"job": "Engineer, production", "company": "Sims, Sullivan and Williams", "ssn": "265-27-0655", "residence": "99423 Kevin Well Suite 063\nPort Ashleyfurt, MH 23743", "current_location": ["83.4138085", "-168.523560"], "blood_group": "A+", "website": ["http://wilson.com/"], "username": "margaretbriggs", "name": "Diana Patton", "sex": "F", "address": "6563 Raymond View Apt. 374\nNew Elizabeth, PA 13332", "mail": "[email protected]", "birthdate": "1976-10-09"}', null, null);

CREATE TABLE new_table4 (AAA,DDD) PRIMARY KEY (AAA) ORDER BY (AAA,DDD) AS SELECT  AAA,DDD FROM duplicate_table_demo_datatype_not_replicated_all_varchar;

insert into duplicate_table_demo_datatype_not_replicated_all_varchar values ('2002-06-01 06:41:00', 'avyrsNKyHZRZWSQHwEcF', 'rXqOtyBBGSGBZtfMmXGs', 'RGjbgvEWoLjEsluMLvSr', 28, -15084.26432, 'dgaHgKFIQPiPeeIJQzyC', -362432798191.5, False, 'SRKmuyZCEfyHlfyzHqrJ', 'SDWBiWsSxMuMPARxeyYE', 'gWFQBAXYBYRdRjHWHNxj', '', 10, '1977-04-29 00:06:02', [4876, 8942, 16048], '{"job": "Conservator, museum/gallery", "company": "Norris-Gonzalez", "ssn": "198-24-8966", "residence": "435 Alyssa Courts Suite 700\nPort Richard, HI 26864", "current_location": ["87.8411225", "134.128252"], "blood_group": "B+", "website": ["https://lewis.com/", "https://stone.com/", "https://www.gutierrez-graham.com/"], "username": "jeffjohnson", "name": "Bailey Bradford", "sex": "F", "address": "72750 Alyssa Villages\nLake Calebchester, GA 50031", "mail": "[email protected]", "birthdate": "1960-06-27"}', null, null);

CREATE TABLE new_table5 (AAA,DDD) PRIMARY KEY (AAA) ORDER BY (AAA,DDD) AS SELECT  AAA,DDD FROM duplicate_table_demo_datatype_not_replicated_all_varchar;

Expected behavior (Required)

success

Real behavior (Required)

fail

mysql> CREATE TABLE new_table4 (AAA,DDD) PRIMARY KEY (AAA) ORDER BY (AAA,DDD) AS SELECT  AAA,DDD FROM duplicate_table_demo_datatype_not_replicated_all_varchar;
ERROR 1064 (HY000): Distribution column[DDD] is not key column

StarRocks version (Required)

• You can get the StarRocks version by executing SQL select current_version() main-acbb4f7

[srlch]

It seems that it still failed even there is no index.