List:Commits« Previous MessageNext Message »
From:Ole John Aske Date:October 11 2010 12:16pm
Subject:bzr push into mysql-5.1-telco-7.0-spj-scan-vs-scan branch
(ole.john.aske:3309 to 3310)
View as plain text  
 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
 3309 Ole John Aske	2010-10-11
      spj-svs: Added missing handling NULL values in pruned scan keys.
      
      Pruned keys may be constructed from linkedValues which may contain a NULL value. As a
      pruned key implies equality on the fields in the key, a NULL value in the prune key can
      never match any existing tuple on the datanodes.
      
      We can therefore eliminate any pruned SCANREQ with NULL value in the prune key.
      
      In addition to the fix above, this commit also adds:
      
      - Checking for errors returns from ::expand().
      - add ndbrequire(!hasNull) to those places where we do not expect (and does not handle) 
        NULL values in the key produced by ::expand().
      - Some more DEBUG output for debugging pruned scans.
      - Moves the 'prune pattern' (SI_PRUNE_PATTERN) to be the last part of the 'optional'
        part of a Qserialized ueryTree node - Required as 'parent' relationship for 
        the node should be established by ::parseDA() before we could ::expand() 
        the pattern for the pruned key.

    modified:
      mysql-test/suite/ndb/r/ndb_join_pushdown.result
      mysql-test/suite/ndb/t/ndb_join_pushdown.test
      storage/ndb/src/kernel/blocks/dbspj/Dbspj.hpp
      storage/ndb/src/kernel/blocks/dbspj/DbspjMain.cpp
      storage/ndb/src/ndbapi/NdbQueryBuilder.cpp
=== 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 push into mysql-5.1-telco-7.0-spj-scan-vs-scan branch(ole.john.aske:3309 to 3310) Ole John Aske11 Oct