Comparing two columns: <= does not work, but >= does

Question

I want to select column1 data where it's less than or equal with column2. However, when I try using <= I get an empty result, despite having data in column1 which is less than column2.

My code:

router.get('/', function(req, res, next) {
    db('items').select().where('column1', '<=', 'column2').then((notify)=>{
      console.log(notify)
      res.render('index', { title: 'Express', notify:notify })
  })
})

and the result:

[]

which as you see is empty!

If I use greater or equal:

 .where('column1', '>=', 'column2')

I get all the rows:

[ RowDataPacket {
    column_id: 1,
    column1: 99,
    column2: 10, },
  RowDataPacket {
    column_id: 2,
    column1: 10,
    column2: 10, },
  RowDataPacket {
    column_id: 3,
    column1: 29,
    column2: 12,} ]

Why is this?

Can u check/log what query returned from knex? – Suresh Prajapati Oct 08 '19 at 11:08 — Suresh Prajapati, Oct 08 '19 at 11:08

Rich Churcher · Accepted Answer · 2019-10-16T06:32:55.450

The problem is, SQL works this way, but Knex doesn't! Knex is essentially comparing the value of column1 to the string 'column2'. So, it would be as if we ran the SQL query:

SELECT * FROM items
    WHERE column1 <= 'column2';

Now, in MySQL (MariaDB in my examples) a <= comparison for an integer against a string returns 0 (false). However, a >= comparison returns 1, which explains the confusing results you're seeing. Some other databases would display an error (Postgres for example):

postgres=# SELECT 1 <= 'foo';
ERROR:  invalid input syntax for integer: "foo"
LINE 1: SELECT 1 <= 'foo';                   ^

but MariaDB will let you get away with it:

MariaDB [(none)]> SELECT 1 <= 'foo';
+------------+
| 1 <= 'foo' |
+------------+
|          0 |
+------------+
1 row in set (0.000 sec)

MariaDB [(none)]> SELECT 1 >= 'foo';
+------------+
| 1 >= 'foo' |
+------------+
|          1 |
+------------+
1 row in set (0.000 sec)

To solve this, you need to tell Knex that you actually want the column value on the right hand side of the comparison, not a string. You can accomplish this using knex.ref. Try this:

db("items")
  .where("column1", "<=", db.ref("column2"))
  .then(console.log)
  .catch(console.error);

See also: With knexjs, how do I compare two columns in the .where() function?.

Comparing two columns: <= does not work, but >= does

1 Answers1