There may be a situation when you are running a query in Interactive Mode where the same query is returning different results.
Since the SQL is newly generated every time you run your query, the inconsistency may be stemming from different joins being returned each time the query is run. To diagnose if this is happening, switch to SQL mode after running your Interactive Mode query or click on the “Preview SQL” button to see if different joins are being generated between your tables.
For example, an inner join may be switching between:
FROM 'subscriptions' INNER JOIN 'users' ON 'subscriptions'.'user_id' = 'users'.'id'
FROM 'subscriptions' INNER JOIN 'users' ON 'subscriptions'.'id' = 'users'.'subscription_id'
When selecting columns from two tables, Chartio takes the shortest join path between two tables. But since there are 2 foreign keys set between the Subscriptions table and Users table, Chartio may use either of the 2 foreign keys to connect the data between the two tables. This is why the query is returning different values.
If Chartio has chosen a join path that is different than the one you would like to use, you may click the “Regenerate Join” button to regenerate the join in the query.
Once you save your chart, the chart will be locked to the join path shown in the SQL preview.