Missing support for VALUE() function in MariaDB >= 10.3?

[robertscheck]

I might be wrong but it seems like WordPress 5.3 (and trunk as of writing) seem to miss support for MariaDB 10.3 due to incompatible changed VALUES() function. It's important to not mix up the VALUES() function with the VALUES keyword from the INSERT keyword (see: also ​https://mariadb.com/kb/en/library/values-value/).

​https://mariadb.com/kb/en/library/upgrading-from-mariadb-102-to-mariadb-103/#functions says:

As a result of implementing Table Value Constructors, the VALUES function has been renamed to VALUE().

A simple grep through WordPress 5.3 sources leads me to:

$ grep -r "VALUES(" *
wp-includes/taxonomy.php:			if ( false === $wpdb->query( "INSERT INTO $wpdb->term_relationships (object_id, term_taxonomy_id, term_order) VALUES " . join( ',', $values ) . ' ON DUPLICATE KEY UPDATE term_order = VALUES(term_order)' ) ) {
wp-includes/option.php:	$result = $wpdb->query( $wpdb->prepare( "INSERT INTO `$wpdb->options` (`option_name`, `option_value`, `autoload`) VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE `option_name` = VALUES(`option_name`), `option_value` = VALUES(`option_value`), `autoload` = VALUES(`autoload`)", $option, $serialized_value, $autoload ) );
wp-includes/SimplePie/Cache/MySQL.php:					$query = $this->mysql->prepare('INSERT INTO `' . $this->options['extras']['prefix'] . 'cache_data` (`id`, `items`, `data`, `mtime`) VALUES(:feed, :count, :data, :time)');
wp-includes/SimplePie/Cache/MySQL.php:							$query = $this->mysql->prepare('INSERT INTO `' . $this->options['extras']['prefix'] . 'items` (`feed_id`, `id`, `data`, `posted`) VALUES(:feed, :id, :data, :date)');
wp-includes/SimplePie/Cache/MySQL.php:					$query = $this->mysql->prepare('INSERT INTO `' . $this->options['extras']['prefix'] . 'cache_data` (`id`, `items`, `data`, `mtime`) VALUES(:id, 0, :data, :time)');
$ 

Based on the grep output, I would say the following files are affected:

• wp-includes/taxonomy.php
• wp-includes/option.php

Given MySQL seems to stay with the VALUES() function and MariaDB >= 10.3 starts using VALUE() function (older MariaDB versions will stay with the old VALUES() function as well), this likely needs some MySQL/MariaDB version specific conditional code.

[dd32]

Hi @robertscheck and welcome to Trac!

Thankfully this doesn't really affect us, or most people by the look of it.

As the ​MariaDB page reads:

The VALUES() function can still be used even from MariaDB 10.3.3, but only in INSERT ... ON DUPLICATE KEY UPDATE statements; it's a syntax error otherwise.

which is the case for the wp-includes/taxonomy.php and wp-includes/options.php instances.

wp-includes/SimplePie

Can be ignored, as we don't use or expose it's MySQL caching component, but it should be reported upstream to them if it affect them differently.

What the MariaDB change does is that it makes calling the VALUES() function outside of it's intended use-case as invalid.
So for example, under MariaDB 10.2 the following would be an acceptable query.

SELECT id, VALUES(id) as will_return_null FROM wp_posts LIMIT 1;

Under 10.3.3 the above query will fail as VALUES() is used incorrectly, and you need to use VALUE() instead (But I really have no idea under what circumstances you'd ever actually use that - I'm sure it has an actual purpose though).

As far as I can tell, no further action is required by WordPress here, so I'm marking it as invalid. If I've misunderstood the ticket or MariaDB comments can still be added and/or the ticket re-opened.