polardbxengine/mysql-test/suite/x/t/delete_sql_o.test

########### ../t/delete_sql.test                           #############
###                                                                    #
### This test runs aims to run DELETE statement variant                #
### with mysqlxtest client.                                            #
### Test covers                                                        #
###  - delete with safe_update mode(ON/OFF)                            #
###  - delete with WHERE,LIMIT,ORDER BY clauses                        #
###  - delete on multiple table                                        #
###  - delete with transaction                                         #
###  - delete with partition                                           #
###                                                                    #
########################################################################
#

--echo ==============================================
--echo     SQL DELETE SCENARIOS
--echo ==============================================
--echo 

## Preamble
--echo ================================================================================
--echo PREAMBLE
--echo ================================================================================
--source include/xplugin_preamble.inc
CREATE USER deletesqluser@localhost  identified by 'deletesqluser';
GRANT ALL ON *.* TO deletesqluser@localhost;

LET $orig_sql_safe_updates = `select @@global.sql_safe_updates`;

## TEST STARTS HERE
--echo ================================================================================
--echo TEST START
--echo ================================================================================
--write_file $MYSQL_TMP_DIR/mysqlx-delete_sql.tmp
-->sql
 DROP SCHEMA if EXISTS MySQLXplugin;
 CREATE SCHEMA MySQLXplugin DEFAULT CHARSET='utf8';
 USE MySQLXplugin;

 CREATE TABLE Categories (
 CategoryID int NOT NULL AUTO_INCREMENT,
 CategoryName varchar(100),
 CategoryDescription varchar(200),
 CategoryIMEI tinyint,	  
 CategoryDecimal decimal(5,2),
 PRIMARY key (CategoryID)
 );

 INSERT INTO Categories(CategoryID, CategoryName, CategoryDescription, CategoryIMEI, CategoryDecimal)
 VALUES
 (1,'Sports','Sports related category',1,235.15),
 (2,'Entertaiment','Entertaiment related category',2,235.15),
 (3, 'Home','Home related category',3,235.15),
 (4, 'Kitchen','Kitchen related category',4,235.15);

 CREATE TABLE ExtraCategoryInfo (
 ExtraCategoryInfoID int NOT NULL AUTO_INCREMENT,
 CategoryID int NOT NULL,
 MainParentCategoryName varchar(100),
 PRIMARY key (ExtraCategoryInfoID)
 );

 INSERT INTO ExtraCategoryInfo(ExtraCategoryInfoID, CategoryID, MainParentCategoryName)
 VALUES
 (1,1,'People'),
 (2,2,'People'),
 (3,2,'House'),
 (4,3,'House');
 
 ## SQL safe updates as 1 will allow to delete only when you use a key column in the wehere clause
 SET SQL_SAFE_UPDATES = 1;

 DELETE FROM ExtraCategoryInfo WHERE ExtraCategoryInfoID in (2,3) AND CategoryID = 2 LIMIT 1;
 DELETE FROM ExtraCategoryInfo WHERE ExtraCategoryInfoID = 1;

 ## Error expected because of the safe mode = 1
-->endsql
-->expecterror 1175
-->sql
 DELETE FROM ExtraCategoryInfo WHERE MainParentCategoryName = 'People';
-->endsql
-->expecterror 1175
-->sql
 DELETE FROM ExtraCategoryInfo WHERE MainParentCategoryName = 'House' AND CategoryID in (4);
-->endsql
-->expecterror 1175
-->sql
 DELETE FROM Categories WHERE CategoryIMEI = 1;
-->endsql
-->expecterror 1175
-->sql
 DELETE FROM ExtraCategoryInfo WHERE EXISTS 
  (SELECT * FROM Categories WHERE Categories.CategoryID = ExtraCategoryInfo.CategoryID 
  AND ExtraCategoryInfo.MainParentCategoryName = 'People' );

 SELECT * FROM ExtraCategoryInfo;

 # Run with transction
 START TRANSACTION; 
 INSERT INTO ExtraCategoryInfo(ExtraCategoryInfoID, CategoryID, MainParentCategoryName)
 VALUES
 (5,4,'Hotel'),
 (6,4,'Hotel');
 SELECT * FROM ExtraCategoryInfo;
 DELETE FROM ExtraCategoryInfo WHERE ExtraCategoryInfoID = 5;
 SELECT * FROM ExtraCategoryInfo;
 COMMIT;

 INSERT INTO ExtraCategoryInfo(ExtraCategoryInfoID, CategoryID, MainParentCategoryName)
 VALUES
 (7,1,'GROUND'),
 (8,1,'GROUND');
 SELECT * FROM ExtraCategoryInfo;
 START TRANSACTION; 
 DELETE FROM ExtraCategoryInfo WHERE ExtraCategoryInfoID = 7;
 SELECT * FROM ExtraCategoryInfo;
 ROLLBACK; 
 SELECT * FROM ExtraCategoryInfo;

-->endsql
EOF

--exec $MYSQLXTEST -u deletesqluser --password='deletesqluser' --file=$MYSQL_TMP_DIR/mysqlx-delete_sql.tmp 2>&1
--remove_file $MYSQL_TMP_DIR/mysqlx-delete_sql.tmp

--echo .
--echo #---------- SAFE MODE OFF ----------#
--echo .


--write_file $MYSQL_TMP_DIR/mysqlx-delete_sql_safemode_0.tmp
-->sql

 USE MySQLXplugin;
 ## SQL safe updates as 0 will allow to delete without the need to use a key column 
 SET SQL_SAFE_UPDATES = 0;

 SELECT COUNT(*) FROM ExtraCategoryInfo;
 DELETE FROM ExtraCategoryInfo;
 SELECT COUNT(*) FROM ExtraCategoryInfo;

 INSERT INTO ExtraCategoryInfo(ExtraCategoryInfoID, CategoryID, MainParentCategoryName)
 VALUES
 (1,1,'People'),
 (2,2,'People'),
 (3,2,'House'),
 (4,3,'House');

 SELECT COUNT(*) FROM ExtraCategoryInfo;
 DELETE FROM ExtraCategoryInfo WHERE MainParentCategoryName = 'People';
 DELETE FROM ExtraCategoryInfo WHERE MainParentCategoryName = 'House' AND CategoryID in (4);
 DELETE FROM Categories WHERE CategoryIMEI = 1;
 SELECT COUNT(*) FROM ExtraCategoryInfo;
 DELETE FROM ExtraCategoryInfo WHERE EXISTS 
  (SELECT * FROM Categories WHERE Categories.CategoryID = ExtraCategoryInfo.CategoryID 
  AND ExtraCategoryInfo.MainParentCategoryName = 'House' );
 SELECT COUNT(*) FROM ExtraCategoryInfo;

 # USE ORDER BY with DELETE and QUICK
 SELECT * FROM Categories;
 DELETE QUICK FROM Categories ORDER BY CategoryName DESC LIMIT 2;
 SELECT * FROM Categories;
 # LOW_PRIORITY
 DELETE LOW_PRIORITY FROM Categories;
 DELETE FROM ExtraCategoryInfo; 
 
 # Re-populate table data
 INSERT INTO Categories(CategoryID, CategoryName, CategoryDescription, CategoryIMEI, CategoryDecimal)
 VALUES
 (1,'Sports','Sports related category',1,235.15),
 (2,'Entertaiment','Entertaiment related category',2,235.15),
 (3, 'Home','Home related category',3,235.15),
 (4, 'Kitchen','Kitchen related category',4,235.15),
 (5, 'Office','office related category',5,235.15);
 INSERT INTO ExtraCategoryInfo(ExtraCategoryInfoID, CategoryID, MainParentCategoryName)
 VALUES
 (1,1,'People'),
 (2,2,'People'),
 (3,2,'House'),
 (4,3,'House'),
 (5,4,'Hotel'),
 (6,4,'Hotel'),
 (7,7,'Hotel'),
 (8,8,'Hotel'),
 (9,9,'Hotel');

 ALTER TABLE ExtraCategoryInfo
    PARTITION BY RANGE (ExtraCategoryInfoID) (
      PARTITION p0 VALUES LESS THAN (8),
      PARTITION p1 VALUES LESS THAN (10)
    );
 SELECT * FROM Categories;
 SELECT * FROM ExtraCategoryInfo;
 # DELETE USING PARTITION
 DELETE FROM ExtraCategoryInfo PARTITION (p1) LIMIT 1; 
 SELECT * FROM ExtraCategoryInfo;
 DELETE FROM ExtraCategoryInfo PARTITION (p1); 
 SELECT * FROM ExtraCategoryInfo;

 # DELETE FROM MULTIPLE TABLE
 DELETE FROM Categories,ExtraCategoryInfo USING Categories INNER JOIN ExtraCategoryInfo
 WHERE Categories.CategoryID=ExtraCategoryInfo.CategoryID;  

 SELECT * FROM Categories;
 SELECT * FROM ExtraCategoryInfo;

-->endsql

 ## Cleanup
-->echo ================================================================================
-->echo CLEAN UP
-->echo ================================================================================
-->sql
 DROP SCHEMA if EXISTS MySQLXplugin;
 DROP USER deletesqluser@localhost;
 SET SQL_SAFE_UPDATES = 1;
-->endsql
EOF

--exec $MYSQLXTEST -u deletesqluser --password='deletesqluser' --file=$MYSQL_TMP_DIR/mysqlx-delete_sql_safemode_0.tmp 2>&1

## Cleanup
--remove_file $MYSQL_TMP_DIR/mysqlx-delete_sql_safemode_0.tmp

--disable_query_log
EVAL SET global sql_safe_updates = $orig_sql_safe_updates;
--enable_query_log