sidorares/nodejs-mysql-native — Issue #54: Mangled results when using LEAST().
Author: chrisdew
Created: 2011-12-02 13:35:25+00:00
State: open

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 } ] }

```javascript
/*
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 = "trust_reporting";
var username = "root";
var password = "bizzzare";
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: trust_reporting
-- ------------------------------------------------------
-- 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;
*/
```