[Bug] The storage balance constraints are not correctly created

[datejada]

Description

The is a mix of storage level variables in the balance of the representative periods, see the following constraint which is for the FR_Pump_Hydro_Open, but the DE_Pump_Hydro_Open appears on the storage balance constraint.

balance_storage_over_clustered_year[FR_Pump_Hydro_Open,2050,224:224] : 27.428571428571427 flow[(FR_Pump_Hydro_Open, FR_E_Balance), 2050, 1, 1:24] - 21 flow[(FR_E_Balance, FR_Pump_Hydro_Open), 2050, 1, 1:24] - storage_level_over_clustered_year[DE_Pump_Hydro_Open,2050,365:365] + storage_level_over_clustered_year[FR_Pump_Hydro_Open,2050,224:224] == 0

Package Version

0.11.0

Julia Version

1.11.3

Reproduction steps

Run the benchmark and check the .lp

Relevant log output

Operating System

No response

[datejada]

Here is the problem: these queries don't keep the order of the tables when the number of rows is large (it seems like greater than 10000)

TulipaEnergyModel.jl/src/variables/create.jl

Lines 87 to 123 in 19dd03f

	DuckDB.query(
	connection,
	"CREATE OR REPLACE TEMP SEQUENCE id START 1;
	CREATE OR REPLACE TABLE var_storage_level_rep_period AS
	SELECT
	nextval('id') as index,
	t_low.asset,
	t_low.year,
	t_low.rep_period,
	t_low.time_block_start,
	t_low.time_block_end
	FROM t_lowest_all AS t_low
	LEFT JOIN asset
	ON t_low.asset = asset.asset
	WHERE
	asset.type = 'storage'
	AND asset.is_seasonal = false;
	",
	)
	DuckDB.query(
	connection,
	"CREATE OR REPLACE TEMP SEQUENCE id START 1;
	CREATE OR REPLACE TABLE var_storage_level_over_clustered_year AS
	SELECT
	nextval('id') as index,
	asset.asset,
	attr.year,
	attr.period_block_start,
	attr.period_block_end,
	FROM asset_timeframe_time_resolution AS attr
	LEFT JOIN asset
	ON attr.asset = asset.asset
	WHERE
	asset.type = 'storage'
	",
	)

[datejada]

The LEFT JOIN asset is messing with the linear index assumption for a large number of rows :(

[datejada]

Tulipa files to reproduce the infeasibility problem:

tulipa-energy-model-files.zip

[suvayu]

The LEFT JOIN asset is messing with the linear index assumption for a large number of rows :(

Could you illustrate the assumption with an example (just some dummy column values)?