Bug #88166 .find() method function not parsing SearchConditionStr correctly

• Submitted: 20 Oct 2017 11:50
• Modified: 23 Apr 2018 5:37
• Reporter: Andy Fusniak
• Status: No Feedback
• Category: Connector for Node.js
• Severity: S1 (Critical)
• Version: 8.0.8
• OS: MacOS (node v6.11.4)
• CPU Architecture: Any
• Tags: NodeJS, XDevAPI

[20 Oct 2017 11:50] Andy Fusniak

Description:
Function {
  message: 'Parse error on line 1:\nname like :name AND age <\n-----^\nExpecting \'.\', \'(\', got \'like\'',
  hash: 
   { text: 'like',
     token: 'like',
     line: 0,
     loc: { first_line: 1, last_line: 1, first_column: 0, last_column: 4 },
     expected: [ '\'.\'', '\'(\'' ] } }

How to repeat:
// --------------------------------------------------
// Connecting to MySQL Server and working with a Collection

const mysqlx = require('@mysql/xdevapi');

// Connect to server
mysqlx.getSession( {
  host: 'localhost', port: '33060',
  dbUser: 'root',
  dbPassword: 'mysql',
  ssl: false
}).then(function(session) {
  return session.getSchema('test1137');
}).then(function(db) {
  // Create a new collection 'my_collection'
  return db.createCollection('my_collection');
}).then(function(myColl) {
  // Insert documents
  return Promise.all([
    myColl.add({name: 'Sakila', age: 15}).execute(),
    myColl.add({name: 'Susanne', age: 24}).execute(),
    myColl.add({name: 'Mike', age: 39}).execute()
  ]).then(function() {
    // Find a document
    return myColl.find('name like :name AND age < :age')
      .bind({ name: 'S*', age: 20 }).limit(1).execute();
  }).then(function(docs) {
    // Print document
    console.log(docs.fetchOne());

    // Drop the collection
    return myColl.drop();
  });
}).catch(function(err) {
  // Handle error
  console.error(err);
});

[20 Oct 2017 19:10] Andy Fusniak

I've changed the string passed to the .find() method to

'$.name like :name && $.age < :age'

and this appears to satisfy the parser. However, the callback function passed to the .execute() method returns undefined (I'm not sure why the .execute() method takes a callback as this seems to break the convention of the module that appears to use Promise-based asynchronous control flow). 

The MySQL logs show a syntactically valid SQL query. When trying the query manually at the mysqlsh prompt the following output can be seen: 

mysql-sql> SELECT doc FROM `test1137`.`my_collection` WHERE (JSON_UNQUOTE(JSON_EXTRACT(doc,'$.name')) LIKE ('S*' AND (JSON_EXTRACT(doc,'$.age') < 20))) LIMIT 1;                                        ;
Empty set, 3 warnings (0.00 sec)
Warning (code 1292): Truncated incorrect DOUBLE value: 'S*'
Warning (code 1292): Truncated incorrect DOUBLE value: 'S*'
Warning (code 1292): Truncated incorrect DOUBLE value: 'S*'
mysql-sql>

I'm raising this to a Critical since without any means of searching the collections using Node.js means this module is unfortunately unusable.

// --------------------------------------------------
// Connecting to MySQL Server and working with a Collection

const mysqlx = require('@mysql/xdevapi');

// Connect to server
mysqlx.getSession( {
  host: 'localhost', port: '33060',
  dbUser: 'root',
  dbPassword: 'mysql',
  ssl: false
}).then(function(session) {
  return session.getSchema('test1137');
}).then(function(db) {
  // Create a new collection 'my_collection'
  return db.createCollection('my_collection');
}).then(function(myColl) {
  // Insert documents
  return Promise.all([
    myColl.add({name: 'Sakila', age: 15}).execute(),
    myColl.add({name: 'Susanne', age: 24}).execute(),
    myColl.add({name: 'Mike', age: 39}).execute()
  ]).then(function() {
    // Find a document
    let p = myColl.find('$.name like :name && $.age < :age')
      .bind({ name: 'S*', age: 20 }).limit(1).execute(function exResult(docs) {
        console.log(docs);
        process.exit();
      });
    console.log(p);
    return p;
  }).then(function(docs) {
    // Print document
    console.log(docs.fetchOne());

    // Drop the collection
    return myColl.drop();
  });
}).catch(function(err) {
  // Handle error
  console.error(err);
});

[2 Feb 2018 18:37] Rui Quelhas

Hi Andy,

sorry for the late reply, this got lost in the cracks somehow.

As you mentioned, the API relies on a kind of push-based cursor (in the form of a callback) to harvest resultset data (the Promise is just returned when the operation effectively finishes). So, in order to pick up the matching documents with `find()`, you would need to provide a callback that would do the work.

Looking at your test case, the callback will be called with undefined since you are using an incorrect LIKE pattern. You should use 'S%' instead of 'S*'. Also, the `drop()` method is no longer part of the API since 8.0.7. To drop a collection you can use `db.dropCollection('my_collection')`.

[24 Apr 2018 1:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".