introduce test cases to this project
jack-kwok80 opened this issue · 0 comments
jack-kwok80 commented
introduce test cases to this project
Test results:
FAIL test/jointQuery.test.ts (18.901 s)
● Console
console.log
Translate this natural language query into SQL without changing the case of the entries given by me:
"show me the total order count group by color of cars"
Use this table schema:
CREATE TABLE cars (id INT, make TEXT, model TEXT, year INT, color TEXT);CREATE TABLE customer (cus_id INT, name TEXT, sex INT, age INT);CREATE TABLE order (order_id INT, car_id INT, cus_id INT, number INT, price DOUBLE, date DATE);
SQL Query:
at src/translateToSQL.ts:6:11
console.log
Translate this natural language query into SQL without changing the case of the entries given by me:
"show me the total order count group by color of cars and sex of customer"
Use this table schema:
CREATE TABLE cars (id INT, make TEXT, model TEXT, year INT, color TEXT);CREATE TABLE customer (cus_id INT, name TEXT, sex INT, age INT);CREATE TABLE order (order_id INT, car_id INT, cus_id INT, number INT, price DOUBLE, date DATE);
SQL Query:
at src/translateToSQL.ts:6:11
console.log
Translate this natural language query into SQL without changing the case of the entries given by me:
"show me the count of cars that are green"
Use this table schema:
CREATE TABLE cars (id INT, make TEXT, model TEXT, year INT, color TEXT);CREATE TABLE customer (cus_id INT, name TEXT, sex INT, age INT);CREATE TABLE order (order_id INT, car_id INT, cus_id INT, number INT, price DOUBLE, date DATE);
SQL Query:
at src/translateToSQL.ts:6:11
● Joint Query › show me the total order count group by color of cars
expect(received).toEqual(expected) // deep equality
- Expected - 3
+ Received + 3
- SELECT cars.color, SUM(order.count) as total_order_count FROM cars
+ SELECT color, COUNT(order_id) AS total_order_count FROM cars
- JOIN order ON cars.car_id = order.car_id
+ JOIN order ON cars.id = order.car_id
- GROUP BY cars.color;
+ GROUP BY color;
15 | const case1: TestCase = { inputText, sql};
16 | const result = translateToSQL(case1.inputText, process.env.OPENAI_API_KEY, tableSchema);
> 17 | expect(await result).toEqual(case1.sql);
| ^
18 | });
19 | it("show me the total order count group by color of cars and sex of customer",async () => {
20 | const inputText = "show me the total order count group by color of cars and sex of customer";
at test/jointQuery.test.ts:17:30
at step (test/jointQuery.test.ts:33:23)
at Object.next (test/jointQuery.test.ts:14:53)
at fulfilled (test/jointQuery.test.ts:5:58)
● Joint Query › show me the total order count group by color of cars and sex of customer
expect(received).toEqual(expected) // deep equality
- Expected - 1
+ Received + 5
- SELECT COUNT(*) FROM cars;
+ SELECT color, sex, COUNT(order_id) AS total_order_count
+ FROM cars
+ INNER JOIN order ON cars.id = order.car_id
+ INNER JOIN customer ON order.cus_id = customer.cus_id
+ GROUP BY color, sex;
22 | const case1: TestCase = { inputText, sql};
23 | const result = translateToSQL(case1.inputText, process.env.OPENAI_API_KEY, tableSchema);
> 24 | expect(await result).toEqual(case1.sql);
| ^
25 | });
26 | it("show me the count of cars that are green",async () => {
27 | const inputText = "show me the count of cars that are green";
at test/jointQuery.test.ts:24:30
at step (test/jointQuery.test.ts:33:23)
at Object.next (test/jointQuery.test.ts:14:53)
at fulfilled (test/jointQuery.test.ts:5:58)
● Joint Query › show me the count of cars that are green
expect(received).toEqual(expected) // deep equality
- Expected - 5
+ Received + 1
- SELECT cars.color, customer.sex, SUM(order.count) as total_order_count
- FROM cars
- JOIN order ON cars.car_id = order.car_id
- JOIN customer ON order.cus_id = customer.cus_id
- GROUP BY cars.color, customer.sex;
+ SELECT COUNT(*) FROM cars WHERE color = 'Green';
29 | const case1: TestCase = { inputText, sql};
30 | const result = translateToSQL(case1.inputText, process.env.OPENAI_API_KEY, tableSchema);
> 31 | expect(await result).toEqual(case1.sql);
| ^
32 | });
33 | });
at test/jointQuery.test.ts:31:30
at step (test/jointQuery.test.ts:33:23)
at Object.next (test/jointQuery.test.ts:14:53)
at fulfilled (test/jointQuery.test.ts:5:58)
FAIL test/singleQuery.test.ts (19.299 s)
● Console
console.log
Translate this natural language query into SQL without changing the case of the entries given by me:
"show me all the cars that are red"
Use this table schema:
CREATE TABLE cars (id INT, make TEXT, model TEXT, year INT, color TEXT);CREATE TABLE customer (cus_id INT, name TEXT, sex INT, age INT);CREATE TABLE order (order_id INT, car_id INT, cus_id INT, number INT, price DOUBLE, date DATE);
SQL Query:
at src/translateToSQL.ts:6:11
console.log
Translate this natural language query into SQL without changing the case of the entries given by me:
"show me the count of cars"
Use this table schema:
CREATE TABLE cars (id INT, make TEXT, model TEXT, year INT, color TEXT);CREATE TABLE customer (cus_id INT, name TEXT, sex INT, age INT);CREATE TABLE order (order_id INT, car_id INT, cus_id INT, number INT, price DOUBLE, date DATE);
SQL Query:
at src/translateToSQL.ts:6:11
console.log
Translate this natural language query into SQL without changing the case of the entries given by me:
"show me the count of cars that are green"
Use this table schema:
CREATE TABLE cars (id INT, make TEXT, model TEXT, year INT, color TEXT);CREATE TABLE customer (cus_id INT, name TEXT, sex INT, age INT);CREATE TABLE order (order_id INT, car_id INT, cus_id INT, number INT, price DOUBLE, date DATE);
SQL Query:
at src/translateToSQL.ts:6:11
console.log
Translate this natural language query into SQL without changing the case of the entries given by me:
"show me the customer that age older than 20 years"
Use this table schema:
CREATE TABLE cars (id INT, make TEXT, model TEXT, year INT, color TEXT);CREATE TABLE customer (cus_id INT, name TEXT, sex INT, age INT);CREATE TABLE order (order_id INT, car_id INT, cus_id INT, number INT, price DOUBLE, date DATE);
SQL Query:
at src/translateToSQL.ts:6:11
console.log
Translate this natural language query into SQL without changing the case of the entries given by me:
"show me the total order count that date between '2023-03-01' AND '2023-03-31'"
Use this table schema:
CREATE TABLE cars (id INT, make TEXT, model TEXT, year INT, color TEXT);CREATE TABLE customer (cus_id INT, name TEXT, sex INT, age INT);CREATE TABLE order (order_id INT, car_id INT, cus_id INT, number INT, price DOUBLE, date DATE);
SQL Query:
at src/translateToSQL.ts:6:11
● Single Query › show me the total order count that date between '2023-03-01' AND '2023-03-31'
expect(received).toEqual(expected) // deep equality
Expected: "SELECT sum(count) FROM order WHERE date BETWEEN '2023-03-01' AND '2023-03-31';"
Received: "SELECT COUNT(order_id) FROM order WHERE date BETWEEN '2023-03-01' AND '2023-03-31';"
43 | const case1: TestCase = { inputText, sql};
44 | const result = translateToSQL(case1.inputText, process.env.OPENAI_API_KEY, tableSchema);
> 45 | expect(await result).toEqual(case1.sql);
| ^
46 | });
47 | });
at test/singleQuery.test.ts:45:30
at step (test/singleQuery.test.ts:33:23)
at Object.next (test/singleQuery.test.ts:14:53)
at fulfilled (test/singleQuery.test.ts:5:58)
Test Suites: 2 failed, 2 total
Tests: 4 failed, 4 passed, 8 total
Snapshots: 0 total
Time: 20.693 s
Ran all test suites.