You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
144 lines
3.6 KiB
144 lines
3.6 KiB
drop table if exists t1, t2;
|
|
create table t1 (a int, b varchar(64));
|
|
-- Load a static XML file
|
|
load xml infile '../../std_data/loadxml.dat' into table t1
|
|
rows identified by '<row>';
|
|
select * from t1 order by a;
|
|
a b
|
|
1 b1
|
|
2 b2
|
|
3 b3
|
|
11 b11
|
|
111 b111
|
|
112 b112 & < > " ' &unknown; -- check entities
|
|
212 b212
|
|
213 b213
|
|
214 b214
|
|
215 b215
|
|
216 &bb b;
|
|
delete from t1;
|
|
-- Load a static XML file with 'IGNORE num ROWS'
|
|
load xml infile '../../std_data/loadxml.dat' into table t1
|
|
rows identified by '<row>' ignore 4 rows;
|
|
select * from t1 order by a;
|
|
a b
|
|
111 b111
|
|
112 b112 & < > " ' &unknown; -- check entities
|
|
212 b212
|
|
213 b213
|
|
214 b214
|
|
215 b215
|
|
216 &bb b;
|
|
-- Check 'mysqldump --xml' + 'LOAD XML' round trip
|
|
delete from t1;
|
|
load xml infile 'MYSQLTEST_VARDIR/tmp/loadxml-dump.xml' into table t1 rows identified by '<row>';;
|
|
select * from t1 order by a;
|
|
a b
|
|
111 b111
|
|
112 b112 & < > " ' &unknown; -- check entities
|
|
212 b212
|
|
213 b213
|
|
214 b214
|
|
215 b215
|
|
216 &bb b;
|
|
--Check that default row tag is '<row>
|
|
delete from t1;
|
|
load xml infile 'MYSQLTEST_VARDIR/tmp/loadxml-dump.xml' into table t1;;
|
|
select * from t1 order by a;
|
|
a b
|
|
111 b111
|
|
112 b112 & < > " ' &unknown; -- check entities
|
|
212 b212
|
|
213 b213
|
|
214 b214
|
|
215 b215
|
|
216 &bb b;
|
|
-- Check that 'xml' is not a keyword
|
|
select 1 as xml;
|
|
xml
|
|
1
|
|
create table t2(fl text);
|
|
LOAD XML LOCAL INFILE "MYSQLTEST_VARDIR/tmp/loadxml-dump.xml" INTO TABLE t2 ROWS IDENTIFIED BY '<person>';;
|
|
drop table t1;
|
|
drop table t2;
|
|
create table t1 (
|
|
id int(11) not null,
|
|
text text,
|
|
primary key (id)
|
|
) default charset=latin1;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
load xml infile '../../std_data/loadxml2.dat' into table t1;
|
|
select * from t1;
|
|
id text
|
|
1 line1
|
|
line2
|
|
line3
|
|
drop table t1;
|
|
#
|
|
# Bug#51571 load xml infile causes server crash
|
|
#
|
|
CREATE TABLE t1 (a text, b text);
|
|
LOAD XML INFILE '../../std_data/loadxml.dat' INTO TABLE t1
|
|
ROWS IDENTIFIED BY '<row>' (a,@b) SET b=concat('!',@b);
|
|
SELECT * FROM t1 ORDER BY a;
|
|
a b
|
|
1 !b1
|
|
11 !b11
|
|
111 !b111
|
|
112 !b112 & < > " ' &unknown; -- check entities
|
|
2 !b2
|
|
212 !b212
|
|
213 !b213
|
|
214 !b214
|
|
215 !b215
|
|
216 !&bb b;
|
|
3 !b3
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug#16171518 LOAD XML DOES NOT HANDLE EMPTY ELEMENTS
|
|
#
|
|
CREATE TABLE t1 (col1 VARCHAR(3), col2 VARCHAR(3), col3 VARCHAR(3), col4 VARCHAR(4));
|
|
LOAD XML INFILE '../../std_data/bug16171518_1.dat' INTO TABLE t1;
|
|
SELECT * FROM t1 ORDER BY col1, col2, col3, col4;
|
|
col1 col2 col3 col4
|
|
0bc def ghi jkl
|
|
1no NULL pqr stu
|
|
2BC DEF GHI JKL
|
|
3NO NULL PQR STU
|
|
4bc def ghi jkl
|
|
5no pqr stu vwx
|
|
6BC DEF NULL JKL
|
|
7NO PQR STU VWX
|
|
8bc def ghi NULL
|
|
9kl NULL mno pqr
|
|
ABC DEF NULL JKL
|
|
MNO NULL STU VWX
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (col1 VARCHAR(3), col2 VARCHAR(3), col3 INTEGER);
|
|
LOAD XML INFILE '../../std_data/bug16171518_2.dat' INTO TABLE t1;
|
|
SELECT * FROM t1 ORDER BY col1, col2, col3;
|
|
col1 col2 col3
|
|
ABC DEF NULL
|
|
GHI NULL 123
|
|
DROP TABLE t1;
|
|
#
|
|
# Regression test added in WL#8063
|
|
#
|
|
CREATE TABLE t1 (col1 VARCHAR(3), col2 VARCHAR(3), col3 INTEGER);
|
|
CREATE TABLE t2(i INT NOT NULL);
|
|
CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (NULL);
|
|
LOAD XML INFILE '../../std_data/bug16171518_2.dat' INTO TABLE t1;
|
|
ERROR 23000: Column 'i' cannot be null
|
|
DROP TABLE t1, t2;
|
|
CREATE TABLE t3 (col1 VARCHAR(3), col2 VARCHAR(3), col3 INTEGER);
|
|
CREATE VIEW v3 AS SELECT * FROM t3 WHERE col3 < 0 WITH CHECK OPTION;
|
|
LOAD XML INFILE '../../std_data/bug16171518_2.dat' INTO TABLE v3;
|
|
ERROR HY000: CHECK OPTION failed 'test.v3'
|
|
DROP VIEW v3;
|
|
DROP TABLE t3;
|
|
CREATE TABLE t4 (col1 VARCHAR(3), col2 VARCHAR(3), col3 INTEGER, col4 INT NOT NULL);
|
|
LOAD XML INFILE '../../std_data/bug16171518_2.dat' INTO TABLE t4 (col1, col2, col3) SET col4 = NULL;
|
|
ERROR 23000: Column 'col4' cannot be null
|
|
DROP TABLE t4;
|
|
#
|
|
|