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) List-Archive: http://lists.mysql.com/commits/120497 Message-Id: <20101011121652.F423421D@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============1664727281663862014==" --===============1664727281663862014== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline 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: --===============1664727281663862014== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/ole.john.aske@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: ole.john.aske@stripped\ # fd4e2463knqc7ztm # target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\ # /mysql-5.1-telco-7.0-spj-scan-scan/ # testament_sha1: c9107735ac9b4fb84e57e5f4704ba9d657201bf3 # timestamp: 2010-10-11 14:16:52 +0200 # source_branch: bzr+ssh://oaske@stripped/bzrroot/server\ # /mysql-5.1-telco-7.0-spj/ # base_revision_id: ole.john.aske@stripped\ # t18b2uru9vxxowha # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWfBpyjEABeZ/gA+QAEhb5/// f//+6r////5gCW+eTqu5nRQpE1QddsDQAAoMJKJGp+VB5Q9QaD01Gmmmg0ZMhkD1AANAAASiCDRk mmI0UaaZMmQ0ZMmgPSaAaAaAGgcZME0MhkZGTQ0AaDIwgGg0aZDENABIoppMhkNANAABkBoBoA0D Jo9IABxkwTQyGRkZNDQBoMjCAaDRpkMQ0AEiQmiaaaCZMRpkTCmTNFNplHqNqMhhqNGgNpMjkICY ipNRNNSqZSipv8HlGEbf0Jr5TwdKJzcQ9BkFRqhPNADiRJyIEkw+3E5hgtX5nZ6B45HflyHPQH4V 8KoG0Kho0zcc0ymq6dhWl2hCEaBiEkRBJDHjBCDD2GffbNBVRjTCxXrAxhRvsAhErl0krVPOJYin ZdIp1C0aQ0mxtE+kj3J0qDTttyttdMUOHfSUzqkNzZSXPOdjJ9VeNlthO+JuerlzCaP9JBmkNmKQ eCS3hM0+r5/JLkEByDckE1AbrjUL6dwRmyFklwt2wSu8PYHMD6hN6IKnO0+4DGFlH/XMXahMu1+a 5YDts73vrckSgNoeWXj07vHIiJ/MDhWeeulyzo1AlYmlRVqOckeWECUSPAtdByZNAeQNGOX2fm7l jS8qG0SVut4k0lj2zyBSb7OoaJH0MMdXMBkersvtbD/baK1gsx1SqGPYhgSNNUS6oRNP6cIC6BiL dWpFTGm8ktsGmFYIBGcBoCghYTUiYcySaSmZhZdgCYNAxjoJAxMNSIrbrCGPhFZRHkOv3HZb13QH EVThu88MbeWbQsYEQA0A2f7SUIFDQNpBlLTIVC13GY790nmD8G2dc2kEceTg4yQG9Klon5dCat2d M8zsJ97MiJutsvtErQGMQsVmWtTvoaT30Ec7DaxZ/lHAD7M0sZitGYpMaQNJDGNqgBwiaGl0xnFf 5ytln6bcqC4pHtGem0wsnUsKox/HluyAWjEhytJ0dpOOgPNj7t0LUrRCCIQ1Sqrc9EFznhGZKcMd PVYaCR3ZCciDEP7E38ccbLiPC7nDW1rAaPrtdZkt2FovPyUqmc29Y+nrLi/jpycTGcceRMDsFgxZ KpBkLsQXSPKB1wXl/WSOoCQbRXTc3GGyEaeuyhu2R021VpO7ZI2GpbakyxYuLyRKR5tMWFtHU5+u LlNGcxNknnYVDwKGQrparoYji5SKpkIEGrsOAuGTOdas9wEGk6LFoca5KMmgUhcspXVdtIxoKSwz 480H5c+87BYYuxbN0rm3RwMbbpNDgDq4aAMplYX4XuvvCIVIYirCBGUJscYo200FjBLjXfldmfdc DR5MMAldZS+kiy64bM5VEWFhni+SpQV00sdFhRcrWtEtkzRsTVi1nQm12iWjAMi0RvV8WJlUFLJA 6uinaitr09ofK/ZLVMmzSFAlCRCRAHUOnpTEBG4xPQb1JtuZX6ip/fxgQFgl6wICiA1vB3JB4gQL iQd5xgTQpXbY2O4BEJ/YkEJCvAkkokVB3lgWEkDoHvAwPrBn0yykDQiEAKHWkr0ikpAL8LRPJEEt xNsaX4gsFifFhbrCZ+QSwAkggYYBYQEA0LKB6gNhpA1gfAXxAkFZJbwaQUVEhhQoEgYG3EMxJTCS QSRgHuuRekqhccwcyRIC3IWAsqSvA3pI+JzotoCqEEF5lC4xmkGIWAQTGWJJi0gWhgGwtAoA4eM/ I0k0MJAnAZANSIMydJFcEw3zUrHIok0fALD5PnkH01BfBe32ZiIIghwK9IZ6qiP5PASH8fNWgiee 0EmIFosDVTNlVC+8RCqeh7Gcq/rEhMBSRILZOJMMUZ9dEsiSy5805uNcO3KOhlzziZcXKR9JorU6 toLg0Bh9AA899hmxNQF64nAOP8Xm+mYvuBMXas+izVAL4C5cSsgg+1SJJqQ5QShkiQlsi8O6OoWQ xGX682mJ8N50G6ANWQLgcIrYYJHcEXevM//CAgz5ufRgAdy+yAoIDGqRlpq6F8HcNs8DpjDUeWlC hBJvyyRKomKBMCFFnnqCDR3AS3yzaqEj5S8txEt9xe4mXo1lMlpuO9/TGWTv1tNq5cSQMxC632rP +2FfRvp99cTMA12ZOpMNZ0XTx+846t6Z+EZua5DeW4BNHXgDAmiAXEsqlyY127x9BwO6NxU4yssO BrAt7FavvOs0nWw+RpLMEWeJQDMlIOddu/PEhfoqyQluWRgbEl3hihSMzOjI7ubzIfsFZqt8neQn I4hkDaJlSYcUMAzSAiZlQ2ejfqrUNCGaBI7UdgE8xQZ2LACZ5O1I5fN4iVRcKBwaD1ii5JZDmR4A dOIsUg7c66QnciUgokuQkr5fV2ngBMFQSkSZgELvDf43I9UZWXHSW4kkk00JjvOSvJA0BPqobtgx WYCvLaqdbRJYk51AdH6Hc5Nu1zEriAayoC8oWlUIsD0ziSD9nyXCRMt7gIyuqNYmr2mhsGu/QT7N /26+GAfpAlUSWxfge5MjysmWpJiWQGZsjGzoYSD5pkiwM57AWhFJK7vmmc1cnmWyQrszGDJL9QKu RG8VAPR7U8ts+loyS48NYGYy8wkZjj1OBkHmcJCkCho67AmCk/fshLtx8NCyTRqRct29JTnnHpFx LS15SNinxcpjKGsYn6cNXQU8EavuA+AmMfFxMVeFHpRgQ1kLGnbtESwN+zSECq/Bdp4XxVEpRDZi BQSVyKd5F9hmokEDPIkmtJjMRC8oqqxJTqw8A4+I2x7AOGIIvQrTPbLll56lyaMWkXc0iukuAvFI 7Ba/KBQSWQaNIWMK4gpKvqEjVXmMCCxZy9o0xCQ5+Kx806JGnOntRb7VJa9QiiFqTQHanitOhdKl N8NW4Fr5LAwVv0AcuSj/F3JFOFCQ8GnKMQ== --===============1664727281663862014==--