Named parameters in nested WHERE clauses not working

[misner]

I use a lot of data-api-client queries inside my lambdas (js/node) with no issue but I stumbled into a weird issue.

This code in my lambda is working:

`SELECT             
        qr.query_result_id,
        qr.url,       
        MAX(coe.email_nb) as max_email_nb_sent_by_this_customer_to_this_author_on_this_query
      FROM
        query_results qr
      LEFT JOIN
        customers_outreach_emails coe
      ON
        qr.author_email = coe.author_email AND
        coe.customer_id = ${customerId}         
      WHERE              
        qr.status = :status AND 
        qr.query_id = :query_id AND
        qr.author_email IS NOT NULL AND
        qr.article_publication_day >= CURDATE() - INTERVAL ${oldest_article_associated_with_outreached_person} DAY AND
        qr.query_result_id NOT IN (
          SELECT query_result_id
          FROM customers_outreach_emails
          WHERE            
            customer_id = 2 AND 
            query_id = 2
        ) AND        
        qr.ready_for_being_used_for_emailing IS TRUE
      GROUP BY
        qr.author_email
      ORDER BY 
        qr.query_result_id ASC
      LIMIT 
        20
`,
 { 
      status: process.env.CONTEXT === "production" ? "prod" : "dev",
      query_id: queryId,
      customer_id: customerId,
      query_id2: queryId,
      customer_id2: customerId,
      customer_id3: customerId,
    }    

But as soon as I change customer_id = 2 to customer_id = :customer_id, like this:

 WHERE            
          customer_id = :customer_id AND 
          query_id = 2

... it starts to fail ! So the query below fail... this one little change makes everything crumble :(

`SELECT             
        qr.query_result_id,
        qr.url,       
        MAX(coe.email_nb) as max_email_nb_sent_by_this_customer_to_this_author_on_this_query
      FROM
        query_results qr
      LEFT JOIN
        customers_outreach_emails coe
      ON
        qr.author_email = coe.author_email AND
        coe.customer_id = ${customerId}         
      WHERE              
        qr.status = :status AND 
        qr.query_id = :query_id AND
        qr.author_email IS NOT NULL AND
        qr.article_publication_day >= CURDATE() - INTERVAL ${oldest_article_associated_with_outreached_person} DAY AND
        qr.query_result_id NOT IN (
          SELECT query_result_id
          FROM customers_outreach_emails
          WHERE            
            customer_id = :customer_id AND 
            query_id = 2
        ) AND 
        qr.ready_for_being_used_for_emailing IS TRUE
      GROUP BY
        qr.author_email
      ORDER BY 
        qr.query_result_id ASC
      LIMIT 
        20
`,
 { 
      status: process.env.CONTEXT === "production" ? "prod" : "dev",
      query_id: queryId,
      customer_id: customerId,
      query_id2: queryId,
      customer_id2: customerId,
      customer_id3: customerId,
    }  

The error I get on Cloudwatch is:

{
    "errorMessage": "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':customer_id AND \n            query_id = 2\n        ) AND\n        qr.author_email' at line 34",
    "errorType": "BadRequestException",
    "stackTrace": [
        "            query_id = 2",
        "        ) AND",
        "        qr.author_email' at line 34",
        "Object.extractError (webpack:///./node_modules/aws-sdk/lib/protocol/json.js?:51:27)",
        "Request.extractError (webpack:///./node_modules/aws-sdk/lib/protocol/rest_json.js?:55:8)",
        "Request.callListeners (webpack:///./node_modules/aws-sdk/lib/sequential_executor.js?:106:20)",
        "Request.emit (webpack:///./node_modules/aws-sdk/lib/sequential_executor.js?:78:10)",
        "Request.emit (webpack:///./node_modules/aws-sdk/lib/request.js?:683:14)",
        "Request.transition (webpack:///./node_modules/aws-sdk/lib/request.js?:22:10)",
        "AcceptorStateMachine.runTo (webpack:///./node_modules/aws-sdk/lib/state_machine.js?:14:12)",
        "eval (webpack:///./node_modules/aws-sdk/lib/state_machine.js?:26:10)",
        "Request.eval (webpack:///./node_modules/aws-sdk/lib/request.js?:38:9)",
        "Request.eval (webpack:///./node_modules/aws-sdk/lib/request.js?:685:12)"
    ]
}

My intuition which might be wrong: because you say Named parameters MUST be sent in order (https://github.com/jeremydaly/data-api-client#named-parameters-must-be-sent-in-order), it means when it's a nested WHERE, it's not because it comes as the third parameter (if you read the code from up to bottom) that's it's actually the third that SQL "sees", so my order of parameters in the parameters block is wrong.

How can I make this work? In the "worst" case where dat-api-client does not work inside "nested" WHERE, then can I use the string literal ${ } as it's a JavaScript file and it might be interpreted (and of course remove it from the parameters block) ?

 WHERE            
          customer_id = ${customer_id} AND 
          query_id = 2

I tried and it seems replacing everywhere i have :named_parameters with literals ${ } work. If the case I can use ${ } whenever in the sql query I used to have :named_parameter, what is the value-added of named parameters, I mean is there a case where I can't use ${ } and should only use your named parameters ?

Thanks

[jeremydaly]

The named parameter order issue was supposedly resolved, so it could be the library that is messing things up. I'm removing the code that reorders the parameters and will test that.

[jeremydaly]

@misner,

Give this a try with v1.0. The nested parsing should work now that we removed the need to "order" named parameters.

Thanks, Jeremy

[misner]

Thanks, will try it!