When used with SQLite, 'order by' in prepared statement didn't work

[yandongliu]

So I was using a prepared statement like

select * from table1 order by ?

and then ran it with parameter filled

db.all(stmt, 'ctime desc', function(err,rows){...}

Returned results were not sorted. I can see the SQL sent to SQLite was

select * from table1 order by 'ctime desc'

the quotes of which is not supported by SQLite. Without the single quotes it would work.

Any way to work around this?

[springmeyer]

Can you provide a small standalone testcase?

[yandongliu]

sure.

So the SQL statement from node-sqlite3 has quotes and my manual one doesn't, and the output of of both statements is:

sqlite> select id, ctime from rating order by 'ctime desc'; 2| 3| 5| 6|1374607053 7| 8|1374608847 9| 10| 11|1374519836 12|1374519931 16| 17| 18|1374608477 19|1374608503 20|1374612057 21|1374612719 22|1374612730 23|1374616785 24|1374617012

sqlite> select id, ctime from rating order by ctime desc; 12|1374519931 11|1374519836 24|1374617012 23|1374616785 22|1374612730 21|1374612719 20|1374612057 8|1374608847 19|1374608503 18|1374608477 6|1374607053 2| 3| 5| 7| 9| 10| 16| 17|

Related code snippet:

order='ctime desc' stmt = 'select id, ctime from rating order by $order'; db.all(stmt, {$order:order}, function(err,rows){...});

[springmeyer]

no, by standalone testcase I mean a small bit of sample data and a fully working/complete script that replicates the problem.

[springmeyer]

order by might be suffering from same issue as group by: #205.

If you have a fully working testcase (and not just a snippet) then feel free to re-open.

[scurker]

I encountered this issue myself today as well - when you run prepared statements with along with 'order by'.

You can replicate this by seeding your database with the following data:

CREATE TABLE cities (city text, state text);
INSERT INTO "cities" VALUES('Modesto','California');
INSERT INTO "cities" VALUES('Orlando','Florida');
INSERT INTO "cities" VALUES('San Diego','California');
INSERT INTO "cities" VALUES('El Paso','Texas');
INSERT INTO "cities" VALUES('St. Paul','Minnesota');
INSERT INTO "cities" VALUES('Bakersfield','California');
INSERT INTO "cities" VALUES('Jacksonville','Florida');
INSERT INTO "cities" VALUES('San Bernadino','California');
INSERT INTO "cities" VALUES('Buffalo','New York');
INSERT INTO "cities" VALUES('Boston','Massachusetts');
INSERT INTO "cities" VALUES('Albuquerque','New Mexico');
INSERT INTO "cities" VALUES('Sacramento','California');
INSERT INTO "cities" VALUES('Chula Vista','California');
INSERT INTO "cities" VALUES('Columbus','Ohio');
INSERT INTO "cities" VALUES('Wichita','Kansas');

Now, if I run

db.all('select * from cities order by city', function(err, result) {
  console.log(result);
});

Everything comes back in the correct sort order.

[ { city: 'Albuquerque', state: 'New Mexico' },
  { city: 'Bakersfield', state: 'California' },
  { city: 'Boston', state: 'Massachusetts' },
  { city: 'Buffalo', state: 'New York' },
  { city: 'Chula Vista', state: 'California' },
  { city: 'Columbus', state: 'Ohio' },
  { city: 'El Paso', state: 'Texas' },
  { city: 'Jacksonville', state: 'Florida' },
  { city: 'Modesto', state: 'California' },
  { city: 'Orlando', state: 'Florida' },
  { city: 'Sacramento', state: 'California' },
  { city: 'San Bernadino', state: 'California' },
  { city: 'San Diego', state: 'California' },
  { city: 'St. Paul', state: 'Minnesota' },
  { city: 'Wichita', state: 'Kansas' } ]

However, running the same query through a prepared statement with order by as one of the parameters, the results are simply returned in row order.

var stmt = db.prepare('select * from cities order by ?');
stmt.all('city', function(err, results) {
  console.log(results);
});

[ { city: 'Modesto', state: 'California' },
  { city: 'Orlando', state: 'Florida' },
  { city: 'San Diego', state: 'California' },
  { city: 'El Paso', state: 'Texas' },
  { city: 'St. Paul', state: 'Minnesota' },
  { city: 'Bakersfield', state: 'California' },
  { city: 'Jacksonville', state: 'Florida' },
  { city: 'San Bernadino', state: 'California' },
  { city: 'Buffalo', state: 'New York' },
  { city: 'Boston', state: 'Massachusetts' },
  { city: 'Albuquerque', state: 'New Mexico' },
  { city: 'Sacramento', state: 'California' },
  { city: 'Chula Vista', state: 'California' },
  { city: 'Columbus', state: 'Ohio' },
  { city: 'Wichita', state: 'Kansas' } ]

This is with using node-sqlite3 @ 2.2.7 and sqlite3 @ 3.7.13. I couldn't find anything in sqlite's documentation on whether or not it allows using prepared statements with order by parameters - so everything might be working correctly, just unexpectedly. I can work around this by appending the order as part of the prepared statement string, I just wanted to post my findings.

[jangxx]

This is still not fixed unfortunately. I just encountered this problem using node-sqlite 3.1.0 with node 4.2.1.

[Mithgol]

Yes, with the above test case I can see this problem in node-sqlite3 3.1.1 on Node v5.1.0 as well.

However, I am not convinced that this is a node-sqlite3's issue.

Perhaps, like in #330, we are facing an internal limitation of SQLite.

Is it really possible to use a parameter as a substitute for a column's name?

I know SQLite's “Binding Values To Prepared Statements” says “literals may be replaced by a parameter”. But columns' names are (as far as I know) identifiers, not literals.

[sky5walk]

I have the same error with v3.13.0.