List:Commits« Previous MessageNext Message »
From:Ole John Aske Date:October 11 2010 12:16pm
Subject:bzr commit into mysql-5.1-telco-7.0-spj-scan-vs-scan branch
(ole.john.aske:3310)
View as plain text  
#At file:///net/fimafeng09/export/home/tmp/oleja/mysql/mysql-5.1-telco-7.0-spj-scan-scan/ based on revid:ole.john.aske@stripped

 3310 Ole John Aske	2010-10-11
      spj-svs: Enhanced RQG test to partition test tables on only some of the column in the primary key.
        Also:
      
       - create a mix of unique / non-unique indexes in order instead of only unique indexes.
       - Added indexes on (var)char columns for some of the tables to improve testcoverage.

    modified:
      storage/ndb/test/rqg/runall.sh
      storage/ndb/test/rqg/spj_test.yy
=== modified file 'storage/ndb/test/rqg/runall.sh'
--- a/storage/ndb/test/rqg/runall.sh	2010-07-13 13:37:10 +0000
+++ b/storage/ndb/test/rqg/runall.sh	2010-10-11 12:16:22 +0000
@@ -57,6 +57,7 @@ BEGIN
 
   declare tabname varchar(255);
   declare indextype varchar(32);
+  declare _unique varchar(16);
   declare done integer default 0;
   declare cnt integer default 0;
   declare c cursor for 
@@ -85,40 +86,123 @@ BEGIN
        EXECUTE stmt;
 
        set cnt = cnt+1;
-       if ((cnt%2) = 0) then
-         set indextype = 'USING HASH';
-       else
-         set indextype = '';
+
+       set _unique = '';
+       set indextype = '';
+       ## Create a mix if 'CREATE INDEX' | 'CREATE UNIQUE INDEX [USING HASH] '
+
+       if ((cnt%3) <> 0) then
+         set _unique = ' UNIQUE';
+
+         if ((cnt%2) = 0) then
+           set indextype = ' USING HASH';
+         else
+           set indextype = '';
+         end if;
        end if;
 
-       ## Add some composite unique indexes
-       if tabname > 'O' then
-          set @ddl = CONCAT('CREATE UNIQUE INDEX ix1 ', indextype,
+       ## Add some composite, possibly unique, indexes
+       if tabname > 'T' then
+          set @ddl = CONCAT('DROP INDEX col_varchar_10_unique ON ', dstdb, '.', tabname);
+          PREPARE stmt from @ddl;
+          EXECUTE stmt;
+
+          set @ddl = CONCAT('CREATE', _unique, ' INDEX ix1 ', indextype,
+                            ' ON ', dstdb, '.', tabname, 
+                            '(col_char_16,col_char_16_unique)');
+          PREPARE stmt from @ddl;
+          EXECUTE stmt;
+          set @ddl = CONCAT('CREATE', _unique, ' INDEX ix2 ', indextype,
+                            ' ON ', dstdb, '.', tabname, 
+                            '(col_varchar_256,col_varchar_10_unique)');
+          PREPARE stmt from @ddl;
+          EXECUTE stmt;
+
+       elseif tabname > 'O' then
+          set @ddl = CONCAT('CREATE', _unique, ' INDEX ix1 ', indextype,
                             ' ON ', dstdb, '.', tabname, 
                             '(col_int,col_int_unique)');
           PREPARE stmt from @ddl;
           EXECUTE stmt;
-          set @ddl = CONCAT('CREATE UNIQUE INDEX ix2 ', indextype,
+          set @ddl = CONCAT('CREATE', _unique, ' INDEX ix2 ', indextype,
                             ' ON ', dstdb, '.', tabname, 
                             '(col_int_key,col_int_unique)');
           PREPARE stmt from @ddl;
           EXECUTE stmt;
 
        elseif tabname > 'H' then
-          set @ddl = CONCAT('CREATE UNIQUE INDEX ix3 ', indextype,
+          set @ddl = CONCAT('CREATE', _unique, ' INDEX ix3 ', indextype,
                             ' ON ', dstdb, '.', tabname, 
                             '(col_int,col_int_key,col_int_unique)');
           PREPARE stmt from @ddl;
           EXECUTE stmt;
 
        else
-          set @ddl = CONCAT('CREATE UNIQUE INDEX col_int_unique ', indextype,
+          set @ddl = CONCAT('CREATE', _unique, ' INDEX ix1', indextype,
                             ' ON ', dstdb, '.', tabname, 
                             '(col_int_unique)');
           PREPARE stmt from @ddl;
           EXECUTE stmt;
        end if;
 
+       ## Modify primary key & partition for some tables
+       if ((cnt%3) = 0) then
+          set @ddl = CONCAT('UPDATE ', srcdb, '.', tabname, ' SET col_int=0 WHERE col_int IS NULL');
+          PREPARE stmt from @ddl;
+          EXECUTE stmt;
+           set @ddl = CONCAT('ALTER TABLE ', dstdb, '.', tabname,
+                               '  CHANGE COLUMN pk pk INT(11) NOT NULL',
+                               ', DROP PRIMARY KEY',
+                               ', ADD PRIMARY KEY ', '(col_int,pk)');
+           PREPARE stmt from @ddl;
+           EXECUTE stmt;
+           set @ddl = CONCAT('ALTER TABLE ', dstdb, '.', tabname, ' PARTITION BY KEY(col_int)');
+           PREPARE stmt from @ddl;
+           EXECUTE stmt;
+
+        elseif ((cnt%5) = 0) then
+           set @ddl = CONCAT('UPDATE ', srcdb, '.', tabname, ' SET col_int=0 WHERE col_int IS NULL');
+           PREPARE stmt from @ddl;
+           EXECUTE stmt;
+           set @ddl = CONCAT('ALTER TABLE ', dstdb, '.', tabname,
+                               '  CHANGE COLUMN pk pk INT(11) NOT NULL',
+                               ', DROP PRIMARY KEY',
+                               ', ADD PRIMARY KEY', indextype, '(col_int,pk)');
+           PREPARE stmt from @ddl;
+           EXECUTE stmt;
+           set @ddl = CONCAT('ALTER TABLE ', dstdb, '.', tabname, ' PARTITION BY KEY(pk)');
+           PREPARE stmt from @ddl;
+           EXECUTE stmt;
+
+        elseif ((cnt%7) = 0) then
+           set @ddl = CONCAT('DELETE FROM ', srcdb, '.', tabname, ' WHERE col_char_16 IS NULL');
+           PREPARE stmt from @ddl;
+           EXECUTE stmt;
+           set @ddl = CONCAT('ALTER TABLE ', dstdb, '.', tabname,
+                               '  CHANGE COLUMN pk pk INT(11) NOT NULL',
+                               ', DROP PRIMARY KEY',
+                               ', ADD PRIMARY KEY', indextype, '(col_char_16,pk)');
+           PREPARE stmt from @ddl;
+           EXECUTE stmt;
+           set @ddl = CONCAT('ALTER TABLE ', dstdb, '.', tabname, ' PARTITION BY KEY(col_char_16)');
+           PREPARE stmt from @ddl;
+           EXECUTE stmt;
+
+        elseif ((cnt%11) = 0) then
+           set @ddl = CONCAT('DELETE FROM ', srcdb, '.', tabname, ' WHERE col_varchar_10 IS NULL');
+           PREPARE stmt from @ddl;
+           EXECUTE stmt;
+           set @ddl = CONCAT('ALTER TABLE ', dstdb, '.', tabname,
+                               '  CHANGE COLUMN pk pk INT(11) NOT NULL',
+                               ', DROP PRIMARY KEY',
+                               ', ADD PRIMARY KEY', indextype, '(pk,col_varchar_256)');
+           PREPARE stmt from @ddl;
+           EXECUTE stmt;
+           set @ddl = CONCAT('ALTER TABLE ', dstdb, '.', tabname, ' PARTITION BY KEY(col_varchar_256)');
+           PREPARE stmt from @ddl;
+           EXECUTE stmt;
+        end if;
+
        set @ddl = CONCAT('INSERT INTO ', dstdb, '.', tabname, 
                          ' SELECT * FROM ', srcdb, '.', tabname);
        PREPARE stmt from @ddl;
@@ -181,7 +265,7 @@ check_query(){
 $ecp
 --echo kalle
 --sorted_result
---error 0,1048,1054,1242
+--error 0,233,1242,4006
 $sql
 --exit
 EOF
@@ -281,7 +365,7 @@ do
 	echo "--eval set ndb_join_pushdown='\$NDB_JOIN_PUSHDOWN';"
 	echo "$ecp"
 	${gensql} --seed=$us --queries=$queries --dsn=$dsn --grammar=$grammar|
-	awk '{ print "--sorted_result"; print "--error 0,1242"; print; }'
+        awk '{ print "--sorted_result"; print "--error 0,233,1242,4006"; print; }'
 	echo "--exit"
     ) > ${opre}_test.sql
 

=== modified file 'storage/ndb/test/rqg/spj_test.yy'
--- a/storage/ndb/test/rqg/spj_test.yy	2010-07-13 13:35:00 +0000
+++ b/storage/ndb/test/rqg/spj_test.yy	2010-10-11 12:16:22 +0000
@@ -312,10 +312,22 @@ int_multi_conditions:
    int_condition AND int_condition
  ;
 
-
 char_condition:
    existing_left_table.char_field_name = existing_right_table.char_field_name   # General rule
  | existing_left_table.char_indexed = existing_right_table.char_indexed         # Want more joins on indexed field
+ | char_multi_conditions
+ ;
+
+char_multi_conditions:
+   # ix1(col_char_16,col_char_16_unique)
+   existing_left_table.col_char_16 = existing_right_table.col_char_16 AND
+   existing_left_table.col_char_16_unique = existing_right_table.col_char_16_unique
+ |
+   # ix2(col_varchar_256,col_char_16_unique)
+   existing_left_table.col_varchar_256 = existing_right_table.col_varchar_256 AND
+   existing_left_table.col_varchar_10_unique = existing_right_table.col_varchar_10_unique
+ |
+   char_condition AND char_condition
  ;
 
 existing_left_table:


Attachment: [text/bzr-bundle] bzr/ole.john.aske@oracle.com-20101011121622-fd4e2463knqc7ztm.bundle
Thread
bzr commit into mysql-5.1-telco-7.0-spj-scan-vs-scan branch(ole.john.aske:3310) Ole John Aske11 Oct