Alasql: Second run of same SQL throws "Cannot set property 'columnid' of undefined"

[lpgeiger]

Running a function twice with ALASQL will fail the second time. Perhaps related to 'pivot'?

See an example here: http://jsfiddle.net/oczewn6e/

Run the example above in browser console for the error:
TypeError: Cannot set property 'columnid' of undefined

var alasql = require('alasql');

var test = function(){
    var data = [
    { VendorId: 'SPIKE', IncomeDay: 'FRI', IncomeAmount: 100 },
    { VendorId: 'SPIKE', IncomeDay: 'MON', IncomeAmount: 300 },
    { VendorId: 'FREDS', IncomeDay: 'SUN', IncomeAmount: 400 },
    { VendorId: 'SPIKE', IncomeDay: 'WED', IncomeAmount: 500 },
    { VendorId: 'SPIKE', IncomeDay: 'TUE', IncomeAmount: 200 },
    { VendorId: 'JOHNS', IncomeDay: 'WED', IncomeAmount: 900 },
    { VendorId: 'SPIKE', IncomeDay: 'FRI', IncomeAmount: 100 }]

    var res = alasql('select VendorId,  IncomeDay, [IncomeAmount] from ? pivot (AVG(IncomeAmount) for IncomeDay)', [data])

    return res
}

console.log( test() );
console.log( test() ); // Fails on the second call.

[mathiasrw]

In your link it says pritn instead of print the second time - but fixing that I ALSO GET AN ERROR THE SECOND TIME !

http://jsfiddle.net/hyfr88hx/1/

so crazy!

I will look into if the hashing is creating problems

[lpgeiger]

Oops, yes it was misspelled.

Yes quite crazy, I had no idea what was happening. I'd like to use it in production code. Pivot is 30X faster than the solution I was using before :)

Is there anyway I can temporarily solve this? Is there a workaround by deleting any alasql state?

[mathiasrw]

If you set set alasql.options.cache = false it will work:

http://jsfiddle.net/hyfr88hx/4/

[lpgeiger]

Cool that's perfect for now. Thanks!

[lpgeiger]

@mathiasrw I'd like to help with this. But I'm still trying to learn how caching works with alasql. Are there any docs on reusing a db created from JS objects?

I'd like an example of how multiple queries could be run but JSON data is only loaded once. For example: http://jsfiddle.net/z5j3uaa7/

Or do I have to load the JS every time? Thanks in advance!

[mathiasrw]

@keyscores You are awesome.

how caching works with alasql

The parsed SQL from first run results in an abstract syncax three (AST). The AST is then "executed" to fetch the correct results. The cashing stores the AST of an SQL so we dont need to parse the same SQL again, but can go directly to fetch the correct results. The caching check is implemented in https://github.com/agershun/alasql/blob/c160c6eeca407c33c7fcdfd8a8dae9a301799e4e/src/17alasql.js#L190
I am not sub queries are cashed seperately.

I have a feeling the problem is within the cash - because if we change the SQL in the two runs in the jsfiddle it passes perfectly: http://jsfiddle.net/hyfr88hx/5/

Are there any docs on reusing a db created from JS objects?

Not that I can find. I suggest deepcloning alasql.tables and see if it works out.

Or do I have to load the JS every time?

With JS you mean JSON? You can put the data into a SQL table and avoid giving the data as parameter.

Please hit me with more questios as you need!

[lpgeiger]

@mathiasrw Thanks for a complete response!
Yes the functions work fine if the SQL is changed, as in your example.

I'm curious about deep cloning alasql.tables. But how would I write the query, one the table is cloned? I don't event know the name of the table, when the JSON is loaded. Do you have an example of deep cloning a table created by JSON?

The goal is to try to a avoid overhead of loading and reloading large data ( arrays of 50,000 elements for example). Crossfilter.js for example, keeps the state of the database across multiple js calls.

[mathiasrw]

I don't event know the name of the table, when the JSON is loaded

data is not "loaded" but ran directly on your "data" object.

How does your data looks? If you have a lot of rows we must make sure there are indexes on the fields you search on - to be sure it works as fast as possible

[lpgeiger]

Yes I have a JSON dataset of 30K rows. I'd like to avoid reindexing every time the function runs. Is that possible?

[mathiasrw]

I dont know. Maybe @agershun has an input?