phoenix-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jamestay...@apache.org
Subject [2/3] git commit: PHOENIX-1016 Support MINVALUE, MAXVALUE, and CYCLE options in CREATE SEQUENCE (Thomas D'Silva)
Date Sat, 19 Jul 2014 23:41:10 GMT
PHOENIX-1016 Support MINVALUE, MAXVALUE, and CYCLE options in CREATE SEQUENCE (Thomas D'Silva)


Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo
Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/15e1108f
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/15e1108f
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/15e1108f

Branch: refs/heads/3.0
Commit: 15e1108fdde3abde0bba87ec2e40035f89868d30
Parents: 6d4c9f4
Author: James Taylor <jtaylor@salesforce.com>
Authored: Sat Jul 19 16:42:24 2014 -0700
Committer: James Taylor <jtaylor@salesforce.com>
Committed: Sat Jul 19 16:42:24 2014 -0700

----------------------------------------------------------------------
 .../org/apache/phoenix/end2end/SequenceIT.java  | 699 +++++++++++++++++--
 phoenix-core/src/main/antlr3/PhoenixSQL.g       |  13 +-
 .../phoenix/compile/CreateSequenceCompiler.java | 161 +++--
 .../coprocessor/SequenceRegionObserver.java     | 114 ++-
 .../phoenix/exception/SQLExceptionCode.java     |  12 +-
 .../phoenix/jdbc/PhoenixDatabaseMetaData.java   |   7 +
 .../apache/phoenix/jdbc/PhoenixStatement.java   |  16 +-
 .../phoenix/parse/CreateSequenceStatement.java  |  45 +-
 .../apache/phoenix/parse/ParseNodeFactory.java  |   9 +-
 .../apache/phoenix/parse/SelectStatement.java   |   2 +-
 .../phoenix/query/ConnectionQueryServices.java  |   2 +-
 .../query/ConnectionQueryServicesImpl.java      |  31 +-
 .../query/ConnectionlessQueryServicesImpl.java  |  34 +-
 .../query/DelegateConnectionQueryServices.java  |   8 +-
 .../apache/phoenix/query/QueryConstants.java    |  19 +-
 .../apache/phoenix/schema/MetaDataClient.java   |  25 +-
 .../org/apache/phoenix/schema/Sequence.java     | 241 +++++--
 .../org/apache/phoenix/schema/SequenceInfo.java |  29 +
 .../org/apache/phoenix/util/KeyValueUtil.java   |  18 +
 .../org/apache/phoenix/util/SequenceUtil.java   |  72 ++
 .../TenantSpecificViewIndexCompileTest.java     |   4 +-
 .../apache/phoenix/jdbc/PhoenixTestDriver.java  |   1 +
 .../java/org/apache/phoenix/query/BaseTest.java |   3 +-
 .../apache/phoenix/util/SequenceUtilTest.java   | 117 ++++
 24 files changed, 1428 insertions(+), 254 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/phoenix/blob/15e1108f/phoenix-core/src/it/java/org/apache/phoenix/end2end/SequenceIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/SequenceIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SequenceIT.java
index 84bfece..1dbdb52 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/SequenceIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SequenceIT.java
@@ -28,6 +28,7 @@ import java.sql.DriverManager;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
+import java.util.List;
 import java.util.Map;
 import java.util.Properties;
 
@@ -39,16 +40,19 @@ import org.apache.phoenix.schema.SequenceNotFoundException;
 import org.apache.phoenix.util.PhoenixRuntime;
 import org.apache.phoenix.util.QueryUtil;
 import org.apache.phoenix.util.ReadOnlyProps;
+import org.apache.phoenix.util.SequenceUtil;
 import org.apache.phoenix.util.TestUtil;
 import org.junit.Assert;
 import org.junit.BeforeClass;
 import org.junit.Test;
 import org.junit.experimental.categories.Category;
 
+import com.google.common.collect.Lists;
 import com.google.common.collect.Maps;
 
 @Category(ClientManagedTimeTest.class)
 public class SequenceIT extends BaseClientManagedTimeIT {
+    private static final String NEXT_VAL_SQL = "SELECT NEXT VALUE FOR foo.bar FROM SYSTEM.\"SEQUENCE\"";
     private static final long BATCH_SIZE = 3;
     
     private Connection conn;
@@ -98,22 +102,22 @@ public class SequenceIT extends BaseClientManagedTimeIT {
 
 		}
 	}
-
+	
 	@Test
-	public void testCreateSequence() throws Exception {	
+    public void testCreateSequence() throws Exception { 
         nextConnection();
-		conn.createStatement().execute("CREATE SEQUENCE alpha.omega START WITH 2 INCREMENT BY 4");
+        conn.createStatement().execute("CREATE SEQUENCE alpha.omega START WITH 2 INCREMENT BY 4");
         nextConnection();
-		String query = "SELECT sequence_schema, sequence_name, current_value, increment_by FROM SYSTEM.\"SEQUENCE\" WHERE sequence_name='OMEGA'";
-		ResultSet rs = conn.prepareStatement(query).executeQuery();
-		assertTrue(rs.next());
-		assertEquals("ALPHA", rs.getString("sequence_schema"));
-		assertEquals("OMEGA", rs.getString("sequence_name"));
-		assertEquals(2, rs.getInt("current_value"));
-		assertEquals(4, rs.getInt("increment_by"));
-		assertFalse(rs.next());
+        String query = "SELECT sequence_schema, sequence_name, current_value, increment_by FROM SYSTEM.\"SEQUENCE\" WHERE sequence_name='OMEGA'";
+        ResultSet rs = conn.prepareStatement(query).executeQuery();
+        assertTrue(rs.next());
+        assertEquals("ALPHA", rs.getString("sequence_schema"));
+        assertEquals("OMEGA", rs.getString("sequence_name"));
+        assertEquals(null, rs.getBytes("current_value"));
+        assertEquals(4, rs.getInt("increment_by"));
+        assertFalse(rs.next());
 	}
-		
+    
     @Test
     public void testCurrentValueFor() throws Exception {
         ResultSet rs;
@@ -126,6 +130,7 @@ public class SequenceIT extends BaseClientManagedTimeIT {
             fail();
         } catch (SQLException e) {
             assertEquals(SQLExceptionCode.CANNOT_CALL_CURRENT_BEFORE_NEXT_VALUE.getErrorCode(), e.getErrorCode());
+            assertTrue(e.getNextException()==null);
         }
         
         rs = conn.createStatement().executeQuery("SELECT NEXT VALUE FOR used.nowhere FROM SYSTEM.\"SEQUENCE\"");
@@ -146,7 +151,7 @@ public class SequenceIT extends BaseClientManagedTimeIT {
         assertTrue(rs.next());
         assertEquals("ALPHA", rs.getString("sequence_schema"));
         assertEquals("OMEGA", rs.getString("sequence_name"));
-        assertEquals(2, rs.getInt("current_value"));
+        assertEquals(null, rs.getBytes("current_value"));
         assertEquals(4, rs.getInt("increment_by"));
         assertFalse(rs.next());
 
@@ -168,18 +173,7 @@ public class SequenceIT extends BaseClientManagedTimeIT {
         nextConnection();
 		conn.createStatement().execute("CREATE SEQUENCE foo.bar START WITH 3 INCREMENT BY 2");
         nextConnection();
-		String query = "SELECT NEXT VALUE FOR foo.bar FROM SYSTEM.\"SEQUENCE\"";
-		ResultSet rs = conn.prepareStatement(query).executeQuery();
-		assertTrue(rs.next());
-		assertEquals(3, rs.getInt(1));
-
-		rs = conn.prepareStatement(query).executeQuery();
-		assertTrue(rs.next());
-		assertEquals(5, rs.getInt(1));
-
-		rs = conn.prepareStatement(query).executeQuery();
-		assertTrue(rs.next());
-		assertEquals(7, rs.getInt(1));
+        assertSequenceValuesForSingleRow(3, 5, 7);
 	}
 
 	@Test
@@ -200,30 +194,45 @@ public class SequenceIT extends BaseClientManagedTimeIT {
         assertEquals(3, rs.getInt(1));
 	}
 
-	@Test
-	public void testSequenceCreation() throws Exception {		
+    @Test
+    public void testSequenceCreation() throws Exception {
         nextConnection();
-		conn.createStatement().execute("CREATE SEQUENCE alpha.gamma START WITH 2 INCREMENT BY 3 CACHE 5");
+        conn.createStatement()
+                .execute(
+                    "CREATE SEQUENCE alpha.gamma START WITH 2 INCREMENT BY 3 MINVALUE 0 MAXVALUE 10 CYCLE CACHE 5");
         nextConnection();
-        ResultSet rs = conn.createStatement().executeQuery("SELECT start_with, increment_by, cache_size, sequence_schema, sequence_name FROM SYSTEM.\"SEQUENCE\"");
+        ResultSet rs =
+                conn.createStatement()
+                        .executeQuery(
+                            "SELECT start_with, current_value, increment_by, cache_size, min_value, max_value, cycle_flag, sequence_schema, sequence_name FROM SYSTEM.\"SEQUENCE\"");
         assertTrue(rs.next());
-        assertEquals(2, rs.getLong(1));
-        assertEquals(3, rs.getLong(2));
-        assertEquals(5, rs.getLong(3));
-        assertEquals("ALPHA", rs.getString(4));
-        assertEquals("GAMMA", rs.getString(5));
+        assertEquals(2, rs.getLong("start_with"));
+        assertEquals(null, rs.getBytes("current_value"));
+        assertEquals(3, rs.getLong("increment_by"));
+        assertEquals(5, rs.getLong("cache_size"));
+        assertEquals(0, rs.getLong("min_value"));
+        assertEquals(10, rs.getLong("max_value"));
+        assertEquals(true, rs.getBoolean("cycle_flag"));
+        assertEquals("ALPHA", rs.getString("sequence_schema"));
+        assertEquals("GAMMA", rs.getString("sequence_name"));
         assertFalse(rs.next());
-		rs = conn.createStatement().executeQuery("SELECT NEXT VALUE FOR alpha.gamma, CURRENT VALUE FOR alpha.gamma FROM SYSTEM.\"SEQUENCE\"");
+        rs =
+                conn.createStatement()
+                        .executeQuery(
+                            "SELECT NEXT VALUE FOR alpha.gamma, CURRENT VALUE FOR alpha.gamma FROM SYSTEM.\"SEQUENCE\"");
         assertTrue(rs.next());
         assertEquals(2, rs.getLong(1));
         assertEquals(2, rs.getLong(2));
         assertFalse(rs.next());
-        rs = conn.createStatement().executeQuery("SELECT CURRENT VALUE FOR alpha.gamma, NEXT VALUE FOR alpha.gamma FROM SYSTEM.\"SEQUENCE\"");
+        rs =
+                conn.createStatement()
+                        .executeQuery(
+                            "SELECT CURRENT VALUE FOR alpha.gamma, NEXT VALUE FOR alpha.gamma FROM SYSTEM.\"SEQUENCE\"");
         assertTrue(rs.next());
         assertEquals(5, rs.getLong(1));
         assertEquals(5, rs.getLong(2));
         assertFalse(rs.next());
-	}
+    }
 
     @Test
     public void testSameMultipleSequenceValues() throws Exception {
@@ -240,16 +249,16 @@ public class SequenceIT extends BaseClientManagedTimeIT {
     }
 
     @Test
-	public void testMultipleSequenceValues() throws Exception {
+    public void testMultipleSequenceValues() throws Exception {
         nextConnection();
-		conn.createStatement().execute("CREATE SEQUENCE alpha.zeta START WITH 4 INCREMENT BY 7");
-		conn.createStatement().execute("CREATE SEQUENCE alpha.kappa START WITH 9 INCREMENT BY 2");
+        conn.createStatement().execute("CREATE SEQUENCE alpha.zeta START WITH 4 INCREMENT BY 7");
+        conn.createStatement().execute("CREATE SEQUENCE alpha.kappa START WITH 9 INCREMENT BY 2");
         nextConnection();
-		String query = "SELECT NEXT VALUE FOR alpha.zeta, NEXT VALUE FOR alpha.kappa FROM SYSTEM.\"SEQUENCE\"";
-		ResultSet rs = conn.prepareStatement(query).executeQuery();
-		assertTrue(rs.next());
-		assertEquals(4, rs.getInt(1));
-		assertEquals(9, rs.getInt(2));
+        String query = "SELECT NEXT VALUE FOR alpha.zeta, NEXT VALUE FOR alpha.kappa FROM SYSTEM.\"SEQUENCE\"";
+        ResultSet rs = conn.prepareStatement(query).executeQuery();
+        assertTrue(rs.next());
+        assertEquals(4, rs.getInt(1));
+        assertEquals(9, rs.getInt(2));
         assertTrue(rs.next());
         assertEquals(4+7, rs.getInt(1));
         assertEquals(9+2, rs.getInt(2));
@@ -266,8 +275,79 @@ public class SequenceIT extends BaseClientManagedTimeIT {
         assertEquals(9+2*3, rs.getInt(2));
         assertFalse(rs.next());
         conn.close();
-	}
-	
+    }
+    
+    @Test
+    public void testMultipleSequencesNoCycle() throws Exception {
+        nextConnection();
+        conn.createStatement().execute(
+            "CREATE SEQUENCE alpha.zeta START WITH 4 INCREMENT BY 7 MAXVALUE 24");
+        conn.createStatement().execute(
+            "CREATE SEQUENCE alpha.kappa START WITH 9 INCREMENT BY -2 MINVALUE 5");
+        nextConnection();
+        String query =
+                "SELECT NEXT VALUE FOR alpha.zeta, NEXT VALUE FOR alpha.kappa FROM SYSTEM.\"SEQUENCE\"";
+        ResultSet rs = conn.prepareStatement(query).executeQuery();
+        assertTrue(rs.next());
+        assertEquals(4, rs.getInt(1));
+        assertEquals(9, rs.getInt(2));
+        assertTrue(rs.next());
+        assertEquals(4 + 7, rs.getInt(1));
+        assertEquals(9 - 2, rs.getInt(2));
+        assertFalse(rs.next());
+        conn.close();
+        
+        nextConnection();
+        rs = conn.prepareStatement(query).executeQuery();
+        assertTrue(rs.next());
+        assertEquals(4 + 7 * 2, rs.getInt(1));
+        assertEquals(9 - 2 * 2, rs.getInt(2));
+        try {
+            rs.next();
+            fail();
+        } catch (SQLException e) {
+            SQLException sqlEx1 =
+                    SequenceUtil.getException("ALPHA", "ZETA",
+                        SQLExceptionCode.SEQUENCE_VAL_REACHED_MAX_VALUE);
+            SQLException sqlEx2 =
+                    SequenceUtil.getException("ALPHA", "KAPPA",
+                        SQLExceptionCode.SEQUENCE_VAL_REACHED_MIN_VALUE);
+            verifyExceptions(e, Lists.newArrayList(sqlEx1.getMessage(), sqlEx2.getMessage()));
+        }
+        conn.close();
+    }
+    
+    @Test
+    public void testMultipleSequencesCycle() throws Exception {
+        nextConnection();
+        conn.createStatement().execute(
+            "CREATE SEQUENCE alpha.zeta START WITH 4 INCREMENT BY 7 MINVALUE 4 MAXVALUE 19 CYCLE");
+        conn.createStatement().execute(
+            "CREATE SEQUENCE alpha.kappa START WITH 9 INCREMENT BY -2 MINVALUE 5 MAXVALUE 9 CYCLE");
+        nextConnection();
+        String query =
+                "SELECT NEXT VALUE FOR alpha.zeta, NEXT VALUE FOR alpha.kappa FROM SYSTEM.\"SEQUENCE\"";
+        ResultSet rs = conn.prepareStatement(query).executeQuery();
+        assertTrue(rs.next());
+        assertEquals(4, rs.getInt(1));
+        assertEquals(9, rs.getInt(2));
+        assertTrue(rs.next());
+        assertEquals(4 + 7, rs.getInt(1));
+        assertEquals(9 - 2, rs.getInt(2));
+        assertFalse(rs.next());
+        conn.close();
+        
+        nextConnection();
+        rs = conn.prepareStatement(query).executeQuery();
+        assertTrue(rs.next());
+        assertEquals(4 + 7 * 2, rs.getInt(1));
+        assertEquals(9 - 2 * 2, rs.getInt(2));
+        assertTrue(rs.next());
+        assertEquals(4, rs.getInt(1));
+        assertEquals(9, rs.getInt(2));
+        conn.close();
+    }
+    
 	@Test
 	public void testCompilerOptimization() throws Exception {
 		nextConnection();
@@ -459,8 +539,8 @@ public class SequenceIT extends BaseClientManagedTimeIT {
             stmt1.execute();
         }
         conn1.commit();
-        conn1.close(); // will return unused sequences, so no gaps now
-        
+        conn1.close(); 
+       
         nextConnection();
         Connection conn2 = conn;
         conn = null; // So that call to nextConnection doesn't close it
@@ -470,7 +550,7 @@ public class SequenceIT extends BaseClientManagedTimeIT {
             stmt2.execute();
         }
         conn2.commit();
-        conn1.close();
+        conn2.close();
         
         nextConnection();
         ResultSet rs = conn.createStatement().executeQuery("SELECT k FROM foo");
@@ -573,7 +653,7 @@ public class SequenceIT extends BaseClientManagedTimeIT {
         rs = conn.createStatement().executeQuery("SELECT sequence_name, current_value FROM SYSTEM.\"SEQUENCE\" WHERE sequence_name='BAR'");
         assertTrue(rs.next());
         assertEquals("BAR", rs.getString(1));
-        assertEquals(1, rs.getLong(2));
+        assertEquals(null, rs.getBytes(2));
         conn.close();
         conn2.close();
 
@@ -592,10 +672,10 @@ public class SequenceIT extends BaseClientManagedTimeIT {
         nextConnection();
         conn.createStatement().execute("CREATE SEQUENCE foo.bar START WITH 3 INCREMENT BY 2");
         nextConnection();
-        String query = "SELECT COALESCE(NEXT VALUE FOR foo.bar,1) FROM SYSTEM.\"SEQUENCE\"";
+        String query = "SELECT LPAD(ENCODE(NEXT VALUE FOR foo.bar,'base62'),5,'0') FROM SYSTEM.\"SEQUENCE\"";
         ResultSet rs = conn.prepareStatement(query).executeQuery();
         assertTrue(rs.next());
-        assertEquals(3, rs.getInt(1));
+        assertEquals("00003", rs.getString(1));
     }
     
     @Test
@@ -609,11 +689,510 @@ public class SequenceIT extends BaseClientManagedTimeIT {
         assertEquals(4, rs.getInt(1));
     }
     
-	private void nextConnection() throws Exception {
-	    if (conn != null) conn.close();
-	    long ts = nextTimestamp();
-		Properties props = new Properties(TestUtil.TEST_PROPERTIES);
-		props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts));
-		conn = DriverManager.getConnection(getUrl(), props);
-	}	
+    private void nextConnection() throws Exception {
+        if (conn != null) conn.close();
+        long ts = nextTimestamp();
+        Properties props = new Properties(TestUtil.TEST_PROPERTIES);
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts));
+        conn = DriverManager.getConnection(getUrl(), props);
+    }   
+    
+    @Test
+    public void testSequenceDefault() throws Exception {
+        nextConnection();    
+        conn.createStatement().execute("CREATE SEQUENCE foo.bar");
+        nextConnection();
+        assertSequenceValuesForSingleRow(1, 2, 3);
+        conn.createStatement().execute("DROP SEQUENCE foo.bar");
+        
+        nextConnection();
+        conn.createStatement().execute("CREATE SEQUENCE foo.bar INCREMENT BY -1");
+        nextConnection();
+        assertSequenceValuesForSingleRow(1, 0, -1);
+        conn.createStatement().execute("DROP SEQUENCE foo.bar");
+        
+        nextConnection();
+        conn.createStatement().execute("CREATE SEQUENCE foo.bar MINVALUE 10");
+        nextConnection();
+        assertSequenceValuesForSingleRow(10, 11, 12);
+        conn.createStatement().execute("DROP SEQUENCE foo.bar");
+        
+        nextConnection();
+        conn.createStatement().execute("CREATE SEQUENCE foo.bar INCREMENT BY -1 MINVALUE 10 ");
+        nextConnection();
+        assertSequenceValuesForSingleRow(Long.MAX_VALUE, Long.MAX_VALUE - 1, Long.MAX_VALUE - 2);
+        conn.createStatement().execute("DROP SEQUENCE foo.bar");
+
+        nextConnection();
+        conn.createStatement().execute("CREATE SEQUENCE foo.bar MAXVALUE 0");
+        nextConnection();
+        assertSequenceValuesForSingleRow(Long.MIN_VALUE, Long.MIN_VALUE + 1, Long.MIN_VALUE + 2);
+        conn.createStatement().execute("DROP SEQUENCE foo.bar");
+        
+        nextConnection();
+        conn.createStatement().execute("CREATE SEQUENCE foo.bar INCREMENT BY -1 MAXVALUE 0");
+        nextConnection();
+        assertSequenceValuesForSingleRow(0, -1, -2);
+    }
+
+    @Test
+    public void testSequenceValidateStartValue() throws Exception {
+        nextConnection();
+        try {
+            conn.createStatement().execute(
+                "CREATE SEQUENCE foo.bar1 START WITH 1 INCREMENT BY 1 MINVALUE 2 MAXVALUE 3");
+            fail();
+        } catch (SQLException e) {
+            assertEquals(SQLExceptionCode.STARTS_WITH_MUST_BE_BETWEEN_MIN_MAX_VALUE.getErrorCode(),
+                e.getErrorCode());
+            assertTrue(e.getNextException() == null);
+        }
+
+        try {
+            conn.createStatement().execute(
+                "CREATE SEQUENCE foo.bar2 START WITH 4 INCREMENT BY 1 MINVALUE 2 MAXVALUE 3");
+            fail();
+        } catch (SQLException e) {
+            assertEquals(SQLExceptionCode.STARTS_WITH_MUST_BE_BETWEEN_MIN_MAX_VALUE.getErrorCode(),
+                e.getErrorCode());
+            assertTrue(e.getNextException() == null);
+        }
+    }
+
+    @Test
+    public void testSequenceValidateMinValue() throws Exception {
+        nextConnection();
+        try {
+            conn.createStatement().execute("CREATE SEQUENCE foo.bar MINVALUE abc");
+            fail();
+        } catch (SQLException e) {
+            assertEquals(SQLExceptionCode.MINVALUE_MUST_BE_CONSTANT.getErrorCode(),
+                e.getErrorCode());
+            assertTrue(e.getNextException() == null);
+        }
+    }
+
+    @Test
+    public void testSequenceValidateMaxValue() throws Exception {
+        nextConnection();
+        try {
+            conn.createStatement().execute("CREATE SEQUENCE foo.bar MAXVALUE null");
+            fail();
+        } catch (SQLException e) {
+            assertEquals(SQLExceptionCode.MAXVALUE_MUST_BE_CONSTANT.getErrorCode(),
+                e.getErrorCode());
+            assertTrue(e.getNextException() == null);
+        }
+    }
+
+    @Test
+    public void testSequenceValidateMinValueLessThanOrEqualToMaxValue() throws Exception {
+        nextConnection();
+        try {
+            conn.createStatement().execute("CREATE SEQUENCE foo.bar MINVALUE 2 MAXVALUE 1");
+            fail();
+        } catch (SQLException e) {
+            assertEquals(
+                SQLExceptionCode.MINVALUE_MUST_BE_LESS_THAN_OR_EQUAL_TO_MAXVALUE.getErrorCode(),
+                e.getErrorCode());
+            assertTrue(e.getNextException() == null);
+        }
+    }
+
+    @Test
+    public void testSequenceValidateIncrementConstant() throws Exception {
+        nextConnection();
+        try {
+            conn.createStatement().execute("CREATE SEQUENCE foo.bar INCREMENT null");
+            fail();
+        } catch (SQLException e) {
+            assertEquals(SQLExceptionCode.INCREMENT_BY_MUST_BE_CONSTANT.getErrorCode(),
+                e.getErrorCode());
+            assertTrue(e.getNextException() == null);
+        }
+    }
+
+    @Test
+    public void testSequenceValidateIncrementNotEqualToZero() throws Exception {
+        nextConnection();
+        try {
+            conn.createStatement().execute("CREATE SEQUENCE foo.bar INCREMENT 0");
+            fail();
+        } catch (SQLException e) {
+            assertEquals(SQLExceptionCode.INCREMENT_BY_MUST_NOT_BE_ZERO.getErrorCode(),
+                e.getErrorCode());
+            assertTrue(e.getNextException() == null);
+        }
+    }
+    
+    @Test
+    public void testSequenceStartWithMinMaxSameValueIncreasingCycle() throws Exception {
+        nextConnection();
+        conn.createStatement()
+                .execute(
+                    "CREATE SEQUENCE foo.bar START WITH 3 INCREMENT BY 1 MINVALUE 3 MAXVALUE 3 CYCLE CACHE 1");
+        nextConnection();
+        assertSequenceValuesForSingleRow(3, 3, 3);
+    }
+    
+    @Test
+    public void testSequenceStartWithMinMaxSameValueDecreasingCycle() throws Exception {
+        nextConnection();
+        conn.createStatement()
+                .execute(
+                    "CREATE SEQUENCE foo.bar START WITH 3 INCREMENT BY -1 MINVALUE 3 MAXVALUE 3 CYCLE CACHE 2");
+        nextConnection();
+        assertSequenceValuesForSingleRow(3, 3, 3);
+    }
+    
+    @Test
+    public void testSequenceStartWithMinMaxSameValueIncreasingNoCycle() throws Exception {
+        nextConnection();
+        conn.createStatement()
+                .execute(
+                    "CREATE SEQUENCE foo.bar START WITH 3 INCREMENT BY 1 MINVALUE 3 MAXVALUE 3 CACHE 1");
+        nextConnection();
+        assertSequenceValuesForSingleRow(3);
+        try {
+            ResultSet rs = conn.createStatement().executeQuery(NEXT_VAL_SQL);
+            rs.next();
+            fail();
+        } catch (SQLException e) {
+            assertEquals(SQLExceptionCode.SEQUENCE_VAL_REACHED_MAX_VALUE.getErrorCode(),
+                e.getErrorCode());
+            assertTrue(e.getNextException() == null);
+        }
+    }
+    
+    @Test
+    public void testSequenceStartWithMinMaxSameValueDecreasingNoCycle() throws Exception {
+        nextConnection();
+        conn.createStatement()
+                .execute(
+                    "CREATE SEQUENCE foo.bar START WITH 3 INCREMENT BY -1 MINVALUE 3 MAXVALUE 3 CACHE 2");
+        nextConnection();
+        assertSequenceValuesForSingleRow(3);
+        try {
+            ResultSet rs = conn.createStatement().executeQuery(NEXT_VAL_SQL);
+            rs.next();
+            fail();
+        } catch (SQLException e) {
+            assertEquals(SQLExceptionCode.SEQUENCE_VAL_REACHED_MIN_VALUE.getErrorCode(),
+                e.getErrorCode());
+            assertTrue(e.getNextException() == null);
+        }
+    }
+
+    @Test
+    public void testSequenceIncreasingCycle() throws Exception {
+        nextConnection();
+        conn.createStatement()
+                .execute(
+                    "CREATE SEQUENCE foo.bar START WITH 2 INCREMENT BY 3 MINVALUE 1 MAXVALUE 10 CYCLE CACHE 2");
+        nextConnection();
+        assertSequenceValuesForSingleRow(2, 5, 8, 1, 4, 7, 10, 1, 4);
+    }
+
+    @Test
+    public void testSequenceDecreasingCycle() throws Exception {
+        nextConnection();
+        conn.createStatement()
+                .execute(
+                    "CREATE SEQUENCE foo.bar START WITH 3 INCREMENT BY -2 MINVALUE 1 MAXVALUE 10 CYCLE CACHE 2");
+        nextConnection();
+        assertSequenceValuesForSingleRow(3, 1, 10, 8, 6, 4, 2, 10, 8);
+    }
+
+    @Test
+    public void testSequenceIncreasingNoCycle() throws Exception {
+        nextConnection();
+        // client throws exception
+        conn.createStatement().execute(
+            "CREATE SEQUENCE foo.bar START WITH 2 INCREMENT BY 3 MINVALUE 1 MAXVALUE 10 CACHE 100");
+        nextConnection();
+        assertSequenceValuesForSingleRow(2, 5, 8);
+        try {
+            ResultSet rs = conn.createStatement().executeQuery(NEXT_VAL_SQL);
+            rs.next();
+            fail();
+        } catch (SQLException e) {
+            assertEquals(SQLExceptionCode.SEQUENCE_VAL_REACHED_MAX_VALUE.getErrorCode(),
+                e.getErrorCode());
+            assertTrue(e.getNextException() == null);
+        }
+    }
+
+    @Test
+    public void testSequenceIncreasingUsingMaxValueNoCycle() throws Exception {
+        nextConnection();
+        // server throws exception
+        conn.createStatement().execute(
+            "CREATE SEQUENCE foo.bar START WITH 8 INCREMENT BY 2 MINVALUE 1 MAXVALUE 10 CACHE 2");
+        nextConnection();
+        assertSequenceValuesForSingleRow(8, 10);
+        try {
+            ResultSet rs = conn.createStatement().executeQuery(NEXT_VAL_SQL);
+            rs.next();
+            fail();
+        } catch (SQLException e) {
+            assertEquals(SQLExceptionCode.SEQUENCE_VAL_REACHED_MAX_VALUE.getErrorCode(),
+                e.getErrorCode());
+            assertTrue(e.getNextException() == null);
+        }
+    }
+
+    @Test
+    public void testSequenceDecreasingNoCycle() throws Exception {
+        nextConnection();
+        // client will throw exception
+        conn.createStatement()
+                .execute(
+                    "CREATE SEQUENCE foo.bar START WITH 4 INCREMENT BY -2 MINVALUE 1 MAXVALUE 10 CACHE 100");
+        nextConnection();
+        assertSequenceValuesForSingleRow(4, 2);
+        try {
+            ResultSet rs = conn.createStatement().executeQuery(NEXT_VAL_SQL);
+            rs.next();
+            fail();
+        } catch (SQLException e) {
+            assertEquals(SQLExceptionCode.SEQUENCE_VAL_REACHED_MIN_VALUE.getErrorCode(),
+                e.getErrorCode());
+            assertTrue(e.getNextException() == null);
+        }
+    }
+
+    @Test
+    public void testSequenceDecreasingUsingMinValueNoCycle() throws Exception {
+        nextConnection();
+        // server will throw exception
+        conn.createStatement().execute(
+            "CREATE SEQUENCE foo.bar START WITH 3 INCREMENT BY -2 MINVALUE 1 MAXVALUE 10 CACHE 2");
+        nextConnection();
+        assertSequenceValuesForSingleRow(3, 1);
+        try {
+            ResultSet rs = conn.createStatement().executeQuery(NEXT_VAL_SQL);
+            rs.next();
+            fail();
+        } catch (SQLException e) {
+            assertEquals(SQLExceptionCode.SEQUENCE_VAL_REACHED_MIN_VALUE.getErrorCode(),
+                e.getErrorCode());
+            assertTrue(e.getNextException() == null);
+        }
+    }
+
+    @Test
+    public void testSequenceIncreasingOverflowNoCycle() throws Exception {
+        nextConnection();
+        // start with Long.MAX_VALUE
+        conn.createStatement().execute(
+            "CREATE SEQUENCE foo.bar START WITH 9223372036854775807 INCREMENT BY 1 CACHE 10");
+        nextConnection();
+        assertSequenceValuesForSingleRow(Long.MAX_VALUE);
+        try {
+            ResultSet rs = conn.createStatement().executeQuery(NEXT_VAL_SQL);
+            rs.next();
+            fail();
+        } catch (SQLException e) {
+            assertEquals(SQLExceptionCode.SEQUENCE_VAL_REACHED_MAX_VALUE.getErrorCode(),
+                e.getErrorCode());
+            assertTrue(e.getNextException() == null);
+        }
+    }
+
+    @Test
+    public void testSequenceIncreasingOverflowCycle() throws Exception {
+        nextConnection();
+        // start with Long.MAX_VALUE
+        conn.createStatement()
+                .execute(
+                    "CREATE SEQUENCE foo.bar START WITH 9223372036854775807 INCREMENT BY 9223372036854775807 CYCLE CACHE 10");
+        nextConnection();
+        assertSequenceValuesForSingleRow(Long.MAX_VALUE, Long.MIN_VALUE, -1, Long.MAX_VALUE - 1,
+            Long.MIN_VALUE, -1);
+    }
+
+    @Test
+    public void testSequenceDecreasingOverflowNoCycle() throws Exception {
+        nextConnection();
+        // start with Long.MIN_VALUE + 1
+        conn.createStatement().execute(
+            "CREATE SEQUENCE foo.bar START WITH -9223372036854775807 INCREMENT BY -1 CACHE 10");
+        nextConnection();
+        assertSequenceValuesForSingleRow(Long.MIN_VALUE + 1, Long.MIN_VALUE);
+        try {
+            ResultSet rs = conn.createStatement().executeQuery(NEXT_VAL_SQL);
+            rs.next();
+            fail();
+        } catch (SQLException e) {
+            assertEquals(SQLExceptionCode.SEQUENCE_VAL_REACHED_MIN_VALUE.getErrorCode(),
+                e.getErrorCode());
+            assertTrue(e.getNextException() == null);
+        }
+    }
+
+    @Test
+    public void testSequenceDecreasingOverflowCycle() throws Exception {
+        nextConnection();
+        // start with Long.MIN_VALUE + 1
+        conn.createStatement()
+                .execute(
+                    "CREATE SEQUENCE foo.bar START WITH -9223372036854775807 INCREMENT BY -9223372036854775807 CYCLE CACHE 10");
+        nextConnection();
+        assertSequenceValuesForSingleRow(Long.MIN_VALUE + 1, Long.MAX_VALUE, 0, Long.MIN_VALUE + 1,
+            Long.MAX_VALUE, 0);
+    }
+
+    @Test
+    public void testMultipleSequenceValuesNoCycle() throws Exception {
+        nextConnection();
+        conn.createStatement().execute(
+            "CREATE SEQUENCE foo.bar START WITH 1 INCREMENT BY 2 MINVALUE 1 MAXVALUE 10 CACHE 2");
+        conn.createStatement().execute("CREATE SEQUENCE foo.bar2");
+        nextConnection();
+        assertSequenceValuesMultipleSeq(1, 3);
+        assertSequenceValuesMultipleSeq(5, 7);
+
+        ResultSet rs = conn.prepareStatement(NEXT_VAL_SQL).executeQuery();
+        assertTrue(rs.next());
+        assertEquals(9, rs.getInt(1));
+        try {
+            assertTrue(rs.next());
+            fail();
+        } catch (SQLException e) {
+            assertEquals(SQLExceptionCode.SEQUENCE_VAL_REACHED_MAX_VALUE.getErrorCode(),
+                e.getErrorCode());
+            assertTrue(e.getNextException() == null);
+        }
+
+        try {
+            rs = conn.prepareStatement(NEXT_VAL_SQL).executeQuery();
+            rs.next();
+            fail();
+        } catch (SQLException e) {
+            assertEquals(SQLExceptionCode.SEQUENCE_VAL_REACHED_MAX_VALUE.getErrorCode(),
+                e.getErrorCode());
+            assertTrue(e.getNextException() == null);
+        }
+    }
+
+    @Test
+    public void testMultipleSequenceValuesCycle() throws Exception {
+        nextConnection();
+        conn.createStatement()
+                .execute(
+                    "CREATE SEQUENCE foo.bar START WITH 1 INCREMENT BY 2 MINVALUE 1 MAXVALUE 10 CYCLE CACHE 2");
+        conn.createStatement().execute("CREATE SEQUENCE foo.bar2");
+        nextConnection();
+        assertSequenceValuesMultipleSeq(1, 3);
+        assertSequenceValuesMultipleSeq(5, 7);
+        assertSequenceValuesMultipleSeq(9, 1);
+        assertSequenceValuesMultipleSeq(3, 5);
+        assertSequenceValuesMultipleSeq(7, 9);
+        assertSequenceValuesMultipleSeq(1, 3);
+        assertSequenceValuesMultipleSeq(5, 7);
+    }
+
+    @Test
+    public void testUpsertSelectGroupByWithSequence() throws Exception {
+        nextConnection();
+        conn.createStatement().execute("CREATE SEQUENCE foo.bar");
+        nextConnection();
+
+        conn.createStatement()
+                .execute(
+                    "CREATE TABLE EVENTS (event_id BIGINT NOT NULL PRIMARY KEY, user_id char(15), val BIGINT )");
+        conn.createStatement()
+                .execute(
+                    "CREATE TABLE METRICS (metric_id char(15) NOT NULL PRIMARY KEY, agg_id char(15), metric_val INTEGER )");
+
+        nextConnection();
+        // 2 rows for user1, 3 rows for user2 and 1 row for user3
+        insertEvent(1, "user1", 1);
+        insertEvent(2, "user2", 1);
+        insertEvent(3, "user1", 1);
+        insertEvent(4, "user2", 1);
+        insertEvent(5, "user2", 1);
+        insertEvent(6, "user3", 1);
+        conn.commit();
+        nextConnection();
+
+        conn.createStatement()
+                .execute(
+                    "UPSERT INTO METRICS SELECT 'METRIC_'||(LPAD(ENCODE(NEXT VALUE FOR foo.bar,'base62'),5,'0')), user_id, sum(val) FROM events GROUP BY user_id ORDER BY user_id");
+        conn.commit();
+        nextConnection();
+
+        PreparedStatement stmt =
+                conn.prepareStatement("SELECT metric_id, agg_id, metric_val FROM METRICS");
+        ResultSet rs = stmt.executeQuery();
+        assertTrue(rs.next());
+        assertEquals("METRIC_00001", rs.getString("metric_id"));
+        assertEquals("user1", rs.getString("agg_id"));
+        assertEquals(2, rs.getLong("metric_val"));
+        assertTrue(rs.next());
+        assertEquals("METRIC_00002", rs.getString("metric_id"));
+        assertEquals("user2", rs.getString("agg_id"));
+        assertEquals(3, rs.getLong("metric_val"));
+        assertTrue(rs.next());
+        assertEquals("METRIC_00003", rs.getString("metric_id"));
+        assertEquals("user3", rs.getString("agg_id"));
+        assertEquals(1, rs.getLong("metric_val"));
+        assertFalse(rs.next());
+    }
+
+    private void insertEvent(long id, String userId, long val) throws SQLException {
+        PreparedStatement stmt = conn.prepareStatement("UPSERT INTO events VALUES(?,?,?)");
+        stmt.setLong(1, id);
+        stmt.setString(2, userId);
+        stmt.setLong(3, val);
+        stmt.execute();
+    }
+
+    /**
+     * Helper to verify the sequence values returned in multiple ResultSets each containing one row
+     * @param seqVals expected sequence values (one per ResultSet)
+     */
+    private void assertSequenceValuesForSingleRow(long... seqVals)
+            throws SQLException {
+        PreparedStatement stmt = conn.prepareStatement(NEXT_VAL_SQL);
+        for (long seqVal : seqVals) {
+            ResultSet rs = stmt.executeQuery();
+            assertTrue(rs.next());
+            assertEquals(seqVal, rs.getLong(1));
+            assertFalse(rs.next());
+            rs.close();
+        }
+        stmt.close();
+    }
+
+    /**
+     * Helper to verify the sequence values returned in a single ResultSet containing multiple row
+     * @param seqVals expected sequence values (from one ResultSet)
+     */
+    private void assertSequenceValuesMultipleSeq(long... seqVals) throws SQLException {
+        PreparedStatement stmt = conn.prepareStatement(NEXT_VAL_SQL);
+        ResultSet rs = stmt.executeQuery();
+        for (long seqVal : seqVals) {
+            assertTrue(rs.next());
+            assertEquals(seqVal, rs.getLong(1));
+        }
+        assertFalse(rs.next());
+        rs.close();
+        stmt.close();
+    }
+
+    private void verifyExceptions(SQLException sqlE, List<String> expectedExceptions) {
+        List<String> missingExceptions = Lists.newArrayList(expectedExceptions);
+        List<String> unexpectedExceptions = Lists.newArrayList();
+        do {
+            if (!expectedExceptions.contains(sqlE.getMessage())) {
+                unexpectedExceptions.add(sqlE.getMessage());
+            }
+            missingExceptions.remove(sqlE.getMessage());
+        } while ((sqlE = sqlE.getNextException()) != null);
+        if (unexpectedExceptions.size() != 0 && missingExceptions.size() != 0) {
+            fail("Actual exceptions does not match expected exceptions. Unexpected exceptions : "
+                    + unexpectedExceptions + " missing exceptions : " + missingExceptions);
+        }
+    }
 }
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/phoenix/blob/15e1108f/phoenix-core/src/main/antlr3/PhoenixSQL.g
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/antlr3/PhoenixSQL.g b/phoenix-core/src/main/antlr3/PhoenixSQL.g
index c4e7f6b..4ba4c99 100644
--- a/phoenix-core/src/main/antlr3/PhoenixSQL.g
+++ b/phoenix-core/src/main/antlr3/PhoenixSQL.g
@@ -101,6 +101,9 @@ tokens
     DERIVE='derive';
     ANY='any';
     SOME='some';
+    MINVALUE='minvalue';
+    MAXVALUE='maxvalue';
+    CYCLE='cycle';
 }
 
 
@@ -138,6 +141,7 @@ import java.util.Collections;
 import java.util.Stack;
 import java.sql.SQLException;
 import org.apache.phoenix.expression.function.CountAggregateFunction;
+import org.apache.phoenix.jdbc.PhoenixDatabaseMetaData;
 import org.apache.phoenix.query.QueryConstants;
 import org.apache.phoenix.schema.SortOrder;
 import org.apache.phoenix.schema.IllegalDataException;
@@ -392,10 +396,13 @@ create_index_node returns [CreateIndexStatement ret]
 // Parse a create sequence statement.
 create_sequence_node returns [CreateSequenceStatement ret]
     :   CREATE SEQUENCE  (IF NOT ex=EXISTS)? t=from_table_name
-        (START WITH? s=int_literal_or_bind)?
-        (INCREMENT BY? i=int_literal_or_bind)?
+        (START WITH? s=value_expression)?
+        (INCREMENT BY? i=value_expression)?
+        (MINVALUE min=value_expression)?
+        (MAXVALUE max=value_expression)?
+        (cyc=CYCLE)? 
         (CACHE c=int_literal_or_bind)?
-    { $ret = factory.createSequence(t, s, i, c, ex!=null, getBindCount()); }
+    { $ret = factory.createSequence(t, s, i, c, min, max, cyc!=null, ex!=null, getBindCount()); }
     ;
 
 int_literal_or_bind returns [ParseNode ret]

http://git-wip-us.apache.org/repos/asf/phoenix/blob/15e1108f/phoenix-core/src/main/java/org/apache/phoenix/compile/CreateSequenceCompiler.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/CreateSequenceCompiler.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/CreateSequenceCompiler.java
index 94f13cb..d31a8f1 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/compile/CreateSequenceCompiler.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/CreateSequenceCompiler.java
@@ -32,12 +32,14 @@ import org.apache.phoenix.parse.BindParseNode;
 import org.apache.phoenix.parse.CreateSequenceStatement;
 import org.apache.phoenix.parse.ParseNode;
 import org.apache.phoenix.query.QueryServices;
+import org.apache.phoenix.parse.TableName;
 import org.apache.phoenix.query.QueryServicesOptions;
 import org.apache.phoenix.schema.MetaDataClient;
 import org.apache.phoenix.schema.PDataType;
 import org.apache.phoenix.schema.PDatum;
 import org.apache.phoenix.schema.SortOrder;
 
+import org.apache.phoenix.util.SequenceUtil;
 
 public class CreateSequenceCompiler {
     private final PhoenixStatement statement;
@@ -105,77 +107,134 @@ public class CreateSequenceCompiler {
     private static final PDatum LONG_DATUM = new LongDatum();
     private static final PDatum INTEGER_DATUM = new IntegerDatum();
 
+    private void validateNodeIsStateless(CreateSequenceStatement sequence, ParseNode node,
+            SQLExceptionCode code) throws SQLException {
+        if (!node.isStateless()) {
+            TableName sequenceName = sequence.getSequenceName();
+            throw SequenceUtil.getException(sequenceName.getSchemaName(), sequenceName.getTableName(), code);
+        }
+    }
+
+    private long evalExpression(CreateSequenceStatement sequence, StatementContext context,
+            Expression expression, SQLExceptionCode code) throws SQLException {
+        ImmutableBytesWritable ptr = context.getTempPtr();
+        expression.evaluate(null, ptr);
+        if (ptr.getLength() == 0 || !expression.getDataType().isCoercibleTo(PDataType.LONG)) {
+            TableName sequenceName = sequence.getSequenceName();
+            throw SequenceUtil.getException(sequenceName.getSchemaName(), sequenceName.getTableName(), code);
+        }
+        return (Long) PDataType.LONG.toObject(ptr, expression.getDataType());
+    }
+
     public MutationPlan compile(final CreateSequenceStatement sequence) throws SQLException {
         ParseNode startsWithNode = sequence.getStartWith();
         ParseNode incrementByNode = sequence.getIncrementBy();
-        if (!startsWithNode.isStateless()) {
-            throw new SQLExceptionInfo.Builder(SQLExceptionCode.STARTS_WITH_MUST_BE_CONSTANT)
-            .setSchemaName(sequence.getSequenceName().getSchemaName())
-            .setTableName(sequence.getSequenceName().getTableName()).build().buildException();
-        }
-        if (!incrementByNode.isStateless()) {
-            throw new SQLExceptionInfo.Builder(SQLExceptionCode.INCREMENT_BY_MUST_BE_CONSTANT)
-            .setSchemaName(sequence.getSequenceName().getSchemaName())
-            .setTableName(sequence.getSequenceName().getTableName()).build().buildException();
-        }
+        ParseNode maxValueNode = sequence.getMaxValue();
+        ParseNode minValueNode = sequence.getMinValue();
         ParseNode cacheNode = sequence.getCacheSize();
-        if (cacheNode != null && !cacheNode.isStateless()) {
-            throw new SQLExceptionInfo.Builder(SQLExceptionCode.CACHE_MUST_BE_NON_NEGATIVE_CONSTANT)
-            .setSchemaName(sequence.getSequenceName().getSchemaName())
-            .setTableName(sequence.getSequenceName().getTableName()).build().buildException();
+
+        // validate parse nodes
+        if (startsWithNode!=null) {
+            validateNodeIsStateless(sequence, startsWithNode,
+                SQLExceptionCode.START_WITH_MUST_BE_CONSTANT);
         }
-        
+        validateNodeIsStateless(sequence, incrementByNode,
+            SQLExceptionCode.INCREMENT_BY_MUST_BE_CONSTANT);
+        validateNodeIsStateless(sequence, maxValueNode, 
+            SQLExceptionCode.MAXVALUE_MUST_BE_CONSTANT);
+        validateNodeIsStateless(sequence, minValueNode, 
+            SQLExceptionCode.MINVALUE_MUST_BE_CONSTANT);
+        if (cacheNode != null) {
+            validateNodeIsStateless(sequence, cacheNode,
+                SQLExceptionCode.CACHE_MUST_BE_NON_NEGATIVE_CONSTANT);
+        }
+
         final PhoenixConnection connection = statement.getConnection();
-        
         final StatementContext context = new StatementContext(statement);
+        
+        // add param meta data if required
         if (startsWithNode instanceof BindParseNode) {
-            context.getBindManager().addParamMetaData((BindParseNode)startsWithNode, LONG_DATUM);
+            context.getBindManager().addParamMetaData((BindParseNode) startsWithNode, LONG_DATUM);
         }
         if (incrementByNode instanceof BindParseNode) {
-            context.getBindManager().addParamMetaData((BindParseNode)incrementByNode, LONG_DATUM);
+            context.getBindManager().addParamMetaData((BindParseNode) incrementByNode, LONG_DATUM);
+        }
+        if (maxValueNode instanceof BindParseNode) {
+            context.getBindManager().addParamMetaData((BindParseNode) maxValueNode, LONG_DATUM);
+        }
+        if (minValueNode instanceof BindParseNode) {
+            context.getBindManager().addParamMetaData((BindParseNode) minValueNode, LONG_DATUM);
         }
         if (cacheNode instanceof BindParseNode) {
-            context.getBindManager().addParamMetaData((BindParseNode)cacheNode, INTEGER_DATUM);
+            context.getBindManager().addParamMetaData((BindParseNode) cacheNode, INTEGER_DATUM);
         }
-        ExpressionCompiler expressionCompiler = new ExpressionCompiler(context);
-        Expression startsWithExpr = startsWithNode.accept(expressionCompiler);
-        ImmutableBytesWritable ptr = context.getTempPtr();
-        startsWithExpr.evaluate(null, ptr);
-        if (ptr.getLength() == 0 || !startsWithExpr.getDataType().isCoercibleTo(PDataType.LONG)) {
-            throw new SQLExceptionInfo.Builder(SQLExceptionCode.STARTS_WITH_MUST_BE_CONSTANT)
-            .setSchemaName(sequence.getSequenceName().getSchemaName())
-            .setTableName(sequence.getSequenceName().getTableName()).build().buildException();
-        }
-        final long startsWith = (Long)PDataType.LONG.toObject(ptr, startsWithExpr.getDataType());
-
-        Expression incrementByExpr = incrementByNode.accept(expressionCompiler);
-        incrementByExpr.evaluate(null, ptr);
-        if (ptr.getLength() == 0 || !incrementByExpr.getDataType().isCoercibleTo(PDataType.LONG)) {
-            throw new SQLExceptionInfo.Builder(SQLExceptionCode.INCREMENT_BY_MUST_BE_CONSTANT)
-            .setSchemaName(sequence.getSequenceName().getSchemaName())
-            .setTableName(sequence.getSequenceName().getTableName()).build().buildException();
-        }
-        final long incrementBy = (Long)PDataType.LONG.toObject(ptr, incrementByExpr.getDataType());
         
-        long cacheSizeValue = connection.getQueryServices().getProps().getLong(QueryServices.SEQUENCE_CACHE_SIZE_ATTRIB,QueryServicesOptions.DEFAULT_SEQUENCE_CACHE_SIZE);
-        if (cacheNode != null) {
-            Expression cacheSizeExpr = cacheNode.accept(expressionCompiler);
-            cacheSizeExpr.evaluate(null, ptr);
-            if (ptr.getLength() != 0 && (!cacheSizeExpr.getDataType().isCoercibleTo(PDataType.LONG) || (cacheSizeValue = (Long)PDataType.LONG.toObject(ptr, cacheSizeExpr.getDataType())) < 0)) {
-                throw new SQLExceptionInfo.Builder(SQLExceptionCode.CACHE_MUST_BE_NON_NEGATIVE_CONSTANT)
-                .setSchemaName(sequence.getSequenceName().getSchemaName())
-                .setTableName(sequence.getSequenceName().getTableName()).build().buildException();
+        ExpressionCompiler expressionCompiler = new ExpressionCompiler(context);        
+        final long incrementBy =
+                evalExpression(sequence, context, incrementByNode.accept(expressionCompiler),
+                    SQLExceptionCode.INCREMENT_BY_MUST_BE_CONSTANT);
+        if (incrementBy == 0) {
+            throw SequenceUtil.getException(sequence.getSequenceName().getSchemaName(), sequence
+                    .getSequenceName().getTableName(),
+                SQLExceptionCode.INCREMENT_BY_MUST_NOT_BE_ZERO);
+        }
+        final long maxValue =
+                evalExpression(sequence, context, maxValueNode.accept(expressionCompiler),
+                    SQLExceptionCode.MAXVALUE_MUST_BE_CONSTANT);
+        final long minValue =
+                evalExpression(sequence, context, minValueNode.accept(expressionCompiler),
+                    SQLExceptionCode.MINVALUE_MUST_BE_CONSTANT);
+        if (minValue>maxValue) {
+            TableName sequenceName = sequence.getSequenceName();
+            throw SequenceUtil.getException(sequenceName.getSchemaName(),
+                sequenceName.getTableName(),
+                SQLExceptionCode.MINVALUE_MUST_BE_LESS_THAN_OR_EQUAL_TO_MAXVALUE);
+        }
+        
+        long startsWithValue;
+        if (startsWithNode == null) {
+            startsWithValue = incrementBy > 0 ? minValue : maxValue;
+        } else {
+            startsWithValue =
+                    evalExpression(sequence, context, startsWithNode.accept(expressionCompiler),
+                        SQLExceptionCode.START_WITH_MUST_BE_CONSTANT);
+            if (startsWithValue < minValue || startsWithValue > maxValue) {
+                TableName sequenceName = sequence.getSequenceName();
+                throw SequenceUtil.getException(sequenceName.getSchemaName(),
+                    sequenceName.getTableName(),
+                    SQLExceptionCode.STARTS_WITH_MUST_BE_BETWEEN_MIN_MAX_VALUE);
+            }
+        }
+        final long startsWith = startsWithValue;
+
+        long cacheSizeValue;
+        if (cacheNode == null) {
+            cacheSizeValue =
+                    connection
+                            .getQueryServices()
+                            .getProps()
+                            .getLong(QueryServices.SEQUENCE_CACHE_SIZE_ATTRIB,
+                                QueryServicesOptions.DEFAULT_SEQUENCE_CACHE_SIZE);
+        }
+        else {
+            cacheSizeValue = 
+                    evalExpression(sequence, context, cacheNode.accept(expressionCompiler),
+                        SQLExceptionCode.CACHE_MUST_BE_NON_NEGATIVE_CONSTANT);
+            if (cacheSizeValue < 0) {
+                TableName sequenceName = sequence.getSequenceName();
+                throw SequenceUtil.getException(sequenceName.getSchemaName(),
+                    sequenceName.getTableName(),
+                    SQLExceptionCode.CACHE_MUST_BE_NON_NEGATIVE_CONSTANT);
             }
         }
         final long cacheSize = Math.max(1L, cacheSizeValue);
-        
 
-        final MetaDataClient client = new MetaDataClient(connection);        
-        return new MutationPlan() {           
+        final MetaDataClient client = new MetaDataClient(connection);
+        return new MutationPlan() {
 
             @Override
             public MutationState execute() throws SQLException {
-                return client.createSequence(sequence, startsWith, incrementBy, cacheSize);
+                return client.createSequence(sequence, startsWith, incrementBy, cacheSize, minValue, maxValue);
             }
 
             @Override
@@ -189,7 +248,7 @@ public class CreateSequenceCompiler {
             }
 
             @Override
-            public ParameterMetaData getParameterMetaData() {                
+            public ParameterMetaData getParameterMetaData() {
                 return context.getBindManager().getParameterMetaData();
             }
 

http://git-wip-us.apache.org/repos/asf/phoenix/blob/15e1108f/phoenix-core/src/main/java/org/apache/phoenix/coprocessor/SequenceRegionObserver.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/coprocessor/SequenceRegionObserver.java b/phoenix-core/src/main/java/org/apache/phoenix/coprocessor/SequenceRegionObserver.java
index 875bb0c..cc3c0b4 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/coprocessor/SequenceRegionObserver.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/coprocessor/SequenceRegionObserver.java
@@ -19,6 +19,7 @@
 package org.apache.phoenix.coprocessor;
 
 import java.io.IOException;
+import java.sql.SQLException;
 import java.util.Collections;
 import java.util.List;
 import java.util.Map;
@@ -50,6 +51,7 @@ import org.apache.phoenix.schema.SortOrder;
 import org.apache.phoenix.util.ByteUtil;
 import org.apache.phoenix.util.KeyValueUtil;
 import org.apache.phoenix.util.MetaDataUtil;
+import org.apache.phoenix.util.SequenceUtil;
 import org.apache.phoenix.util.ServerUtil;
 
 /**
@@ -81,7 +83,6 @@ public class SequenceRegionObserver extends BaseRegionObserver {
                         QueryConstants.EMPTY_COLUMN_BYTES, timestamp, errorCodeBuf)));
     }
     /**
-     * 
      * Use PreIncrement hook of BaseRegionObserver to overcome deficiencies in Increment
      * implementation (HBASE-10254):
      * 1) Lack of recognition and identification of when the key value to increment doesn't exist
@@ -123,25 +124,112 @@ public class SequenceRegionObserver extends BaseRegionObserver {
                 if (validateOnly) {
                     return result;
                 }
-                KeyValue currentValueKV = Sequence.getCurrentValueKV(result);
+                
                 KeyValue incrementByKV = Sequence.getIncrementByKV(result);
-                KeyValue cacheSizeKV = Sequence.getCacheSizeKV(result);
-                long value = PDataType.LONG.getCodec().decodeLong(currentValueKV.getBuffer(), currentValueKV.getValueOffset(), SortOrder.getDefault());
-                long incrementBy = PDataType.LONG.getCodec().decodeLong(incrementByKV.getBuffer(), incrementByKV.getValueOffset(), SortOrder.getDefault());
-                long cacheSize = PDataType.LONG.getCodec().decodeLong(cacheSizeKV.getBuffer(), cacheSizeKV.getValueOffset(), SortOrder.getDefault());
-                value += incrementBy * cacheSize;
-                byte[] valueBuffer = new byte[PDataType.LONG.getByteSize()];
-                PDataType.LONG.getCodec().encodeLong(value, valueBuffer, 0);
+                KeyValue currentValueKV = Sequence.getCurrentValueKV(result);               
+                KeyValue cacheSizeKV = Sequence.getCacheSizeKV(result);        
+                KeyValue cycleKV = Sequence.getCycleKV(result);
+                KeyValue minValueKV = Sequence.getMinValueKV(result);
+                KeyValue maxValueKV = Sequence.getMaxValueKV(result);
+                
+                // Hold timestamp constant for sequences, so that clients always only see the latest
+                // value regardless of when they connect.
                 Put put = new Put(row, currentValueKV.getTimestamp());
-                // Hold timestamp constant for sequences, so that clients always only see the latest value
-                // regardless of when they connect.
-                KeyValue newCurrentValueKV = KeyValueUtil.newKeyValue(row, currentValueKV.getFamily(), currentValueKV.getQualifier(), currentValueKV.getTimestamp(), valueBuffer);
+                
+                // create a copy of the key values, used for the new Return
+                List<KeyValue> newkvs = Sequence.getCells(result);
+                
+                long incrementBy =
+                        PDataType.LONG.getCodec().decodeLong(incrementByKV.getBuffer(),
+                            incrementByKV.getValueOffset(), SortOrder.getDefault());
+                     
+                long cacheSize =
+                        PDataType.LONG.getCodec().decodeLong(cacheSizeKV.getBuffer(),
+                            cacheSizeKV.getValueOffset(), SortOrder.getDefault());
+                
+                // if the minValue, maxValue, or cycle is null this sequence has been upgraded from
+                // a lower version. Set minValue, maxValue and cycle to Long.MIN_VALUE, Long.MAX_VALUE and true 
+                // respectively in order to maintain existing behavior and also update the KeyValues on the server 
+                long minValue;
+                if (minValueKV == null) {
+                    minValue = Long.MIN_VALUE;
+                    // create new key value for put
+                    byte[] newMinValueBuffer = new byte[PDataType.LONG.getByteSize()];
+                    PDataType.LONG.getCodec().encodeLong(minValue, newMinValueBuffer, 0);
+                    KeyValue newMinValueKV = KeyValueUtil.newKeyValue(row, PhoenixDatabaseMetaData.SEQUENCE_FAMILY_BYTES,
+                                PhoenixDatabaseMetaData.MIN_VALUE_BYTES, currentValueKV.getTimestamp(), newMinValueBuffer);
+                    put.add(newMinValueKV);
+                    // update key value in returned Result
+                    Sequence.replaceMinValueKV(newkvs, newMinValueKV);
+                }
+                else {
+                    minValue = PDataType.LONG.getCodec().decodeLong(minValueKV.getBuffer(),
+                                minValueKV.getValueOffset(), SortOrder.getDefault());
+                }           
+                long maxValue;
+                if (maxValueKV == null) {
+                    maxValue = Long.MAX_VALUE;
+                    // create new key value for put
+                    byte[] newMaxValueBuffer = new byte[PDataType.LONG.getByteSize()];
+                    PDataType.LONG.getCodec().encodeLong(maxValue, newMaxValueBuffer, 0);
+                    KeyValue newMaxValueKV = KeyValueUtil.newKeyValue(row, PhoenixDatabaseMetaData.SEQUENCE_FAMILY_BYTES,
+                        PhoenixDatabaseMetaData.MAX_VALUE_BYTES, currentValueKV.getTimestamp(), newMaxValueBuffer);
+                    put.add(newMaxValueKV);
+                    // update key value in returned Result
+                    Sequence.replaceMaxValueKV(newkvs, newMaxValueKV);
+                }
+                else {
+                    maxValue =  PDataType.LONG.getCodec().decodeLong(maxValueKV.getBuffer(),
+                            maxValueKV.getValueOffset(), SortOrder.getDefault());
+                }
+                boolean cycle;
+                if (cycleKV == null) {
+                    cycle = false;
+                    // create new key value for put
+                    KeyValue newCycleKV = KeyValueUtil.newKeyValue(row, PhoenixDatabaseMetaData.SEQUENCE_FAMILY_BYTES,
+                        PhoenixDatabaseMetaData.CYCLE_FLAG_BYTES, currentValueKV.getTimestamp(), PDataType.FALSE_BYTES);
+                    put.add(newCycleKV);
+                    // update key value in returned Result
+                    Sequence.replaceCycleValueKV(newkvs, newCycleKV);
+                }
+                else {
+                    cycle = (Boolean) PDataType.BOOLEAN.toObject(cycleKV.getBuffer(),
+                            cycleKV.getValueOffset(), cycleKV.getValueLength());
+                }
+                long currentValue;
+                // initialize current value to start value
+                if (currentValueKV.getValueLength()==0) {
+                    KeyValue startValueKV = Sequence.getStartValueKV(result);
+                    currentValue =
+                            PDataType.LONG.getCodec().decodeLong(startValueKV.getBuffer(),
+                                startValueKV.getValueOffset(), SortOrder.getDefault());
+                }
+                else {
+                    currentValue =
+                            PDataType.LONG.getCodec().decodeLong(currentValueKV.getBuffer(),
+                                currentValueKV.getValueOffset(), SortOrder.getDefault());      
+                    try {
+                        // set currentValue to nextValue
+                        currentValue =
+                                SequenceUtil.getNextValue(currentValue, minValue, maxValue,
+                                    incrementBy, cacheSize, cycle);
+                    } catch (SQLException sqlE) {
+                        return getErrorResult(row, maxTimestamp, sqlE.getErrorCode());
+                    }
+                }
+                byte[] newCurrentValueBuffer = new byte[PDataType.LONG.getByteSize()];
+                PDataType.LONG.getCodec().encodeLong(currentValue, newCurrentValueBuffer, 0);
+                KeyValue newCurrentValueKV = KeyValueUtil.newKeyValue(row, currentValueKV, newCurrentValueBuffer);
                 put.add(newCurrentValueKV);
+                Sequence.replaceCurrentValueKV(newkvs, newCurrentValueKV);
+                
+                // update the KeyValues on the server
                 @SuppressWarnings("unchecked")
                 Pair<Mutation,Integer>[] mutations = new Pair[1];
                 mutations[0] = new Pair<Mutation,Integer>(put, lid);
                 region.batchMutate(mutations);
-                return Sequence.replaceCurrentValueKV(result, newCurrentValueKV);
+                // return a Result with the updated KeyValues
+                return new Result(newkvs);
             } finally {
                 region.releaseRowLock(lid);
             }

http://git-wip-us.apache.org/repos/asf/phoenix/blob/15e1108f/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java b/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java
index 39b951d..d6ff6d5 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java
@@ -226,13 +226,21 @@ public enum SQLExceptionCode {
             return new SequenceNotFoundException(info.getSchemaName(), info.getTableName());
         }
     }),
-    STARTS_WITH_MUST_BE_CONSTANT(1202, "42Z02", "Sequence STARTS WITH value must be an integer or long constant."),
+    START_WITH_MUST_BE_CONSTANT(1202, "42Z02", "Sequence START WITH value must be an integer or long constant."),
     INCREMENT_BY_MUST_BE_CONSTANT(1203, "42Z03", "Sequence INCREMENT BY value must be an integer or long constant."),
     CACHE_MUST_BE_NON_NEGATIVE_CONSTANT(1204, "42Z04", "Sequence CACHE value must be a non negative integer constant."),
     INVALID_USE_OF_NEXT_VALUE_FOR(1205, "42Z05", "NEXT VALUE FOR may only be used as in a SELECT or an UPSERT VALUES expression."),
     CANNOT_CALL_CURRENT_BEFORE_NEXT_VALUE(1206, "42Z06", "NEXT VALUE FOR must be called before CURRENT VALUE FOR is called."),
     EMPTY_SEQUENCE_CACHE(1207, "42Z07", "No more cached sequence values"),
-
+    MINVALUE_MUST_BE_CONSTANT(1208, "42Z08", "Sequence MINVALUE must be an integer or long constant."),
+    MAXVALUE_MUST_BE_CONSTANT(1209, "42Z09", "Sequence MAXVALUE must be an integer or long constant."),
+    MINVALUE_MUST_BE_LESS_THAN_OR_EQUAL_TO_MAXVALUE(1210, "42Z10", "Sequence MINVALUE must be less than or equal to MAXVALUE."),
+    STARTS_WITH_MUST_BE_BETWEEN_MIN_MAX_VALUE(1211, "42Z11",
+            "STARTS WITH value must be greater than or equal to MINVALUE and less than or equal to MAXVALUE"),
+    SEQUENCE_VAL_REACHED_MAX_VALUE(1212, "42Z12", "Reached MAXVALUE of sequence"),
+    SEQUENCE_VAL_REACHED_MIN_VALUE(1213, "42Z13", "Reached MINVALUE of sequence"),
+    INCREMENT_BY_MUST_NOT_BE_ZERO(1214, "42Z14", "Sequence INCREMENT BY value cannot be zero"),
+                    
     /** Parser error. (errorcode 06, sqlState 42P) */
     PARSER_ERROR(601, "42P00", "Syntax error.", Factory.SYTAX_ERROR),
     MISSING_TOKEN(602, "42P00", "Syntax error.", Factory.SYTAX_ERROR),

http://git-wip-us.apache.org/repos/asf/phoenix/blob/15e1108f/phoenix-core/src/main/java/org/apache/phoenix/jdbc/PhoenixDatabaseMetaData.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/jdbc/PhoenixDatabaseMetaData.java b/phoenix-core/src/main/java/org/apache/phoenix/jdbc/PhoenixDatabaseMetaData.java
index a41935f..27a53e4 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/jdbc/PhoenixDatabaseMetaData.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/jdbc/PhoenixDatabaseMetaData.java
@@ -198,10 +198,17 @@ public class PhoenixDatabaseMetaData implements DatabaseMetaData, org.apache.pho
     public static final byte[] CURRENT_VALUE_BYTES = Bytes.toBytes(CURRENT_VALUE);
     public static final String START_WITH = "START_WITH";
     public static final byte[] START_WITH_BYTES = Bytes.toBytes(START_WITH);
+    // MIN_VALUE, MAX_VALUE and CYCLE_FLAG were added in 3.0
+    public static final String MIN_VALUE = "MIN_VALUE";
+    public static final byte[] MIN_VALUE_BYTES = Bytes.toBytes(MIN_VALUE);
+    public static final String MAX_VALUE = "MAX_VALUE";
+    public static final byte[] MAX_VALUE_BYTES = Bytes.toBytes(MAX_VALUE);
     public static final String INCREMENT_BY = "INCREMENT_BY";
     public static final byte[] INCREMENT_BY_BYTES = Bytes.toBytes(INCREMENT_BY);
     public static final String CACHE_SIZE = "CACHE_SIZE";
     public static final byte[] CACHE_SIZE_BYTES = Bytes.toBytes(CACHE_SIZE);
+    public static final String CYCLE_FLAG = "CYCLE_FLAG";
+    public static final byte[] CYCLE_FLAG_BYTES = Bytes.toBytes(CYCLE_FLAG);
     public static final String KEY_SEQ = "KEY_SEQ";
     public static final byte[] KEY_SEQ_BYTES = Bytes.toBytes(KEY_SEQ);
     public static final String SUPERTABLE_NAME = "SUPERTABLE_NAME";

http://git-wip-us.apache.org/repos/asf/phoenix/blob/15e1108f/phoenix-core/src/main/java/org/apache/phoenix/jdbc/PhoenixStatement.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/jdbc/PhoenixStatement.java b/phoenix-core/src/main/java/org/apache/phoenix/jdbc/PhoenixStatement.java
index d4c677b..4b63cfe 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/jdbc/PhoenixStatement.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/jdbc/PhoenixStatement.java
@@ -487,9 +487,12 @@ public class PhoenixStatement implements Statement, SQLCloseable, org.apache.pho
     
     private static class ExecutableCreateSequenceStatement extends	CreateSequenceStatement implements CompilableStatement {
 
-		public ExecutableCreateSequenceStatement(TableName sequenceName, ParseNode startWith, ParseNode incrementBy, ParseNode cacheSize, boolean ifNotExists, int bindCount) {
-			super(sequenceName, startWith, incrementBy, cacheSize, ifNotExists, bindCount);
-		}
+        public ExecutableCreateSequenceStatement(TableName sequenceName, ParseNode startWith,
+                ParseNode incrementBy, ParseNode cacheSize, ParseNode minValue, ParseNode maxValue,
+                boolean cycle, boolean ifNotExists, int bindCount) {
+            super(sequenceName, startWith, incrementBy, cacheSize, minValue, maxValue, cycle,
+                    ifNotExists, bindCount);
+        }
 
 		@SuppressWarnings("unchecked")
         @Override
@@ -744,8 +747,11 @@ public class PhoenixStatement implements Statement, SQLCloseable, org.apache.pho
         }
         
         @Override
-        public CreateSequenceStatement createSequence(TableName tableName, ParseNode startsWith, ParseNode incrementBy, ParseNode cacheSize, boolean ifNotExists, int bindCount){
-        	return new ExecutableCreateSequenceStatement(tableName, startsWith, incrementBy, cacheSize, ifNotExists, bindCount);
+        public CreateSequenceStatement createSequence(TableName tableName, ParseNode startsWith,
+                ParseNode incrementBy, ParseNode cacheSize, ParseNode minValue, ParseNode maxValue,
+                boolean cycle, boolean ifNotExists, int bindCount) {
+            return new ExecutableCreateSequenceStatement(tableName, startsWith, incrementBy,
+                    cacheSize, minValue, maxValue, cycle, ifNotExists, bindCount);
         }
         
         @Override

http://git-wip-us.apache.org/repos/asf/phoenix/blob/15e1108f/phoenix-core/src/main/java/org/apache/phoenix/parse/CreateSequenceStatement.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/parse/CreateSequenceStatement.java b/phoenix-core/src/main/java/org/apache/phoenix/parse/CreateSequenceStatement.java
index 44d5f34..2e0c943 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/parse/CreateSequenceStatement.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/parse/CreateSequenceStatement.java
@@ -20,24 +20,37 @@ package org.apache.phoenix.parse;
 public class CreateSequenceStatement extends MutableStatement {
 
     public static CreateSequenceStatement create(TableName sequenceName) {
-        return new CreateSequenceStatement(sequenceName, null, null, null, true, 0);
+        return new CreateSequenceStatement(sequenceName, null, null, null, null, null, false, true,
+                0);
     }
     
 	private final TableName sequenceName;
 	private final ParseNode startWith;
 	private final ParseNode incrementBy;
     private final ParseNode cacheSize;
+    private final ParseNode minValue;
+    private final ParseNode maxValue;
+    private final boolean cycle;
     private final boolean ifNotExists;
 	private final int bindCount;
 
-	protected CreateSequenceStatement(TableName sequenceName, ParseNode startsWith, ParseNode incrementBy, ParseNode cacheSize, boolean ifNotExists, int bindCount) {
-		this.sequenceName = sequenceName;
-		this.startWith = startsWith == null ? LiteralParseNode.ONE : startsWith;
-		this.incrementBy = incrementBy == null ? LiteralParseNode.ONE : incrementBy;
-        this.cacheSize = cacheSize == null ? null : cacheSize;
-		this.ifNotExists = ifNotExists;
-		this.bindCount = bindCount;
-	}
+    protected CreateSequenceStatement(TableName sequenceName, ParseNode startWith,
+            ParseNode incrementBy, ParseNode cacheSize, ParseNode minValue, ParseNode maxValue,
+            boolean cycle, boolean ifNotExists, int bindCount) {
+        this.sequenceName = sequenceName;
+        // if MINVALUE, MAXVALUE and START WITH are not specified, set START WITH to 1 in order to
+        // maintain backward compatibility
+        this.startWith =
+                (minValue == null && maxValue == null && startWith == null) ? LiteralParseNode.ONE
+                        : startWith;
+        this.minValue = minValue == null ? new LiteralParseNode(Long.MIN_VALUE) : minValue;
+        this.maxValue = maxValue == null ? new LiteralParseNode(Long.MAX_VALUE) : maxValue;
+        this.incrementBy = incrementBy == null ? LiteralParseNode.ONE : incrementBy;
+        this.cacheSize = cacheSize;
+        this.cycle = cycle;
+        this.ifNotExists = ifNotExists;
+        this.bindCount = bindCount;
+    }
 
 	@Override
 	public int getBindCount() {
@@ -56,7 +69,19 @@ public class CreateSequenceStatement extends MutableStatement {
         return cacheSize;
     }
 
-	public ParseNode getStartWith() {
+	public ParseNode getMinValue() {
+        return minValue;
+    }
+
+    public ParseNode getMaxValue() {
+        return maxValue;
+    }
+
+    public boolean getCycle() {
+        return cycle;
+    }
+
+    public ParseNode getStartWith() {
 		return startWith;
 	}
 

http://git-wip-us.apache.org/repos/asf/phoenix/blob/15e1108f/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeFactory.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeFactory.java b/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeFactory.java
index 4b27696..088902e 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeFactory.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeFactory.java
@@ -272,9 +272,12 @@ public class ParseNodeFactory {
         return new CreateIndexStatement(indexName, dataTable, pkConstraint, includeColumns, splits, props, ifNotExists, bindCount);
     }
     
-    public CreateSequenceStatement createSequence(TableName tableName, ParseNode startsWith, ParseNode incrementBy, ParseNode cacheSize, boolean ifNotExits, int bindCount){
-    	return new CreateSequenceStatement(tableName, startsWith, incrementBy, cacheSize, ifNotExits, bindCount);
-    } 
+    public CreateSequenceStatement createSequence(TableName tableName, ParseNode startsWith,
+            ParseNode incrementBy, ParseNode cacheSize, ParseNode minValue, ParseNode maxValue,
+            boolean cycle, boolean ifNotExits, int bindCount) {
+        return new CreateSequenceStatement(tableName, startsWith, incrementBy, cacheSize, minValue,
+                maxValue, cycle, ifNotExits, bindCount);
+    }
     
     public DropSequenceStatement dropSequence(TableName tableName, boolean ifExits, int bindCount){
         return new DropSequenceStatement(tableName, ifExits, bindCount);

http://git-wip-us.apache.org/repos/asf/phoenix/blob/15e1108f/phoenix-core/src/main/java/org/apache/phoenix/parse/SelectStatement.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/parse/SelectStatement.java b/phoenix-core/src/main/java/org/apache/phoenix/parse/SelectStatement.java
index 055bc18..8270710 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/parse/SelectStatement.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/parse/SelectStatement.java
@@ -37,7 +37,7 @@ public class SelectStatement implements FilterableStatement {
     public static final SelectStatement SELECT_ONE =
             new SelectStatement(
                     Collections.<TableNode>emptyList(), null, false, 
-                    Collections.<AliasedNode>singletonList(new AliasedNode(null,new LiteralParseNode(1))),
+                    Collections.<AliasedNode>singletonList(new AliasedNode(null, LiteralParseNode.ONE)),
                     null, Collections.<ParseNode>emptyList(),
                     null, Collections.<OrderByNode>emptyList(),
                     null, 0, false);

http://git-wip-us.apache.org/repos/asf/phoenix/blob/15e1108f/phoenix-core/src/main/java/org/apache/phoenix/query/ConnectionQueryServices.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/query/ConnectionQueryServices.java b/phoenix-core/src/main/java/org/apache/phoenix/query/ConnectionQueryServices.java
index 035c77b..f085464 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/query/ConnectionQueryServices.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/query/ConnectionQueryServices.java
@@ -86,7 +86,7 @@ public interface ConnectionQueryServices extends QueryServices, MetaDataMutated
 
     boolean hasInvalidIndexConfiguration();
     
-    long createSequence(String tenantId, String schemaName, String sequenceName, long startWith, long incrementBy, long cacheSize, long timestamp) throws SQLException;
+    long createSequence(String tenantId, String schemaName, String sequenceName, long startWith, long incrementBy, long cacheSize, long minValue, long maxValue, boolean cycle, long timestamp) throws SQLException;
     long dropSequence(String tenantId, String schemaName, String sequenceName, long timestamp) throws SQLException;
     void validateSequences(List<SequenceKey> sequenceKeys, long timestamp, long[] values, SQLException[] exceptions, Sequence.ValueOp action) throws SQLException;
     void incrementSequences(List<SequenceKey> sequenceKeys, long timestamp, long[] values, SQLException[] exceptions) throws SQLException;

http://git-wip-us.apache.org/repos/asf/phoenix/blob/15e1108f/phoenix-core/src/main/java/org/apache/phoenix/query/ConnectionQueryServicesImpl.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/query/ConnectionQueryServicesImpl.java b/phoenix-core/src/main/java/org/apache/phoenix/query/ConnectionQueryServicesImpl.java
index 21b998b..d02859b 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/query/ConnectionQueryServicesImpl.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/query/ConnectionQueryServicesImpl.java
@@ -22,6 +22,7 @@ import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.COLUMN_COUNT;
 import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.COLUMN_FAMILY;
 import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.COLUMN_NAME;
 import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.COLUMN_SIZE;
+import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.CYCLE_FLAG;
 import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.DATA_TABLE_NAME;
 import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.DATA_TABLE_NAME_BYTES;
 import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.DATA_TYPE;
@@ -31,6 +32,8 @@ import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.IMMUTABLE_ROWS;
 import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.INDEX_STATE;
 import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.KEY_SEQ;
 import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.LINK_TYPE;
+import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.MAX_VALUE;
+import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.MIN_VALUE;
 import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.NULLABLE;
 import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.ORDINAL_POSITION;
 import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.PK_NAME;
@@ -189,7 +192,7 @@ public class ConnectionQueryServicesImpl extends DelegateQueryServices implement
     private volatile boolean initialized;
     private volatile boolean closed;
     private volatile SQLException initializationException;
-    private ConcurrentMap<SequenceKey,Sequence> sequenceMap = Maps.newConcurrentMap();
+    protected ConcurrentMap<SequenceKey,Sequence> sequenceMap = Maps.newConcurrentMap();
     private KeyValueBuilder kvBuilder;
 
     private PMetaData newEmptyMetaData() {
@@ -1370,7 +1373,7 @@ public class ConnectionQueryServicesImpl extends DelegateQueryServices implement
                             try {
                                 metaConnection.createStatement().executeUpdate(QueryConstants.CREATE_TABLE_METADATA);
                             } catch (NewerTableAlreadyExistsException ignore) {
-                                // Ignore, as this will happen if the SYSTEM.TABLE already exists at this fixed timestamp.
+                                // Ignore, as this will happen if the SYSTEM.CATALOG already exists at this fixed timestamp.
                                 // A TableAlreadyExistsException is not thrown, since the table only exists *after* this fixed timestamp.
                             } catch (TableAlreadyExistsException ignore) {
                                 // This will occur if we have an older SYSTEM.CATALOG and we need to update it to include
@@ -1382,6 +1385,15 @@ public class ConnectionQueryServicesImpl extends DelegateQueryServices implement
                             } catch (NewerTableAlreadyExistsException ignore) {
                                 // Ignore, as this will happen if the SYSTEM.SEQUENCE already exists at this fixed timestamp.
                                 // A TableAlreadyExistsException is not thrown, since the table only exists *after* this fixed timestamp.
+                            } catch (TableAlreadyExistsException ignore) {
+                                // This will occur if we have an older SYSTEM.SEQUENCE, so we need to update it to include
+                                // any new columns we've added.
+                                String newColumns =
+                                        MIN_VALUE + " " + PDataType.LONG.getSqlTypeName() + ", " 
+                                                + MAX_VALUE + " " + PDataType.LONG.getSqlTypeName() + ", " 
+                                                + CYCLE_FLAG + " " + PDataType.BOOLEAN.getSqlTypeName();
+                                metaConnection = addColumnsIfNotExists(metaConnection, PhoenixDatabaseMetaData.SEQUENCE_TABLE_NAME, 
+                                    MetaDataProtocol.MIN_SYSTEM_TABLE_TIMESTAMP, newColumns); 
                             }
                             upgradeMetaDataTo3_0(url, props);
                         } catch (SQLException e) {
@@ -1510,8 +1522,9 @@ public class ConnectionQueryServicesImpl extends DelegateQueryServices implement
     }
 
     @Override
-    public long createSequence(String tenantId, String schemaName, String sequenceName, long startWith, long incrementBy, long cacheSize, long timestamp) 
-            throws SQLException {
+    public long createSequence(String tenantId, String schemaName, String sequenceName,
+            long startWith, long incrementBy, long cacheSize, long minValue, long maxValue,
+            boolean cycle, long timestamp) throws SQLException {
         SequenceKey sequenceKey = new SequenceKey(tenantId, schemaName, sequenceName);
         Sequence newSequences = new Sequence(sequenceKey);
         Sequence sequence = sequenceMap.putIfAbsent(sequenceKey, newSequences);
@@ -1521,11 +1534,12 @@ public class ConnectionQueryServicesImpl extends DelegateQueryServices implement
         try {
             sequence.getLock().lock();
             // Now that we have the lock we need, create the sequence
-            Append append = sequence.createSequence(startWith, incrementBy, cacheSize, timestamp);
-            HTableInterface htable = this.getTable(PhoenixDatabaseMetaData.SEQUENCE_TABLE_NAME_BYTES);
+            Append append = sequence.createSequence(startWith, incrementBy, cacheSize, timestamp, minValue, maxValue, cycle);
+            HTableInterface htable =
+                    this.getTable(PhoenixDatabaseMetaData.SEQUENCE_TABLE_NAME_BYTES);
             try {
                 Result result = htable.append(append);
-                return sequence.createSequence(result);
+                return sequence.createSequence(result, minValue, maxValue, cycle);
             } catch (IOException e) {
                 throw ServerUtil.parseServerException(e);
             } finally {
@@ -1639,6 +1653,8 @@ public class ConnectionQueryServicesImpl extends DelegateQueryServices implement
                     toIncrementList.add(sequence);
                     Increment inc = sequence.newIncrement(timestamp, action);
                     incrementBatch.add(inc);
+                } catch (SQLException e) {
+                    exceptions[i] = e;
                 }
             }
             if (toIncrementList.isEmpty()) {
@@ -1757,6 +1773,7 @@ public class ConnectionQueryServicesImpl extends DelegateQueryServices implement
 
     // Take no locks, as this only gets run when there are no open connections
     // so there's no danger of contention.
+    @SuppressWarnings("deprecation")
     private void returnAllSequences(ConcurrentMap<SequenceKey,Sequence> sequenceMap) throws SQLException {
         List<Append> mutations = Lists.newArrayListWithExpectedSize(sequenceMap.size());
         for (Sequence sequence : sequenceMap.values()) {

http://git-wip-us.apache.org/repos/asf/phoenix/blob/15e1108f/phoenix-core/src/main/java/org/apache/phoenix/query/ConnectionlessQueryServicesImpl.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/query/ConnectionlessQueryServicesImpl.java b/phoenix-core/src/main/java/org/apache/phoenix/query/ConnectionlessQueryServicesImpl.java
index d264d38..a48f811 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/query/ConnectionlessQueryServicesImpl.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/query/ConnectionlessQueryServicesImpl.java
@@ -59,6 +59,7 @@ import org.apache.phoenix.schema.PTableKey;
 import org.apache.phoenix.schema.PTableType;
 import org.apache.phoenix.schema.Sequence;
 import org.apache.phoenix.schema.SequenceAlreadyExistsException;
+import org.apache.phoenix.schema.SequenceInfo;
 import org.apache.phoenix.schema.SequenceKey;
 import org.apache.phoenix.schema.SequenceNotFoundException;
 import org.apache.phoenix.schema.TableAlreadyExistsException;
@@ -68,6 +69,7 @@ import org.apache.phoenix.util.MetaDataUtil;
 import org.apache.phoenix.util.PhoenixRuntime;
 import org.apache.phoenix.util.PropertiesUtil;
 import org.apache.phoenix.util.SchemaUtil;
+import org.apache.phoenix.util.SequenceUtil;
 
 import com.google.common.collect.Maps;
 
@@ -82,7 +84,7 @@ import com.google.common.collect.Maps;
  */
 public class ConnectionlessQueryServicesImpl extends DelegateQueryServices implements ConnectionQueryServices  {
     private PMetaData metaData;
-    private final Map<SequenceKey, Long> sequenceMap = Maps.newHashMap();
+    private final Map<SequenceKey, SequenceInfo> sequenceMap = Maps.newHashMap();
     private KeyValueBuilder kvBuilder;
     private volatile boolean initialized;
     private volatile SQLException initializationException;
@@ -312,13 +314,14 @@ public class ConnectionlessQueryServicesImpl extends DelegateQueryServices imple
     }
 
     @Override
-    public long createSequence(String tenantId, String schemaName, String sequenceName, long startWith, long incrementBy, long cacheSize, long timestamp)
-            throws SQLException {
+    public long createSequence(String tenantId, String schemaName, String sequenceName,
+            long startWith, long incrementBy, long cacheSize, long minValue, long maxValue,
+            boolean cycle, long timestamp) throws SQLException {
         SequenceKey key = new SequenceKey(tenantId, schemaName, sequenceName);
         if (sequenceMap.get(key) != null) {
             throw new SequenceAlreadyExistsException(schemaName, sequenceName);
         }
-        sequenceMap.put(key, startWith);
+        sequenceMap.put(key, new SequenceInfo(startWith, incrementBy, minValue, maxValue, 1l, cycle)) ;
         return timestamp;
     }
 
@@ -336,11 +339,11 @@ public class ConnectionlessQueryServicesImpl extends DelegateQueryServices imple
             SQLException[] exceptions, Sequence.ValueOp action) throws SQLException {
         int i = 0;
         for (SequenceKey key : sequenceKeys) {
-            Long value = sequenceMap.get(key);
-            if (value == null) {
+            SequenceInfo info = sequenceMap.get(key);
+            if (info == null) {
                 exceptions[i] = new SequenceNotFoundException(key.getSchemaName(), key.getSequenceName());
             } else {
-                values[i] = value;          
+                values[i] = info.sequenceValue;          
             }
             i++;
         }
@@ -351,11 +354,14 @@ public class ConnectionlessQueryServicesImpl extends DelegateQueryServices imple
             SQLException[] exceptions) throws SQLException {
         int i = 0;
         for (SequenceKey key : sequenceKeys) {
-            Long value = sequenceMap.get(key);
-            if (value == null) {
-                exceptions[i] = new SequenceNotFoundException(key.getSchemaName(), key.getSequenceName());
+            SequenceInfo info = sequenceMap.get(key);
+            if (info == null) {
+                exceptions[i] =
+                        new SequenceNotFoundException(key.getSchemaName(), key.getSequenceName());
             } else {
-                values[i] = value++;
+                values[i] = info.sequenceValue;
+                info.sequenceValue =
+                        SequenceUtil.getNextValue(key, info);
             }
             i++;
         }
@@ -370,13 +376,13 @@ public class ConnectionlessQueryServicesImpl extends DelegateQueryServices imple
 
     @Override
     public long currentSequenceValue(SequenceKey sequenceKey, long timestamp) throws SQLException {
-        Long value = sequenceMap.get(sequenceKey);
-        if (value == null) {
+        SequenceInfo info = sequenceMap.get(sequenceKey);
+        if (info == null) {
             throw new SQLExceptionInfo.Builder(SQLExceptionCode.CANNOT_CALL_CURRENT_BEFORE_NEXT_VALUE)
             .setSchemaName(sequenceKey.getSchemaName()).setTableName(sequenceKey.getSequenceName())
             .build().buildException();
         }
-        return value;
+        return info.sequenceValue;
     }
 
     @Override

http://git-wip-us.apache.org/repos/asf/phoenix/blob/15e1108f/phoenix-core/src/main/java/org/apache/phoenix/query/DelegateConnectionQueryServices.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/query/DelegateConnectionQueryServices.java b/phoenix-core/src/main/java/org/apache/phoenix/query/DelegateConnectionQueryServices.java
index cae52dd..4fa7993 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/query/DelegateConnectionQueryServices.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/query/DelegateConnectionQueryServices.java
@@ -171,9 +171,11 @@ public class DelegateConnectionQueryServices extends DelegateQueryServices imple
     }
 
     @Override
-    public long createSequence(String tenantId, String schemaName, String sequenceName, long startWith,
-            long incrementBy, long cacheSize, long timestamp) throws SQLException {
-        return getDelegate().createSequence(tenantId, schemaName, sequenceName, startWith, incrementBy, cacheSize, timestamp);
+    public long createSequence(String tenantId, String schemaName, String sequenceName,
+            long startWith, long incrementBy, long cacheSize, long minValue, long maxValue,
+            boolean cycle, long timestamp) throws SQLException {
+        return getDelegate().createSequence(tenantId, schemaName, sequenceName, startWith,
+            incrementBy, cacheSize, minValue, maxValue, cycle, timestamp);
     }
 
     @Override


Mime
View raw message