Query returns inconsistent results

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 click on the “Preview SQL” button to see if different joins are being generated between your tables. You’ll want to keep this open and run the query again, if the join paths are changing, you’ll see this reflected in the “Preview SQL” generated window.

For example, an inner join may be switching between the columns it joins on:

FROM 'subscriptions' INNER JOIN 'users' ON 'subscriptions'.'user_id' = 'users'.'id'

and

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.

Additionally, in Interactive Mode, there are ways where you can set the path you’d like Chartio to take. Add a filter using a column from the table you want to route the join path through. Choose a non-nullable column (e.g. the primary key) and filter by is not null. This will force the join path to be generated through the desired table.

Once you save your chart, the chart will be locked to the join path shown in the SQL preview.