merge bug

--------------030006010705020701050708
Content-Type: text/plain; charset=ISO-2022-JP
Content-Transfer-Encoding: 7bit

Hi,

I am developing database for data mining research.
I need to merge some identical structured tables.
However, some paticular structured tables cause merge
error.
For example, using attatched file

C\>mysql test < mergebug.sql
ERROR 1016 at line 72: Can't open file: 'detail-of-application.MRG'.
(errno: 143)

C:\>mysql test < mergedummy.sql
count(*)
2

The differencies within these two case is existance of one dummy field.
Also you can get normal result by reducing the last field from
bug case table.

--
Yusuke Naito
Artificial Life Laboratory, Inc.
E-mail:naito@alife-lab.co.jp


--------------030006010705020701050708
Content-Type: text/plain;
name="mergebug.sql"
Content-Transfer-Encoding: base64
Content-Disposition: inline;
filename="mergebug.sql"

LS0gTXlTUUwgZHVtcCA5LjA4DQotLQ0KLS0gSG9zdDogbG9jYWxob3N0ICAg IERhdGFiYXNl
OiBzcGF0DQotLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0t
LS0tLS0tLS0tLS0NCi0tIFNlcnZlciB2ZXJzaW9uCTQuMC4xMy1udA0KDQot LQ0KLS0gVGFi
bGUgc3RydWN0dXJlIGZvciB0YWJsZSAnREVUQUlMLU9GLUFQUExJQ0FUSU9O MCcNCi0tDQoN
CmRyb3AgdGFibGUgaWYgZXhpc3RzIGBkZXRhaWwtb2YtYXBwbGljYXRpb24w YDsgDQpDUkVB
VEUgVEFCTEUgYGRldGFpbC1vZi1hcHBsaWNhdGlvbjBgICgNCiAgaWQgYmln aW50KDIwKSB1
bnNpZ25lZCBkZWZhdWx0IE5VTEwsDQogIGZvcmVpZ25fa2V5IGJpZ2ludCgy MCkgdW5zaWdu
ZWQgZGVmYXVsdCBOVUxMLA0KICBwYXJlbnRfbmFtZSB2YXJjaGFyKDUwKSBk ZWZhdWx0IE5V
TEwsDQogIGBFWEFNSU5FUi1DT0RFYCB2YXJjaGFyKDgpIGRlZmF1bHQgTlVM TCwNCiAgYEtJ
TkQtT0YtRklOQUwtREVDSVNJT04tQ09ERWAgY2hhcigxKSBkZWZhdWx0IE5V TEwsDQogIGBG
SU5BTC1ERUNJU0lPTi1ESVNQQVRDSC1EQVRFYCBkYXRlIGRlZmF1bHQgTlVM TCwNCiAgYEtJ
TkQtT0YtRVhBTUlOQVRJT05gIGNoYXIoMikgZGVmYXVsdCBOVUxMLA0KICBg VFJBTlNGRVIt
T0YtUklHSFQtQU5ELUxJQ0VOU0lOR2AgY2hhcigxKSBkZWZhdWx0IE5VTEws DQogIGBQVUJM
SUMtTlVJU0FOQ0UtUFJFVkVOVC1BUlQtTUFSS2AgY2hhcigxKSBkZWZhdWx0 IE5VTEwNCikg
VFlQRT1NeUlTQU07DQoNCi0tDQotLSBEdW1waW5nIGRhdGEgZm9yIHRhYmxl ICdERVRBSUwt
T0YtQVBQTElDQVRJT04wJw0KLS0NCg0KSU5TRVJUIElOVE8gYERFVEFJTC1P Ri1BUFBMSUNB
VElPTjBgIFZBTFVFUyAoMzAwMDAwMDAwMDA4LDMwMDAwMDAwMDAwMCwnZnVu ZGFtZW50YWwt
YXJ0aWNsZS1pbmZvJywnMDAwMDc0MzMnLCcwJywnMTk4OC0wMS0xMycsJzAx JyxOVUxMLE5V
TEwpOw0KDQotLQ0KLS0gVGFibGUgc3RydWN0dXJlIGZvciB0YWJsZSAnREVU QUlMLU9GLUFQ
UExJQ0FUSU9OMScNCi0tDQoNCmRyb3AgdGFibGUgaWYgZXhpc3RzIGBkZXRh aWwtb2YtYXBw
bGljYXRpb24xYDsgDQpDUkVBVEUgVEFCTEUgYGRldGFpbC1vZi1hcHBsaWNh dGlvbjFgICgN
CiAgaWQgYmlnaW50KDIwKSB1bnNpZ25lZCBkZWZhdWx0IE5VTEwsDQogIGZv cmVpZ25fa2V5
IGJpZ2ludCgyMCkgdW5zaWduZWQgZGVmYXVsdCBOVUxMLA0KICBwYXJlbnRf bmFtZSB2YXJj
aGFyKDUwKSBkZWZhdWx0IE5VTEwsDQogIGBFWEFNSU5FUi1DT0RFYCB2YXJj aGFyKDgpIGRl
ZmF1bHQgTlVMTCwNCiAgYEtJTkQtT0YtRklOQUwtREVDSVNJT04tQ09ERWAg Y2hhcigxKSBk
ZWZhdWx0IE5VTEwsDQogIGBGSU5BTC1ERUNJU0lPTi1ESVNQQVRDSC1EQVRF YCBkYXRlIGRl
ZmF1bHQgTlVMTCwNCiAgYEtJTkQtT0YtRVhBTUlOQVRJT05gIGNoYXIoMikg ZGVmYXVsdCBO
VUxMLA0KICBgVFJBTlNGRVItT0YtUklHSFQtQU5ELUxJQ0VOU0lOR2AgY2hh cigxKSBkZWZh
dWx0IE5VTEwsDQogIGBQVUJMSUMtTlVJU0FOQ0UtUFJFVkVOVC1BUlQtTUFS S2AgY2hhcigx
KSBkZWZhdWx0IE5VTEwNCikgVFlQRT1NeUlTQU07DQoNCi0tDQotLSBEdW1w aW5nIGRhdGEg
Zm9yIHRhYmxlICdERVRBSUwtT0YtQVBQTElDQVRJT04xJw0KLS0NCg0KSU5T RVJUIElOVE8g
YERFVEFJTC1PRi1BUFBMSUNBVElPTjFgIFZBTFVFUyAoMjAwMDAwMDAwMDE2 LDIwMDAwMDAw
MDAwMCwnZnVuZGFtZW50YWwtYXJ0aWNsZS1pbmZvJywnMDAwMDc4MDgnLCcx JywnMTk5MC0w
MS0zMScsJzAxJyxOVUxMLE5VTEwpOw0KDQotLQ0KLS0NCi0tDQoNCmRyb3Ag dGFibGUgaWYg
ZXhpc3RzIGBERVRBSUwtT0YtQVBQTElDQVRJT05gOyANCmNyZWF0ZSB0YWJs ZSBgREVUQUlM
LU9GLUFQUExJQ0FUSU9OYChpZCBCSUdJTlQgVU5TSUdORUQgUFJJTUFSWSBL RVksDQpmb3Jl
aWduX2tleSBCSUdJTlQgVU5TSUdORUQsDQpwYXJlbnRfbmFtZSB2YXJjaGFy KDUwKSwNCmBF
WEFNSU5FUi1DT0RFYCB2YXJjaGFyKDgpLA0KYEtJTkQtT0YtRklOQUwtREVD SVNJT04tQ09E
RWAgY2hhcigxKSwNCmBGSU5BTC1ERUNJU0lPTi1ESVNQQVRDSC1EQVRFYCBk YXRlLA0KYEtJ
TkQtT0YtRVhBTUlOQVRJT05gIGNoYXIoMiksDQpgVFJBTlNGRVItT0YtUklH SFQtQU5ELUxJ
Q0VOU0lOR2AgY2hhcigxKSwNCmBQVUJMSUMtTlVJU0FOQ0UtUFJFVkVOVC1B UlQtTUFSS2Ag
Y2hhcigxKSwgDQppbmRleChwYXJlbnRfbmFtZSxmb3JlaWduX2tleSkpDQp0 eXBlPW1lcmdl
IA0KdW5pb249KGBERVRBSUwtT0YtQVBQTElDQVRJT04wYCwNCmBERVRBSUwt T0YtQVBQTElD
QVRJT04xYCk7DQoNCnNlbGVjdCBjb3VudCgqKSBmcm9tIGBERVRBSUwtT0Yt QVBQTElDQVRJ
T05gOw==
--------------030006010705020701050708
Content-Type: text/plain;
name="mergedummy.sql"
Content-Transfer-Encoding: base64
Content-Disposition: inline;
filename="mergedummy.sql"

LS0gTXlTUUwgZHVtcCA5LjA4DQotLQ0KLS0gSG9zdDogbG9jYWxob3N0ICAg IERhdGFiYXNl
OiBzcGF0DQotLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0t
LS0tLS0tLS0tLS0NCi0tIFNlcnZlciB2ZXJzaW9uCTQuMC4xMy1udA0KDQot LQ0KLS0gVGFi
bGUgc3RydWN0dXJlIGZvciB0YWJsZSAnREVUQUlMLU9GLUFQUExJQ0FUSU9O MCcNCi0tDQoN
CmRyb3AgdGFibGUgaWYgZXhpc3RzIGBkZXRhaWwtb2YtYXBwbGljYXRpb24w YDsgDQpDUkVB
VEUgVEFCTEUgYGRldGFpbC1vZi1hcHBsaWNhdGlvbjBgICgNCiAgaWQgYmln aW50KDIwKSB1
bnNpZ25lZCBkZWZhdWx0IE5VTEwsDQogIGZvcmVpZ25fa2V5IGJpZ2ludCgy MCkgdW5zaWdu
ZWQgZGVmYXVsdCBOVUxMLA0KICBwYXJlbnRfbmFtZSB2YXJjaGFyKDUwKSBk ZWZhdWx0IE5V
TEwsDQogIGBFWEFNSU5FUi1DT0RFYCB2YXJjaGFyKDgpIGRlZmF1bHQgTlVM TCwNCiAgYEtJ
TkQtT0YtRklOQUwtREVDSVNJT04tQ09ERWAgY2hhcigxKSBkZWZhdWx0IE5V TEwsDQogIGBG
SU5BTC1ERUNJU0lPTi1ESVNQQVRDSC1EQVRFYCBkYXRlIGRlZmF1bHQgTlVM TCwNCiAgYEtJ
TkQtT0YtRVhBTUlOQVRJT05gIGNoYXIoMikgZGVmYXVsdCBOVUxMLA0KICBg VFJBTlNGRVIt
T0YtUklHSFQtQU5ELUxJQ0VOU0lOR2AgY2hhcigxKSBkZWZhdWx0IE5VTEws DQogIGBQVUJM
SUMtTlVJU0FOQ0UtUFJFVkVOVC1BUlQtTUFSS2AgY2hhcigxKSBkZWZhdWx0 IE5VTEwsDQog
IGR1bW15IGNoYXIoMSkgZGVmYXVsdCBOVUxMDQopIFRZUEU9TXlJU0FNOw0K DQotLQ0KLS0g
RHVtcGluZyBkYXRhIGZvciB0YWJsZSAnREVUQUlMLU9GLUFQUExJQ0FUSU9O MCcNCi0tDQoN
CklOU0VSVCBJTlRPIGBERVRBSUwtT0YtQVBQTElDQVRJT04wYCBWQUxVRVMg KDMwMDAwMDAw
MDAwOCwzMDAwMDAwMDAwMDAsJ2Z1bmRhbWVudGFsLWFydGljbGUtaW5mbycs JzAwMDA3NDMz
JywnMCcsJzE5ODgtMDEtMTMnLCcwMScsTlVMTCxOVUxMLE5VTEwpOw0KDQot LQ0KLS0gVGFi
bGUgc3RydWN0dXJlIGZvciB0YWJsZSAnREVUQUlMLU9GLUFQUExJQ0FUSU9O MScNCi0tDQoN
CmRyb3AgdGFibGUgaWYgZXhpc3RzIGBkZXRhaWwtb2YtYXBwbGljYXRpb24x YDsgDQpDUkVB
VEUgVEFCTEUgYGRldGFpbC1vZi1hcHBsaWNhdGlvbjFgICgNCiAgaWQgYmln aW50KDIwKSB1
bnNpZ25lZCBkZWZhdWx0IE5VTEwsDQogIGZvcmVpZ25fa2V5IGJpZ2ludCgy MCkgdW5zaWdu
ZWQgZGVmYXVsdCBOVUxMLA0KICBwYXJlbnRfbmFtZSB2YXJjaGFyKDUwKSBk ZWZhdWx0IE5V
TEwsDQogIGBFWEFNSU5FUi1DT0RFYCB2YXJjaGFyKDgpIGRlZmF1bHQgTlVM TCwNCiAgYEtJ
TkQtT0YtRklOQUwtREVDSVNJT04tQ09ERWAgY2hhcigxKSBkZWZhdWx0IE5V TEwsDQogIGBG
SU5BTC1ERUNJU0lPTi1ESVNQQVRDSC1EQVRFYCBkYXRlIGRlZmF1bHQgTlVM TCwNCiAgYEtJ
TkQtT0YtRVhBTUlOQVRJT05gIGNoYXIoMikgZGVmYXVsdCBOVUxMLA0KICBg VFJBTlNGRVIt
T0YtUklHSFQtQU5ELUxJQ0VOU0lOR2AgY2hhcigxKSBkZWZhdWx0IE5VTEws DQogIGBQVUJM
SUMtTlVJU0FOQ0UtUFJFVkVOVC1BUlQtTUFSS2AgY2hhcigxKSBkZWZhdWx0 IE5VTEwsDQog
IGR1bW15IGNoYXIoMSkgZGVmYXVsdCBOVUxMDQopIFRZUEU9TXlJU0FNOw0K DQotLQ0KLS0g
RHVtcGluZyBkYXRhIGZvciB0YWJsZSAnREVUQUlMLU9GLUFQUExJQ0FUSU9O MScNCi0tDQoN
CklOU0VSVCBJTlRPIGBERVRBSUwtT0YtQVBQTElDQVRJT04xYCBWQUxVRVMg KDIwMDAwMDAw
MDAxNiwyMDAwMDAwMDAwMDAsJ2Z1bmRhbWVudGFsLWFydGljbGUtaW5mbycs JzAwMDA3ODA4
JywnMScsJzE5OTAtMDEtMzEnLCcwMScsTlVMTCxOVUxMLE5VTEwpOw0KDQot LQ0KLS0NCi0t
DQoNCmRyb3AgdGFibGUgaWYgZXhpc3RzIGBERVRBSUwtT0YtQVBQTElDQVRJ T05gOyANCmNy
ZWF0ZSB0YWJsZSBgREVUQUlMLU9GLUFQUExJQ0FUSU9OYChpZCBCSUdJTlQg VU5TSUdORUQg
UFJJTUFSWSBLRVksDQpmb3JlaWduX2tleSBCSUdJTlQgVU5TSUdORUQsDQpw YXJlbnRfbmFt
ZSB2YXJjaGFyKDUwKSwNCmBFWEFNSU5FUi1DT0RFYCB2YXJjaGFyKDgpLA0K YEtJTkQtT0Yt
RklOQUwtREVDSVNJT04tQ09ERWAgY2hhcigxKSwNCmBGSU5BTC1ERUNJU0lP Ti1ESVNQQVRD
SC1EQVRFYCBkYXRlLA0KYEtJTkQtT0YtRVhBTUlOQVRJT05gIGNoYXIoMiks DQpgVFJBTlNG
RVItT0YtUklHSFQtQU5ELUxJQ0VOU0lOR2AgY2hhcigxKSwNCmBQVUJMSUMt TlVJU0FOQ0Ut
UFJFVkVOVC1BUlQtTUFSS2AgY2hhcigxKSwNCmR1bW15IGNoYXIoMSksIA0K aW5kZXgocGFy
ZW50X25hbWUsZm9yZWlnbl9rZXkpKQ0KdHlwZT1tZXJnZSANCnVuaW9uPShg REVUQUlMLU9G
LUFQUExJQ0FUSU9OMGAsDQpgREVUQUlMLU9GLUFQUExJQ0FUSU9OMWApOw0K DQpzZWxlY3Qg
Y291bnQoKikgZnJvbSBgREVUQUlMLU9GLUFQUExJQ0FUSU9OYDs=

--------------030006010705020701050708
Content-Type: text/plain; charset=us-ascii

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org
--------------030006010705020701050708--

Re: merge bug

=?ISO-2022-JP?B?GyRCRmJGI000MnAbKEI=?= writes:
> Hi,
>
> I am developing database for data mining research.
> I need to merge some identical structured tables.
> However, some paticular structured tables cause merge
> error.
> For example, using attatched file
>
> C\>mysql test < mergebug.sql
> ERROR 1016 at line 72: Can't open file: 'detail-of-application.MRG'.
> (errno: 143)
>
> C:\>mysql test < mergedummy.sql
> count(*)
> 2
>
> The differencies within these two case is existance of one dummy field.
> Also you can get normal result by reducing the last field from
> bug case table.
>
> --
> Yusuke Naito
> Artificial Life Laboratory, Inc.
> E-mail:naito@alife-lab.co.jp
HI!

Tables have to be 100 % identical in definition.

One additional column is sufficient to stop usage of the merged table.

--

Regards,

--
For technical support contracts, go to https://order.mysql.com/?ref=msmi
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB
/_/ /_/\_, /___/\___\_\___/ Fulltime Developer and Support Coordinator
<___/ www.mysql.com Larnaca, Cyprus


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: merge bug

Yes

Of course, the additional field is also defined in merge table.
In both case, three tables, two are child and one merged
table, have identical structure. The error only depends on the
field combination.

Sinisa Milivojevic wrote:

>=?ISO-2022-JP?B?GyRCRmJGI000MnAbKEI=?= writes:
>
>
>>Hi,
>>
>>I am developing database for data mining research.
>>I need to merge some identical structured tables.
>>However, some paticular structured tables cause merge
>>error.
>>For example, using attatched file
>>
>>C\>mysql test < mergebug.sql
>>ERROR 1016 at line 72: Can't open file: 'detail-of-application.MRG'.
>>(errno: 143)
>>
>>C:\>mysql test < mergedummy.sql
>>count(*)
>>2
>>
>>The differencies within these two case is existance of one dummy field.
>>Also you can get normal result by reducing the last field from
>>bug case table.
>>
>>--
>> Yusuke Naito
>> Artificial Life Laboratory, Inc.
>> E-mail:naito@alife-lab.co.jp
>>
>>
>HI!
>
>Tables have to be 100 % identical in definition.
>
>One additional column is sufficient to stop usage of the merged table.
>
>
>
--
Yusuke Naito
Artificial Life Laboratory, Inc.
E-mail:naito@alife-lab.co.jp URL:www.alife-lab.co.jp





--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: merge bug

=?windows-1252?Q??????= writes:
> Yes
>
> Of course, the additional field is also defined in merge table.
> In both case, three tables, two are child and one merged
> table, have identical structure. The error only depends on the
> field combination.
>

ALL tables contained within MERGE table and MERGE table itself should
have 100 % same structure.

If that is a case and you still get error, that could be our bug,
otherwise it is not a bug.

--

Regards,

--
For technical support contracts, go to https://order.mysql.com/?ref=msmi
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB
/_/ /_/\_, /___/\___\_\___/ Fulltime Developer and Support Coordinator
<___/ www.mysql.com Larnaca, Cyprus


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: merge bug

I show the error case simply again. These three tables
are 100% identical. However this cause error.
(OS=Windows XP,CPU=AthlonXP 3200+,Chipset=nForce2 Ultra
Memory=DDR400 1G, HD=RAID 0 500GB(250GB * 2),
mysql=4.0.13,default-character-set=sjis)
--
drop table if exists `detail-of-application0`;
CREATE TABLE `detail-of-application0` (
id bigint(20) unsigned ,
foreign_key bigint(20) unsigned ,
parent_name varchar(50) ,
`EXAMINER-CODE` varchar(8) ,
`KIND-OF-FINAL-DECISION-CODE` char(1) ,
`FINAL-DECISION-DISPATCH-DATE` date ,
`KIND-OF-EXAMINATION` char(2) ,
`TRANSFER-OF-RIGHT-AND-LICENSING` char(1) ,
`PUBLIC-NUISANCE-PREVENT-ART-MARK` char(1)
) ;

drop table if exists `detail-of-application1`;
CREATE TABLE `detail-of-application1` (
id bigint(20) unsigned ,
foreign_key bigint(20) unsigned ,
parent_name varchar(50) ,
`EXAMINER-CODE` varchar(8) ,
`KIND-OF-FINAL-DECISION-CODE` char(1) ,
`FINAL-DECISION-DISPATCH-DATE` date ,
`KIND-OF-EXAMINATION` char(2) ,
`TRANSFER-OF-RIGHT-AND-LICENSING` char(1) ,
`PUBLIC-NUISANCE-PREVENT-ART-MARK` char(1)
) ;

drop table if exists `DETAIL-OF-APPLICATION`;
create table `DETAIL-OF-APPLICATION`(id BIGINT UNSIGNED PRIMARY KEY,
foreign_key BIGINT UNSIGNED,
parent_name varchar(50),
`EXAMINER-CODE` varchar(8),
`KIND-OF-FINAL-DECISION-CODE` char(1),
`FINAL-DECISION-DISPATCH-DATE` date,
`KIND-OF-EXAMINATION` char(2),
`TRANSFER-OF-RIGHT-AND-LICENSING` char(1),
`PUBLIC-NUISANCE-PREVENT-ART-MARK` char(1),
)
type=merge
union=(`DETAIL-OF-APPLICATION0`,
`DETAIL-OF-APPLICATION1`);

select count(*) from `DETAIL-OF-APPLICATION`;
--
ERROR 1016 at line 72: Can't open file: 'detail-of-application.MRG'.
(errno: 143)
--
Sinisa Milivojevic wrote:
> =?windows-1252?Q??????= writes:
>
>>Yes
>>
>>Of course, the additional field is also defined in merge table.
>> In both case, three tables, two are child and one merged
>>table, have identical structure. The error only depends on the
>>field combination.
>>
>
>
> ALL tables contained within MERGE table and MERGE table itself should
> have 100 % same structure.
>
> If that is a case and you still get error, that could be our bug,
> otherwise it is not a bug.
>


--
Yusuke Naito ????
Artificial Life Laboratory, Inc. (?)???????
E-mail:naito@alife-lab.co.jp URL:www.alife-lab.co.jp



--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: merge bug

Yusuke Naito writes:
> I show the error case simply again. These three tables
> are 100% identical. However this cause error.
> (OS=Windows XP,CPU=AthlonXP 3200+,Chipset=nForce2 Ultra
> Memory=DDR400 1G, HD=RAID 0 500GB(250GB * 2),
> mysql=4.0.13,default-character-set=sjis)
> --
> drop table if exists `detail-of-application0`;
> CREATE TABLE `detail-of-application0` (
> id bigint(20) unsigned ,
> foreign_key bigint(20) unsigned ,
> parent_name varchar(50) ,
> `EXAMINER-CODE` varchar(8) ,
> `KIND-OF-FINAL-DECISION-CODE` char(1) ,
> `FINAL-DECISION-DISPATCH-DATE` date ,
> `KIND-OF-EXAMINATION` char(2) ,
> `TRANSFER-OF-RIGHT-AND-LICENSING` char(1) ,
> `PUBLIC-NUISANCE-PREVENT-ART-MARK` char(1)
> ) ;
>
> drop table if exists `detail-of-application1`;
> CREATE TABLE `detail-of-application1` (
> id bigint(20) unsigned ,
> foreign_key bigint(20) unsigned ,
> parent_name varchar(50) ,
> `EXAMINER-CODE` varchar(8) ,
> `KIND-OF-FINAL-DECISION-CODE` char(1) ,
> `FINAL-DECISION-DISPATCH-DATE` date ,
> `KIND-OF-EXAMINATION` char(2) ,
> `TRANSFER-OF-RIGHT-AND-LICENSING` char(1) ,
> `PUBLIC-NUISANCE-PREVENT-ART-MARK` char(1)
> ) ;
>
> drop table if exists `DETAIL-OF-APPLICATION`;
> create table `DETAIL-OF-APPLICATION`(id BIGINT UNSIGNED PRIMARY KEY,
> foreign_key BIGINT UNSIGNED,
> parent_name varchar(50),
> `EXAMINER-CODE` varchar(8),
> `KIND-OF-FINAL-DECISION-CODE` char(1),
> `FINAL-DECISION-DISPATCH-DATE` date,
> `KIND-OF-EXAMINATION` char(2),
> `TRANSFER-OF-RIGHT-AND-LICENSING` char(1),
> `PUBLIC-NUISANCE-PREVENT-ART-MARK` char(1),
> )
> type=merge
> union=(`DETAIL-OF-APPLICATION0`,
> `DETAIL-OF-APPLICATION1`);
>
> select count(*) from `DETAIL-OF-APPLICATION`;
> --
> ERROR 1016 at line 72: Can't open file: 'detail-of-application.MRG'.
> (errno: 143)

How are they identical when third one has a PRIMARY key, while others
do not have one.

--

Regards,

--
For technical support contracts, go to https://order.mysql.com/?ref=msmi
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB
/_/ /_/\_, /___/\___\_\___/ Fulltime Developer and Support Coordinator
<___/ www.mysql.com Larnaca, Cyprus


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: merge bug

Thank you so much!
I believed the PRIMARY KEY option not contribute
the structural comparison.
The result by adding to children tables PRIMARY KEY
are successful.
However, the most of tables in my DB, I could succeeded
to merge tables without children's PRIMARY KEY. Then I miss
understood the error is bug. For example, following script
is successful. Is this bug? I believe this is not bug,
so that we can merge tables without defining PRIMARY KEY or
index in those tables. If we can use PRIMARY KEY or index
by defining in type=merge table only, it is very convenient.
--
drop table if exists `FILING-INFO0`;
create table `FILING-INFO0` (id BIGINT UNSIGNED ,
foreign_key BIGINT UNSIGNED,
parent_name varchar(50),
LAW varchar(1),
`APPLICATION-NUMBER` varchar(10),
`FILING-DATE` date,
`KIND-OF-APPLICATION` varchar(4),
`RETROACTED-DATE` date);

drop table if exists `FILING-INFO1`;
create table `FILING-INFO1` (id BIGINT UNSIGNED ,
foreign_key BIGINT UNSIGNED,
parent_name varchar(50),
LAW varchar(1),
`APPLICATION-NUMBER` varchar(10),
`FILING-DATE` date,
`KIND-OF-APPLICATION` varchar(4),
`RETROACTED-DATE` date);

drop table if exists `FILING-INFO`;
create table `FILING-INFO` (id BIGINT UNSIGNED PRIMARY KEY,
foreign_key BIGINT UNSIGNED,
parent_name varchar(50),
LAW varchar(1),
`APPLICATION-NUMBER` varchar(10),
`FILING-DATE` date,
`KIND-OF-APPLICATION` varchar(4),
`RETROACTED-DATE` date, index(parent_name,foreign_key))
type=merge
union=(`FILING-INFO0`,`FILING-INFO1`);

select count(*) from `FILING-INFO`;
--
count(*)
0
--
Sinisa Milivojevic wrote:
> Yusuke Naito writes:
>
>>I show the error case simply again. These three tables
>>are 100% identical. However this cause error.
>>(OS=Windows XP,CPU=AthlonXP 3200+,Chipset=nForce2 Ultra
>>Memory=DDR400 1G, HD=RAID 0 500GB(250GB * 2),
>>mysql=4.0.13,default-character-set=sjis)
>>--
>>drop table if exists `detail-of-application0`;
>>CREATE TABLE `detail-of-application0` (
>> id bigint(20) unsigned ,
>> foreign_key bigint(20) unsigned ,
>> parent_name varchar(50) ,
>> `EXAMINER-CODE` varchar(8) ,
>> `KIND-OF-FINAL-DECISION-CODE` char(1) ,
>> `FINAL-DECISION-DISPATCH-DATE` date ,
>> `KIND-OF-EXAMINATION` char(2) ,
>> `TRANSFER-OF-RIGHT-AND-LICENSING` char(1) ,
>> `PUBLIC-NUISANCE-PREVENT-ART-MARK` char(1)
>>) ;
>>
>>drop table if exists `detail-of-application1`;
>>CREATE TABLE `detail-of-application1` (
>> id bigint(20) unsigned ,
>> foreign_key bigint(20) unsigned ,
>> parent_name varchar(50) ,
>> `EXAMINER-CODE` varchar(8) ,
>> `KIND-OF-FINAL-DECISION-CODE` char(1) ,
>> `FINAL-DECISION-DISPATCH-DATE` date ,
>> `KIND-OF-EXAMINATION` char(2) ,
>> `TRANSFER-OF-RIGHT-AND-LICENSING` char(1) ,
>> `PUBLIC-NUISANCE-PREVENT-ART-MARK` char(1)
>>) ;
>>
>>drop table if exists `DETAIL-OF-APPLICATION`;
>>create table `DETAIL-OF-APPLICATION`(id BIGINT UNSIGNED PRIMARY KEY,
>>foreign_key BIGINT UNSIGNED,
>>parent_name varchar(50),
>>`EXAMINER-CODE` varchar(8),
>>`KIND-OF-FINAL-DECISION-CODE` char(1),
>>`FINAL-DECISION-DISPATCH-DATE` date,
>>`KIND-OF-EXAMINATION` char(2),
>>`TRANSFER-OF-RIGHT-AND-LICENSING` char(1),
>>`PUBLIC-NUISANCE-PREVENT-ART-MARK` char(1),
>>)
>>type=merge
>>union=(`DETAIL-OF-APPLICATION0`,
>>`DETAIL-OF-APPLICATION1`);
>>
>>select count(*) from `DETAIL-OF-APPLICATION`;
>>--
>>ERROR 1016 at line 72: Can't open file: 'detail-of-application.MRG'.
>>(errno: 143)
>
>
> How are they identical when third one has a PRIMARY key, while others
> do not have one.
>


--
Yusuke Naito ????
Artificial Life Laboratory, Inc. (?)???????
E-mail:naito@alife-lab.co.jp URL:www.alife-lab.co.jp




--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: merge bug

Hi!

On Jul 20, Yusuke Naito wrote:
> Thank you so much!
> I believed the PRIMARY KEY option not contribute
> the structural comparison.
> The result by adding to children tables PRIMARY KEY
> are successful.
> However, the most of tables in my DB, I could succeeded
> to merge tables without children's PRIMARY KEY.

It's because for some queries MyQL decided ot use an index, so it does
not matter if child tables have it. But if MySQL optimizer decided to
resolve the query by using an index - then MERGE table will try to use
the same index of underlying MyISAM tables, so it won't work if they
don't have one.

>Then I miss
> understood the error is bug. For example, following script
> is successful. Is this bug? I believe this is not bug,
> so that we can merge tables without defining PRIMARY KEY or
> index in those tables. If we can use PRIMARY KEY or index
> by defining in type=merge table only, it is very convenient.

No, it's meaningless. MERGE tables do not create indexes on their own,
having an index in MERGE table only is an indication to optimizer that
it can use this index of underlying MyISAM tables, if it will be
reasonable. So, it's no sense in having an index declared for MERGE
table if it's not present in underlying MyISAM tables.
Opposite situation is possible - if underlying tables have some index
that is not declared for MERGE table, than it won't be an error,
but simply this index will never be used when accessing MERGE table (of
course it can be used if MyISAM tables are accessed in the query
directly).

Regards,
Sergei

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/ www.mysql.com

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org