forked from florajs/sql-parser
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathjoin.js
More file actions
120 lines (102 loc) · 4.23 KB
/
join.js
File metadata and controls
120 lines (102 loc) · 4.23 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
'use strict';
const { expect } = require('chai');
const { Parser } = require('../../');
describe('joins', () => {
const parser = new Parser();
it('should parse implicit joins', () => {
const ast = parser.parse('SELECT * FROM t, a.b b, c.d as cd');
expect(ast.from).to.eql([
{ db: null, table: 't', as: null },
{ db: 'a', table: 'b', as: 'b' },
{ db: 'c', table: 'd', as: 'cd' }
]);
});
['left', 'right', 'full'].forEach((join) => {
[' ', ' outer '].forEach((outer) => {
it(`should parse ${join}${outer}joins`, () => {
const ast = parser.parse(`SELECT * FROM t ${join} ${outer} join d on t.d = d.a`);
expect(ast.from).to.eql([
{ db: null, table: 't', as: null },
{
db: null,
table: 'd',
as: null,
join: `${join.toUpperCase()} JOIN`,
on: {
type: 'binary_expr',
operator: '=',
left: { type: 'column_ref', table: 't', column: 'd' },
right: { type: 'column_ref', table: 'd', column: 'a' }
}
}
]);
});
});
});
it('should parse joined subselect', () => {
const ast = parser.parse('SELECT * FROM t1 JOIN (SELECT id, col1 FROM t2) someAlias ON t1.id = someAlias.id');
expect(ast.from).to.eql([
{ db: null, table: 't1', as: null },
{
expr: {
with: null,
type: 'select',
options: null,
distinct: null,
from: [{ db: null, table: 't2', as: null }],
columns: [
{ expr: { type: 'column_ref', table: null, column: 'id' }, as: null },
{ expr: { type: 'column_ref', table: null, column: 'col1' }, as: null }
],
where: null,
groupby: null,
having: null,
orderby: null,
limit: null,
parentheses: true
},
as: 'someAlias',
join: 'INNER JOIN',
on: {
type: 'binary_expr',
operator: '=',
left: { type: 'column_ref', table: 't1', column: 'id' },
right: { type: 'column_ref', table: 'someAlias', column: 'id' }
},
lateral: false,
columns: null
}
]);
});
it('should parse joins with USING (single column)', () => {
const ast = parser.parse('SELECT * FROM t1 JOIN t2 USING (id)');
expect(ast.from).to.eql([
{ db: null, table: 't1', as: null },
{ db: null, table: 't2', as: null, join: 'INNER JOIN', using: ['id'] }
]);
});
it('should parse joins with USING (multiple columns)', () => {
const ast = parser.parse('SELECT * FROM t1 JOIN t2 USING (id1, id2)');
expect(ast.from).to.eql([
{ db: null, table: 't1', as: null },
{ db: null, table: 't2', as: null, join: 'INNER JOIN', using: ['id1', 'id2'] }
]);
});
it('should parse LATERAL joins', () => {
const ast = parser.parse(
'SELECT * FROM t1 JOIN LATERAL (SELECT id FROM t2 WHERE t1.id = t2.t1id) AS subselect ON TRUE'
);
const [, lateralJoin] = ast.from;
expect(lateralJoin).to.have.property('lateral', true);
});
it('should parse derived column list with single column', () => {
const ast = parser.parse('SELECT id FROM (SELECT 1) t(id)');
const [subSelect] = ast.from;
expect(subSelect).to.have.property('columns').and.to.eql(['id']);
});
it('should parse derived column list with multiple columns', () => {
const ast = parser.parse('SELECT id1, id2 FROM (SELECT 1, 2) t(id1, id2)');
const [subSelect] = ast.from;
expect(subSelect).to.have.property('columns').and.to.eql(['id1', 'id2']);
});
});