[MDEV-14895] Incompatible undocumented change in behavior of VALUES() function Created: 2018-01-08  Updated: 2018-01-10  Resolved: 2018-01-10

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

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Ian Gilfillan
Resolution: Fixed Votes: 0
Labels: regression


 Description    

create table t1 (i int);
 
insert into t1 values (1);
 
insert into t1 select values(i) from t1;
 
select * from t1;

As documented, function VALUES only makes actual sense in INSERT .. ON DUPLICATE KEY UPDATE statements, and in other cases it returns NULL. It is indeed so with 10.2 (and before, and with MySQL):

MariaDB [test]> insert into t1 select values(i) from t1;
 
Query OK, 1 row affected (0.05 sec)
 
Records: 1  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> select * from t1;
 
+------+
 
| i    |
 
+------+
 
|    1 |
 
| NULL |
 
+------+
 
2 rows in set (0.00 sec)

However 10.3 does not allow it anymore:

MariaDB [test]> insert into t1 select values(i) from t1;
 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'values(i) from t1' at line 1
 
MariaDB [test]> select * from t1;
 
+------+
 
| i    |
 
+------+
 
|    1 |
 
+------+
 
1 row in set (0.00 sec)

And this works instead:

MariaDB [test]> insert into t1 select value(i) from t1;
 
Query OK, 1 row affected (0.04 sec)
 
Records: 1  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> select * from t1;
 
+------+
 
| i    |
 
+------+
 
|    1 |
 
| NULL |
 
+------+
 
2 rows in set (0.00 sec)

I suppose it can break various upgrade scenarios, so if it's intentional, hopefully there is a good enough reason for that; in this case, please re-categorize it as a documentation issue.

 Comments   
Comment by Sergei Golubchik [ 2018-01-09 ]

Intentional. It 10.3 we support Table Value Constructors, MDEV-12172. It's SQL-99 feature.
An example is

SELECT * FROM VALUES (1,'xx'), (5,'yyy'), (1,'zzz');

This grammar is incompatible with MariaDB non-standard function VALUES(), so the function was renamed to VALUE(). We managed to make the parser to recognize old name VALUES() in the context where this function was supposed to be used, in ON DUPLICATE KEY UPDATE. But in cases where the function VALUES() makes no sense, the parser treats VALUES as a table value constructor.

It's a documentation issue.

Comment by Ian Gilfillan [ 2018-01-10 ]

Updated at https://mariadb.com/kb/en/library/values-value/

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