title: Spring Batch: JdbcPagingItemReader does not work through setSortKeys and cast(id as unsigned)
body: <p>I am working with <code>Spring Batch</code> version <code>3.0.7</code> with <code>Mysql</code></p>
  
  <p>I had the problem that the <code>person_reader</code> table has the <code>id</code> column defined how <code>varchar</code> and thus the following happens for a simple <code>select</code> <em>ordered</em>:</p>
  
  <pre><code>id
  --
  1
  10 &lt;--- !!!
  2
  3
  ...
  </code></pre>
  
  <p>What is need it is</p>
  
  <pre><code>id
  --
  1
  2
  3
  ...
  10 &lt;--- ok!
  ... 
  </code></pre>
  
  <p>The <strong>solution</strong> is use <code>ORDER BY cast(id as unsigned)</code></p>
  
  <p>If I use <code>JdbcCursorItemReader</code> I can use in peace:</p>
  
  <pre><code>setSql("SELECT id, first_name, last_name FROM person_reader ORDER BY cast(id as unsigned)");
  </code></pre>
  
  <p>It works fine. Until here all is ok.</p>
  
  <p><strong>Note:</strong> for the batch process, is very important read the data in the correct order.</p>
  
  <p>The problem is with <code>JdbcPagingItemReader</code></p>
  
  <p><strong>First Try</strong>:</p>
  
  <pre><code>    JdbcPagingItemReader&lt;Person&gt; itemReader = new JdbcPagingItemReader&lt;&gt;();
      itemReader.setDataSource(dataSource);
      itemReader.setPageSize(100);
      itemReader.setRowMapper(new PersonRowMapper());
  
      MySqlPagingQueryProvider pagingQueryProvider = new MySqlPagingQueryProvider();
      pagingQueryProvider.setSelectClause("SELECT id, first_name, last_name");
      pagingQueryProvider.setFromClause("FROM person_reader ORDER BY cast(id as unsigned)");
  
      Map&lt;String, Order&gt; sortKeys= new HashMap&lt;&gt;();
      sortKeys.put("id", Order.ASCENDING);
      pagingQueryProvider.setSortKeys(sortKeys);
  
      itemReader.setQueryProvider(pagingQueryProvider); 
  </code></pre>
  
  <p>Observe:</p>
  
  <ul>
  <li><code>setFromClause("FROM person_reader ORDER BY cast(id as unsigned)")</code></li>
  <li><code>put("id", Order.ASCENDING)</code></li>
  </ul>
  
  <p>The following error appears:</p>
  
  <pre><code>org.springframework.jdbc.BadSqlGrammarException: StatementCallback;
  bad SQL grammar [SELECT id, first_name, last_name FROM person_reader ORDER BY cast(id as unsigned) ORDER BY id ASC LIMIT 100];
  nested exception is java.sql.SQLSyntaxErrorException:
  You have an error in your SQL syntax;
  check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY id ASC LIMIT 100' at line 1
  </code></pre>
  
  <p>Has sense, the <code>SQL</code> generated <code>SELECT id, first_name, last_name FROM person_reader ORDER BY cast(id as unsigned) ORDER BY id ASC LIMIT 100</code> is not correct.</p>
  
  <p><strong>Second Try</strong></p>
  
  <pre><code>    JdbcPagingItemReader&lt;Person&gt; itemReader = new JdbcPagingItemReader&lt;&gt;();
      itemReader.setDataSource(dataSource);
      itemReader.setPageSize(100);
      itemReader.setRowMapper(new PersonRowMapper());
  
      MySqlPagingQueryProvider pagingQueryProvider = new MySqlPagingQueryProvider();
      pagingQueryProvider.setSelectClause("SELECT id, first_name, last_name");
      pagingQueryProvider.setFromClause("FROM person_reader");
  
      Map&lt;String, Order&gt; sortKeys= new HashMap&lt;&gt;();
      sortKeys.put("cast(id as unsigned)", Order.ASCENDING);
      pagingQueryProvider.setSortKeys(sortKeys);
  
      itemReader.setQueryProvider(pagingQueryProvider);
  </code></pre>
  
  <p>Observe:</p>
  
  <ul>
  <li><code>setFromClause("FROM person_reader")</code></li>
  <li><code>put("cast(id as unsigned)", Order.ASCENDING)</code></li>
  </ul>
  
  <p>The following error appears:</p>
  
  <pre><code>org.springframework.jdbc.UncategorizedSQLException: StatementCallback;
  uncategorized SQLException for SQL [SELECT id, first_name, last_name FROM person_reader ORDER BY cast(id as unsigned) ASC LIMIT 100];
  SQL state [S0022]; error code [0]; Column 'cast(id as unsigned)' not found.;
  nested exception is java.sql.SQLException: Column 'cast(id as unsigned)' not found.
  </code></pre>
  
  <p>Has sense about the 'missing' column</p>
  
  <p><code>Order</code> is an <code>enum</code> and thus it can't be extended.</p>
  
  <p>What is the correct approach?</p>
  
  <p><strong>Alpha</strong></p>
  
  <p>If I use in <code>MySQL</code>'s workbench the following:</p>
  
  <ul>
  <li><code>SELECT id, first_name, last_name, cast(id as unsigned) as sb_sort_column FROM person_reader ORDER BY sb_sort_column ASC</code></li>
  </ul>
  
  <p>It works fine. If I use:</p>
  
  <pre><code>MySqlPagingQueryProvider pagingQueryProvider = new MySqlPagingQueryProvider();
  pagingQueryProvider.setSelectClause("SELECT id, first_name, last_name, cast(id as unsigned) as sb_sort_column");
  pagingQueryProvider.setFromClause("FROM person_reader");
  
  Map&lt;String, Order&gt; sortKeys= new HashMap&lt;&gt;();
  sortKeys.put("sb_sort_column", Order.ASCENDING);
  pagingQueryProvider.setSortKeys(sortKeys);
  
  itemReader.setQueryProvider(pagingQueryProvider);
  </code></pre>
  
  <p>The following error arises:</p>
  
  <pre><code>org.springframework.jdbc.BadSqlGrammarException: 
  PreparedStatementCallback; 
  bad SQL grammar [SELECT id, first_name, last_name, cast(id as unsigned) as sb_sort_column FROM person_reader WHERE ((sb_sort_column &gt; ?)) ORDER BY sb_sort_column ASC LIMIT 100]; 
  nested exception is java.sql.SQLSyntaxErrorException: 
  Unknown column 'sb_sort_column' in 'where clause'
  </code></pre>
  
  <p>From above see carefully the 3rd line about the <code>sql</code> statement generated. </p>