maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #12250
Progress report - Week 4
Greetings,
Hope you are safe and doing great,
This post describes the things I've done in my fourth week [25-31 May] of
Community Bonding Period under the mentor-ship of Sergei Golubchik and
Oleksandr Byelkin for GSoC-20. The tasks taken up for this week was to
study and analyze *INSERT SELECT RETURNING* and developing a test
suite for *MULTIPLE
TABLE DELETE RETURNING.*
The following attachment contains the test cases for evaluating Multiple
Delete Returning, as they are developed before actual implementation of
code, please do notify me of any improvement needed, I'd be glad to resolve.
Regards,
Mohammed Hammaad Mateen
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
Attachment:
TEST_SUITE_#1.pdf
Description: Adobe PDF document
Follow ups
