Merge pull request #23923 from nextcloud/bugfix/oci/fix-greatest-order-for-oracle

Fix greatest/least order for oracle

3 files changed, 69 insertions, 0 deletions

diff --git a/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php b/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php
index a49edc505f5..23a834a9c49 100644
--- a/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php
+++ b/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php
@@ -27,6 +27,9 @@ namespace OC\DB\QueryBuilder\FunctionBuilder;
 use OC\DB\QueryBuilder\QueryFunction;
 use OC\DB\QueryBuilder\QuoteHelper;
 use OCP\DB\QueryBuilder\IFunctionBuilder;
+use OCP\DB\QueryBuilder\ILiteral;
+use OCP\DB\QueryBuilder\IParameter;
+use OCP\DB\QueryBuilder\IQueryFunction;
 
 class FunctionBuilder implements IFunctionBuilder {
 	/** @var QuoteHelper */
@@ -87,10 +90,20 @@ class FunctionBuilder implements IFunctionBuilder {
 		return new QueryFunction('MIN(' . $this->helper->quoteColumnName($field) . ')');
 	}
 
+	/**
+	 * @param string|ILiteral|IParameter|IQueryFunction $x
+	 * @param string|ILiteral|IParameter|IQueryFunction $y
+	 * @return IQueryFunction
+	 */
 	public function greatest($x, $y) {
 		return new QueryFunction('GREATEST(' . $this->helper->quoteColumnName($x) . ', ' . $this->helper->quoteColumnName($y) . ')');
 	}
 
+	/**
+	 * @param string|ILiteral|IParameter|IQueryFunction $x
+	 * @param string|ILiteral|IParameter|IQueryFunction $y
+	 * @return IQueryFunction
+	 */
 	public function least($x, $y) {
 		return new QueryFunction('LEAST(' . $this->helper->quoteColumnName($x) . ', ' . $this->helper->quoteColumnName($y) . ')');
 	}
diff --git a/lib/private/DB/QueryBuilder/FunctionBuilder/OCIFunctionBuilder.php b/lib/private/DB/QueryBuilder/FunctionBuilder/OCIFunctionBuilder.php
index f9347210c13..aec92574581 100644
--- a/lib/private/DB/QueryBuilder/FunctionBuilder/OCIFunctionBuilder.php
+++ b/lib/private/DB/QueryBuilder/FunctionBuilder/OCIFunctionBuilder.php
@@ -24,9 +24,52 @@
 namespace OC\DB\QueryBuilder\FunctionBuilder;
 
 use OC\DB\QueryBuilder\QueryFunction;
+use OCP\DB\QueryBuilder\ILiteral;
+use OCP\DB\QueryBuilder\IParameter;
+use OCP\DB\QueryBuilder\IQueryFunction;
 
 class OCIFunctionBuilder extends FunctionBuilder {
 	public function md5($input) {
 		return new QueryFunction('LOWER(DBMS_OBFUSCATION_TOOLKIT.md5 (input => UTL_RAW.cast_to_raw(' . $this->helper->quoteColumnName($input) .')))');
 	}
+
+	/**
+	 * As per https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions060.htm
+	 * Oracle uses the first value to cast the rest or the values. So when the
+	 * first value is a literal, plain value or column, instead of doing the
+	 * math, it will cast the expression to int and continue with a "0". So when
+	 * the second parameter is a function or column, we have to put that as
+	 * first parameter.
+	 *
+	 * @param string|ILiteral|IParameter|IQueryFunction $x
+	 * @param string|ILiteral|IParameter|IQueryFunction $y
+	 * @return IQueryFunction
+	 */
+	public function greatest($x, $y) {
+		if (is_string($y) || $y instanceof IQueryFunction) {
+			return parent::greatest($y, $x);
+		}
+
+		return parent::greatest($x, $y);
+	}
+
+	/**
+	 * As per https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions060.htm
+	 * Oracle uses the first value to cast the rest or the values. So when the
+	 * first value is a literal, plain value or column, instead of doing the
+	 * math, it will cast the expression to int and continue with a "0". So when
+	 * the second parameter is a function or column, we have to put that as
+	 * first parameter.
+	 *
+	 * @param string|ILiteral|IParameter|IQueryFunction $x
+	 * @param string|ILiteral|IParameter|IQueryFunction $y
+	 * @return IQueryFunction
+	 */
+	public function least($x, $y) {
+		if (is_string($y) || $y instanceof IQueryFunction) {
+			return parent::least($y, $x);
+		}
+
+		return parent::least($x, $y);
+	}
 }
diff --git a/lib/private/DB/QueryBuilder/FunctionBuilder/SqliteFunctionBuilder.php b/lib/private/DB/QueryBuilder/FunctionBuilder/SqliteFunctionBuilder.php
index 6d8e947c407..f759fb22fde 100644
--- a/lib/private/DB/QueryBuilder/FunctionBuilder/SqliteFunctionBuilder.php
+++ b/lib/private/DB/QueryBuilder/FunctionBuilder/SqliteFunctionBuilder.php
@@ -25,16 +25,29 @@
 namespace OC\DB\QueryBuilder\FunctionBuilder;
 
 use OC\DB\QueryBuilder\QueryFunction;
+use OCP\DB\QueryBuilder\ILiteral;
+use OCP\DB\QueryBuilder\IParameter;
+use OCP\DB\QueryBuilder\IQueryFunction;
 
 class SqliteFunctionBuilder extends FunctionBuilder {
 	public function concat($x, $y) {
 		return new QueryFunction('(' . $this->helper->quoteColumnName($x) . ' || ' . $this->helper->quoteColumnName($y) . ')');
 	}
 
+	/**
+	 * @param string|ILiteral|IParameter|IQueryFunction $x
+	 * @param string|ILiteral|IParameter|IQueryFunction $y
+	 * @return IQueryFunction
+	 */
 	public function greatest($x, $y) {
 		return new QueryFunction('MAX(' . $this->helper->quoteColumnName($x) . ', ' . $this->helper->quoteColumnName($y) . ')');
 	}
 
+	/**
+	 * @param string|ILiteral|IParameter|IQueryFunction $x
+	 * @param string|ILiteral|IParameter|IQueryFunction $y
+	 * @return IQueryFunction
+	 */
 	public function least($x, $y) {
 		return new QueryFunction('MIN(' . $this->helper->quoteColumnName($x) . ', ' . $this->helper->quoteColumnName($y) . ')');
 	}