[MDEV-18001] Row constructors in VALUES cause strange behaviour Created: 2018-12-13  Updated: 2022-12-07  Resolved: 2022-12-07

Status: Closed
Project: MariaDB Server
Component/s: N/A
Affects Version/s: 10.3
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Federico Razzoli Assignee: Sergei Petrunia
Resolution: Cannot Reproduce Votes: 0
Labels: values


 Description   

If VALUES has only 1 row and it contains at least 1 row constructor, nothing is returned:

MariaDB [test]> VALUES (1, 2, (3, 3));
 
Query OK, 0 rows affected (0.000 sec)

If it has multiple rows, rows constructors become a 0-value for the first row, and the value from the previous rows for the following rows:

MariaDB [test]> VALUES
 
    ->     ((5,5), 5),
 
    ->     (1,     2),
 
    ->     ((6,6), (7,7)),
 
    ->     ((6,6), (7,7));
 
+-------+---+
 
| (5,5) | 5 |
 
+-------+---+
 
|     0 | 5 |
 
|     1 | 2 |
 
|     1 | 2 |
 
|     1 | 2 |
 
+-------+---+
 
4 rows in set (0.000 sec)

If VALUES is used as a subquery, the anomaly propagates:

MariaDB [test]> SELECT COUNT(*) FROM (VALUES ((1, 2))) v;
 
Query OK, 0 rows affected (0.000 sec)



 Comments   
Comment by Federico Razzoli [ 2018-12-13 ]

I'm actually not saying that row constructors should be supported in this context. But if they aren't, I would expect a behaviour which is consistent with SELECT:

MariaDB [test]> SELECT (1, 2);
 
ERROR 1241 (21000): Operand should contain 1 column(s)

Comment by Alice Sherepa [ 2018-12-13 ] 

MariaDB [test]> with cte(x,y) as (values  (1,1)) select * from cte;
 
+---+---+
 
| x | y |
 
+---+---+
 
| 1 | 1 |
 
+---+---+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test]> with cte(x,y) as  (values  ((1,1))) select * from cte;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> with  cte(x) as  (values  ((1,1)), (2)) select * from cte;
 
+---+
 
| x |
 
+---+
 
| 0 |
 
| 2 |
 
+---+
 
2 rows in set (0.000 sec)
 
 
 
MariaDB [test]> with recursive cte(x) as  (values  ((1,1)), (2) union select x+1 from cte where x<2) select * from cte;
 
+------+
 
| x    |
 
+------+
 
| NULL |
 
|    2 |
 
+------+
 
2 rows in set (0.001 sec)

Comment by Galina Shalygina (Inactive) [ 2019-01-06 ]

MariaDB doesn't support columns of a ROW type. So when a ROW type column is trying to be used such an error appears:

MariaDB [test]> select 1, (1,2);
 
ERROR 1241 (21000): Operand should contain 1 column(s)
 
 
 
MariaDB [test]> CREATE TABLE t1 (a INT);
 
Query OK, 0 rows affected (0.039 sec)
 
 
 
MariaDB [test]> INSERT INTO t1 VALUES (1, (2,3));
 
ERROR 1241 (21000): Operand should contain 1 column(s)

When table value constructor is defined with a ROW constructor no error is returned. Vice versa, such a query is tried to be processed and a wrong result appears or even a server crashes.

To fix it fix_fields_for_tvc() should be changed.
In this method for each value defined in the TVC fix_fields() is called. This call should be changed on fix_fields_if_needed_for_scalar().
This method calls fix_fields_if_needed() and check_cols(1).

The call of check_cols(1) checks if a ROW item has exactly one column. A ROW item consists of one column (value) can be easily transformed into this value. The example will be shown below.

After the changes that are described above:

The result of the query where TVC is defined with a ROW constructor with one value:

MariaDB [test]> VALUES (1,(2));
 
+---+---+
 
| 1 | 2 |
 
+---+---+
 
| 1 | 2 |
 
+---+---+
 
1 row in set (0.001 sec)

With a ROW constructor with several values:

MariaDB [test]> VALUES (1,(2,3));
 
ERROR 1241 (21000): Operand should contain 1 column(s)

Comment by Alice Sherepa [ 2022-12-07 ]

Current 10.3 180b2bcd5389082e200f65ebc13a-10.11 return the expected error:

MariaDB [test]> VALUES (1, 2, (3, 3));
 
ERROR 1241 (21000): Operand should contain 1 column(s)
 
MariaDB [test]>  SELECT COUNT(*) FROM (VALUES ((1, 2))) v;
 
ERROR 1241 (21000): Operand should contain 1 column(s)

Generated at Thu Feb 08 08:40:45 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.