Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3
-
None
-
None
Description
Hi,
When using recursive CTE (WITH RECURSIVE) queries, we noticed that the resultset metadata from the JDBC driver can be incorrect.
Here's an example:
-- Table
|
CREATE TABLE `category` ( |
`id` int(10) unsigned NOT NULL AUTO_INCREMENT, |
`name` varchar(99) NOT NULL, |
`parent_id` int(10) unsigned DEFAULT NULL, |
`boolean` tinyint(1) NOT NULL DEFAULT 0, |
PRIMARY KEY (`id`), |
KEY `category_category_FK` (`parent_id`), |
CONSTRAINT `category_category_FK` FOREIGN KEY (`parent_id`) REFERENCES `category` (`id`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; |
With following data:
INSERT INTO category (id, name, parent_id, `boolean`) VALUES(1, 'root', NULL, 0); |
INSERT INTO category (id, name, parent_id, `boolean`) VALUES(11, 'child', 1, 0); |
INSERT INTO category (id, name, parent_id, `boolean`) VALUES(111, 'grandchild', 11, 0); |
The following query:
WITH RECURSIVE category_ancestors AS ( |
SELECT c.* |
FROM category c |
WHERE c.id = 1 |
UNION ALL |
SELECT rc.* |
FROM category_ancestors ca, category rc |
WHERE rc.parent_id = ca.id |
)
|
SELECT * FROM category_ancestors; |
returns a resultset where the metadata for the last column (boolean) is tinyint(3) whereas we would expect tinyint(1). There's no reason for the precision to change.
The consequence of that is that some client libraries assume the resulting column is not a boolean and refuse to parse it automatically as boolean.