Skip to content

Commit 1e2515b

Browse files
martinandertmartinandert
committed
properly handle empty arrays or arrays containing null values given to IN / NOT IN operator
1 parent 21f0038 commit 1e2515b

4 files changed

Lines changed: 155 additions & 6 deletions

File tree

‎lib/dialect/postgres.js‎

Lines changed: 80 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -141,6 +141,8 @@ Postgres.prototype.visit = function(node) {
141141
case 'PREFIX UNARY' : return this.visitPrefixUnary(node);
142142
case 'BINARY' : return this.visitBinary(node);
143143
case 'TERNARY' : return this.visitTernary(node);
144+
case 'IN' : return this.visitIn(node);
145+
case 'NOT IN' : return this.visitNotIn(node);
144146
case 'CASE' : return this.visitCase(node);
145147
case 'AT' : return this.visitAt(node);
146148
case 'SLICE' : return this.visitSlice(node);
@@ -290,9 +292,9 @@ Postgres.prototype.visitFrom = function(from) {
290292
};
291293

292294
Postgres.prototype.visitWhere = function(where) {
293-
this._visitingWhere = true;
295+
this._visitingWhere = true;
294296
var result = ['WHERE', where.nodes.map(this.visit.bind(this)).join(', ')];
295-
this._visitingWhere = false;
297+
this._visitingWhere = false;
296298
return result;
297299
};
298300

@@ -369,6 +371,81 @@ Postgres.prototype.visitTernary = function(ternary) {
369371
return [text];
370372
};
371373

374+
Postgres.prototype.visitIn = function(binary) {
375+
var self = this;
376+
var text = '(';
377+
378+
if (Array.isArray(binary.right)) {
379+
if (binary.right.length) {
380+
var params = [];
381+
var hasNull = false;
382+
383+
binary.right.forEach(function(node) {
384+
if (node.type === 'PARAMETER' && node._val === null) {
385+
hasNull = true;
386+
} else {
387+
params.push(self.visit(node));
388+
}
389+
});
390+
391+
if (params.length) {
392+
text += this.visit(binary.left) + ' IN (' + params.join(', ') + ')';
393+
394+
if (hasNull) {
395+
text += ' OR ' + this.visit(binary.left) + ' IS NULL';
396+
}
397+
} else { // implicitely has null
398+
text += this.visit(binary.left) + ' IS NULL';
399+
}
400+
} else {
401+
text += '1=0';
402+
}
403+
} else {
404+
text += this.visit(binary.left) + ' IN ' + this.visit(binary.right);
405+
}
406+
407+
text += ')';
408+
return [text];
409+
};
410+
411+
Postgres.prototype.visitNotIn = function(binary) {
412+
var self = this;
413+
var text = '(';
414+
415+
if (Array.isArray(binary.right)) {
416+
if (binary.right.length) {
417+
var params = [];
418+
var hasNull = false;
419+
420+
binary.right.forEach(function(node) {
421+
if (node.type === 'PARAMETER' && node._val === null) {
422+
hasNull = true;
423+
} else {
424+
params.push(self.visit(node));
425+
}
426+
});
427+
428+
if (params.length && hasNull) {
429+
text += 'NOT (';
430+
text += this.visit(binary.left) + ' IN (' + params.join(', ') + ')';
431+
text += ' OR ' + this.visit(binary.left) + ' IS NULL';
432+
text += ')';
433+
} else if (params.length) {
434+
text += this.visit(binary.left) + ' NOT IN (' + params.join(', ') + ')';
435+
} else { // implicitely has null
436+
text += this.visit(binary.left) + ' IS NOT NULL';
437+
}
438+
} else {
439+
text += '1=1';
440+
}
441+
} else {
442+
text += this.visit(binary.left) + ' NOT IN ' + this.visit(binary.right);
443+
}
444+
445+
text += ')';
446+
return [text];
447+
};
448+
372449
Postgres.prototype.visitCase = function(caseExp) {
373450
assert(caseExp.whenList.length == caseExp.thenList.length);
374451

@@ -542,7 +619,7 @@ Postgres.prototype.visitColumn = function(columnNode) {
542619
var col = table.columns[i];
543620
var aliased = col.name !== (col.alias || col.property);
544621
hasAliases = hasAliases || aliased;
545-
allCols.push(this.quote(col.name) + (aliased ? ' AS ' + this.quote(col.alias || col.property) : ''));
622+
allCols.push(this.quote(col.name) + (aliased ? ' AS ' + this.quote(col.alias || col.property) : ''));
546623
}
547624
txt.push(hasAliases ? allCols.join(', ') : '*');
548625
}

‎lib/node/in.js‎

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,28 @@
1+
'use strict';
2+
3+
var _ = require('lodash');
4+
var Node = require(__dirname);
5+
var valueExpressionMixin = require(__dirname + '/valueExpression');
6+
7+
var valueExpressionMixed = false;
8+
var InNode = Node.define(_.extend({
9+
type: 'IN',
10+
constructor: function(config) {
11+
Node.call(this);
12+
this.left = config.left;
13+
this.right = config.right;
14+
15+
// Delay mixin to runtime, when all nodes have been defined, and
16+
// mixin only once. ValueExpressionMixin has circular dependencies.
17+
if (!valueExpressionMixed) {
18+
valueExpressionMixed = true;
19+
_.extend(InNode.prototype, valueExpressionMixin());
20+
}
21+
},
22+
}));
23+
24+
// allow aliasing
25+
var AliasNode = require(__dirname + '/alias');
26+
_.extend(InNode.prototype, AliasNode.AliasMixin);
27+
28+
module.exports = InNode;

‎lib/node/notIn.js‎

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,28 @@
1+
'use strict';
2+
3+
var _ = require('lodash');
4+
var Node = require(__dirname);
5+
var valueExpressionMixin = require(__dirname + '/valueExpression');
6+
7+
var valueExpressionMixed = false;
8+
var NotInNode = Node.define(_.extend({
9+
type: 'NOT IN',
10+
constructor: function(config) {
11+
Node.call(this);
12+
this.left = config.left;
13+
this.right = config.right;
14+
15+
// Delay mixin to runtime, when all nodes have been defined, and
16+
// mixin only once. ValueExpressionMixin has circular dependencies.
17+
if (!valueExpressionMixed) {
18+
valueExpressionMixed = true;
19+
_.extend(NotInNode.prototype, valueExpressionMixin());
20+
}
21+
},
22+
}));
23+
24+
// allow aliasing
25+
var AliasNode = require(__dirname + '/alias');
26+
_.extend(NotInNode.prototype, AliasNode.AliasMixin);
27+
28+
module.exports = NotInNode;

‎lib/node/valueExpression.js‎

Lines changed: 19 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -14,6 +14,8 @@ var processParams = function(val) {
1414
// "thunk" around it.
1515
var ValueExpressionMixin = function() {
1616
var BinaryNode = require('./binary');
17+
var InNode = require('./in');
18+
var NotInNode = require('./notIn');
1719
var CastNode = require('./cast');
1820
var PostfixUnaryNode = require('./postfixUnary');
1921
var TernaryNode = require('./ternary');
@@ -25,7 +27,7 @@ var ValueExpressionMixin = function() {
2527
/*jshint unused: false */
2628
return function(val) {
2729
return new PostfixUnaryNode({
28-
left : this.toNode(),
30+
left : this.toNode(),
2931
operator : operator
3032
});
3133
};
@@ -41,6 +43,20 @@ var ValueExpressionMixin = function() {
4143
};
4244
};
4345

46+
var inMethod = function(val) {
47+
return new InNode({
48+
left : this.toNode(),
49+
right : processParams(val)
50+
});
51+
};
52+
53+
var notInMethod = function(val) {
54+
return new NotInNode({
55+
left : this.toNode(),
56+
right : processParams(val)
57+
});
58+
};
59+
4460
var ternaryMethod = function(operator, separator) {
4561
return function(middle, right) {
4662
return new TernaryNode({
@@ -116,8 +132,8 @@ var ValueExpressionMixin = function() {
116132
pathText : binaryMethod('#>>'),
117133
like : binaryMethod('LIKE'),
118134
notLike : binaryMethod('NOT LIKE'),
119-
in : binaryMethod('IN'),
120-
notIn : binaryMethod('NOT IN'),
135+
in : inMethod,
136+
notIn : notInMethod,
121137
between : ternaryMethod('BETWEEN', 'AND'),
122138
at : atMethod,
123139
slice : sliceMethod,

0 commit comments

Comments
 (0)