Re: [Help-glpk] Beware of using Access and Excel tables with ODBC (SQL statement) in MathProg

[Andrew Makhorin]

>> You could insert the following statement:
>>
>>    display I, J, a, b, d, f, c, x;
>>
>> after the solve statement to see all the input data, intermediate
>> results, and the solution. Then it would be easy to compare the data
>> for different runs and find out where the data are distorted.

> I think this is good idea of debugging these models.

> Here the results below.

> Noli
> ~~~~~~~~
> This is the Excel model
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Model has been successfully generated
> GLPK Simplex Optimizer, v4.42
> 6 rows, 6 columns, 18 non-zeros
> Preprocessing...
> 5 rows, 6 columns, 12 non-zeros
> Scaling...
>  A: min|aij| = 1.000e+000  max|aij| = 1.000e+000  ratio = 1.000e+000
> Problem data seem to be well scaled
> Constructing initial basis...
> Size of triangular part = 5
>       0: obj =  0.000000000e+000  infeas = 9.000e+002 (0)
> *     4: obj =  1.662750000e+002  infeas = 0.000e+000 (0)
> *     7: obj =  1.536750000e+002  infeas = 0.000e+000 (0)
> OPTIMAL SOLUTION FOUND
> Time used:   0.0 secs
> Memory used: 0.1 Mb (141576 bytes)
> Writing result...
> Connected to EXCEL 08.00.0000 -
> C:\Down2\glpk-4.42\glpk-4.42\examples\sql\transp
> INSERT INTO [transp_result$] VALUES (?,?,?)
> Writing result...
> Display statement at line 96
> I:
>    'San Diego'
>    Seattle
> J:
>    Chicago
>    'New York'
>    Topeka
> a['San Diego'] = 600
> a[Seattle] = 350
> b[Chicago] = 300
> b['New York'] = 325
> b[Topeka] = 275
> d[Seattle,'New York'] = 2.5
> d[Seattle,Chicago] = 1.7
> d[Seattle,Topeka] = 1.8
> d['San Diego',Chicago] = 1.8
> d['San Diego','New York'] = 2.5
> d['San Diego',Topeka] = 1.4
> f = 90
> c['San Diego',Chicago] = 0.162
> c['San Diego','New York'] = 0.225
> c['San Diego',Topeka] = 0.126
> c[Seattle,Chicago] = 0.153
> c[Seattle,'New York'] = 0.225
> c[Seattle,Topeka] = 0.162
> x['San Diego',Chicago] = 0
> x['San Diego','New York'] = 325
> x['San Diego',Topeka] = 275
> x[Seattle,Chicago] = 300
> x[Seattle,'New York'] = 0
> x[Seattle,Topeka] = 0
> San Diego     Chicago           0
> San Diego    New York         325
> San Diego      Topeka         275
> Seattle     Chicago         300
> Seattle    New York           0
> Seattle      Topeka           0
> Model has been successfully processed
>>Exit code: 0    Time: 0.420

> This is the sqlite3 model run.
> ~~~~~~~~~~~~~~~~~~~~~~~
> Model has been successfully generated
> GLPK Simplex Optimizer, v4.42
> 6 rows, 6 columns, 18 non-zeros
> Preprocessing...
> 5 rows, 6 columns, 12 non-zeros
> Scaling...
>  A: min|aij| = 1.000e+000  max|aij| = 1.000e+000  ratio = 1.000e+000
> Problem data seem to be well scaled
> Constructing initial basis...
> Size of triangular part = 5
>       0: obj =  0.000000000e+000  infeas = 9.000e+002 (0)
> *     4: obj =  1.561500000e+002  infeas = 0.000e+000 (0)
> *     5: obj =  1.536750000e+002  infeas = 0.000e+000 (0)
> OPTIMAL SOLUTION FOUND
> Time used:   0.0 secs
> Memory used: 0.1 Mb (133563 bytes)
> Writing result...
> Connected to SQLite 3.6.22 -
> C:\Down2\glpk-4.42\glpk-4.42\examples\sql\transp.db3
> DELETE FROM transp_result
> INSERT INTO transp_result VALUES (?,?,?)
> Display statement at line 107
> I:
>    Seattle
>    'San Diego'
> J:
>    'New York'
>    Chicago
>    Topeka
> a[Seattle] = 350
> a['San Diego'] = 600
> b['New York'] = 325
> b[Chicago] = 300
> b[Topeka] = 275
> d[Seattle,'New York'] = 2.5
> d[Seattle,Chicago] = 1.7
> d[Seattle,Topeka] = 1.8
> d['San Diego','New York'] = 2.5
> d['San Diego',Chicago] = 1.8
> d['San Diego',Topeka] = 1.4
> f = 90
> c[Seattle,'New York'] = 0.225
> c[Seattle,Chicago] = 0.153
> c[Seattle,Topeka] = 0.162
> c['San Diego','New York'] = 0.225
> c['San Diego',Chicago] = 0.162
> c['San Diego',Topeka] = 0.126
> x[Seattle,'New York'] = 50
> x[Seattle,Chicago] = 300
> x[Seattle,Topeka] = 0
> x['San Diego','New York'] = 275
> x['San Diego',Chicago] = 0
> x['San Diego',Topeka] = 275
> Seattle    New York          50
> Seattle     Chicago         300
> Seattle      Topeka           0
> San Diego    New York         275
> San Diego     Chicago           0
> San Diego      Topeka         275
> Model has been successfully processed
>>Exit code: 0    Time: 0.215

It is funny, however, both solutions are correct.

The lp instance is dual degenerate and therefore has multiple optima.
Let, for example, you have the "sqlite" solution:

   x[Seattle,'New York'] = 50
   x[Seattle,Chicago] = 300
   x[Seattle,Topeka] = 0
   x['San Diego','New York'] = 275
   x['San Diego',Chicago] = 0
   x['San Diego',Topeka] = 275

You can decrease the flow x[Seattle,'New York'] by 50 units and
increase the flow x['San Diego','New York'] by the same 50 units that
gives the "excel" solution:

   x['San Diego',Chicago] = 0
   x['San Diego','New York'] = 325
   x['San Diego',Topeka] = 275
   x[Seattle,Chicago] = 300
   x[Seattle,'New York'] = 0
   x[Seattle,Topeka] = 0

Note that the "excel" solution remains primal feasible and the
objective value is *not* changed, because c[Seattle,'New York'] is
the same as c['San Diego','New York'] (= 0.225).