14-Join Queries¶
¶
Join basic
1. Join with inner/left/right/outer/asof/semi/anti/full
2. Join with tb1.ts = tb2.ts
3. Join with tb1.int = tb2.int limit
4. Join with tb1.int = tb2.int and ts filter
5. Join with multiple tables
6. Join with group by/order by/limit
7. Join with having condition
8. Join error cases
9. Join on two database
11. Join on two super table
12. Join on two normal table
13. Join on normal table and super table
14. Join semantic test
15. Join with interval
16. Join bug TS-5863
17. ASOF JOIN right-side ts condition pushdown correctness
¶
Join with tbname
1. Create 1 database and 2 super tables with different schemas
2. Create child tables from super tables with different tag values
3. Insert data into child tables
4. Join on timestamps and tags
5. Check the result of join correctly
¶
Join compare
1. Create normal tables with full data types
2. Insert 6 rows data
3. Check in and not in filter
4. Check >, <, = comparisons between different data types
6. Left join with different data types comparison conditions
¶
Join condition
1. Generate join sql with different join conditions and query conditions
2. Generate error join sql with wrong join conditions and check error status
3. Join with group by and having clause condition
4. Join with where clause condition
¶
Join full data types
1. Create stable sta/stb
2. Create child table a1/a2 from sta, b1/b2 from stb
3. Insert 4 rows data into a1/a2, b1/b2
4. Read query sql from .in file and execute
5. Validate the query result with expected .csv file
6. File .in sql include now()/today() and constant timestamps
7. File .in sql include inner/outer/semi/anti join
8. Check abnormal cases
¶
Join mode
1. test full_join
2. test inner_join
3. test join_boundary
4. test join_explain
5. test join_nested
6. test join_scalar1
7. test join_scalar2
8. test join_timeline
9. test left_anti_join
10. test left_asof_join
11. test left_join
12. test left_semi_join
13. test left_win_join
14. test right_anti_join
15. test right_asof_join
16. test right_join
17. test right_semi_join
18. test right_win_join
19. restart and test again
¶
Join primay key
1. Create necessary databases and tables
2. Insert test data into the tables
3. Join with inner/left/outer/full/semi/anti/asof
4. Join with composite primary key (int32, int64, str)
5. Validate the results of each join query against expected outcomes
6. Join with us/ns/ms precision timestamps
7. Error join checked
¶
Join functions cursor
1. Generate random sql than explain/execute
2. Join with math functions
- unique/mode/sample/abs/sqrt/sin/cos/tan/asin/acos/atan/pow/
- log/floor/ceil/round/mavg/hyperloglog/tail/csum/statecount/
- stateduration/histogram
3. Join with string functions
- ltrim/rtrim/lower/upper/length/
- char_length/substr/concat/concat_ws
4. Join with time functions
- cast/now/today/timezone/timetruncate/to_iso8601/
- to_unixtimestamp/elapsed/timediff
5. taos -f sql execution
¶
Join functions cursor
1. Generate random sql than explain/execute
2. Create another cursor to execute the same sql
3. Join with math functions
- unique/mode/sample/csum
- statecount/stateduration/histogram
5. Join with time functions
- TIMEDIFF_1/TIMEDIFF_2
6. taos -f sql execution
¶
Join interval
1.Create database d1 and d2
2.Create table t1 in d1 with tags and t1 in d2 without tags
3.Insert data into d1.t1 with different tag values
4.Insert data into d2.t1 with same timestamps as d1.t1
5.Join query between d1.t1 and d2.t1 with interval(1a)
6. Check the result of join correctly
7. Jira TS-5803
¶
Join many blocks
1. Create database and two super tables
2. Insert data into each child tables with same timestamps
3. Join two super tables on timestamps and tag columns
4. Check the result of join correctly
¶
Join multi-tables
1.Create super table st0-stb with same schema but different tag numbers
2.Create child tables from super tables with different tag values
3.Insert data into child tables with same timestamps
4.Join tables on timestamps and tag columns from different super tables
5. Check the result of join correctly
¶
Join multi-vnode
1. Create database with multiple vnodes
2. Create two super tables
3. Insert data into each child tables with same timestamps
4. Join two super tables on timestamps and tag columns
5. Check the result of join correctly
¶
Join with ns precision
1. Create two databases with ns precision
2. Create stable and child table in two databases
3. Insert data into two child tables with same timestamps
4. Inner join two tables from two databases on timetruncate(ts) and tag columns
5. Check join result rows is correct
¶
Join with order by
1. Create database with vgroup 1
2. Create 1 stable 'sta' and 1 child table 'tba1'
3. Insert 4 rows data into child table 'tba1'
4. child query as left join table
5. stba1 as right join table
6. Join two tables on timestamp column with different order by combinations
7. Check the result of join correctly
¶
Join inner
1. Create 1 database and 1 super table
2. Create 2 child tables
3. Insert 1 rows data to each child table with different timestamps
4. Inner join two child tables on timestamp with interval(1s)
5. Check the result of join correctly
¶
Join with tbname
1. Create 1 database and 2 super tables with different schemas
2. Create child tables from super tables with different tag values
3. Insert data into child tables with same timestamps
4. Join left table is child query from first super table and where condition
5. Join right table is child query from second super table and where condition
6. Join on timestamps and tbname tag
7. Check the result of join correctly