New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Incorrect order of query parameters when using subqueries #704

Closed
whitelynx opened this Issue Feb 23, 2015 · 3 comments

Comments

Projects
None yet
2 participants
@whitelynx
Contributor

whitelynx commented Feb 23, 2015

When building a query using a subquery, and using parameters in both, the resulting order of the parameters is incorrect. This specifically seems to happen when the main query uses db.raw() in its select clause.

Given this JS:

var subquery = db().select().from('tbl').where({baz: 'some string'}).as('g');
var query = db().select(db.raw('?', [3]), 'g.geom').from(subquery).where({'g.bzzz': false});
console.log(query.toSQL());
console.log(query.toString());

...we get this output:

{ method: 'select',
  options: undefined,
  bindings: [ 'some string', 3, false ],
  sql: 'select ?, "g"."geom" from (select * from "tbl" where "baz" = ?) as "g" where "g"."bzzz" = ?' }
select 'some string', "g"."geom" from (select * from "tbl" where "baz" = '3') as "g" where "g"."bzzz" = 'false'

...whereas we would expect to get:

{ method: 'select',
  options: undefined,
  bindings: [ 3, 'some string', false ],
  sql: 'select ?, "g"."geom" from (select * from "tbl" where "baz" = ?) as "g"' }
select '3', "g"."geom" from (select * from "tbl" where "baz" = 'some string') as "g" where "g"."bzzz" = 'false'

It seems as if the subquery handling code is just naively prepending the subquery's parameters to the outer query's parameters, instead of inserting them between parameters from the outer's select clause and the ones from the outer's where.

@whitelynx

This comment has been minimized.

Contributor

whitelynx commented Feb 23, 2015

Possibly related to #682.

@whitelynx

This comment has been minimized.

Contributor

whitelynx commented Feb 23, 2015

Also, this is at least an issue both on master and in version 0.7.3.

@whitelynx

This comment has been minimized.

Contributor

whitelynx commented Feb 23, 2015

This actually does work correctly when the subquery is in a .where() call, instead of .from():

var subquery = db().select('id').from('tbl').where({baz: 'some string'}).as('g');
var query = db().select(db.raw('?', [3]), 'g.geom').from('tbl').where('parent_id', 'in', subquery).where({bzzz: false});
console.log(query.toSQL());
console.log(query.toString());

{ method: 'select',
  options: undefined,
  bindings: [ 3, 'some string', false ],
  sql: 'select ?, "g"."geom" from "tbl" where "parent_id" in (select "id" from "tbl" where "baz" = ?) as "g" and "bzzz" = ?' }
select '3', "g"."geom" from "tbl" where "parent_id" in (select "id" from "tbl" where "baz" = 'some string') as "g" and "bzzz" = 'false'

@tgriesser tgriesser added the bug label Feb 23, 2015

whitelynx added a commit to whitelynx/knex that referenced this issue Feb 24, 2015

whitelynx added a commit to whitelynx/knex that referenced this issue Feb 24, 2015

whitelynx added a commit to whitelynx/knex that referenced this issue Feb 24, 2015

tgriesser added a commit that referenced this issue Feb 25, 2015

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment