stonedb-5.7-v1.0.2

[hustjieke]

Changes in StoneDB_5.7_v1.0.2 (2023-01-14, Release Candidate)

Big features:

• support User-Defined Functions(UDFs) to handle the dynamic SQL:

create function function_name();
create function function_name(args...);
create function function_name(args...) returns varchar(50)
begin
  SET @sql= select * from where id= args;
  PREPARE s1 FROM @sql;
  EXECUTE s1;
  DEALLOCATE PREPARE s1;
end $$

• Support ESCAPE Keyword;

% :
SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\%' ESCAPE '\\';
SELECT * FROM BUG_12595 WHERE a LIKE 'hakan%' ESCAPE '';
SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\n%' ESCAPE '\n';
_ :
SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\_' ESCAPE '\\';
SELECT * FROM BUG_12595 WHERE a LIKE 'hakan_' ESCAPE '';
SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\n_' ESCAPE '\n';

• Add syntax support for constraints primary key and index:
  Add primary key

ALTER  TABLE t2 ADD id INT AUTO_INCREMENT PRIMARYKEY;
CREATE TABLE `table_name` (`id` int NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主表id',`name` string NOT NULL COMMENT '名称') ;

Drop primary key

DROP INDEX PRIMARYON t;
ALTER TABLE  tbl_name  DROP {INDEX|KEY} index_name

Add index

ALTER TABLE  tbl_name  ADD {FULLTEXT|SPATIAL} [INDEX|KEY][index_name]
CREATE TABLE t1 (age INT, INDEX USING BTREE (age)) ENGINE = Tianmu;

Drop index

DROP  INDEX  index_name  ON  tbl_name;
ALTER TABLE  tbl_name  DROP {INDEX|KEY} index_name

Rename index

  ALTER TABLE  tbl_name   RENAME {INDEX|KEY} old_index_nameTOnew_index_name

• Support changing the character set of tables/fields by using the alter table syntax；

Change character set of table:

ALTER TABLE tablename convert to character set utf8;

Change character set of table field:

ALTER TABLE tablename MODIFY latin1_text_col TEXT CHARACTERSET utf8;
ALTER TABLE tablename MODIFY latin1_varchar_col VARCHAR(M)  CHARACTERSET utf8;
ALTER TABLE tablename MODIFY latin1_text_col CHAR CHARACTERSET utf8;
ALTER TABLE tablename CHANGE colname colname TEXT CHARACT
ER SET utf8;
ALTER TABLE tablename CHANGE colname colname VARCHAR(M) CACTER SET utf8;
ALTER TABLE tablename CHANGE colname colname CHAR CHARACTER SET utf8;

• Support BIT Data Type:

CREATE TABLE tablename(colname BIT(8));
ALTER TABLE tablename add colname BIT(8) comment '';
ALTER TABLE tablename modify column colname BIT(4);
ALTER TABLE tablename modify column colname varchar(20) ;

• Support replace into SQL Syntax；

REPLACE INTO tablename VALUES(1,'new','2013-07-22 10:30:00');
REPLACE INTO tablename VALUES(1,'Old','2014-08-20 18:47:00');

• Syntactically support unsigned and zerofill:

CREATE  TABLE tablename (colname int(4) unsigned zerofill);
ALTER TABLE tablename change column colname colname int(4) unsigned zerofill;
ALTER TABLE tablename change column colname colname int(4);

• The sql_mode adds the mandatory attribute to make the tianmu as the default:
  Notes: sql_mode ='MANDATORY_TIANMU' > default_storage_engine
  Global

set global sql_mode='MANDATORY_TIANMU';

session

set sql_mode='MANDATORY_TIANMU';

my.cnf

[mysqld] 
sql_mode ='MANDATORY_TIANMU'

startup script

./mysqld --sql-mode='MANDATORY_TIANMU'

• Complete mtr test.

Accessibility Notes

• Automatic detection of installation package and and identification ability.
• Rapid deployment as a secondary database for MySQL.

Docs:
The manual has been updated as the code was modified. ( #doc)

• Bug fix:
  bug: GROUP_CONCAT() function return result set error #938
  bug: LEFT JOIN clause did not return the correct result set #925
  bug: select...in...An error was reported when the date was included #829
  bug: Update multiple values does not work with WHERE IN clause #781
  bug: Master slave synchronization - There will be too many tuples problem #818
  bug: Master slave synchronization - Primary key conflict problem #819
  bug: SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\n%' ESCAPE '\n' or ESCAPE '',ERROR : Incorrect arguments to ESCAPE #273
  bug: SELECT * FROM BUG_12595 WHERE a LIKE 'hakan*%' ESCAPE '*'; The query result is empty #271
  Error:Failed to create table share: bad DPN index file #984
  feature: The Tianmu engine supports user defined function #538
  bug: Tianmu column storage engine - performs HashJoin queries - runs out of disk space #558
  ERROR:assert failed on i < m_idx.size() at rc_attr.h:342, msg: [bad dpn index 0/0]  #909
  bug: Query ERROR 1105 (HY000): assert failed on i < m_idx.size() at rc_attr.h:342 #964
  bug: Query result set error，Involving primary key  #988
  bug: ALTER table DROP primary key is forbidden #1034
  bug: incorrect result set,Trigger with primary key which assigns value selected from table to field of row,being inserted/updated.Test results are wrong #581
  bug:If the field you used in the join condition is primary key, using "NOT EXISTS" will return wrong result. #944
  bug: Tianmu error ERROR 6 (HY000)，when execute select..where like NULL #763
  ERROR 1105 (HY000): The query includes syntax that is not supported by the storage engine. Tianmu: UNION of non-matching columns (column no 2) .bug:  #880
  bug: INSERT INTO table error, but some data can be inserted successfully #965
  bug: ./reinstall.sh automatic installation, the sock file directory is inconsistent with the directory in my.cnf #1075
  bug: ERROR 6 (HY000), The query syntax (WHERE NOT IN ) is not supported #767
  bug: Convert DATETIME to TIME type error #995
  bug: STR_TO_DATE() Function does not work well #975
  feat: load data (Incorrect timestamp value) into a StoneDB table, but No warnning was returned #656
  feature: select..INTO OUTFILE..Add parameters：optionally enclosed by '"' escaped by '"' An error #850
  bug: The data type was not determined during load data #852
  feature: Dose not support the ALTER syntax, ALTER table t1 rename t2, add c char(10); #760

---

This discussion was created from the release stonedb-5.7-v1.0.2.