POTENTIAL TEST SUITE FOR MULTIPLE TABLE DELETE RETURNING These test cases are generated before implementation of actual code, if any problem is identified I would be glad to resolve it. Thankyou. Date : 27th May 2020 ## DataBase, Table and View Creation MariaDB [(none)]> create database trying_multiple_delete_ret; Query OK, 1 row affected (0.013 sec) MariaDB [(none)]> use trying_multiple_delete_ret; Database changed MariaDB [trying_multiple_delete_ret]> CREATE TABLE ANIMALS (name varchar(40) primary key, colour varchar(20) not null, weight int(4) not null ); Query OK, 0 rows affected (0.175 sec) MariaDB [trying_multiple_delete_ret]> CREATE TABLE HABITAT (habitat_name varchar(40) primary key, animal_name varchar(40) not null ); Query OK, 0 rows affected (0.154 sec) MariaDB [trying_multiple_delete_ret]> CREATE TABLE FOOD (food_name varchar(20) primary key, animal_name varchar(40) not null ); Query OK, 0 rows affected (0.148 sec) MariaDB [trying_multiple_delete_ret]> CREATE VIEW COLOR AS SELECT name,colour FROM ANIMALS ; Query OK, 0 rows affected (0.101 sec) MariaDB [trying_multiple_delete_ret]> CREATE VIEW WEIGHT AS SELECT name,weight FROM ANIMALS; Query OK, 0 rows affected (0.032 sec) MariaDB [trying_multiple_delete_ret]> describe ANIMALS; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | name | varchar(40) | NO | PRI | NULL | | | colour | varchar(20) | NO | | NULL | | | weight | int(4) | NO | | NULL | | +--------+-------------+------+-----+---------+-------+ 3 rows in set (0.003 sec) MariaDB [trying_multiple_delete_ret]> describe HABITAT; +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | habitat_name | varchar(40) | NO | PRI | NULL | | | animal_name | varchar(40) | NO | | NULL | | +--------------+-------------+------+-----+---------+-------+ 2 rows in set (0.003 sec) MariaDB [trying_multiple_delete_ret]> describe FOOD; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | food_name | varchar(20) | NO | PRI | NULL | | | animal_name | varchar(40) | NO | | NULL | | +-------------+-------------+------+-----+---------+-------+ 2 rows in set (0.003 sec) ## Data Insertion MariaDB [trying_multiple_delete_ret]> INSERT INTO ANIMALS VALUES ('Penguins','CounterShade',23) , ('Polar Bear','White',450) , ('Toucan','Dark Blue',1) , ('Camel','Brown',600) , ('Reindeer','Grey',200) RETURNING *; +------------+--------------+--------+ | name | colour | weight | +------------+--------------+--------+ | Penguins | CounterShade | 23 | | Polar Bear | White | 450 | | Toucan | Dark Blue | 1 | | Camel | Brown | 600 | | Reindeer | Grey | 200 | +------------+--------------+--------+ 5 rows in set (0.015 sec) MariaDB [trying_multiple_delete_ret]> INSERT INTO HABITAT VALUES ('Antarctica','Penguins') , ('Arctic','Polar Bear') , ('Rainforest','Toucan') , ('Desert','Camel') , ('Tundra','Reindeer') RETURNING *; +--------------+-------------+ | habitat_name | animal_name | +--------------+-------------+ | Antarctica | Penguins | | Arctic | Polar Bear | | Rainforest | Toucan | | Desert | Camel | | Tundra | Reindeer | +--------------+-------------+ 5 rows in set (0.018 sec) MariaDB [trying_multiple_delete_ret]> INSERT INTO FOOD VALUES ('Fish','Penguins') , ('Seals','Polar Bear') , ('Fruits','Toucan') , ('Oats','Camel') , ('Herbs','Reindeer') RETURNING *; +-----------+-------------+ | food_name | animal_name | +-----------+-------------+ | Fish | Penguins | | Seals | Polar Bear | | Fruits | Toucan | | Oats | Camel | | Herbs | Reindeer | +-----------+-------------+ 5 rows in set (0.027 sec) ## MULTIPLE TABLE DELETE RETURNING TEST CASES MariaDB [trying_multiple_delete_ret]> DELETE FROM ANIMALS USING ANIMALS,FOOD WHERE ANIMALS.name=FOOD.animal_name and FOOD.food_name LIKE 'H%' RETURNING ANIMALS.name,ANIMALS.colour,FOOD.food_name,UPPER(FOOD.animal_name); EXPECTED OUTPUT: +--------------+----------------+----------------+-------------------------+ | ANIMALS.name | ANIMALS.colour | FOOD.food_name | UPPER(FOOD.animal_name) | +--------------+----------------+----------------+-------------------------+ | Reindeer | Grey | Herbs | REINDEER | +--------------+----------------+----------------+-------------------------+ 1 row in set (0.027 sec) MariaDB [trying_multiple_delete_ret]> DELETE FROM ANIMALS USING ANIMALS,FOOD WHERE ANIMALS.name=FOOD.animal_name and Animals.colour='White' RETURNING ANIMALS.weight,ANIMALS.weight+ANIMALS.weight,FOOD.food_name,UPPER(FOOD.animal_name); EXPECTED OUTPUT: +----------------+-------------------------------+----------------+-------------------------+ | ANIMALS.weight | ANIMALS.weight+ANIMALS.weight | FOOD.food_name | UPPER(FOOD.animal_name) | +----------------+-------------------------------+----------------+-------------------------+ | 450 | 900 | Seals | POLAR BEAR | +----------------+-------------------------------+----------------+-------------------------+ 1 row in set (0.027 sec) MariaDB [trying_multiple_delete_ret]> DELETE FROM ANIMALS USING ANIMALS,HABITAT,FOOD WHERE ANIMALS.name=FOOD.animal_name and FOOD.food_name LIKE '_Z%' RETURNING *; EXPECTED OUTPUT: Empty set (0.001 sec) MariaDB [trying_multiple_delete_ret]> DELETE FROM HABITAT USING ANIMALS,HABITAT,FOOD WHERE ANIMALS.name=FOOD.animal_name and FOOD.food_name LIKE 'H%' RETURNING *; EXPECTED OUTPUT: +--------------+-------------+ | habitat_name | animal_name | +--------------+-------------+ | Antarctica | Penguins | | Arctic | Polar Bear | | Rainforest | Toucan | | Desert | Camel | | Tundra | Reindeer | +--------------+-------------+ 5 rows in set (0.018 sec) MariaDB [trying_multiple_delete_ret]> DELETE FROM COLOR USING COLOR,FOOD WHERE COLOR.name=FOOD.animal_name and FOOD.food_name LIKE 'H%' RETURNING COLOR.name; +------------+ | COLOR.name | +------------+ | Reindeer | +------------+ 1 row in set (0.001 sec) MariaDB [trying_multiple_delete_ret]> INSERT INTO HABITAT VALUES ('Antarctica','Penguins') , ('Arctic','Polar Bear') , ('Rainforest','Toucan') , ('Desert','Camel') , ('Tundra','Reindeer'); Query OK, 5 rows affected (0.027 sec) Records: 5 Duplicates: 0 Warnings: 0 ## I suppose nothing should be explicitly returned in the below test cases even if RETURNING is present in the query; MariaDB [trying_multiple_delete_ret]> ANALYZE DELETE FROM HABITAT USING ANIMALS,HABITAT,FOOD WHERE ANIMALS.name=FOOD.animal_name and HABITAT.habitat_name = 'Tundra' RETURNING *; EXPECTED OUTPUT: {When Tundra is deleted} +------+-------------+---------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+---------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------------+ | 1 | SIMPLE | HABITAT | const | PRIMARY | PRIMARY | 42 | const | 1 | NULL | 100.00 | NULL | | | 1 | SIMPLE | ANIMALS | index | PRIMARY | PRIMARY | 42 | NULL | 5 | 5.00 | 100.00 | 100.00 | Using index | | 1 | SIMPLE | FOOD | ALL | NULL | NULL | NULL | NULL | 5 | 5.00 | 100.00 | 20.00 | Using where | +------+-------------+---------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------------+ 3 rows in set (0.037 sec) MariaDB [trying_multiple_delete_ret]> ANALYZE DELETE FROM HABITAT USING ANIMALS,HABITAT,FOOD WHERE ANIMALS.name=FOOD.animal_name and HABITAT.habitat_name = 'Tundra' RETURNING *; EXPECTED OUTPUT: {When there is nothing to delete} +------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+--------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+--------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table | +------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+--------------------------------+ 1 row in set (0.002 sec) MariaDB [trying_multiple_delete_ret]> EXPLAIN DELETE FROM HABITAT USING ANIMALS,HABITAT,FOOD WHERE ANIMALS.name=FOOD.animal_name and HABITAT.habitat_name = 'Rainforest' RETURNING *; EXPECTED OUTPUT: +------+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+ | 1 | SIMPLE | HABITAT | const | PRIMARY | PRIMARY | 42 | const | 1 | | | 1 | SIMPLE | ANIMALS | index | PRIMARY | PRIMARY | 42 | NULL | 5 | Using index | | 1 | SIMPLE | FOOD | ALL | NULL | NULL | NULL | NULL | 5 | Using where | +------+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+ 3 rows in set (0.002 sec) MariaDB [trying_multiple_delete_ret]> EXPLAIN DELETE FROM HABITAT USING ANIMALS,HABITAT,FOOD WHERE ANIMALS.name=FOOD.animal_name and HABITAT.habitat_name = 'Rainforest' RETURNING *; EXPECTED OUTPUT: +------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | +------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ 1 row in set (0.002 sec) MariaDB [trying_multiple_delete_ret]> EXPLAIN EXTENDED DELETE FROM HABITAT USING ANIMALS,HABITAT,FOOD WHERE ANIMALS.name=FOOD.animal_name and HABITAT.habitat_name = 'Desert' RETURNING *; EXPECTED OUTPUT: +------+-------------+---------+-------+---------------+---------+---------+-------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+---------+-------+---------------+---------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | HABITAT | const | PRIMARY | PRIMARY | 42 | const | 1 | 100.00 | | | 1 | SIMPLE | ANIMALS | index | PRIMARY | PRIMARY | 42 | NULL | 5 | 100.00 | Using index | | 1 | SIMPLE | FOOD | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using where | +------+-------------+---------+-------+---------------+---------+---------+-------+------+----------+-------------+ 3 rows in set (0.002 sec) MariaDB [trying_multiple_delete_ret]> EXPLAIN EXTENDED DELETE FROM HABITAT USING ANIMALS,HABITAT,FOOD WHERE ANIMALS.name=FOOD.animal_name and HABITAT.habitat_name = 'Desert' RETURNING *; EXPECTED OUTPUT: +------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+ 1 row in set (0.002 sec) MariaDB [trying_multiple_delete_ret]> EXPLAIN FORMAT = "json" DELETE FROM HABITAT USING ANIMALS,HABITAT,FOOD WHERE ANIMALS.name=FOOD.animal_name and HABITAT.habitat_name = 'Antarctica' RETURNING *; EXPECTED OUTPUT: +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "table": { "table_name": "HABITAT", "access_type": "const", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "42", "used_key_parts": ["habitat_name"], "ref": ["const"], "rows": 1, "filtered": 100 }, "table": { "table_name": "ANIMALS", "access_type": "index", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "42", "used_key_parts": ["name"], "rows": 5, "filtered": 100, "using_index": true }, "table": { "table_name": "FOOD", "access_type": "ALL", "rows": 5, "filtered": 100, "attached_condition": "FOOD.animal_name = ANIMALS.`name`" } } } | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.002 sec) MariaDB [trying_multiple_delete_ret]> EXPLAIN FORMAT = "json" DELETE FROM HABITAT USING ANIMALS,HABITAT,FOOD WHERE ANIMALS.name=FOOD.animal_name and HABITAT.habitat_name = 'Antarctica' RETURNING *; EXPECTED OUTPUT: +--------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "table": { "message": "Impossible WHERE noticed after reading const tables" } } } | +--------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.002 sec) ## Clean Up MariaDB [trying_multiple_delete_ret]> drop table ANIMALS; MariaDB [trying_multiple_delete_ret]> drop table FOOD; MariaDB [trying_multiple_delete_ret]> drop table HABITAT; MariaDB [trying_multiple_delete_ret]> drop view COLOR; MariaDB [trying_multiple_delete_ret]> drop view WEIGHT; ## Done