Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
11.4.4, 11.4.5
-
None
-
Debian GNU/Linux 12 (bookworm)
Description
Testing simple SQL query from Spider table. An additional "artificial" column (first column) in the query is breaking the result, offsetting it by one column.
Note, that this extra column can only be safely added to the end of columns list.
Environment: two databases on the same server, with spider enabled. Both contains similar table (one original, and one spider table, linked with the first one).
Prepare environment:
DROP USER IF EXISTS 'test_user';
|
DROP DATABASE IF EXISTS `test_db1`;
|
DROP DATABASE IF EXISTS `test_db2`;
|
DROP SERVER IF EXISTS `test_srv1`;
|
|
|
CREATE DATABASE `test_db1`;
|
CREATE DATABASE `test_db2`;
|
|
|
CREATE USER 'test_user'@'%' IDENTIFIED BY 'password';
|
|
|
GRANT ALL PRIVILEGES ON `test_db1`.* TO 'test_user'@'%';
|
GRANT ALL PRIVILEGES ON `test_db2`.* TO 'test_user'@'%';
|
|
|
CREATE SERVER `test_srv1` FOREIGN DATA WRAPPER mysql OPTIONS(
|
HOST '127.0.0.1',
|
USER 'test_user',
|
PASSWORD 'password',
|
PORT 3306);
|
|
|
USE `test_db1`;
|
|
|
CREATE TABLE `table1` (
|
`id` bigint(20) unsigned NOT NULL,
|
`login` bigint(20) unsigned NOT NULL,
|
`source` int(10) unsigned NOT NULL,
|
PRIMARY KEY (`id`,`source`)
|
) ENGINE=InnoDB;
|
|
|
USE `test_db2`;
|
|
|
CREATE TABLE `table1` (
|
`id` bigint(20) unsigned NOT NULL,
|
`login` bigint(20) unsigned NOT NULL,
|
`source` int(10) unsigned NOT NULL,
|
PRIMARY KEY (`id`,`source`)
|
) ENGINE=SPIDER REMOTE_SERVER="test_srv1" REMOTE_DATABASE="test_db1" REMOTE_TABLE="table1";
|
|
|
USE `test_db1`;
|
|
|
TRUNCATE TABLE `table1`;
|
INSERT INTO `table1`
|
(`id`, `login`, `source` )
|
VALUES
|
(1,12345,100),
|
(2,12345,100),
|
(3,12345,100);
|
Test:
USE `test_db2`;
|
|
|
SELECT `id`, `login`, `source` from `table1`;
|
SELECT -1 as x, `id`, `login`, `source` from `table1`;
|
Expected results:
id login source
|
1 12345 100
|
2 12345 100
|
3 12345 100
|
|
|
x id login source
|
-1 1 12345 100
|
-1 2 12345 100
|
-1 3 12345 100
|
Actual results:
id login source
|
1 12345 100
|
2 12345 100
|
3 12345 100
|
|
|
x id login source
|
-1 1 12345 100
|
-1 0 2 12345
|
-1 0 3 12345
|
Please note, that additional column "x" is processed correctly on MariaDB version 11.4.3, but incorrectly on 11.4.4 and 11.4.5 (tested on both versions). Only the first row is correct.
The value of column "x" does not matter (tried NULL, 0, etc).
Attachments
Issue Links
- duplicates
-
-
- Closed
-
- is blocked by
-
-
- Closed
-