HIVE-13856 Fetching transaction batches during ACID streaming against Hive Metastore using Oracle DB fails (Eugene Koifman, reviewed by Wei Zheng)
Project: http://git-wip-us.apache.org/repos/asf/hive/repo Commit: http://git-wip-us.apache.org/repos/asf/hive/commit/77f563f2 Tree: http://git-wip-us.apache.org/repos/asf/hive/tree/77f563f2 Diff: http://git-wip-us.apache.org/repos/asf/hive/diff/77f563f2 Branch: refs/heads/branch-2.1 Commit: 77f563f260e8cd4c4c65099912d7807c200a843e Parents: a1fe682 Author: Eugene Koifman <[email protected]> Authored: Tue May 31 20:26:50 2016 -0700 Committer: Eugene Koifman <[email protected]> Committed: Tue May 31 20:26:50 2016 -0700 ---------------------------------------------------------------------- .../hadoop/hive/metastore/txn/TxnHandler.java | 369 +++++++++++-------- .../hadoop/hive/metastore/txn/TestTxnUtils.java | 50 ++- 2 files changed, 254 insertions(+), 165 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/hive/blob/77f563f2/metastore/src/java/org/apache/hadoop/hive/metastore/txn/TxnHandler.java ---------------------------------------------------------------------- diff --git a/metastore/src/java/org/apache/hadoop/hive/metastore/txn/TxnHandler.java b/metastore/src/java/org/apache/hadoop/hive/metastore/txn/TxnHandler.java index 57517dc..c859cbd 100644 --- a/metastore/src/java/org/apache/hadoop/hive/metastore/txn/TxnHandler.java +++ b/metastore/src/java/org/apache/hadoop/hive/metastore/txn/TxnHandler.java @@ -185,6 +185,7 @@ abstract class TxnHandler implements TxnStore, TxnStore.MutexAPI { private long deadlockRetryInterval; protected HiveConf conf; protected DatabaseProduct dbProduct; + private SQLGenerator sqlGenerator; // (End user) Transaction timeout, in milliseconds. private long timeout; @@ -232,6 +233,7 @@ abstract class TxnHandler implements TxnStore, TxnStore.MutexAPI { setupJdbcConnectionPool(conf); dbConn = getDbConn(Connection.TRANSACTION_READ_COMMITTED); determineDatabaseProduct(dbConn); + sqlGenerator = new SQLGenerator(dbProduct, conf); } catch (SQLException e) { String msg = "Unable to instantiate JDBC connection pooling, " + e.getMessage(); LOG.error(msg); @@ -444,7 +446,7 @@ abstract class TxnHandler implements TxnStore, TxnStore.MutexAPI { if (numTxns > maxTxns) numTxns = maxTxns; stmt = dbConn.createStatement(); - String s = addForUpdateClause("select ntxn_next from NEXT_TXN_ID"); + String s = sqlGenerator.addForUpdateClause("select ntxn_next from NEXT_TXN_ID"); LOG.debug("Going to execute query <" + s + ">"); rs = stmt.executeQuery(s); if (!rs.next()) { @@ -458,40 +460,14 @@ abstract class TxnHandler implements TxnStore, TxnStore.MutexAPI { long now = getDbTime(dbConn); List<Long> txnIds = new ArrayList<Long>(numTxns); - ArrayList<String> queries = new ArrayList<String>(); - String query; - String insertClause = "insert into TXNS (txn_id, txn_state, txn_started, txn_last_heartbeat, txn_user, txn_host) values "; - StringBuilder valuesClause = new StringBuilder(); + List<String> rows = new ArrayList<>(); for (long i = first; i < first + numTxns; i++) { txnIds.add(i); - - if (i > first && - (i - first) % conf.getIntVar(HiveConf.ConfVars.METASTORE_DIRECT_SQL_MAX_ELEMENTS_VALUES_CLAUSE) == 0) { - // wrap up the current query, and start a new one - query = insertClause + valuesClause.toString(); - queries.add(query); - - valuesClause.setLength(0); - valuesClause.append("(").append(i).append(", 'o', ").append(now).append(", ").append(now) - .append(", '").append(rqst.getUser()).append("', '").append(rqst.getHostname()) - .append("')"); - - continue; - } - - if (i > first) { - valuesClause.append(", "); - } - - valuesClause.append("(").append(i).append(", 'o', ").append(now).append(", ").append(now) - .append(", '").append(rqst.getUser()).append("', '").append(rqst.getHostname()) - .append("')"); + rows.add(i + "," + quoteChar(TXN_OPEN) + "," + now + "," + now + "," + quoteString(rqst.getUser()) + "," + quoteString(rqst.getHostname())); } - - query = insertClause + valuesClause.toString(); - queries.add(query); - + List<String> queries = sqlGenerator.createInsertValuesStmt( + "TXNS (txn_id, txn_state, txn_started, txn_last_heartbeat, txn_user, txn_host)", rows); for (String q : queries) { LOG.debug("Going to execute update <" + q + ">"); stmt.execute(q); @@ -611,45 +587,39 @@ abstract class TxnHandler implements TxnStore, TxnStore.MutexAPI { dbConn = getDbConn(Connection.TRANSACTION_READ_COMMITTED); stmt = dbConn.createStatement(); /** - * This S4U will mutex with other commitTxn() and openTxns(). - * -1 below makes txn intervals look like [3,3] [4,4] if all txns are serial - * Note: it's possible to have several txns have the same commit id. Suppose 3 txns start - * at the same time and no new txns start until all 3 commit. - * We could've incremented the sequence for commitId is well but it doesn't add anything functionally. - */ - commitIdRs = stmt.executeQuery(addForUpdateClause("select ntxn_next - 1 from NEXT_TXN_ID")); - if(!commitIdRs.next()) { - throw new IllegalStateException("No rows found in NEXT_TXN_ID"); - } - long commitId = commitIdRs.getLong(1); - /** * Runs at READ_COMMITTED with S4U on TXNS row for "txnid". S4U ensures that no other * operation can change this txn (such acquiring locks). While lock() and commitTxn() * should not normally run concurrently (for same txn) but could due to bugs in the client * which could then corrupt internal transaction manager state. Also competes with abortTxn(). */ lockHandle = lockTransactionRecord(stmt, txnid, TXN_OPEN); - if(lockHandle == null) { + if (lockHandle == null) { //this also ensures that txn is still there and in expected state (hasn't been timed out) ensureValidTxn(dbConn, txnid, stmt); shouldNeverHappen(txnid); } - Savepoint undoWriteSetForCurrentTxn = dbConn.setSavepoint(); - int numCompsWritten = stmt.executeUpdate("insert into WRITE_SET (ws_database, ws_table, ws_partition, ws_txnid, ws_commit_id, ws_operation_type)" + - " select tc_database, tc_table, tc_partition, tc_txnid, " + commitId + ", tc_operation_type " + - "from TXN_COMPONENTS where tc_txnid=" + txnid + " and tc_operation_type IN(" + quoteChar(OpertaionType.UPDATE.sqlConst) + "," + quoteChar(OpertaionType.DELETE.sqlConst) + ")"); - if(numCompsWritten == 0) { + String conflictSQLSuffix = "from TXN_COMPONENTS where tc_txnid=" + txnid + " and tc_operation_type IN(" + + quoteChar(OpertaionType.UPDATE.sqlConst) + "," + quoteChar(OpertaionType.DELETE.sqlConst) + ")"; + rs = stmt.executeQuery(sqlGenerator.addLimitClause(1, "tc_operation_type " + conflictSQLSuffix)); + if (rs.next()) { + close(rs); + //here means currently committing txn performed update/delete and we should check WW conflict /** - * current txn didn't update/delete anything (may have inserted), so just proceed with commit - * - * We only care about commit id for write txns, so for RO (when supported) txns we don't - * have to mutex on NEXT_TXN_ID. - * Consider: if RO txn is after a W txn, then RO's openTxns() will be mutexed with W's - * commitTxn() because both do S4U on NEXT_TXN_ID and thus RO will see result of W txn. - * If RO < W, then there is no reads-from relationship. + * This S4U will mutex with other commitTxn() and openTxns(). + * -1 below makes txn intervals look like [3,3] [4,4] if all txns are serial + * Note: it's possible to have several txns have the same commit id. Suppose 3 txns start + * at the same time and no new txns start until all 3 commit. + * We could've incremented the sequence for commitId is well but it doesn't add anything functionally. */ - } - else { + commitIdRs = stmt.executeQuery(sqlGenerator.addForUpdateClause("select ntxn_next - 1 from NEXT_TXN_ID")); + if (!commitIdRs.next()) { + throw new IllegalStateException("No rows found in NEXT_TXN_ID"); + } + long commitId = commitIdRs.getLong(1); + Savepoint undoWriteSetForCurrentTxn = dbConn.setSavepoint(); + int numCompsWritten = stmt.executeUpdate( + "insert into WRITE_SET (ws_database, ws_table, ws_partition, ws_txnid, ws_commit_id, ws_operation_type)" + + " select tc_database, tc_table, tc_partition, tc_txnid, " + commitId + ", tc_operation_type " + conflictSQLSuffix); /** * see if there are any overlapping txns wrote the same element, i.e. have a conflict * Since entire commit operation is mutexed wrt other start/commit ops, @@ -660,28 +630,28 @@ abstract class TxnHandler implements TxnStore, TxnStore.MutexAPI { * [17,18] committed and [18,19] committing now - these overlap (here 18 started while 17 was still running) */ rs = stmt.executeQuery - (addLimitClause(1, "committed.ws_txnid, committed.ws_commit_id, committed.ws_database," + - "committed.ws_table, committed.ws_partition, cur.ws_commit_id " + + (sqlGenerator.addLimitClause(1, "committed.ws_txnid, committed.ws_commit_id, committed.ws_database," + + "committed.ws_table, committed.ws_partition, cur.ws_commit_id " + "from WRITE_SET committed INNER JOIN WRITE_SET cur " + - "ON committed.ws_database=cur.ws_database and committed.ws_table=cur.ws_table " + + "ON committed.ws_database=cur.ws_database and committed.ws_table=cur.ws_table " + //For partitioned table we always track writes at partition level (never at table) //and for non partitioned - always at table level, thus the same table should never //have entries with partition key and w/o - "and (committed.ws_partition=cur.ws_partition or (committed.ws_partition is null and cur.ws_partition is null)) " + - "where cur.ws_txnid <= committed.ws_commit_id" + //txns overlap; could replace ws_txnid + "and (committed.ws_partition=cur.ws_partition or (committed.ws_partition is null and cur.ws_partition is null)) " + + "where cur.ws_txnid <= committed.ws_commit_id" + //txns overlap; could replace ws_txnid // with txnid, though any decent DB should infer this - " and cur.ws_txnid=" + txnid + //make sure RHS of join only has rows we just inserted as + " and cur.ws_txnid=" + txnid + //make sure RHS of join only has rows we just inserted as // part of this commitTxn() op - " and committed.ws_txnid <> " + txnid + //and LHS only has committed txns + " and committed.ws_txnid <> " + txnid + //and LHS only has committed txns //U+U and U+D is a conflict but D+D is not and we don't currently track I in WRITE_SET at all - " and (committed.ws_operation_type=" + quoteChar(OpertaionType.UPDATE.sqlConst) + - " OR cur.ws_operation_type=" + quoteChar(OpertaionType.UPDATE.sqlConst) + ")")); - if(rs.next()) { + " and (committed.ws_operation_type=" + quoteChar(OpertaionType.UPDATE.sqlConst) + + " OR cur.ws_operation_type=" + quoteChar(OpertaionType.UPDATE.sqlConst) + ")")); + if (rs.next()) { //found a conflict String committedTxn = "[" + JavaUtils.txnIdToString(rs.getLong(1)) + "," + rs.getLong(2) + "]"; StringBuilder resource = new StringBuilder(rs.getString(3)).append("/").append(rs.getString(4)); String partitionName = rs.getString(5); - if(partitionName != null) { + if (partitionName != null) { resource.append('/').append(partitionName); } String msg = "Aborting [" + JavaUtils.txnIdToString(txnid) + "," + rs.getLong(6) + "]" + " due to a write conflict on " + resource + @@ -691,17 +661,27 @@ abstract class TxnHandler implements TxnStore, TxnStore.MutexAPI { dbConn.rollback(undoWriteSetForCurrentTxn); LOG.info(msg); //todo: should make abortTxns() write something into TXNS.TXN_META_INFO about this - if(abortTxns(dbConn, Collections.singletonList(txnid), true) != 1) { + if (abortTxns(dbConn, Collections.singletonList(txnid), true) != 1) { throw new IllegalStateException(msg + " FAILED!"); } dbConn.commit(); close(null, stmt, dbConn); throw new TxnAbortedException(msg); - } - else { + } else { //no conflicting operations, proceed with the rest of commit sequence } } + else { + /** + * current txn didn't update/delete anything (may have inserted), so just proceed with commit + * + * We only care about commit id for write txns, so for RO (when supported) txns we don't + * have to mutex on NEXT_TXN_ID. + * Consider: if RO txn is after a W txn, then RO's openTxns() will be mutexed with W's + * commitTxn() because both do S4U on NEXT_TXN_ID and thus RO will see result of W txn. + * If RO < W, then there is no reads-from relationship. + */ + } // Move the record from txn_components into completed_txn_components so that the compactor // knows where to look to compact. String s = "insert into COMPLETED_TXN_COMPONENTS select tc_txnid, tc_database, tc_table, " + @@ -823,7 +803,7 @@ abstract class TxnHandler implements TxnStore, TxnStore.MutexAPI { */ private ResultSet lockTransactionRecord(Statement stmt, long txnId, Character txnState) throws SQLException, MetaException { String query = "select TXN_STATE from TXNS where TXN_ID = " + txnId + (txnState != null ? " AND TXN_STATE=" + quoteChar(txnState) : ""); - ResultSet rs = stmt.executeQuery(addForUpdateClause(query)); + ResultSet rs = stmt.executeQuery(sqlGenerator.addForUpdateClause(query)); if(rs.next()) { return rs; } @@ -866,7 +846,7 @@ abstract class TxnHandler implements TxnStore, TxnStore.MutexAPI { * 2nd nl_next=8. Then 8 goes first to insert into HIVE_LOCKS and acquires the locks. Then 7 unblocks, * and add it's W locks but it won't see locks from 8 since to be 'fair' {@link #checkLock(java.sql.Connection, long)} * doesn't block on locks acquired later than one it's checking*/ - String s = addForUpdateClause("select nl_next from NEXT_LOCK_ID"); + String s = sqlGenerator.addForUpdateClause("select nl_next from NEXT_LOCK_ID"); LOG.debug("Going to execute query <" + s + ">"); rs = stmt.executeQuery(s); if (!rs.next()) { @@ -881,6 +861,7 @@ abstract class TxnHandler implements TxnStore, TxnStore.MutexAPI { stmt.executeUpdate(s); if (txnid > 0) { + List<String> rows = new ArrayList<>(); /** * todo QueryPlan has BaseSemanticAnalyzer which has acidFileSinks list of FileSinkDesc * FileSinkDesc.table is ql.metadata.Table @@ -923,17 +904,20 @@ abstract class TxnHandler implements TxnStore, TxnStore.MutexAPI { String dbName = lc.getDbname(); String tblName = lc.getTablename(); String partName = lc.getPartitionname(); - s = "insert into TXN_COMPONENTS " + - "(tc_txnid, tc_database, tc_table, tc_partition, tc_operation_type) " + - "values (" + txnid + ", '" + dbName + "', " + + rows.add(txnid + ", '" + dbName + "', " + (tblName == null ? "null" : "'" + tblName + "'") + ", " + (partName == null ? "null" : "'" + partName + "'")+ "," + - quoteString(OpertaionType.fromDataOperationType(lc.getOperationType()).toString()) + ")"; - LOG.debug("Going to execute update <" + s + ">"); - int modCount = stmt.executeUpdate(s); + quoteString(OpertaionType.fromDataOperationType(lc.getOperationType()).toString())); + } + List<String> queries = sqlGenerator.createInsertValuesStmt( + "TXN_COMPONENTS (tc_txnid, tc_database, tc_table, tc_partition, tc_operation_type)", rows); + for(String query : queries) { + LOG.debug("Going to execute update <" + query + ">"); + int modCount = stmt.executeUpdate(query); } } + List<String> rows = new ArrayList<>(); long intLockId = 0; for (LockComponent lc : rqst.getComponent()) { if(lc.isSetOperationType() && lc.getOperationType() == DataOperationType.UNSET) { @@ -959,17 +943,7 @@ abstract class TxnHandler implements TxnStore, TxnStore.MutexAPI { break; } long now = getDbTime(dbConn); - s = "insert into HIVE_LOCKS " + - "(hl_lock_ext_id, hl_lock_int_id, hl_txnid, " + - "hl_db, " + - "hl_table, " + - "hl_partition, " + - "hl_lock_state, hl_lock_type, " + - "hl_last_heartbeat, " + - "hl_user, " + - "hl_host, " + - "hl_agent_info) values(" + - extLockId + ", " + intLockId + "," + txnid + ", " + + rows.add(extLockId + ", " + intLockId + "," + txnid + ", " + quoteString(dbName) + ", " + valueOrNullLiteral(tblName) + ", " + valueOrNullLiteral(partName) + ", " + @@ -978,9 +952,15 @@ abstract class TxnHandler implements TxnStore, TxnStore.MutexAPI { (isValidTxn(txnid) ? 0 : now) + ", " + valueOrNullLiteral(rqst.getUser()) + ", " + valueOrNullLiteral(rqst.getHostname()) + ", " + - valueOrNullLiteral(rqst.getAgentInfo()) + ")"; - LOG.debug("Going to execute update <" + s + ">"); - stmt.executeUpdate(s); + valueOrNullLiteral(rqst.getAgentInfo()));// + ")"; + } + List<String> queries = sqlGenerator.createInsertValuesStmt( + "HIVE_LOCKS (hl_lock_ext_id, hl_lock_int_id, hl_txnid, hl_db, " + + "hl_table, hl_partition,hl_lock_state, hl_lock_type, " + + "hl_last_heartbeat, hl_user, hl_host, hl_agent_info)", rows); + for(String query : queries) { + LOG.debug("Going to execute update <" + query + ">"); + int modCount = stmt.executeUpdate(query); } dbConn.commit(); success = true; @@ -1347,7 +1327,7 @@ abstract class TxnHandler implements TxnStore, TxnStore.MutexAPI { long generateCompactionQueueId(Statement stmt) throws SQLException, MetaException { // Get the id for the next entry in the queue - String s = addForUpdateClause("select ncq_next from NEXT_COMPACTION_QUEUE_ID"); + String s = sqlGenerator.addForUpdateClause("select ncq_next from NEXT_COMPACTION_QUEUE_ID"); LOG.debug("going to execute query <" + s + ">"); ResultSet rs = stmt.executeQuery(s); if (!rs.next()) { @@ -1548,13 +1528,16 @@ abstract class TxnHandler implements TxnStore, TxnStore.MutexAPI { //partitions have been written to. w/o this WRITE_SET would contain entries for partitions not actually //written to int modCount = stmt.executeUpdate(deleteSql); + List<String> rows = new ArrayList<>(); for (String partName : rqst.getPartitionnames()) { - String s = - "insert into TXN_COMPONENTS (tc_txnid, tc_database, tc_table, tc_partition, tc_operation_type) values (" + - rqst.getTxnid() + "," + quoteString(rqst.getDbname()) + "," + quoteString(rqst.getTablename()) + - "," + quoteString(partName) + "," + quoteChar(ot.sqlConst) + ")"; - LOG.debug("Going to execute update <" + s + ">"); - modCount = stmt.executeUpdate(s); + rows.add(rqst.getTxnid() + "," + quoteString(rqst.getDbname()) + "," + quoteString(rqst.getTablename()) + + "," + quoteString(partName) + "," + quoteChar(ot.sqlConst)); + } + List<String> queries = sqlGenerator.createInsertValuesStmt( + "TXN_COMPONENTS (tc_txnid, tc_database, tc_table, tc_partition, tc_operation_type)", rows); + for(String query : queries) { + LOG.debug("Going to execute update <" + query + ">"); + modCount = stmt.executeUpdate(query); } LOG.debug("Going to commit"); dbConn.commit(); @@ -2347,7 +2330,7 @@ abstract class TxnHandler implements TxnStore, TxnStore.MutexAPI { sb.setLength(sb.length() - 4);//nuke trailing " or " sb.append(")"); //1 row is sufficient to know we have to kill the query - rs = stmt.executeQuery(addLimitClause(1, sb.toString())); + rs = stmt.executeQuery(sqlGenerator.addLimitClause(1, sb.toString())); if(rs.next()) { /** * if here, it means we found an already committed txn which overlaps with the current one and @@ -2817,35 +2800,6 @@ abstract class TxnHandler implements TxnStore, TxnStore.MutexAPI { } /** - * Suppose you have a query "select a,b from T" and you want to limit the result set - * to the first 5 rows. The mechanism to do that differs in different DB. - * Make {@code noSelectsqlQuery} to be "a,b from T" and this method will return the - * appropriately modified row limiting query. - */ - private String addLimitClause(int numRows, String noSelectsqlQuery) throws MetaException { - switch (dbProduct) { - case DERBY: - //http://db.apache.org/derby/docs/10.7/ref/rrefsqljoffsetfetch.html - return "select " + noSelectsqlQuery + " fetch first " + numRows + " rows only"; - case MYSQL: - //http://www.postgresql.org/docs/7.3/static/queries-limit.html - case POSTGRES: - //https://dev.mysql.com/doc/refman/5.0/en/select.html - return "select " + noSelectsqlQuery + " limit " + numRows; - case ORACLE: - //newer versions (12c and later) support OFFSET/FETCH - return "select * from (select " + noSelectsqlQuery + ") where rownum <= " + numRows; - case SQLSERVER: - //newer versions (2012 and later) support OFFSET/FETCH - //https://msdn.microsoft.com/en-us/library/ms189463.aspx - return "select TOP(" + numRows + ") " + noSelectsqlQuery; - default: - String msg = "Unrecognized database product name <" + dbProduct + ">"; - LOG.error(msg); - throw new MetaException(msg); - } - } - /** * Isolation Level Notes * Plain: RC is OK * This will find transactions that have timed out and abort them. @@ -2874,7 +2828,7 @@ abstract class TxnHandler implements TxnStore, TxnStore.MutexAPI { stmt = dbConn.createStatement(); String s = " txn_id from TXNS where txn_state = '" + TXN_OPEN + "' and txn_last_heartbeat < " + (now - timeout); - s = addLimitClause(250 * TIMED_OUT_TXN_ABORT_BATCH_SIZE, s); + s = sqlGenerator.addLimitClause(250 * TIMED_OUT_TXN_ABORT_BATCH_SIZE, s); LOG.debug("Going to execute query <" + s + ">"); rs = stmt.executeQuery(s); if(!rs.next()) { @@ -3170,33 +3124,6 @@ abstract class TxnHandler implements TxnStore, TxnStore.MutexAPI { return ex.getMessage() + " (SQLState=" + ex.getSQLState() + ", ErrorCode=" + ex.getErrorCode() + ")"; } /** - * Given a {@code selectStatement}, decorated it with FOR UPDATE or semantically equivalent - * construct. If the DB doesn't support, return original select. - */ - private String addForUpdateClause(String selectStatement) throws MetaException { - switch (dbProduct) { - case DERBY: - //https://db.apache.org/derby/docs/10.1/ref/rrefsqlj31783.html - //sadly in Derby, FOR UPDATE doesn't meant what it should - return selectStatement; - case MYSQL: - //http://dev.mysql.com/doc/refman/5.7/en/select.html - case ORACLE: - //https://docs.oracle.com/cd/E17952_01/refman-5.6-en/select.html - case POSTGRES: - //http://www.postgresql.org/docs/9.0/static/sql-select.html - return selectStatement + " for update"; - case SQLSERVER: - //https://msdn.microsoft.com/en-us/library/ms189499.aspx - //https://msdn.microsoft.com/en-us/library/ms187373.aspx - return selectStatement + " with(updlock)"; - default: - String msg = "Unrecognized database product name <" + dbProduct + ">"; - LOG.error(msg); - throw new MetaException(msg); - } - } - /** * Useful for building SQL strings * @param value may be {@code null} */ @@ -3267,7 +3194,7 @@ abstract class TxnHandler implements TxnStore, TxnStore.MutexAPI { ResultSet rs = null; try { try { - String sqlStmt = addForUpdateClause("select MT_COMMENT from AUX_TABLE where MT_KEY1=" + quoteString(key) + " and MT_KEY2=0"); + String sqlStmt = sqlGenerator.addForUpdateClause("select MT_COMMENT from AUX_TABLE where MT_KEY1=" + quoteString(key) + " and MT_KEY2=0"); lockInternal(); dbConn = getDbConn(Connection.TRANSACTION_READ_COMMITTED); stmt = dbConn.createStatement(); @@ -3358,4 +3285,126 @@ abstract class TxnHandler implements TxnStore, TxnStore.MutexAPI { } } } + /** + * Helper class that generates SQL queries with syntax specific to target DB + */ + static final class SQLGenerator { + private final DatabaseProduct dbProduct; + private final HiveConf conf; + SQLGenerator(DatabaseProduct dbProduct, HiveConf conf) { + this.dbProduct = dbProduct; + this.conf = conf; + } + /** + * Genereates "Insert into T(a,b,c) values(1,2,'f'),(3,4,'c')" for appropriate DB + * @param tblColumns e.g. "T(a,b,c)" + * @param rows e.g. list of Strings like 3,4,'d' + * @return fully formed INSERT INTO ... statements + */ + List<String> createInsertValuesStmt(String tblColumns, List<String> rows) { + if(rows == null || rows.size() == 0) { + return Collections.emptyList(); + } + List<String> insertStmts = new ArrayList<>(); + StringBuilder sb = new StringBuilder(); + switch (dbProduct) { + case ORACLE: + if(rows.size() > 1) { + //http://www.oratable.com/oracle-insert-all/ + //https://livesql.oracle.com/apex/livesql/file/content_BM1LJQ87M5CNIOKPOWPV6ZGR3.html + for (int numRows = 0; numRows < rows.size(); numRows++) { + if (numRows % conf.getIntVar(HiveConf.ConfVars.METASTORE_DIRECT_SQL_MAX_ELEMENTS_VALUES_CLAUSE) == 0) { + if (numRows > 0) { + sb.append(" select * from dual"); + insertStmts.add(sb.toString()); + } + sb.setLength(0); + sb.append("insert all "); + } + sb.append("into ").append(tblColumns).append(" values(").append(rows.get(numRows)).append(") "); + } + sb.append("select * from dual"); + insertStmts.add(sb.toString()); + return insertStmts; + } + //fall through + case DERBY: + case MYSQL: + case POSTGRES: + case SQLSERVER: + for(int numRows = 0; numRows < rows.size(); numRows++) { + if(numRows % conf.getIntVar(HiveConf.ConfVars.METASTORE_DIRECT_SQL_MAX_ELEMENTS_VALUES_CLAUSE) == 0) { + if(numRows > 0) { + insertStmts.add(sb.substring(0, sb.length() - 1));//exclude trailing comma + } + sb.setLength(0); + sb.append("insert into ").append(tblColumns).append(" values"); + } + sb.append('(').append(rows.get(numRows)).append("),"); + } + insertStmts.add(sb.substring(0, sb.length() - 1));//exclude trailing comma + return insertStmts; + default: + String msg = "Unrecognized database product name <" + dbProduct + ">"; + LOG.error(msg); + throw new IllegalStateException(msg); + } + } + /** + * Given a {@code selectStatement}, decorated it with FOR UPDATE or semantically equivalent + * construct. If the DB doesn't support, return original select. + */ + private String addForUpdateClause(String selectStatement) throws MetaException { + switch (dbProduct) { + case DERBY: + //https://db.apache.org/derby/docs/10.1/ref/rrefsqlj31783.html + //sadly in Derby, FOR UPDATE doesn't meant what it should + return selectStatement; + case MYSQL: + //http://dev.mysql.com/doc/refman/5.7/en/select.html + case ORACLE: + //https://docs.oracle.com/cd/E17952_01/refman-5.6-en/select.html + case POSTGRES: + //http://www.postgresql.org/docs/9.0/static/sql-select.html + return selectStatement + " for update"; + case SQLSERVER: + //https://msdn.microsoft.com/en-us/library/ms189499.aspx + //https://msdn.microsoft.com/en-us/library/ms187373.aspx + return selectStatement + " with(updlock)"; + default: + String msg = "Unrecognized database product name <" + dbProduct + ">"; + LOG.error(msg); + throw new MetaException(msg); + } + } + /** + * Suppose you have a query "select a,b from T" and you want to limit the result set + * to the first 5 rows. The mechanism to do that differs in different DB. + * Make {@code noSelectsqlQuery} to be "a,b from T" and this method will return the + * appropriately modified row limiting query. + */ + private String addLimitClause(int numRows, String noSelectsqlQuery) throws MetaException { + switch (dbProduct) { + case DERBY: + //http://db.apache.org/derby/docs/10.7/ref/rrefsqljoffsetfetch.html + return "select " + noSelectsqlQuery + " fetch first " + numRows + " rows only"; + case MYSQL: + //http://www.postgresql.org/docs/7.3/static/queries-limit.html + case POSTGRES: + //https://dev.mysql.com/doc/refman/5.0/en/select.html + return "select " + noSelectsqlQuery + " limit " + numRows; + case ORACLE: + //newer versions (12c and later) support OFFSET/FETCH + return "select * from (select " + noSelectsqlQuery + ") where rownum <= " + numRows; + case SQLSERVER: + //newer versions (2012 and later) support OFFSET/FETCH + //https://msdn.microsoft.com/en-us/library/ms189463.aspx + return "select TOP(" + numRows + ") " + noSelectsqlQuery; + default: + String msg = "Unrecognized database product name <" + dbProduct + ">"; + LOG.error(msg); + throw new MetaException(msg); + } + } + } } http://git-wip-us.apache.org/repos/asf/hive/blob/77f563f2/metastore/src/test/org/apache/hadoop/hive/metastore/txn/TestTxnUtils.java ---------------------------------------------------------------------- diff --git a/metastore/src/test/org/apache/hadoop/hive/metastore/txn/TestTxnUtils.java b/metastore/src/test/org/apache/hadoop/hive/metastore/txn/TestTxnUtils.java index a2195ed..cf0a7d4 100644 --- a/metastore/src/test/org/apache/hadoop/hive/metastore/txn/TestTxnUtils.java +++ b/metastore/src/test/org/apache/hadoop/hive/metastore/txn/TestTxnUtils.java @@ -17,17 +17,12 @@ */ package org.apache.hadoop.hive.metastore.txn; -import org.apache.commons.io.FileUtils; -import org.apache.hadoop.fs.FileUtil; import org.apache.hadoop.hive.conf.HiveConf; import org.junit.After; import org.junit.Assert; import org.junit.Before; import org.junit.Test; -import org.slf4j.Logger; -import org.slf4j.LoggerFactory; -import java.io.File; import java.sql.Connection; import java.sql.ResultSet; import java.sql.Statement; @@ -119,6 +114,51 @@ public class TestTxnUtils { TxnDbUtil.closeResources(conn, stmt, rs); } } + @Test + public void testSQLGenerator() throws Exception { + //teseted on Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production + TxnHandler.SQLGenerator sqlGenerator = + new TxnHandler.SQLGenerator(TxnHandler.DatabaseProduct.ORACLE, conf); + List<String> rows = new ArrayList<>(); + rows.add("'yellow', 1"); + List<String> sql = sqlGenerator.createInsertValuesStmt("colors(name, category)", rows); + Assert.assertEquals("Number of stmts", 1, sql.size()); + Assert.assertEquals("Wrong stmt", "insert into colors(name, category) values('yellow', 1)", sql.get(0)); + rows.add("'red', 2"); + rows.add("'orange', 3"); + sql = sqlGenerator.createInsertValuesStmt("colors(name, category)", rows); + Assert.assertEquals("Number of stmts", 1, sql.size()); + + Assert.assertEquals("Wrong stmt", + "insert all into colors(name, category) values('yellow', 1) into colors(name, category) values('red', 2) into colors(name, category) values('orange', 3) select * from dual", sql.get(0)); + for(int i = 0; i < conf.getIntVar(HiveConf.ConfVars.METASTORE_DIRECT_SQL_MAX_ELEMENTS_VALUES_CLAUSE); i++) { + rows.add("\'G\'," + i); + } + sql = sqlGenerator.createInsertValuesStmt("colors(name, category)", rows); + Assert.assertEquals("Number of stmts", 2, sql.size()); + Assert.assertEquals("Wrong stmt", "insert all into colors(name, category) values('yellow', 1) into colors(name, category) values('red', 2) into colors(name, category) values('orange', 3) into colors(name, category) values('G',0) into colors(name, category) values('G',1) into colors(name, category) values('G',2) into colors(name, category) values('G',3) into colors(name, category) values('G',4) into colors(name, category) values('G',5) into colors(name, category) values('G',6) into colors(name, category) values('G',7) into colors(name, category) values('G',8) into colors(name, category) values('G',9) into colors(name, category) values('G',10) into colors(name, category) values('G',11) into colors(name, category) values('G',12) into colors(name, category) values('G',13) into colors(name, category) values('G',14) into colors(name, category) values('G',15) into colors(name, category) values('G',16) into colors(name, category) values('G',17) into colors(name, category) values('G',18) into colors(name, category) values('G',19) into colors(name, category) values('G',20) into colors(name, category) values('G',21) into colors(name, category) values('G',22) into colors(name, category) values('G',23) into colors(name, category) values('G',24) into colors(name, category) values('G',25) into colors(name, category) values('G',26) into colors(name, category) values('G',27) into colors(name, category) values('G',28) into colors(name, category) values('G',29) into colors(name, category) values('G',30) into colors(name, category) values('G',31) into colors(name, category) values('G',32) into colors(name, category) values('G',33) into colors(name, category) values('G',34) into colors(name, category) values('G',35) into colors(name, category) values('G',36) into colors(name, category) values('G',37) into colors(name, category) values('G',38) into colors(name, category) values('G',39) into colors(name, category) values('G',40) into colors(name, category) values('G',41) into co lors(name, category) values('G',42) into colors(name, category) values('G',43) into colors(name, category) values('G',44) into colors(name, category) values('G',45) into colors(name, category) values('G',46) into colors(name, category) values('G',47) into colors(name, category) values('G',48) into colors(name, category) values('G',49) into colors(name, category) values('G',50) into colors(name, category) values('G',51) into colors(name, category) values('G',52) into colors(name, category) values('G',53) into colors(name, category) values('G',54) into colors(name, category) values('G',55) into colors(name, category) values('G',56) into colors(name, category) values('G',57) into colors(name, category) values('G',58) into colors(name, category) values('G',59) into colors(name, category) values('G',60) into colors(name, category) values('G',61) into colors(name, category) values('G',62) into colors(name, category) values('G',63) into colors(name, category) values('G',64) into colors(nam e, category) values('G',65) into colors(name, category) values('G',66) into colors(name, category) values('G',67) into colors(name, category) values('G',68) into colors(name, category) values('G',69) into colors(name, category) values('G',70) into colors(name, category) values('G',71) into colors(name, category) values('G',72) into colors(name, category) values('G',73) into colors(name, category) values('G',74) into colors(name, category) values('G',75) into colors(name, category) values('G',76) into colors(name, category) values('G',77) into colors(name, category) values('G',78) into colors(name, category) values('G',79) into colors(name, category) values('G',80) into colors(name, category) values('G',81) into colors(name, category) values('G',82) into colors(name, category) values('G',83) into colors(name, category) values('G',84) into colors(name, category) values('G',85) into colors(name, category) values('G',86) into colors(name, category) values('G',87) into colors(name, categ ory) values('G',88) into colors(name, category) values('G',89) into colors(name, category) values('G',90) into colors(name, category) values('G',91) into colors(name, category) values('G',92) into colors(name, category) values('G',93) into colors(name, category) values('G',94) into colors(name, category) values('G',95) into colors(name, category) values('G',96) into colors(name, category) values('G',97) into colors(name, category) values('G',98) into colors(name, category) values('G',99) into colors(name, category) values('G',100) into colors(name, category) values('G',101) into colors(name, category) values('G',102) into colors(name, category) values('G',103) into colors(name, category) values('G',104) into colors(name, category) values('G',105) into colors(name, category) values('G',106) into colors(name, category) values('G',107) into colors(name, category) values('G',108) into colors(name, category) values('G',109) into colors(name, category) values('G',110) into colors(name, ca tegory) values('G',111) into colors(name, category) values('G',112) into colors(name, category) values('G',113) into colors(name, category) values('G',114) into colors(name, category) values('G',115) into colors(name, category) values('G',116) into colors(name, category) values('G',117) into colors(name, category) values('G',118) into colors(name, category) values('G',119) into colors(name, category) values('G',120) into colors(name, category) values('G',121) into colors(name, category) values('G',122) into colors(name, category) values('G',123) into colors(name, category) values('G',124) into colors(name, category) values('G',125) into colors(name, category) values('G',126) into colors(name, category) values('G',127) into colors(name, category) values('G',128) into colors(name, category) values('G',129) into colors(name, category) values('G',130) into colors(name, category) values('G',131) into colors(name, category) values('G',132) into colors(name, category) values('G',133) into colors(name, category) values('G',134) into colors(name, category) values('G',135) into colors(name, category) values('G',136) into colors(name, category) values('G',137) into colors(name, category) values('G',138) into colors(name, category) values('G',139) into colors(name, category) values('G',140) into colors(name, category) values('G',141) into colors(name, category) values('G',142) into colors(name, category) values('G',143) into colors(name, category) values('G',144) into colors(name, category) values('G',145) into colors(name, category) values('G',146) into colors(name, category) values('G',147) into colors(name, category) values('G',148) into colors(name, category) values('G',149) into colors(name, category) values('G',150) into colors(name, category) values('G',151) into colors(name, category) values('G',152) into colors(name, category) values('G',153) into colors(name, category) values('G',154) into colors(name, category) values('G',155) into colors(name, category) values ('G',156) into colors(name, category) values('G',157) into colors(name, category) values('G',158) into colors(name, category) values('G',159) into colors(name, category) values('G',160) into colors(name, category) values('G',161) into colors(name, category) values('G',162) into colors(name, category) values('G',163) into colors(name, category) values('G',164) into colors(name, category) values('G',165) into colors(name, category) values('G',166) into colors(name, category) values('G',167) into colors(name, category) values('G',168) into colors(name, category) values('G',169) into colors(name, category) values('G',170) into colors(name, category) values('G',171) into colors(name, category) values('G',172) into colors(name, category) values('G',173) into colors(name, category) values('G',174) into colors(name, category) values('G',175) into colors(name, category) values('G',176) into colors(name, category) values('G',177) into colors(name, category) values('G',178) into colors(name, c ategory) values('G',179) into colors(name, category) values('G',180) into colors(name, category) values('G',181) into colors(name, category) values('G',182) into colors(name, category) values('G',183) into colors(name, category) values('G',184) into colors(name, category) values('G',185) into colors(name, category) values('G',186) into colors(name, category) values('G',187) into colors(name, category) values('G',188) into colors(name, category) values('G',189) into colors(name, category) values('G',190) into colors(name, category) values('G',191) into colors(name, category) values('G',192) into colors(name, category) values('G',193) into colors(name, category) values('G',194) into colors(name, category) values('G',195) into colors(name, category) values('G',196) into colors(name, category) values('G',197) into colors(name, category) values('G',198) into colors(name, category) values('G',199) into colors(name, category) values('G',200) into colors(name, category) values('G',201) into colors(name, category) values('G',202) into colors(name, category) values('G',203) into colors(name, category) values('G',204) into colors(name, category) values('G',205) into colors(name, category) values('G',206) into colors(name, category) values('G',207) into colors(name, category) values('G',208) into colors(name, category) values('G',209) into colors(name, category) values('G',210) into colors(name, category) values('G',211) into colors(name, category) values('G',212) into colors(name, category) values('G',213) into colors(name, category) values('G',214) into colors(name, category) values('G',215) into colors(name, category) values('G',216) into colors(name, category) values('G',217) into colors(name, category) values('G',218) into colors(name, category) values('G',219) into colors(name, category) values('G',220) into colors(name, category) values('G',221) into colors(name, category) values('G',222) into colors(name, category) values('G',223) into colors(name, category) value s('G',224) into colors(name, category) values('G',225) into colors(name, category) values('G',226) into colors(name, category) values('G',227) into colors(name, category) values('G',228) into colors(name, category) values('G',229) into colors(name, category) values('G',230) into colors(name, category) values('G',231) into colors(name, category) values('G',232) into colors(name, category) values('G',233) into colors(name, category) values('G',234) into colors(name, category) values('G',235) into colors(name, category) values('G',236) into colors(name, category) values('G',237) into colors(name, category) values('G',238) into colors(name, category) values('G',239) into colors(name, category) values('G',240) into colors(name, category) values('G',241) into colors(name, category) values('G',242) into colors(name, category) values('G',243) into colors(name, category) values('G',244) into colors(name, category) values('G',245) into colors(name, category) values('G',246) into colors(name, category) values('G',247) into colors(name, category) values('G',248) into colors(name, category) values('G',249) into colors(name, category) values('G',250) into colors(name, category) values('G',251) into colors(name, category) values('G',252) into colors(name, category) values('G',253) into colors(name, category) values('G',254) into colors(name, category) values('G',255) into colors(name, category) values('G',256) into colors(name, category) values('G',257) into colors(name, category) values('G',258) into colors(name, category) values('G',259) into colors(name, category) values('G',260) into colors(name, category) values('G',261) into colors(name, category) values('G',262) into colors(name, category) values('G',263) into colors(name, category) values('G',264) into colors(name, category) values('G',265) into colors(name, category) values('G',266) into colors(name, category) values('G',267) into colors(name, category) values('G',268) into colors(name, category) values('G',269) int o colors(name, category) values('G',270) into colors(name, category) values('G',271) into colors(name, category) values('G',272) into colors(name, category) values('G',273) into colors(name, category) values('G',274) into colors(name, category) values('G',275) into colors(name, category) values('G',276) into colors(name, category) values('G',277) into colors(name, category) values('G',278) into colors(name, category) values('G',279) into colors(name, category) values('G',280) into colors(name, category) values('G',281) into colors(name, category) values('G',282) into colors(name, category) values('G',283) into colors(name, category) values('G',284) into colors(name, category) values('G',285) into colors(name, category) values('G',286) into colors(name, category) values('G',287) into colors(name, category) values('G',288) into colors(name, category) values('G',289) into colors(name, category) values('G',290) into colors(name, category) values('G',291) into colors(name, category) valu es('G',292) into colors(name, category) values('G',293) into colors(name, category) values('G',294) into colors(name, category) values('G',295) into colors(name, category) values('G',296) into colors(name, category) values('G',297) into colors(name, category) values('G',298) into colors(name, category) values('G',299) into colors(name, category) values('G',300) into colors(name, category) values('G',301) into colors(name, category) values('G',302) into colors(name, category) values('G',303) into colors(name, category) values('G',304) into colors(name, category) values('G',305) into colors(name, category) values('G',306) into colors(name, category) values('G',307) into colors(name, category) values('G',308) into colors(name, category) values('G',309) into colors(name, category) values('G',310) into colors(name, category) values('G',311) into colors(name, category) values('G',312) into colors(name, category) values('G',313) into colors(name, category) values('G',314) into colors(name, category) values('G',315) into colors(name, category) values('G',316) into colors(name, category) values('G',317) into colors(name, category) values('G',318) into colors(name, category) values('G',319) into colors(name, category) values('G',320) into colors(name, category) values('G',321) into colors(name, category) values('G',322) into colors(name, category) values('G',323) into colors(name, category) values('G',324) into colors(name, category) values('G',325) into colors(name, category) values('G',326) into colors(name, category) values('G',327) into colors(name, category) values('G',328) into colors(name, category) values('G',329) into colors(name, category) values('G',330) into colors(name, category) values('G',331) into colors(name, category) values('G',332) into colors(name, category) values('G',333) into colors(name, category) values('G',334) into colors(name, category) values('G',335) into colors(name, category) values('G',336) into colors(name, category) values('G',337) in to colors(name, category) values('G',338) into colors(name, category) values('G',339) into colors(name, category) values('G',340) into colors(name, category) values('G',341) into colors(name, category) values('G',342) into colors(name, category) values('G',343) into colors(name, category) values('G',344) into colors(name, category) values('G',345) into colors(name, category) values('G',346) into colors(name, category) values('G',347) into colors(name, category) values('G',348) into colors(name, category) values('G',349) into colors(name, category) values('G',350) into colors(name, category) values('G',351) into colors(name, category) values('G',352) into colors(name, category) values('G',353) into colors(name, category) values('G',354) into colors(name, category) values('G',355) into colors(name, category) values('G',356) into colors(name, category) values('G',357) into colors(name, category) values('G',358) into colors(name, category) values('G',359) into colors(name, category) val ues('G',360) into colors(name, category) values('G',361) into colors(name, category) values('G',362) into colors(name, category) values('G',363) into colors(name, category) values('G',364) into colors(name, category) values('G',365) into colors(name, category) values('G',366) into colors(name, category) values('G',367) into colors(name, category) values('G',368) into colors(name, category) values('G',369) into colors(name, category) values('G',370) into colors(name, category) values('G',371) into colors(name, category) values('G',372) into colors(name, category) values('G',373) into colors(name, category) values('G',374) into colors(name, category) values('G',375) into colors(name, category) values('G',376) into colors(name, category) values('G',377) into colors(name, category) values('G',378) into colors(name, category) values('G',379) into colors(name, category) values('G',380) into colors(name, category) values('G',381) into colors(name, category) values('G',382) into colors(name , category) values('G',383) into colors(name, category) values('G',384) into colors(name, category) values('G',385) into colors(name, category) values('G',386) into colors(name, category) values('G',387) into colors(name, category) values('G',388) into colors(name, category) values('G',389) into colors(name, category) values('G',390) into colors(name, category) values('G',391) into colors(name, category) values('G',392) into colors(name, category) values('G',393) into colors(name, category) values('G',394) into colors(name, category) values('G',395) into colors(name, category) values('G',396) into colors(name, category) values('G',397) into colors(name, category) values('G',398) into colors(name, category) values('G',399) into colors(name, category) values('G',400) into colors(name, category) values('G',401) into colors(name, category) values('G',402) into colors(name, category) values('G',403) into colors(name, category) values('G',404) into colors(name, category) values('G',405) i nto colors(name, category) values('G',406) into colors(name, category) values('G',407) into colors(name, category) values('G',408) into colors(name, category) values('G',409) into colors(name, category) values('G',410) into colors(name, category) values('G',411) into colors(name, category) values('G',412) into colors(name, category) values('G',413) into colors(name, category) values('G',414) into colors(name, category) values('G',415) into colors(name, category) values('G',416) into colors(name, category) values('G',417) into colors(name, category) values('G',418) into colors(name, category) values('G',419) into colors(name, category) values('G',420) into colors(name, category) values('G',421) into colors(name, category) values('G',422) into colors(name, category) values('G',423) into colors(name, category) values('G',424) into colors(name, category) values('G',425) into colors(name, category) values('G',426) into colors(name, category) values('G',427) into colors(name, category) va lues('G',428) into colors(name, category) values('G',429) into colors(name, category) values('G',430) into colors(name, category) values('G',431) into colors(name, category) values('G',432) into colors(name, category) values('G',433) into colors(name, category) values('G',434) into colors(name, category) values('G',435) into colors(name, category) values('G',436) into colors(name, category) values('G',437) into colors(name, category) values('G',438) into colors(name, category) values('G',439) into colors(name, category) values('G',440) into colors(name, category) values('G',441) into colors(name, category) values('G',442) into colors(name, category) values('G',443) into colors(name, category) values('G',444) into colors(name, category) values('G',445) into colors(name, category) values('G',446) into colors(name, category) values('G',447) into colors(name, category) values('G',448) into colors(name, category) values('G',449) into colors(name, category) values('G',450) into colors(nam e, category) values('G',451) into colors(name, category) values('G',452) into colors(name, category) values('G',453) into colors(name, category) values('G',454) into colors(name, category) values('G',455) into colors(name, category) values('G',456) into colors(name, category) values('G',457) into colors(name, category) values('G',458) into colors(name, category) values('G',459) into colors(name, category) values('G',460) into colors(name, category) values('G',461) into colors(name, category) values('G',462) into colors(name, category) values('G',463) into colors(name, category) values('G',464) into colors(name, category) values('G',465) into colors(name, category) values('G',466) into colors(name, category) values('G',467) into colors(name, category) values('G',468) into colors(name, category) values('G',469) into colors(name, category) values('G',470) into colors(name, category) values('G',471) into colors(name, category) values('G',472) into colors(name, category) values('G',473) into colors(name, category) values('G',474) into colors(name, category) values('G',475) into colors(name, category) values('G',476) into colors(name, category) values('G',477) into colors(name, category) values('G',478) into colors(name, category) values('G',479) into colors(name, category) values('G',480) into colors(name, category) values('G',481) into colors(name, category) values('G',482) into colors(name, category) values('G',483) into colors(name, category) values('G',484) into colors(name, category) values('G',485) into colors(name, category) values('G',486) into colors(name, category) values('G',487) into colors(name, category) values('G',488) into colors(name, category) values('G',489) into colors(name, category) values('G',490) into colors(name, category) values('G',491) into colors(name, category) values('G',492) into colors(name, category) values('G',493) into colors(name, category) values('G',494) into colors(name, category) values('G',495) into colors(name, category) v alues('G',496) into colors(name, category) values('G',497) into colors(name, category) values('G',498) into colors(name, category) values('G',499) into colors(name, category) values('G',500) into colors(name, category) values('G',501) into colors(name, category) values('G',502) into colors(name, category) values('G',503) into colors(name, category) values('G',504) into colors(name, category) values('G',505) into colors(name, category) values('G',506) into colors(name, category) values('G',507) into colors(name, category) values('G',508) into colors(name, category) values('G',509) into colors(name, category) values('G',510) into colors(name, category) values('G',511) into colors(name, category) values('G',512) into colors(name, category) values('G',513) into colors(name, category) values('G',514) into colors(name, category) values('G',515) into colors(name, category) values('G',516) into colors(name, category) values('G',517) into colors(name, category) values('G',518) into colors(na me, category) values('G',519) into colors(name, category) values('G',520) into colors(name, category) values('G',521) into colors(name, category) values('G',522) into colors(name, category) values('G',523) into colors(name, category) values('G',524) into colors(name, category) values('G',525) into colors(name, category) values('G',526) into colors(name, category) values('G',527) into colors(name, category) values('G',528) into colors(name, category) values('G',529) into colors(name, category) values('G',530) into colors(name, category) values('G',531) into colors(name, category) values('G',532) into colors(name, category) values('G',533) into colors(name, category) values('G',534) into colors(name, category) values('G',535) into colors(name, category) values('G',536) into colors(name, category) values('G',537) into colors(name, category) values('G',538) into colors(name, category) values('G',539) into colors(name, category) values('G',540) into colors(name, category) values('G',541) into colors(name, category) values('G',542) into colors(name, category) values('G',543) into colors(name, category) values('G',544) into colors(name, category) values('G',545) into colors(name, category) values('G',546) into colors(name, category) values('G',547) into colors(name, category) values('G',548) into colors(name, category) values('G',549) into colors(name, category) values('G',550) into colors(name, category) values('G',551) into colors(name, category) values('G',552) into colors(name, category) values('G',553) into colors(name, category) values('G',554) into colors(name, category) values('G',555) into colors(name, category) values('G',556) into colors(name, category) values('G',557) into colors(name, category) values('G',558) into colors(name, category) values('G',559) into colors(name, category) values('G',560) into colors(name, category) values('G',561) into colors(name, category) values('G',562) into colors(name, category) values('G',563) into colors(name, category) values('G',564) into colors(name, category) values('G',565) into colors(name, category) values('G',566) into colors(name, category) values('G',567) into colors(name, category) values('G',568) into colors(name, category) values('G',569) into colors(name, category) values('G',570) into colors(name, category) values('G',571) into colors(name, category) values('G',572) into colors(name, category) values('G',573) into colors(name, category) values('G',574) into colors(name, category) values('G',575) into colors(name, category) values('G',576) into colors(name, category) values('G',577) into colors(name, category) values('G',578) into colors(name, category) values('G',579) into colors(name, category) values('G',580) into colors(name, category) values('G',581) into colors(name, category) values('G',582) into colors(name, category) values('G',583) into colors(name, category) values('G',584) into colors(name, category) values('G',585) into colors(name, category) values('G',586) into colors(n ame, category) values('G',587) into colors(name, category) values('G',588) into colors(name, category) values('G',589) into colors(name, category) values('G',590) into colors(name, category) values('G',591) into colors(name, category) values('G',592) into colors(name, category) values('G',593) into colors(name, category) values('G',594) into colors(name, category) values('G',595) into colors(name, category) values('G',596) into colors(name, category) values('G',597) into colors(name, category) values('G',598) into colors(name, category) values('G',599) into colors(name, category) values('G',600) into colors(name, category) values('G',601) into colors(name, category) values('G',602) into colors(name, category) values('G',603) into colors(name, category) values('G',604) into colors(name, category) values('G',605) into colors(name, category) values('G',606) into colors(name, category) values('G',607) into colors(name, category) values('G',608) into colors(name, category) values('G',609 ) into colors(name, category) values('G',610) into colors(name, category) values('G',611) into colors(name, category) values('G',612) into colors(name, category) values('G',613) into colors(name, category) values('G',614) into colors(name, category) values('G',615) into colors(name, category) values('G',616) into colors(name, category) values('G',617) into colors(name, category) values('G',618) into colors(name, category) values('G',619) into colors(name, category) values('G',620) into colors(name, category) values('G',621) into colors(name, category) values('G',622) into colors(name, category) values('G',623) into colors(name, category) values('G',624) into colors(name, category) values('G',625) into colors(name, category) values('G',626) into colors(name, category) values('G',627) into colors(name, category) values('G',628) into colors(name, category) values('G',629) into colors(name, category) values('G',630) into colors(name, category) values('G',631) into colors(name, category) values('G',632) into colors(name, category) values('G',633) into colors(name, category) values('G',634) into colors(name, category) values('G',635) into colors(name, category) values('G',636) into colors(name, category) values('G',637) into colors(name, category) values('G',638) into colors(name, category) values('G',639) into colors(name, category) values('G',640) into colors(name, category) values('G',641) into colors(name, category) values('G',642) into colors(name, category) values('G',643) into colors(name, category) values('G',644) into colors(name, category) values('G',645) into colors(name, category) values('G',646) into colors(name, category) values('G',647) into colors(name, category) values('G',648) into colors(name, category) values('G',649) into colors(name, category) values('G',650) into colors(name, category) values('G',651) into colors(name, category) values('G',652) into colors(name, category) values('G',653) into colors(name, category) values('G',654) into colors( name, category) values('G',655) into colors(name, category) values('G',656) into colors(name, category) values('G',657) into colors(name, category) values('G',658) into colors(name, category) values('G',659) into colors(name, category) values('G',660) into colors(name, category) values('G',661) into colors(name, category) values('G',662) into colors(name, category) values('G',663) into colors(name, category) values('G',664) into colors(name, category) values('G',665) into colors(name, category) values('G',666) into colors(name, category) values('G',667) into colors(name, category) values('G',668) into colors(name, category) values('G',669) into colors(name, category) values('G',670) into colors(name, category) values('G',671) into colors(name, category) values('G',672) into colors(name, category) values('G',673) into colors(name, category) values('G',674) into colors(name, category) values('G',675) into colors(name, category) values('G',676) into colors(name, category) values('G',67 7) into colors(name, category) values('G',678) into colors(name, category) values('G',679) into colors(name, category) values('G',680) into colors(name, category) values('G',681) into colors(name, category) values('G',682) into colors(name, category) values('G',683) into colors(name, category) values('G',684) into colors(name, category) values('G',685) into colors(name, category) values('G',686) into colors(name, category) values('G',687) into colors(name, category) values('G',688) into colors(name, category) values('G',689) into colors(name, category) values('G',690) into colors(name, category) values('G',691) into colors(name, category) values('G',692) into colors(name, category) values('G',693) into colors(name, category) values('G',694) into colors(name, category) values('G',695) into colors(name, category) values('G',696) into colors(name, category) values('G',697) into colors(name, category) values('G',698) into colors(name, category) values('G',699) into colors(name, category ) values('G',700) into colors(name, category) values('G',701) into colors(name, category) values('G',702) into colors(name, category) values('G',703) into colors(name, category) values('G',704) into colors(name, category) values('G',705) into colors(name, category) values('G',706) into colors(name, category) values('G',707) into colors(name, category) values('G',708) into colors(name, category) values('G',709) into colors(name, category) values('G',710) into colors(name, category) values('G',711) into colors(name, category) values('G',712) into colors(name, category) values('G',713) into colors(name, category) values('G',714) into colors(name, category) values('G',715) into colors(name, category) values('G',716) into colors(name, category) values('G',717) into colors(name, category) values('G',718) into colors(name, category) values('G',719) into colors(name, category) values('G',720) into colors(name, category) values('G',721) into colors(name, category) values('G',722) into colors (name, category) values('G',723) into colors(name, category) values('G',724) into colors(name, category) values('G',725) into colors(name, category) values('G',726) into colors(name, category) values('G',727) into colors(name, category) values('G',728) into colors(name, category) values('G',729) into colors(name, category) values('G',730) into colors(name, category) values('G',731) into colors(name, category) values('G',732) into colors(name, category) values('G',733) into colors(name, category) values('G',734) into colors(name, category) values('G',735) into colors(name, category) values('G',736) into colors(name, category) values('G',737) into colors(name, category) values('G',738) into colors(name, category) values('G',739) into colors(name, category) values('G',740) into colors(name, category) values('G',741) into colors(name, category) values('G',742) into colors(name, category) values('G',743) into colors(name, category) values('G',744) into colors(name, category) values('G',7 45) into colors(name, category) values('G',746) into colors(name, category) values('G',747) into colors(name, category) values('G',748) into colors(name, category) values('G',749) into colors(name, category) values('G',750) into colors(name, category) values('G',751) into colors(name, category) values('G',752) into colors(name, category) values('G',753) into colors(name, category) values('G',754) into colors(name, category) values('G',755) into colors(name, category) values('G',756) into colors(name, category) values('G',757) into colors(name, category) values('G',758) into colors(name, category) values('G',759) into colors(name, category) values('G',760) into colors(name, category) values('G',761) into colors(name, category) values('G',762) into colors(name, category) values('G',763) into colors(name, category) values('G',764) into colors(name, category) values('G',765) into colors(name, category) values('G',766) into colors(name, category) values('G',767) into colors(name, categor y) values('G',768) into colors(name, category) values('G',769) into colors(name, category) values('G',770) into colors(name, category) values('G',771) into colors(name, category) values('G',772) into colors(name, category) values('G',773) into colors(name, category) values('G',774) into colors(name, category) values('G',775) into colors(name, category) values('G',776) into colors(name, category) values('G',777) into colors(name, category) values('G',778) into colors(name, category) values('G',779) into colors(name, category) values('G',780) into colors(name, category) values('G',781) into colors(name, category) values('G',782) into colors(name, category) values('G',783) into colors(name, category) values('G',784) into colors(name, category) values('G',785) into colors(name, category) values('G',786) into colors(name, category) values('G',787) into colors(name, category) values('G',788) into colors(name, category) values('G',789) into colors(name, category) values('G',790) into color s(name, category) values('G',791) into colors(name, category) values('G',792) into colors(name, category) values('G',793) into colors(name, category) values('G',794) into colors(name, category) values('G',795) into colors(name, category) values('G',796) into colors(name, category) values('G',797) into colors(name, category) values('G',798) into colors(name, category) values('G',799) into colors(name, category) values('G',800) into colors(name, category) values('G',801) into colors(name, category) values('G',802) into colors(name, category) values('G',803) into colors(name, category) values('G',804) into colors(name, category) values('G',805) into colors(name, category) values('G',806) into colors(name, category) values('G',807) into colors(name, category) values('G',808) into colors(name, category) values('G',809) into colors(name, category) values('G',810) into colors(name, category) values('G',811) into colors(name, category) values('G',812) into colors(name, category) values('G', 813) into colors(name, category) values('G',814) into colors(name, category) values('G',815) into colors(name, category) values('G',816) into colors(name, category) values('G',817) into colors(name, category) values('G',818) into colors(name, category) values('G',819) into colors(name, category) values('G',820) into colors(name, category) values('G',821) into colors(name, category) values('G',822) into colors(name, category) values('G',823) into colors(name, category) values('G',824) into colors(name, category) values('G',825) into colors(name, category) values('G',826) into colors(name, category) values('G',827) into colors(name, category) values('G',828) into colors(name, category) values('G',829) into colors(name, category) values('G',830) into colors(name, category) values('G',831) into colors(name, category) values('G',832) into colors(name, category) values('G',833) into colors(name, category) values('G',834) into colors(name, category) values('G',835) into colors(name, catego ry) values('G',836) into colors(name, category) values('G',837) into colors(name, category) values('G',838) into colors(name, category) values('G',839) into colors(name, category) values('G',840) into colors(name, category) values('G',841) into colors(name, category) values('G',842) into colors(name, category) values('G',843) into colors(name, category) values('G',844) into colors(name, category) values('G',845) into colors(name, category) values('G',846) into colors(name, category) values('G',847) into colors(name, category) values('G',848) into colors(name, category) values('G',849) into colors(name, category) values('G',850) into colors(name, category) values('G',851) into colors(name, category) values('G',852) into colors(name, category) values('G',853) into colors(name, category) values('G',854) into colors(name, category) values('G',855) into colors(name, category) values('G',856) into colors(name, category) values('G',857) into colors(name, category) values('G',858) into colo rs(name, category) values('G',859) into colors(name, category) values('G',860) into colors(name, category) values('G',861) into colors(name, category) values('G',862) into colors(name, category) values('G',863) into colors(name, category) values('G',864) into colors(name, category) values('G',865) into colors(name, category) values('G',866) into colors(name, category) values('G',867) into colors(name, category) values('G',868) into colors(name, category) values('G',869) into colors(name, category) values('G',870) into colors(name, category) values('G',871) into colors(name, category) values('G',872) into colors(name, category) values('G',873) into colors(name, category) values('G',874) into colors(name, category) values('G',875) into colors(name, category) values('G',876) into colors(name, category) values('G',877) into colors(name, category) values('G',878) into colors(name, category) values('G',879) into colors(name, category) values('G',880) into colors(name, category) values('G' ,881) into colors(name, category) values('G',882) into colors(name, category) values('G',883) into colors(name, category) values('G',884) into colors(name, category) values('G',885) into colors(name, category) values('G',886) into colors(name, category) values('G',887) into colors(name, category) values('G',888) into colors(name, category) values('G',889) into colors(name, category) values('G',890) into colors(name, category) values('G',891) into colors(name, category) values('G',892) into colors(name, category) values('G',893) into colors(name, category) values('G',894) into colors(name, category) values('G',895) into colors(name, category) values('G',896) into colors(name, category) values('G',897) into colors(name, category) values('G',898) into colors(name, category) values('G',899) into colors(name, category) values('G',900) into colors(name, category) values('G',901) into colors(name, category) values('G',902) into colors(name, category) values('G',903) into colors(name, categ ory) values('G',904) into colors(name, category) values('G',905) into colors(name, category) values('G',906) into colors(name, category) values('G',907) into colors(name, category) values('G',908) into colors(name, category) values('G',909) into colors(name, category) values('G',910) into colors(name, category) values('G',911) into colors(name, category) values('G',912) into colors(name, category) values('G',913) into colors(name, category) values('G',914) into colors(name, category) values('G',915) into colors(name, category) values('G',916) into colors(name, category) values('G',917) into colors(name, category) values('G',918) into colors(name, category) values('G',919) into colors(name, category) values('G',920) into colors(name, category) values('G',921) into colors(name, category) values('G',922) into colors(name, category) values('G',923) into colors(name, category) values('G',924) into colors(name, category) values('G',925) into colors(name, category) values('G',926) into col ors(name, category) values('G',927) into colors(name, category) values('G',928) into colors(name, category) values('G',929) into colors(name, category) values('G',930) into colors(name, category) values('G',931) into colors(name, category) values('G',932) into colors(name, category) values('G',933) into colors(name, category) values('G',934) into colors(name, category) values('G',935) into colors(name, category) values('G',936) into colors(name, category) values('G',937) into colors(name, category) values('G',938) into colors(name, category) values('G',939) into colors(name, category) values('G',940) into colors(name, category) values('G',941) into colors(name, category) values('G',942) into colors(name, category) values('G',943) into colors(name, category) values('G',944) into colors(name, category) values('G',945) into colors(name, category) values('G',946) into colors(name, category) values('G',947) into colors(name, category) values('G',948) into colors(name, category) values('G ',949) into colors(name, category) values('G',950) into colors(name, category) values('G',951) into colors(name, category) values('G',952) into colors(name, category) values('G',953) into colors(name, category) values('G',954) into colors(name, category) values('G',955) into colors(name, category) values('G',956) into colors(name, category) values('G',957) into colors(name, category) values('G',958) into colors(name, category) values('G',959) into colors(name, category) values('G',960) into colors(name, category) values('G',961) into colors(name, category) values('G',962) into colors(name, category) values('G',963) into colors(name, category) values('G',964) into colors(name, category) values('G',965) into colors(name, category) values('G',966) into colors(name, category) values('G',967) into colors(name, category) values('G',968) into colors(name, category) values('G',969) into colors(name, category) values('G',970) into colors(name, category) values('G',971) into colors(name, cate gory) values('G',972) into colors(name, category) values('G',973) into colors(name, category) values('G',974) into colors(name, category) values('G',975) into colors(name, category) values('G',976) into colors(name, category) values('G',977) into colors(name, category) values('G',978) into colors(name, category) values('G',979) into colors(name, category) values('G',980) into colors(name, category) values('G',981) into colors(name, category) values('G',982) into colors(name, category) values('G',983) into colors(name, category) values('G',984) into colors(name, category) values('G',985) into colors(name, category) values('G',986) into colors(name, category) values('G',987) into colors(name, category) values('G',988) into colors(name, category) values('G',989) into colors(name, category) values('G',990) into colors(name, category) values('G',991) into colors(name, category) values('G',992) into colors(name, category) values('G',993) into colors(name, category) values('G',994) into co lors(name, category) values('G',995) into colors(name, category) values('G',996) select * from dual", sql.get(0)); + Assert.assertEquals("Wrong stmt", "insert all into colors(name, category) values('G',997) into colors(name, category) values('G',998) into colors(name, category) values('G',999) select * from dual", sql.get(1)); + + sqlGenerator = + new TxnHandler.SQLGenerator(TxnHandler.DatabaseProduct.MYSQL, conf); + rows.clear(); + rows.add("'yellow', 1"); + sql = sqlGenerator.createInsertValuesStmt("colors(name, category)", rows); + Assert.assertEquals("Number of stmts", 1, sql.size()); + Assert.assertEquals("Wrong stmt", "insert into colors(name, category) values('yellow', 1)", sql.get(0)); + rows.add("'red', 2"); + rows.add("'orange', 3"); + sql = sqlGenerator.createInsertValuesStmt("colors(name, category)", rows); + Assert.assertEquals("Number of stmts", 1, sql.size()); + Assert.assertEquals("Wrong stmt", "insert into colors(name, category) values('yellow', 1),('red', 2),('orange', 3)", sql.get(0)); + for(int i = 0; i < conf.getIntVar(HiveConf.ConfVars.METASTORE_DIRECT_SQL_MAX_ELEMENTS_VALUES_CLAUSE); i++) { + rows.add("\'G\'," + i); + } + sql = sqlGenerator.createInsertValuesStmt("colors(name, category)", rows); + Assert.assertEquals("Number of stmts", 2, sql.size()); + Assert.assertEquals("Wrong stmt", "insert into colors(name, category) values('yellow', 1),('red', 2),('orange', 3),('G',0),('G',1),('G',2),('G',3),('G',4),('G',5),('G',6),('G',7),('G',8),('G',9),('G',10),('G',11),('G',12),('G',13),('G',14),('G',15),('G',16),('G',17),('G',18),('G',19),('G',20),('G',21),('G',22),('G',23),('G',24),('G',25),('G',26),('G',27),('G',28),('G',29),('G',30),('G',31),('G',32),('G',33),('G',34),('G',35),('G',36),('G',37),('G',38),('G',39),('G',40),('G',41),('G',42),('G',43),('G',44),('G',45),('G',46),('G',47),('G',48),('G',49),('G',50),('G',51),('G',52),('G',53),('G',54),('G',55),('G',56),('G',57),('G',58),('G',59),('G',60),('G',61),('G',62),('G',63),('G',64),('G',65),('G',66),('G',67),('G',68),('G',69),('G',70),('G',71),('G',72),('G',73),('G',74),('G',75),('G',76),('G',77),('G',78),('G',79),('G',80),('G',81),('G',82),('G',83),('G',84),('G',85),('G',86),('G',87),('G',88),('G',89),('G',90),('G',91),('G',92),('G',93),('G',94),('G',95),('G',96),('G',97),('G',9 8),('G',99),('G',100),('G',101),('G',102),('G',103),('G',104),('G',105),('G',106),('G',107),('G',108),('G',109),('G',110),('G',111),('G',112),('G',113),('G',114),('G',115),('G',116),('G',117),('G',118),('G',119),('G',120),('G',121),('G',122),('G',123),('G',124),('G',125),('G',126),('G',127),('G',128),('G',129),('G',130),('G',131),('G',132),('G',133),('G',134),('G',135),('G',136),('G',137),('G',138),('G',139),('G',140),('G',141),('G',142),('G',143),('G',144),('G',145),('G',146),('G',147),('G',148),('G',149),('G',150),('G',151),('G',152),('G',153),('G',154),('G',155),('G',156),('G',157),('G',158),('G',159),('G',160),('G',161),('G',162),('G',163),('G',164),('G',165),('G',166),('G',167),('G',168),('G',169),('G',170),('G',171),('G',172),('G',173),('G',174),('G',175),('G',176),('G',177),('G',178),('G',179),('G',180),('G',181),('G',182),('G',183),('G',184),('G',185),('G',186),('G',187),('G',188),('G',189),('G',190),('G',191),('G',192),('G',193),('G',194),('G',195),('G',196),('G',197),('G', 198),('G',199),('G',200),('G',201),('G',202),('G',203),('G',204),('G',205),('G',206),('G',207),('G',208),('G',209),('G',210),('G',211),('G',212),('G',213),('G',214),('G',215),('G',216),('G',217),('G',218),('G',219),('G',220),('G',221),('G',222),('G',223),('G',224),('G',225),('G',226),('G',227),('G',228),('G',229),('G',230),('G',231),('G',232),('G',233),('G',234),('G',235),('G',236),('G',237),('G',238),('G',239),('G',240),('G',241),('G',242),('G',243),('G',244),('G',245),('G',246),('G',247),('G',248),('G',249),('G',250),('G',251),('G',252),('G',253),('G',254),('G',255),('G',256),('G',257),('G',258),('G',259),('G',260),('G',261),('G',262),('G',263),('G',264),('G',265),('G',266),('G',267),('G',268),('G',269),('G',270),('G',271),('G',272),('G',273),('G',274),('G',275),('G',276),('G',277),('G',278),('G',279),('G',280),('G',281),('G',282),('G',283),('G',284),('G',285),('G',286),('G',287),('G',288),('G',289),('G',290),('G',291),('G',292),('G',293),('G',294),('G',295),('G',296),('G',297),(' G',298),('G',299),('G',300),('G',301),('G',302),('G',303),('G',304),('G',305),('G',306),('G',307),('G',308),('G',309),('G',310),('G',311),('G',312),('G',313),('G',314),('G',315),('G',316),('G',317),('G',318),('G',319),('G',320),('G',321),('G',322),('G',323),('G',324),('G',325),('G',326),('G',327),('G',328),('G',329),('G',330),('G',331),('G',332),('G',333),('G',334),('G',335),('G',336),('G',337),('G',338),('G',339),('G',340),('G',341),('G',342),('G',343),('G',344),('G',345),('G',346),('G',347),('G',348),('G',349),('G',350),('G',351),('G',352),('G',353),('G',354),('G',355),('G',356),('G',357),('G',358),('G',359),('G',360),('G',361),('G',362),('G',363),('G',364),('G',365),('G',366),('G',367),('G',368),('G',369),('G',370),('G',371),('G',372),('G',373),('G',374),('G',375),('G',376),('G',377),('G',378),('G',379),('G',380),('G',381),('G',382),('G',383),('G',384),('G',385),('G',386),('G',387),('G',388),('G',389),('G',390),('G',391),('G',392),('G',393),('G',394),('G',395),('G',396),('G',397) ,('G',398),('G',399),('G',400),('G',401),('G',402),('G',403),('G',404),('G',405),('G',406),('G',407),('G',408),('G',409),('G',410),('G',411),('G',412),('G',413),('G',414),('G',415),('G',416),('G',417),('G',418),('G',419),('G',420),('G',421),('G',422),('G',423),('G',424),('G',425),('G',426),('G',427),('G',428),('G',429),('G',430),('G',431),('G',432),('G',433),('G',434),('G',435),('G',436),('G',437),('G',438),('G',439),('G',440),('G',441),('G',442),('G',443),('G',444),('G',445),('G',446),('G',447),('G',448),('G',449),('G',450),('G',451),('G',452),('G',453),('G',454),('G',455),('G',456),('G',457),('G',458),('G',459),('G',460),('G',461),('G',462),('G',463),('G',464),('G',465),('G',466),('G',467),('G',468),('G',469),('G',470),('G',471),('G',472),('G',473),('G',474),('G',475),('G',476),('G',477),('G',478),('G',479),('G',480),('G',481),('G',482),('G',483),('G',484),('G',485),('G',486),('G',487),('G',488),('G',489),('G',490),('G',491),('G',492),('G',493),('G',494),('G',495),('G',496),('G',4 97),('G',498),('G',499),('G',500),('G',501),('G',502),('G',503),('G',504),('G',505),('G',506),('G',507),('G',508),('G',509),('G',510),('G',511),('G',512),('G',513),('G',514),('G',515),('G',516),('G',517),('G',518),('G',519),('G',520),('G',521),('G',522),('G',523),('G',524),('G',525),('G',526),('G',527),('G',528),('G',529),('G',530),('G',531),('G',532),('G',533),('G',534),('G',535),('G',536),('G',537),('G',538),('G',539),('G',540),('G',541),('G',542),('G',543),('G',544),('G',545),('G',546),('G',547),('G',548),('G',549),('G',550),('G',551),('G',552),('G',553),('G',554),('G',555),('G',556),('G',557),('G',558),('G',559),('G',560),('G',561),('G',562),('G',563),('G',564),('G',565),('G',566),('G',567),('G',568),('G',569),('G',570),('G',571),('G',572),('G',573),('G',574),('G',575),('G',576),('G',577),('G',578),('G',579),('G',580),('G',581),('G',582),('G',583),('G',584),('G',585),('G',586),('G',587),('G',588),('G',589),('G',590),('G',591),('G',592),('G',593),('G',594),('G',595),('G',596),('G ',597),('G',598),('G',599),('G',600),('G',601),('G',602),('G',603),('G',604),('G',605),('G',606),('G',607),('G',608),('G',609),('G',610),('G',611),('G',612),('G',613),('G',614),('G',615),('G',616),('G',617),('G',618),('G',619),('G',620),('G',621),('G',622),('G',623),('G',624),('G',625),('G',626),('G',627),('G',628),('G',629),('G',630),('G',631),('G',632),('G',633),('G',634),('G',635),('G',636),('G',637),('G',638),('G',639),('G',640),('G',641),('G',642),('G',643),('G',644),('G',645),('G',646),('G',647),('G',648),('G',649),('G',650),('G',651),('G',652),('G',653),('G',654),('G',655),('G',656),('G',657),('G',658),('G',659),('G',660),('G',661),('G',662),('G',663),('G',664),('G',665),('G',666),('G',667),('G',668),('G',669),('G',670),('G',671),('G',672),('G',673),('G',674),('G',675),('G',676),('G',677),('G',678),('G',679),('G',680),('G',681),('G',682),('G',683),('G',684),('G',685),('G',686),('G',687),('G',688),('G',689),('G',690),('G',691),('G',692),('G',693),('G',694),('G',695),('G',696), ('G',697),('G',698),('G',699),('G',700),('G',701),('G',702),('G',703),('G',704),('G',705),('G',706),('G',707),('G',708),('G',709),('G',710),('G',711),('G',712),('G',713),('G',714),('G',715),('G',716),('G',717),('G',718),('G',719),('G',720),('G',721),('G',722),('G',723),('G',724),('G',725),('G',726),('G',727),('G',728),('G',729),('G',730),('G',731),('G',732),('G',733),('G',734),('G',735),('G',736),('G',737),('G',738),('G',739),('G',740),('G',741),('G',742),('G',743),('G',744),('G',745),('G',746),('G',747),('G',748),('G',749),('G',750),('G',751),('G',752),('G',753),('G',754),('G',755),('G',756),('G',757),('G',758),('G',759),('G',760),('G',761),('G',762),('G',763),('G',764),('G',765),('G',766),('G',767),('G',768),('G',769),('G',770),('G',771),('G',772),('G',773),('G',774),('G',775),('G',776),('G',777),('G',778),('G',779),('G',780),('G',781),('G',782),('G',783),('G',784),('G',785),('G',786),('G',787),('G',788),('G',789),('G',790),('G',791),('G',792),('G',793),('G',794),('G',795),('G',79 6),('G',797),('G',798),('G',799),('G',800),('G',801),('G',802),('G',803),('G',804),('G',805),('G',806),('G',807),('G',808),('G',809),('G',810),('G',811),('G',812),('G',813),('G',814),('G',815),('G',816),('G',817),('G',818),('G',819),('G',820),('G',821),('G',822),('G',823),('G',824),('G',825),('G',826),('G',827),('G',828),('G',829),('G',830),('G',831),('G',832),('G',833),('G',834),('G',835),('G',836),('G',837),('G',838),('G',839),('G',840),('G',841),('G',842),('G',843),('G',844),('G',845),('G',846),('G',847),('G',848),('G',849),('G',850),('G',851),('G',852),('G',853),('G',854),('G',855),('G',856),('G',857),('G',858),('G',859),('G',860),('G',861),('G',862),('G',863),('G',864),('G',865),('G',866),('G',867),('G',868),('G',869),('G',870),('G',871),('G',872),('G',873),('G',874),('G',875),('G',876),('G',877),('G',878),('G',879),('G',880),('G',881),('G',882),('G',883),('G',884),('G',885),('G',886),('G',887),('G',888),('G',889),('G',890),('G',891),('G',892),('G',893),('G',894),('G',895),('G' ,896),('G',897),('G',898),('G',899),('G',900),('G',901),('G',902),('G',903),('G',904),('G',905),('G',906),('G',907),('G',908),('G',909),('G',910),('G',911),('G',912),('G',913),('G',914),('G',915),('G',916),('G',917),('G',918),('G',919),('G',920),('G',921),('G',922),('G',923),('G',924),('G',925),('G',926),('G',927),('G',928),('G',929),('G',930),('G',931),('G',932),('G',933),('G',934),('G',935),('G',936),('G',937),('G',938),('G',939),('G',940),('G',941),('G',942),('G',943),('G',944),('G',945),('G',946),('G',947),('G',948),('G',949),('G',950),('G',951),('G',952),('G',953),('G',954),('G',955),('G',956),('G',957),('G',958),('G',959),('G',960),('G',961),('G',962),('G',963),('G',964),('G',965),('G',966),('G',967),('G',968),('G',969),('G',970),('G',971),('G',972),('G',973),('G',974),('G',975),('G',976),('G',977),('G',978),('G',979),('G',980),('G',981),('G',982),('G',983),('G',984),('G',985),('G',986),('G',987),('G',988),('G',989),('G',990),('G',991),('G',992),('G',993),('G',994),('G',995),( 'G',996)", sql.get(0)); + Assert.assertEquals("Wrong stmt", "insert into colors(name, category) values('G',997),('G',998),('G',999)", sql.get(1)); + } @Before public void setUp() throws Exception {
