Data representation issue demonstrable using LEAST() or (x > 0).

[chrisdew]

I've come across a problem when using LEAST() through mysql-native.

I have reproduced the behaviour in a testcase (below).

The query runs without any issues on the MySQL command line and produces:

+-------------+-----+
| reason_zero | num |
+-------------+-----+
|           0 |   1 |
|           1 |   6 |
+-------------+-----+

The program below gives:

mysql connected
result: { insert_id: undefined,
  affected_rows: undefined,
  rows: 
   [ { reason_zero: 4294967296, num: NaN },
     { reason_zero: 25769803777, num: NaN } ] }

If I use the commented-out reason AS reason_zero,, mysql-native works (but that is not the query I need to run).

If I use the commented-out (reason > 0) AS reason_zero,, I get the same failure mode.

I'm using the latest mysql-native (from GitHub). (git pull told me Updating 2b4f1ba..7677112.)

Thanks,

Chris.

P.S. Changing the column types in the database from bigint and smallint to int does not fix the issue.

/*
 This is a minimal test case for issue https://github.com/sidorares/nodejs-mysql-native/issues/54
*/
var my = require("mysql-native");
var sql = 'SELECT '
        //+ 'reason AS reason_zero, '
        //+ '(reason > 0) AS reason_zero, ' // FIXME: why doesn't this work?
        + 'LEAST(reason, 1) AS reason_zero, ' // FIXME: why doesn't this work?
        + 'COUNT(*) AS num '
        + 'FROM event_bearer_ip '
        + 'GROUP BY reason_zero ' 
        ;

var db = my.createTCPClient('127.0.0.1', 3306);
db.auto_prepare = true;

var db_name = "test_db";
var username = "test_user";
var password = "test_password";
db.auth(db_name, username, password);
db.addListener('connect', function() {
  console.info("mysql connected");
  var query = db.execute(sql, []);
  query.on('result', function(result) {
    console.info("result:", result);
    process.exit(0);
  });
  query.on('error', function(err) {
    console.warn("err:", err);
    process.exit(1);
  });
});

/*
-- MySQL dump 10.13  Distrib 5.1.49, for debian-linux-gnu (x86_64)
--
-- Host: localhost    Database: test_db
-- ------------------------------------------------------
-- Server version   5.1.49-1ubuntu8.1-log
--
-- Table structure for table `event_bearer_ip`
--

DROP TABLE IF EXISTS `event_bearer_ip`;
CREATE TABLE `event_bearer_ip` (
  `id` bigint(20) unsigned NOT NULL,
  `reason` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Bearer used for IP';

--
-- Dumping data for table `event_bearer_ip`
--

LOCK TABLES `event_bearer_ip` WRITE;
INSERT INTO `event_bearer_ip` VALUES (2530,1006),(2834,1006),(37177,1009),(60652,1009),(647439,103),(647650,0),(762547,1008);
UNLOCK TABLES;
*/

[chrisdew]

If I make the 'reason' field signed, instead of unsigned, the issue goes away. This make it look as if it is the interpretation of the binary data (within mysql-native) which has an issue.