Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit7d699ee

Browse files
Copilotmathiasrw
andauthored
Unskip tests for CROSS JOIN and FULL OUTER JOIN toclose#2359 (#2369)
Co-authored-by: copilot-swe-agent[bot] <198982749+Copilot@users.noreply.github.com>Co-authored-by: mathiasrw <1063454+mathiasrw@users.noreply.github.com>
1 parent1f8b079 commit7d699ee

File tree

3 files changed

+273
-6
lines changed

3 files changed

+273
-6
lines changed

‎test/test2359.js‎

Lines changed: 267 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,267 @@
1+
if(typeofexports==='object'){
2+
varassert=require('assert');
3+
varalasql=require('..');
4+
}else{
5+
__dirname='.';
6+
}
7+
8+
describe('Test 2359 - Advanced Joins (CROSS JOIN and OUTER JOIN)',function(){
9+
constpluck=(arr,key)=>arr.map(e=>e[key]);
10+
11+
// Test data for JOIN tests
12+
vardata1=[
13+
{a:1,b:10},
14+
{a:2,b:20},
15+
{a:3,b:30},
16+
];
17+
vardata2=[
18+
{b:10,c:100},
19+
{b:20,c:200},
20+
{b:40,c:400},
21+
];
22+
23+
describe('CROSS JOIN Syntax',function(){
24+
before(function(){
25+
alasql('CREATE DATABASE test2359_cross');
26+
alasql('USE test2359_cross');
27+
});
28+
29+
after(function(){
30+
alasql('DROP DATABASE test2359_cross');
31+
});
32+
33+
it('1. FROM JOIN / CROSS JOIN with complex conditions',function(done){
34+
alasql('CREATE TABLE tab0; CREATE TABLE tab2');
35+
36+
varres=alasql(
37+
'SELECT DISTINCT * FROM tab2 cor0 JOIN tab2 cor1 ON + ( 90 ) \
38+
IS NOT NULL, tab0 AS cor2 '
39+
);
40+
assert(Array.isArray(res));
41+
42+
alasql('DROP TABLE tab0; DROP TABLE tab2; ');
43+
done();
44+
});
45+
46+
it('2. SELECT ALL with CROSS JOIN and comma syntax',function(done){
47+
alasql('CREATE TABLE tab1;CREATE TABLE tab2');
48+
varres=alasql('SELECT ALL * FROM tab1 cor0 CROSS JOIN tab1, tab2 AS cor1');
49+
assert(Array.isArray(res));
50+
alasql('DROP TABLE tab1;DROP TABLE tab2');
51+
done();
52+
});
53+
54+
it('3. Simple CROSS JOIN',function(done){
55+
alasql('CREATE TABLE one (a INT, b INT)');
56+
alasql('INSERT INTO one VALUES (1,10),(2,20)');
57+
alasql('CREATE TABLE two (e INT, f INT)');
58+
alasql('INSERT INTO two VALUES (1,100),(2,200)');
59+
60+
varres=alasql('SELECT * FROM one, two');
61+
assert.equal(res.length,4);// 2 x 2 = 4 rows
62+
63+
varres2=alasql('SELECT * FROM one CROSS JOIN two');
64+
assert.equal(res2.length,4);// Same as comma syntax
65+
66+
alasql('DROP TABLE one; DROP TABLE two');
67+
done();
68+
});
69+
});
70+
71+
describe('JOIN with Parameter Arrays (RECORDSET)',function(){
72+
before(function(){
73+
alasql('CREATE DATABASE test2359_params; USE test2359_params');
74+
alasql('CREATE TABLE one(a INT, b INT)');
75+
alasql('CREATE TABLE two(b INT, c INT)');
76+
alasql.options.modifier='RECORDSET';
77+
});
78+
79+
after(function(){
80+
deletealasql.options.modifier;
81+
alasql('DROP DATABASE test2359_params');
82+
});
83+
84+
it('1. JOIN params with USING clause',function(done){
85+
varres=alasql('SELECT one.*,two.* FROM ? one JOIN ? two USING b',[data1,data2]);
86+
varcolres=pluck(res.columns,'columnid');
87+
assert.deepEqual(colres,['a','b','c']);
88+
assert.equal(res.data.length,2);// Matches on b=10 and b=20
89+
done();
90+
});
91+
92+
it('2. JOIN tables with USING clause',function(done){
93+
alasql('SELECT * INTO one FROM ?',[data1]);
94+
alasql('SELECT * INTO two FROM ?',[data2]);
95+
varres=alasql('SELECT one.*,two.* FROM one JOIN two USING b');
96+
varcolres=pluck(res.columns,'columnid');
97+
assert.deepEqual(colres,['a','b','b','c']);
98+
assert.equal(res.data.length,2);// Matches on b=10 and b=20
99+
done();
100+
});
101+
102+
it('3. JOIN params with USING - verify result data',function(done){
103+
varres=alasql('SELECT one.*,two.* FROM ? one JOIN ? two USING b',[data1,data2]);
104+
varcolres=pluck(res.columns,'columnid');
105+
assert.deepEqual(colres,['a','b','c']);
106+
// Verify the actual joined data
107+
assert.equal(res.data[0].a,1);
108+
assert.equal(res.data[0].b,10);
109+
assert.equal(res.data[0].c,100);
110+
assert.equal(res.data[1].a,2);
111+
assert.equal(res.data[1].b,20);
112+
assert.equal(res.data[1].c,200);
113+
done();
114+
});
115+
});
116+
117+
describe('Complex Multi-Table Joins (passing tests)',function(){
118+
before(function(){
119+
alasql('CREATE DATABASE test2359_multi;USE test2359_multi');
120+
alasql.options.modifier='MATRIX';
121+
alasql('CREATE TABLE one (id NVARCHAR(3))');
122+
alasql('CREATE TABLE two (id NVARCHAR(3))');
123+
alasql('CREATE TABLE three (id NVARCHAR(3))');
124+
alasql("INSERT INTO one VALUES ('A'),('AB'),('AC'),('ABC')");
125+
alasql("INSERT INTO two VALUES ('B'),('AB'),('BC'),('ABC')");
126+
alasql("INSERT INTO three VALUES ('C'),('BC'),('AC'),('ABC')");
127+
});
128+
129+
after(function(){
130+
alasql.options.modifier=undefined;
131+
alasql('DROP DATABASE test2359_multi');
132+
});
133+
134+
it('1. INNER AND INNER',function(done){
135+
varres=alasql(
136+
'SELECT one.id AS a, two.id AS b, three.id AS c FROM one INNER JOIN two ON one.id = two.id INNER JOIN three ON two.id = three.id'
137+
);
138+
assert.deepEqual(res,[['ABC','ABC','ABC']]);
139+
done();
140+
});
141+
142+
it('2. INNER AND LEFT',function(done){
143+
varres=alasql(
144+
'SELECT one.id AS a, two.id AS b, three.id AS c FROM one INNER JOIN two ON one.id = two.id LEFT JOIN three ON two.id = three.id'
145+
);
146+
assert.deepEqual(res,[
147+
['AB','AB',undefined],
148+
['ABC','ABC','ABC'],
149+
]);
150+
done();
151+
});
152+
153+
it('3. LEFT AND INNER',function(done){
154+
varres=alasql(
155+
'SELECT one.id AS a, two.id AS b, three.id AS c FROM one LEFT JOIN two ON one.id = two.id INNER JOIN three ON two.id = three.id'
156+
);
157+
assert.deepEqual(res,[['ABC','ABC','ABC']]);
158+
done();
159+
});
160+
161+
it('4. LEFT AND LEFT',function(done){
162+
varres=alasql(
163+
'SELECT one.id AS a, two.id AS b, three.id AS c FROM one LEFT JOIN two ON one.id = two.id LEFT JOIN three ON two.id = three.id'
164+
);
165+
assert.deepEqual(res,[
166+
['A',undefined,undefined],
167+
['AB','AB',undefined],
168+
['AC',undefined,undefined],
169+
['ABC','ABC','ABC'],
170+
]);
171+
done();
172+
});
173+
174+
it('5. RIGHT AND INNER',function(done){
175+
varres=alasql(
176+
'SELECT one.id AS a, two.id AS b, three.id AS c FROM one RIGHT JOIN two ON one.id = two.id INNER JOIN three ON two.id = three.id'
177+
);
178+
assert.deepEqual(res,[
179+
['ABC','ABC','ABC'],
180+
[undefined,'BC','BC'],
181+
]);
182+
done();
183+
});
184+
185+
it('6. RIGHT AND LEFT',function(done){
186+
varres=alasql(
187+
'SELECT one.id AS a, two.id AS b, three.id AS c FROM one RIGHT JOIN two ON one.id = two.id LEFT JOIN three ON two.id = three.id'
188+
);
189+
assert.deepEqual(res,[
190+
['AB','AB',undefined],
191+
['ABC','ABC','ABC'],
192+
[undefined,'B',undefined],
193+
[undefined,'BC','BC'],
194+
]);
195+
done();
196+
});
197+
198+
it('7. OUTER AND INNER',function(done){
199+
varres=alasql(
200+
'SELECT one.id AS a, two.id AS b, three.id AS c FROM one OUTER JOIN two ON one.id = two.id INNER JOIN three ON two.id = three.id'
201+
);
202+
assert.deepEqual(res,[
203+
['ABC','ABC','ABC'],
204+
[undefined,'BC','BC'],
205+
]);
206+
done();
207+
});
208+
209+
it('8. OUTER AND LEFT',function(done){
210+
varres=alasql(
211+
'SELECT one.id AS a, two.id AS b, three.id AS c FROM one OUTER JOIN two ON one.id = two.id LEFT JOIN three ON two.id = three.id'
212+
);
213+
assert.deepEqual(res,[
214+
['A',undefined,undefined],
215+
['AB','AB',undefined],
216+
['AC',undefined,undefined],
217+
['ABC','ABC','ABC'],
218+
[undefined,'B',undefined],
219+
[undefined,'BC','BC'],
220+
]);
221+
done();
222+
});
223+
});
224+
225+
describe('Basic OUTER JOIN functionality',function(){
226+
before(function(){
227+
alasql('CREATE DATABASE test2359_outer;USE test2359_outer');
228+
});
229+
230+
after(function(){
231+
alasql('DROP DATABASE test2359_outer');
232+
});
233+
234+
it('1. OUTER JOIN returns all rows from both tables',function(done){
235+
alasql('CREATE TABLE one (a INT, b INT)');
236+
alasql('INSERT INTO one VALUES (1,10),(2,20),(3,30),(4,40)');
237+
alasql('CREATE TABLE two (e INT, f INT)');
238+
alasql('INSERT INTO two VALUES (1,100),(2,200),(3,300),(1000,1000),(2000,2000)');
239+
240+
varres=alasql('SELECT * FROM one OUTER JOIN two ON one.a = two.e');
241+
assert.equal(res.length,6);// 3 matches + 1 unmatched from one + 2 unmatched from two
242+
243+
alasql('DROP TABLE one; DROP TABLE two');
244+
done();
245+
});
246+
247+
it('2. OUTER JOIN with NULL values',function(done){
248+
alasql('CREATE TABLE table1 (id INT, name STRING)');
249+
alasql("INSERT INTO table1 VALUES (1,'A'),(2,'B'),(3,'C')");
250+
alasql('CREATE TABLE table2 (id INT, val STRING)');
251+
alasql("INSERT INTO table2 VALUES (2,'X'),(3,'Y'),(4,'Z')");
252+
253+
varres=alasql('SELECT * FROM table1 OUTER JOIN table2 ON table1.id = table2.id');
254+
assert.equal(res.length,4);// All unique combinations
255+
256+
// Check that unmatched rows have undefined values
257+
varunmatchedLeft=res.find(r=>r.name==='A');
258+
assert.equal(unmatchedLeft.val,undefined);
259+
260+
varunmatchedRight=res.find(r=>r.val==='Z'&&r.name===undefined);
261+
assert(unmatchedRight);
262+
263+
alasql('DROP TABLE table1; DROP TABLE table2');
264+
done();
265+
});
266+
});
267+
});

‎test/test259.js‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -19,7 +19,7 @@ describe('Test 259 SqlLogic Parser Test #2', function () {
1919
done();
2020
});
2121

22-
it.skip('2. FROM JOIN / CROSS JOIN syntax ',function(done){
22+
it('2. FROM JOIN / CROSS JOIN syntax ',function(done){
2323
alasql('CREATE TABLE tab0; CREATE TABLE tab2');
2424

2525
alasql(

‎test/test270.js‎

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -20,7 +20,7 @@ describe('Test 270 RECORDSET tests', function () {
2020
{b:40,c:400},
2121
];
2222

23-
it.skip('1. Create database',function(done){
23+
it('1. Create database',function(done){
2424
alasql('CREATE DATABASE test270; USE test270');
2525
alasql('CREATE TABLE one(a INT, b INT)');
2626
alasql('CREATE TABLE two(b INT, c INT)');
@@ -184,14 +184,14 @@ describe('Test 270 RECORDSET tests', function () {
184184
done();
185185
});
186186

187-
it.skip('16. JOIN params',function(done){
187+
it('16. JOIN params',function(done){
188188
varres=alasql('SELECT one.*,two.* FROM ? one JOIN ? two USING b',[data1,data2]);
189189
varcolres=pluck(res.columns,'columnid');
190190
assert.deepEqual(colres,['a','b','c']);
191191
done();
192192
});
193193

194-
it.skip('17. JOIN tables',function(done){
194+
it('17. JOIN tables',function(done){
195195
alasql('SELECT * INTO one FROM ?',[data1]);
196196
alasql('SELECT * INTO two FROM ?',[data2]);
197197
varres=alasql('SELECT one.*,two.* FROM one JOIN two USING b');
@@ -200,7 +200,7 @@ describe('Test 270 RECORDSET tests', function () {
200200
done();
201201
});
202202

203-
it.skip('18. JOIN params',function(done){
203+
it('18. JOIN params',function(done){
204204
varres=alasql('SELECT one.*,two.* FROM ? one JOIN ? two USING b',[data1,data2]);
205205
varcolres=pluck(res.columns,'columnid');
206206
assert.deepEqual(colres,['a','b','c']);
@@ -286,7 +286,7 @@ describe('Test 270 RECORDSET tests', function () {
286286
done();
287287
});
288288
*/
289-
it.skip('99. Drop phase',function(done){
289+
it('99. Drop phase',function(done){
290290
deletealasql.options.modifier;
291291
alasql('DROP DATABASE test270');
292292
done();

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp