diff options
author | Marko Mäkelä <marko.makela@mariadb.com> | 2022-10-25 10:04:37 +0300 |
---|---|---|
committer | Marko Mäkelä <marko.makela@mariadb.com> | 2022-10-25 10:04:37 +0300 |
commit | 667d3fbbb51044b20d23150992adbbad1f04aad8 (patch) | |
tree | 6bf1006a9ea5e68f18387205bd224e7c5698278f /storage/connect/mysql-test | |
parent | f19e8559aa3f46c0be427c9bd6534432bc08160c (diff) | |
parent | 34ff5ca8952ff58d99be5028a5920bfe5268f17a (diff) | |
download | mariadb-git-667d3fbbb51044b20d23150992adbbad1f04aad8.tar.gz |
Merge 10.3 into 10.4
Diffstat (limited to 'storage/connect/mysql-test')
3 files changed, 49 insertions, 22 deletions
diff --git a/storage/connect/mysql-test/connect/r/odbc_postgresql.result b/storage/connect/mysql-test/connect/r/odbc_postgresql.result index fd23197c37f..6bd8d75a601 100644 --- a/storage/connect/mysql-test/connect/r/odbc_postgresql.result +++ b/storage/connect/mysql-test/connect/r/odbc_postgresql.result @@ -2,7 +2,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `Name` varchar(256) NOT NULL, `Description` varchar(256) DEFAULT NULL -) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='ODBC' `CATFUNC`='Sources' +) ENGINE=CONNECT DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci `TABLE_TYPE`='ODBC' `CATFUNC`='Sources' SET NAMES utf8; # # Checking CATFUNC=Tables @@ -15,6 +15,7 @@ Table_Cat Table_Schema Table_Name Table_Type Remark mtr public t1 TABLE mtr public t2 TABLE mtr public v1 VIEW +mtr schema1 space_in_column_name TABLE mtr schema1 t1 TABLE mtr schema1 t2 TABLE mtr schema1 t3 TABLE @@ -27,6 +28,7 @@ Table_Cat Table_Schema Table_Name Table_Type Remark mtr public t1 TABLE mtr public t2 TABLE mtr public v1 VIEW +mtr schema1 space_in_column_name TABLE mtr schema1 t1 TABLE mtr schema1 t2 TABLE mtr schema1 t3 TABLE @@ -39,6 +41,7 @@ Table_Cat Table_Schema Table_Name Table_Type Remark mtr public t1 TABLE mtr public t2 TABLE mtr public v1 VIEW +mtr schema1 space_in_column_name TABLE mtr schema1 t1 TABLE mtr schema1 t2 TABLE mtr schema1 t3 TABLE @@ -102,6 +105,7 @@ Table_Cat Table_Schema Table_Name Column_Name Data_Type Type_Name Column_Size Bu mtr public t1 a 4 int4 10 4 0 10 0 mtr public t2 a 4 int4 10 4 0 10 0 mtr public v1 a 4 int4 10 4 0 10 1 +mtr schema1 space_in_column_name my space column 1 bpchar 20 80 NULL NULL 0 mtr schema1 t1 a 1 bpchar 10 40 NULL NULL 0 mtr schema1 t2 a 1 bpchar 10 40 NULL NULL 0 mtr schema1 t3 a 1 bpchar 10 40 NULL NULL 0 @@ -115,6 +119,7 @@ Table_Cat Table_Schema Table_Name Column_Name Data_Type Type_Name Column_Size Bu mtr public t1 a 4 int4 10 4 0 10 0 mtr public t2 a 4 int4 10 4 0 10 0 mtr public v1 a 4 int4 10 4 0 10 1 +mtr schema1 space_in_column_name my space column 1 bpchar 20 80 NULL NULL 0 mtr schema1 t1 a 1 bpchar 10 40 NULL NULL 0 mtr schema1 t2 a 1 bpchar 10 40 NULL NULL 0 mtr schema1 t3 a 1 bpchar 10 40 NULL NULL 0 @@ -157,7 +162,7 @@ SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(10) NOT NULL -) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' `TABLE_TYPE`='ODBC' +) ENGINE=CONNECT DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' `TABLE_TYPE`='ODBC' SELECT * FROM t1; a 10 @@ -168,7 +173,7 @@ SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `a` int(10) NOT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci SELECT * FROM t2; a 10 @@ -189,7 +194,7 @@ SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(10) NOT NULL -) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' `TABLE_TYPE`='ODBC' `TABNAME`='public.t1' +) ENGINE=CONNECT DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' `TABLE_TYPE`='ODBC' `TABNAME`='public.t1' SELECT * FROM t1; a 10 @@ -202,7 +207,7 @@ SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` char(10) NOT NULL -) ENGINE=CONNECT DEFAULT CHARSET=utf8mb3 CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' `TABLE_TYPE`='ODBC' `TABNAME`='schema1.t1' `DATA_CHARSET`='utf8' +) ENGINE=CONNECT DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' `TABLE_TYPE`='ODBC' `TABNAME`='schema1.t1' `DATA_CHARSET`='utf8' SELECT * FROM t1; a aaa @@ -213,8 +218,8 @@ CREATE TABLE t2 AS SELECT * FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( - `a` char(10) CHARACTER SET utf8mb3 NOT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 + `a` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci SELECT * FROM t2; a aaa @@ -237,7 +242,7 @@ SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` char(10) DEFAULT NULL -) ENGINE=CONNECT DEFAULT CHARSET=utf8mb3 CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' `TABLE_TYPE`='ODBC' `TABNAME`='schema1.v1' `DATA_CHARSET`='utf8' +) ENGINE=CONNECT DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' `TABLE_TYPE`='ODBC' `TABNAME`='schema1.v1' `DATA_CHARSET`='utf8' SELECT * FROM t1; a aaa @@ -248,8 +253,8 @@ CREATE TABLE t2 AS SELECT * FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( - `a` char(10) CHARACTER SET utf8mb3 DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 + `a` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci SELECT * FROM t2; a aaa @@ -272,7 +277,7 @@ SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` char(10) NOT NULL -) ENGINE=CONNECT DEFAULT CHARSET=utf8mb3 CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' `TABLE_TYPE`='ODBC' `TABNAME`='schema1.t2' `DATA_CHARSET`='utf8' +) ENGINE=CONNECT DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' `TABLE_TYPE`='ODBC' `TABNAME`='schema1.t2' `DATA_CHARSET`='utf8' SELECT * FROM t1; a xxx @@ -283,8 +288,8 @@ CREATE TABLE t2 AS SELECT * FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( - `a` char(10) CHARACTER SET utf8mb3 NOT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 + `a` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci SELECT * FROM t2; a xxx @@ -306,3 +311,11 @@ DELETE FROM t1 WHERE a='20'; Warnings: Note 1105 schema1.t3: 0 affected rows DROP TABLE t1; +# +# MDEV-29687 ODBC tables do not quote identifier names correctly +# +CREATE TABLE pg_in_maria ENGINE=CONNECT TABNAME='schema1.space_in_column_name' CHARSET=utf8 DATA_CHARSET=utf8 TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' quoted=1; +SELECT * from pg_in_maria; +my space column +My value +DROP TABLE pg_in_maria; diff --git a/storage/connect/mysql-test/connect/t/odbc_postgresql.sql b/storage/connect/mysql-test/connect/t/odbc_postgresql.sql index a795817a4d3..9b22c69af13 100644 --- a/storage/connect/mysql-test/connect/t/odbc_postgresql.sql +++ b/storage/connect/mysql-test/connect/t/odbc_postgresql.sql @@ -2,7 +2,7 @@ -- The SQL script to create PostgreSQL data for odbc_postgresql.test -- -- Run this script as a admin user: --- psql -U postgres < odbc_postgresql.sql +-- sudo -u postgres psql < storage/connect/mysql-test/connect/t/odbc_postgresql.sql SET NAMES 'UTF8'; @@ -11,7 +11,7 @@ DROP USER IF EXISTS mtr; CREATE USER mtr WITH PASSWORD 'mtr'; CREATE DATABASE mtr OWNER=mtr ENCODING='UTF8'; -GRANT ALL ON DATABASE mtr TO mtr; +GRANT ALL PRIVILEGES ON DATABASE mtr TO mtr; \c mtr SET role mtr; CREATE TABLE t1 (a INT NOT NULL); @@ -27,4 +27,6 @@ CREATE TABLE schema1.t2 (a CHAR(10) NOT NULL); INSERT INTO schema1.t2 VALUES ('xxx'),('yyy'),('zzz'),('ÄÖÜ'); CREATE TABLE schema1.t3 (a CHAR(10) NOT NULL, b CHAR(10) NOT NULL); INSERT INTO schema1.t3 VALUES ('xxx', 'aaa'),('yyy', 'bbb'),('zzz', 'ccc'),('ÄÖÜ', 'яяя'); - +CREATE TABLE schema1.space_in_column_name ("my space column" CHAR(20) NOT NULL); +INSERT INTO schema1.space_in_column_name VALUES ('My value'); +\dt schema1.* diff --git a/storage/connect/mysql-test/connect/t/odbc_postgresql.test b/storage/connect/mysql-test/connect/t/odbc_postgresql.test index 86597423d04..ec98453d630 100644 --- a/storage/connect/mysql-test/connect/t/odbc_postgresql.test +++ b/storage/connect/mysql-test/connect/t/odbc_postgresql.test @@ -5,10 +5,10 @@ # To configure your system to be able to run this test, # follow through the following steps: # -# 1. Install and configure PostgreSQL database to stat on the system startup +# 1. Install and configure PostgreSQL database to start on the system startup # # 2. Create user, database, schema and tables to be used by mtr: -# psql -U postgres < odbc_postgresql.sql +# sudo -u postgres psql < storage/connect/mysql-test/connect/t/odbc_postgresql.sql # # 3. Install PostgreSQL ODBC Driver. # - On CentOS, Fedora: @@ -18,18 +18,23 @@ # # 4. Create a data source with the name "ConnectEnginePostgresql" # - On Windows: use odbcadm.exe -# - On Linux: put these lines into /etc/odbc.ini +# - On Linux: put these lines into /etc/odbc.ini or in ~/.odbc.ini # #[ConnectEnginePostgresql] #Description=PostgreSQL DSN for ConnectSE -#Driver=PostgreSQL (should the path to the driver so file) +#Driver=PostgreSQL (should the path to the driver so file, on linux: /usr/lib/x86_64-linux-gnu/odbc/psqlodbca.so) #Database=mtr #Servername=localhost #Port=5432 # # 5. Allow user "mtr" to connect to the database "mtr" -# Add this line into the begginning of pg_hba.conf -# (usually /var/lib/pgsql/data/pg_hba.conf on Linux): +# Find `pg_hba.conf` file: +# Run `SHOW hba_file;` or `locate pg_hba.conf` to find right location +# (usually /var/lib/pgsql/data/pg_hba.conf or /etc/postgresql/[version]/main/pg_hba.conf on Linux) +# Add this line into the beginning of pg_hba.conf: +# For unix socket connection (connect with `psql -U mtr`) +#local mtr mtr password +# For TCP/IP connection (connect with `psql -U mtr -h 127.0.0.1`) #host mtr mtr 127.0.0.1/32 password # # 6. Restart the server: @@ -211,3 +216,10 @@ DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(6), b VARCHAR(6), PRIMARY KEY(a, b)) ENGINE=CONNECT TABNAME='schema1.t3' CHARSET=utf8 DATA_CHARSET=utf8 TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr'; DELETE FROM t1 WHERE a='20'; DROP TABLE t1; + +--echo # +--echo # MDEV-29687 ODBC tables do not quote identifier names correctly +--echo # +CREATE TABLE pg_in_maria ENGINE=CONNECT TABNAME='schema1.space_in_column_name' CHARSET=utf8 DATA_CHARSET=utf8 TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' quoted=1; +SELECT * from pg_in_maria; +DROP TABLE pg_in_maria; |