Merge 10.4 into 10.5

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;