Cant access params from subquery when UPDATE'ing

[tingli111]

It appears that variable binding in compound statement is not supported well.

E.g.

1. Below statement is not working. The data is not updated at all.

DB.exec("UPDATE [Table] SET  [Field1] = 'New' WHERE ( [Field2] = ( SELECT [Field2]  FROM [Table] WHERE [Field3] = '?'  ))", ['aaa']);

2. If we remove the variable binding, then it works.

DB.exec("UPDATE [Table] SET  [Field1] = 'New' WHERE ( [Field2] <= ( SELECT [Field2]  FROM [Table] WHERE [Field3] = 'aaa''  ))");

3. We also tested that below statement is working.

DB.exec("UPDATE [Table] SET  [Field1] = 'New' WHERE ( [Field3] = ? )", ['aaa']);

That means variable binding in compound statement (UPDATE statement containing one SELECT statement) is not supported properly in alasql.

Is this a defect in alasql, or do I miss anything?
Any help or hint would be appreciated.

Update from AlaSQL team: See problem in this jsfiddle: http://jsfiddle.net/1w5mL0we/

[mathiasrw]

Below statement is not working. The data is not updated at all.

Please replace '?' with ? in the first SQL and let me know if it solves your issue

[tingli111]

Hi @mathiasrw,

Thanks for your quick response.
Actually we've tried that before, I mean using ? instead of '?'.
But it didn't work.

[mathiasrw]

Ok.

It might be that you are comparing the value of field2 with a result.

Instead of

SELECT [Field2]  FROM [Table] WHERE [Field3] = '?'

please try

VALUE OF SELECT [Field2]  FROM [Table] WHERE [Field3] = '?'

Bonus information:
It might be that you made a simple version for the sissue, but if

DB.exec("UPDATE [Table] SET  [Field1] = 'New' WHERE ( [Field2] = ( SELECT [Field2]  FROM [Table] WHERE [Field3] = '?'  ))", ['aaa']);

is truely what you are using, I suggest changing it to

DB.exec("UPDATE [Table] SET  [Field1] = 'New' WHERE [Field3] = ?", ['aaa']);

[tingli111]

Hi @mathiasrw ,

Thanks for your reply.

Actually there're all kinds of compound statement in our code, and that one was just a simple example.

E.g.
DB.exec("UPDATE [Table] SET [Field1] = 'New21' WHERE ( [Field2] <= ( SELECT [Field2] FROM [Table] WHERE [Field3] = '?' ))", ['aaa'])

I tried your suggestion and replace it with

DB.exec("UPDATE [Table] SET [Field1] = 'New21' WHERE ( [Field2] <= (VALUE OF SELECT [Field2] FROM [Table] WHERE [Field3] = '?' ))", ['aaa'])

But still it is not working.

Only when I remove the '?' and replacing it with the value 'aaa' it can work. Am I missing anything here?

[mathiasrw]

Would it be possible for you to provide some testdata so I can fiddle with it?

[tingli111]

Sure. Below is the sample data for table "Table".

{
    "data": [
        {
            "field1": "New"
            "field2": 1
            "field3": "aaa"
        },
        {
            "field1": "New"
            "field2": 2
            "field3": "bbb"
        },
    ],
    "columns": [
        {
            "columnid": "field1",
            "dbtypeid": "TEXT"
        },
        {
            "columnid": "field2",
            "dbtypeid": "INTEGER"
        },
        {
            "columnid": "field3",
            "dbtypeid": "TEXT"
        },
    ],
    "xcolumns": {
        "field1": {
            "columnid": "field1",
            "dbtypeid": "TEXT"
        },
        "field2": {
            "columnid": "field2",
            "dbtypeid": "INTEGER"
        },
        "field3": {
            "columnid": "field3",
            "dbtypeid": "TEXT"
        },
    },
    "inddefs": {},
    "indices": {},
    "uniqs": {
        "4569468525": {
            "1": {
                "field1": "New"
                "field2": 1
                "field3": "aaa"
            },
            "2": {
                "field1": "New"
                "field2": 2
                "field3": "bbb"
            },
        }
    },
    "uniqdefs": {},
    "identities": {
        "field2": {
            "step": "1"
            "value": "3"
        }
    },
    "checkfn": [],
    "checkfns": [],
    "beforeinsert": {},
    "afterinsert": {},
    "insteadofinsert": {},
    "beforedelete": {},
    "afterdelete": {},
    "insteadofdelete": {},
    "beforeupdate": {},
    "afterupdate": {},
    "insteadofupdate": {},
    "pk": {
        "columns": [
            "field2"
        ],
        "onrightfns": "r['field2']",
        "hh": 4569468525
    },
    "defaultfns": "'field1':'','field2':'','field3':''",
}

When we use below statement to update, it returns 0 and the data is not updated.
DB.exec("UPDATE [Table] SET [Field1] = 'New21' WHERE ( [Field2] <= (SELECT [Field2] FROM [Table] WHERE [Field3] = '?' ))", ['bbb'])

But if we use below statement to update, it returns 2 and the data is updated.
DB.exec("UPDATE [Table] SET [Field1] = 'New21' WHERE ( [Field2] <= ( SELECT [Field2] FROM [Table] WHERE [Field3] = 'bbb'' ))");

[mathiasrw]

Thank you for your time to explain this. I will try to dig into this

You are very right. I have made a jsfiddle replicating your problem: http://jsfiddle.net/8uwx4kje/

The issue is not on SELECT, but only on UPDATE. Example: http://jsfiddle.net/1w5mL0we/