[Bug] duplicate sort key will cause be crash or compaction fail

[luohaha]

Steps to reproduce the behavior (Required)

1. create table:

   CREATE TABLE `demo333` (
   `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT "",
   `c1` int(11) NULL COMMENT "",
   `c2` int(11) NULL COMMENT "",
   `c3` varchar(100) NULL COMMENT "",
   `c4` varchar(100) NULL COMMENT "",
   `c5` varchar(100) NULL COMMENT "",
   `c6` varchar(100) NULL COMMENT "",
   `c7` tinyint(4) NULL COMMENT "",
   `c8` varchar(100) NULL COMMENT "",
   `c9` varchar(100) NULL COMMENT "",
   `c10` bigint(20) NULL COMMENT "",
   `c11` bigint(20) NULL COMMENT ""
   ) ENGINE=OLAP
   PRIMARY KEY(`id`)
   DISTRIBUTED BY HASH(`id`) BUCKETS 1
   ORDER BY(`c5`, `c1`, `c3`, `c4`, `c5`, `c6`)
   PROPERTIES (
   "replication_num" = "1",
   "in_memory" = "false",
   "enable_persistent_index" = "false",
   "replicated_storage" = "true",
   "compression" = "LZ4"
   );

2. load data.

Expected behavior (Required)

Everything normal

Real behavior (Required)

Compaction fail:

W0928 10:57:46.964624 2551693 storage_engine.cpp:975] failed to perform update compaction. res=Internal error: update compaction rows read(9) != rows written(0)

StarRocks version (Required)

• 3.1.x

[PawaseBB]

The provided SQL code appears to be defining a table named demo333 with a number of columns and various table properties. However, there are a couple of issues in the code:

1. Comment Syntax: You have empty double quotes inside the COMMENT clauses for several columns. Comments should be enclosed within single quotes (') instead of double quotes. For example, COMMENT '' should be changed to COMMENT ''.

2. ENGINE=OLAP: The ENGINE keyword is used to specify the storage engine for a table in MySQL, and "OLAP" is not a valid storage engine in standard MySQL. MySQL typically uses storage engines like InnoDB, MyISAM, etc. If you intended to use a different storage engine, you should specify the correct one.

3. DISTRIBUTED BY HASH and BUCKETS 1: These clauses are not standard MySQL syntax. They appear to be specific to a distributed database system like Apache HBase or Apache Phoenix. If you are using such a system, make sure the syntax is correct for that system.

4. ORDER BY Clause: While it's not incorrect, it's worth noting that the ORDER BY clause in MySQL typically defines the default order for the table rows when retrieved without an explicit ORDER BY in a query. If this is your intention, then it's fine.

Here's a modified version of your SQL code with the comments fixed:

CREATE TABLE `demo333` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '',
  `c1` int(11) NULL COMMENT '',
  `c2` int(11) NULL COMMENT '',
  `c3` varchar(100) NULL COMMENT '',
  `c4` varchar(100) NULL COMMENT '',
  `c5` varchar(100) NULL COMMENT '',
  `c6` varchar(100) NULL COMMENT '',
  `c7` tinyint(4) NULL COMMENT '',
  `c8` varchar(100) NULL COMMENT '',
  `c9` varchar(100) NULL COMMENT '',
  `c10` bigint(20) NULL COMMENT '',
  `c11` bigint(20) NULL COMMENT ''
) ENGINE=InnoDB
PRIMARY KEY(`id`)
ORDER BY(`c5`, `c1`, `c3`, `c4`, `c5`, `c6`)
;

Please adjust the ENGINE and other clauses according to your specific database system if you are not using standard MySQL.

[github-actions[bot]]

We have marked this issue as stale because it has been inactive for 6 months. If this issue is still relevant, removing the stale label or adding a comment will keep it active. Otherwise, we'll close it in 10 days to keep the issue queue tidy. Thank you for your contribution to StarRocks!