How do I limit the number of results from a SQL2 query?

Using Limit in the Query syntax does seem to throw the exception that you have pointed out. A better way to set the limit is to call setLimit on the query object as shown here (See the bold code):

//Create a connection to the CQ repository running on local host 
    Repository repository = JcrUtils.getRepository("http://localhost:4502/crx/server");
   
   //Create a Session
   javax.jcr.Session session = repository.login( new SimpleCredentials("admin", "admin".toCharArray())); 
 
  
//Obtain the query manager for the session ...
  javax.jcr.query.QueryManager queryManager = session.getWorkspace().getQueryManager();
    
//Setup the quesry based on user input     
  String sqlStatement="";
       
  
 //   sqlStatement = "SELECT * FROM [nt:base] AS s WHERE ISDESCENDANTNODE([/content/clientdomain/en]) and ( contains('jcr:description', 'lorem') or contains('jcr:title', 'lorem') ) LIMIT 10";
  
  sqlStatement = "SELECT * FROM [nt:unstructured] WHERE CONTAINS(desc, 'Customer')" ; 
           
  javax.jcr.query.Query query = queryManager.createQuery(sqlStatement,"JCR-SQL2");
  
  query.setLimit(4); // set the limit
   
  //Execute the query and get the results ...
  javax.jcr.query.QueryResult result = query.execute();
   
  //Iterate over the nodes in the results ...
  javax.jcr.NodeIterator nodeIter = result.getNodes();
  
  int count = 0; 
  while ( nodeIter.hasNext() ) {
      
      count++; 
      nodeIter.nextNode();
  }
  
  System.out.println("THere are this many records "+count); 

*****************************************************************************************

This code returns 4 as set by the code. 

Hope this helps!