File indexing completed on 2024-05-26 04:44:47

0001 /* This file is part of the KDE project
0002    Copyright (C) 2003-2016 Jarosław Staniek <staniek@kde.org>
0003 
0004    Based on nexp.cpp : Parser module of Python-like language
0005    (C) 2001 Jarosław Staniek, MIMUW (www.mimuw.edu.pl)
0006 
0007    This library is free software; you can redistribute it and/or
0008    modify it under the terms of the GNU Library General Public
0009    License as published by the Free Software Foundation; either
0010    version 2 of the License, or (at your option) any later version.
0011 
0012    This library is distributed in the hope that it will be useful,
0013    but WITHOUT ANY WARRANTY; without even the implied warranty of
0014    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
0015    Library General Public License for more details.
0016 
0017    You should have received a copy of the GNU Library General Public License
0018    along with this library; see the file COPYING.LIB.  If not, write to
0019    the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
0020  * Boston, MA 02110-1301, USA.
0021  */
0022 
0023 #include "KDbExpression.h"
0024 #include "KDb.h"
0025 #include "KDbQuerySchema.h"
0026 #include "KDbDriver.h"
0027 #include "KDbParser.h"
0028 #include "KDbParser_p.h"
0029 #include "kdb_debug.h"
0030 
0031 #include <QSet>
0032 
0033 #include <vector>
0034 #include <algorithm>
0035 
0036 // Enable to add SQLite-specific functions
0037 //#define KDB_ENABLE_SQLITE_SPECIFIC_FUNCTIONS
0038 
0039 //! A set of names of aggregation SQL functions
0040 class BuiltInAggregates
0041 {
0042 public:
0043     BuiltInAggregates()
0044         : data({ QStringLiteral("SUM"),
0045                  QStringLiteral("MIN"),
0046                  QStringLiteral("MAX"),
0047                  QStringLiteral("AVG"),
0048                  QStringLiteral("COUNT"),
0049                  QStringLiteral("STD"),
0050                  QStringLiteral("STDDEV"),
0051                  QStringLiteral("VARIANCE")
0052                })
0053     {
0054     }
0055     const QSet<QString> data;
0056 };
0057 
0058 Q_GLOBAL_STATIC(BuiltInAggregates, _builtInAggregates)
0059 
0060 //! Type of a single function argument, used with KDbField::Type values.
0061 //! Used to indicate that multiple types are allowed.
0062 enum BuiltInFunctionArgumentType
0063 {
0064     AnyText = KDbField::LastType + 1,
0065     AnyInt,
0066     AnyFloat,
0067     AnyNumber,
0068     Any
0069 };
0070 
0071 //! @return any concrete type matching rule @a argType
0072 static KDbField::Type anyMatchingType(int argType)
0073 {
0074     if (argType == AnyText || argType == Any) {
0075         return KDbField::Text;
0076     }
0077     else if (argType == AnyInt || argType == AnyNumber) {
0078         return KDbField::Integer;
0079     }
0080     else if (argType == AnyFloat) {
0081         return KDbField::Double;
0082     }
0083     return KDbField::InvalidType;
0084 }
0085 
0086 //! Declaration of a single built-in function. It can offer multiple signatures.
0087 class BuiltInFunctionDeclaration
0088 {
0089 public:
0090     inline BuiltInFunctionDeclaration()
0091         : defaultReturnType(KDbField::InvalidType), copyReturnTypeFromArg(-1)
0092     {
0093     }
0094     virtual ~BuiltInFunctionDeclaration() {}
0095     virtual KDbField::Type returnType(const KDbFunctionExpressionData* f, KDbParseInfo* parseInfo) const {
0096         Q_UNUSED(parseInfo);
0097         const KDbNArgExpressionData *argsData = f->args.constData()->convertConst<KDbNArgExpressionData>();
0098         if (argsData->containsNullArgument()) {
0099             return KDbField::Null;
0100         }
0101         if (copyReturnTypeFromArg >= 0 && copyReturnTypeFromArg < argsData->children.count()) {
0102             KDbQueryParameterExpressionData *queryParameterExpressionData
0103                 = argsData->children.at(copyReturnTypeFromArg)
0104                       ->convert<KDbQueryParameterExpressionData>();
0105             if (queryParameterExpressionData) {
0106                 // Set query parameter type (if there are any) to deduced result type
0107                 //! @todo Most likely but can be also other type
0108                 for (size_t i = 0; i < signatures.size(); ++i) {
0109                     int** signature = signatures[i];
0110                     const KDbField::Type t = anyMatchingType(signature[copyReturnTypeFromArg][0]);
0111                     if (t != KDbField::InvalidType) {
0112                         queryParameterExpressionData->m_type = t;
0113                         return t;
0114                     }
0115                 }
0116             }
0117             return argsData->children.at(copyReturnTypeFromArg)->type();
0118         }
0119         return defaultReturnType;
0120     }
0121     std::vector<int**> signatures;
0122 protected:
0123     KDbField::Type defaultReturnType;
0124     int copyReturnTypeFromArg;
0125     friend class BuiltInFunctions;
0126 private:
0127     Q_DISABLE_COPY(BuiltInFunctionDeclaration)
0128 };
0129 
0130 //! Declaration of a single built-in function COALESCE() and similar ones.
0131 class CoalesceFunctionDeclaration : public BuiltInFunctionDeclaration
0132 {
0133 public:
0134     CoalesceFunctionDeclaration() {}
0135     KDbField::Type returnType(const KDbFunctionExpressionData* f, KDbParseInfo* parseInfo) const override {
0136         Q_UNUSED(parseInfo);
0137         // Find type
0138         //! @todo Most likely but can be also other type
0139         KDbField::Type t = KDbField::Integer;
0140         const KDbNArgExpressionData *argsData = f->args.constData()->convertConst<KDbNArgExpressionData>();
0141         foreach(const ExplicitlySharedExpressionDataPointer &expr, argsData->children) {
0142             KDbQueryParameterExpressionData *queryParameterExpressionData = expr->convert<KDbQueryParameterExpressionData>();
0143             const KDbField::Type currentType = expr->type();
0144             if (!queryParameterExpressionData && currentType != KDbField::Null) {
0145                 t = currentType;
0146                 break;
0147             }
0148         }
0149         foreach(const ExplicitlySharedExpressionDataPointer &expr, argsData->children) {
0150             KDbQueryParameterExpressionData *queryParameterExpressionData = expr->convert<KDbQueryParameterExpressionData>();
0151             if (queryParameterExpressionData) {
0152                 // Set query parameter type (if there are any) to deduced result type
0153                 queryParameterExpressionData->m_type = t;
0154             }
0155         }
0156         return t;
0157     }
0158 private:
0159     Q_DISABLE_COPY(CoalesceFunctionDeclaration)
0160 };
0161 
0162 //! Declaration of a single built-in function MIN(), MAX() and similar ones.
0163 //! Its return type is:
0164 //! - NULL if any argument is NULL
0165 //! - valid type if types of all arguments are compatible (e.g. text, numeric, date...)
0166 //! - InvalidType if types of any two are incompatible
0167 class MinMaxFunctionDeclaration : public BuiltInFunctionDeclaration
0168 {
0169     Q_DECLARE_TR_FUNCTIONS(MinMaxFunctionDeclaration)
0170 public:
0171     MinMaxFunctionDeclaration() {}
0172     KDbField::Type returnType(const KDbFunctionExpressionData* f, KDbParseInfo* parseInfo) const override {
0173         const KDbNArgExpressionData *argsData = f->args.constData()->convertConst<KDbNArgExpressionData>();
0174         if (argsData->children.isEmpty()) {
0175             return KDbField::Null;
0176         }
0177         const KDbField::Type type0 = argsData->children.at(0)->type(); // cache: evaluating type of expressions can be expensive
0178         if (nullOrInvalid(type0)) {
0179             return type0;
0180         }
0181         KDbField::TypeGroup prevTg = KDbField::typeGroup(type0); // use typegroup for simplicity
0182         bool prevTgIsAny = argsData->children.at(0)->convertConst<KDbQueryParameterExpressionData>();
0183         for(int i = 1; i < argsData->children.count(); ++i) {
0184             const ExplicitlySharedExpressionDataPointer expr = argsData->children.at(i);
0185             const KDbField::Type t = expr->type();
0186             if (nullOrInvalid(t)) {
0187                 return t;
0188             }
0189             const KDbField::TypeGroup tg = KDbField::typeGroup(t);
0190             const bool tgIsAny = argsData->children.at(i)->convertConst<KDbQueryParameterExpressionData>();
0191             if (prevTgIsAny) {
0192                 if (!tgIsAny) { // no longer "Any" (query parameter)
0193                     prevTgIsAny = false;
0194                     prevTg = tg;
0195                 }
0196                 continue;
0197             } else if (tgIsAny) {
0198                 continue; // use previously found concrete type
0199             }
0200             if ((prevTg == KDbField::IntegerGroup || prevTg == KDbField::FloatGroup)
0201                 && (tg == KDbField::IntegerGroup || tg == KDbField::FloatGroup))
0202             {
0203                 if (prevTg == KDbField::IntegerGroup && tg == KDbField::FloatGroup) {
0204                     prevTg = KDbField::FloatGroup; // int -> float
0205                 }
0206                 continue;
0207             }
0208             if (prevTg == tg) {
0209                 continue;
0210             }
0211             if (parseInfo) {
0212                 parseInfo->setErrorMessage(
0213                     tr("Incompatible types in %1() function").arg(f->name));
0214                 parseInfo->setErrorDescription(
0215                     tr("Argument #%1 of type \"%2\" in function %3() is not "
0216                        "compatible with previous arguments of type \"%4\".")
0217                             .arg(i+1)
0218                             .arg(KDbField::typeName(simpleTypeForGroup(tg)),
0219                                  f->name,
0220                                  KDbField::typeName(simpleTypeForGroup(prevTg))));
0221             }
0222             return KDbField::InvalidType;
0223         }
0224         if (prevTgIsAny) {
0225             //! @todo Most likely Integer but can be also Float/Double/Text/Date...
0226             return KDbField::Integer;
0227         }
0228         const KDbField::Type resultType = safeTypeForGroup(prevTg);
0229         // Set query parameter types (if there are any) to deduced result type
0230         for(ExplicitlySharedExpressionDataPointer expr : argsData->children) {
0231             KDbQueryParameterExpressionData *queryParameterExpressionData = expr->convert<KDbQueryParameterExpressionData>();
0232             if (queryParameterExpressionData) {
0233                 queryParameterExpressionData->m_type = resultType;
0234             }
0235         }
0236         return resultType;
0237     }
0238 private:
0239     static bool nullOrInvalid(KDbField::Type type) {
0240         return type == KDbField::Null || type == KDbField::InvalidType;
0241     }
0242     //! @return safe default type for type group @a tg (too big sizes better than too small)
0243     static KDbField::Type safeTypeForGroup(KDbField::TypeGroup tg) {
0244         switch (tg) {
0245         case KDbField::TextGroup: return KDbField::LongText;
0246         case KDbField::IntegerGroup: return KDbField::BigInteger;
0247         case KDbField::FloatGroup: return KDbField::Double;
0248         case KDbField::BooleanGroup: return KDbField::Boolean;
0249         case KDbField::DateTimeGroup: return KDbField::DateTime;
0250         case KDbField::BLOBGroup: return KDbField::BLOB;
0251         default: break;
0252         }
0253         return KDbField::InvalidType;
0254     }
0255     //! @return resonable default type for type group @a tg (used for displaying in error message)
0256     static KDbField::Type simpleTypeForGroup(KDbField::TypeGroup tg) {
0257         switch (tg) {
0258         case KDbField::TextGroup: return KDbField::Text;
0259         case KDbField::IntegerGroup: return KDbField::Integer;
0260         case KDbField::FloatGroup: return KDbField::Double;
0261         case KDbField::BooleanGroup: return KDbField::Boolean;
0262         case KDbField::DateTimeGroup: return KDbField::DateTime;
0263         case KDbField::BLOBGroup: return KDbField::BLOB;
0264         default: break;
0265         }
0266         return KDbField::InvalidType;
0267     }
0268     Q_DISABLE_COPY(MinMaxFunctionDeclaration)
0269 };
0270 
0271 //! Declaration of a single built-in function RANDOM() and RANDOM(X,Y).
0272 //! Its return type is:
0273 //! - Double when number of arguments is zero
0274 //! - integer if there are two integer arguments (see KDb::maximumForIntegerFieldTypes())
0275 //! - InvalidType for other number of arguments
0276 class RandomFunctionDeclaration : public BuiltInFunctionDeclaration
0277 {
0278     Q_DECLARE_TR_FUNCTIONS(RandomFunctionDeclaration)
0279 public:
0280     RandomFunctionDeclaration() {}
0281     KDbField::Type returnType(const KDbFunctionExpressionData* f, KDbParseInfo* parseInfo) const override {
0282         const KDbNArgExpressionData *argsData = f->args.constData()->convertConst<KDbNArgExpressionData>();
0283         if (argsData->children.isEmpty()) {
0284             return KDbField::Double;
0285         }
0286         if (argsData->children.count() == 2) {
0287             const KDbConstExpressionData *const0 = argsData->children.at(0)->convertConst<KDbConstExpressionData>();
0288             const KDbConstExpressionData *const1 = argsData->children.at(1)->convertConst<KDbConstExpressionData>();
0289             if (const0 && const1) {
0290                 bool ok0;
0291                 const qlonglong val0 = const0->value.toLongLong(&ok0);
0292                 bool ok1;
0293                 const qlonglong val1 = const1->value.toLongLong(&ok1);
0294                 if (ok0 && ok1) {
0295                     if (val0 >= val1) {
0296                         if (parseInfo) {
0297                             parseInfo->setErrorMessage(
0298                                 tr("Invalid arguments of %1() function").arg(f->name));
0299                             parseInfo->setErrorDescription(
0300                                 tr("Value of the first argument should be less than "
0301                                    "value of the second argument."));
0302                         }
0303                         return KDbField::InvalidType;
0304                     }
0305                 }
0306             }
0307             KDbField::Type t0;
0308             KDbField::Type t1;
0309             // deduce query parameter types
0310             KDbQueryParameterExpressionData *queryParameterExpressionData0
0311                 = argsData->children.at(0)->convert<KDbQueryParameterExpressionData>();
0312             KDbQueryParameterExpressionData *queryParameterExpressionData1
0313                 = argsData->children.at(1)->convert<KDbQueryParameterExpressionData>();
0314             if (queryParameterExpressionData0 && queryParameterExpressionData1) {
0315                 queryParameterExpressionData0->m_type = KDbField::Integer;
0316                 queryParameterExpressionData1->m_type = KDbField::Integer;
0317                 t0 = KDbField::Integer;
0318                 t1 = KDbField::Integer;
0319             } else if (queryParameterExpressionData0 && !queryParameterExpressionData1) {
0320                 queryParameterExpressionData0->m_type = KDbField::Integer;
0321                 t0 = queryParameterExpressionData0->m_type;
0322                 t1 = argsData->children.at(1)->type();
0323             } else if (!queryParameterExpressionData0 && queryParameterExpressionData1) {
0324                 queryParameterExpressionData1->m_type = KDbField::Integer;
0325                 t0 = argsData->children.at(0)->type();
0326                 t1 = queryParameterExpressionData1->m_type;
0327             } else {
0328                 t0 = argsData->children.at(0)->type();
0329                 t1 = argsData->children.at(1)->type();
0330             }
0331             return KDb::maximumForIntegerFieldTypes(t0, t1);
0332         }
0333         return KDbField::InvalidType;
0334     }
0335 private:
0336     Q_DISABLE_COPY(RandomFunctionDeclaration)
0337 };
0338 
0339 //! Declaration of a single built-in function CEILING(X) and FLOOR(X).
0340 //! Its return type is:
0341 //! - integer if there are two integer arguments (see KDb::maximumForIntegerFieldTypes())
0342 //! - InvalidType for other number of arguments
0343 class CeilingFloorFunctionDeclaration : public BuiltInFunctionDeclaration
0344 {
0345 public:
0346     CeilingFloorFunctionDeclaration() {}
0347     KDbField::Type returnType(const KDbFunctionExpressionData* f, KDbParseInfo* parseInfo) const override {
0348         Q_UNUSED(parseInfo);
0349         const KDbNArgExpressionData *argsData = f->args.constData()->convertConst<KDbNArgExpressionData>();
0350         if (argsData->children.count() == 1) {
0351             KDbQueryParameterExpressionData *queryParameterExpressionData
0352                 = argsData->children.at(0)->convert<KDbQueryParameterExpressionData>();
0353             if (queryParameterExpressionData) {
0354                 // Set query parameter type (if there are any) to deduced result type
0355                 //! @todo Most likely but can be also other type
0356                 queryParameterExpressionData->m_type = KDbField::Double;
0357                 return KDbField::BigInteger;
0358             }
0359             const KDbField::Type type = argsData->children.at(0)->type(); // cache: evaluating type of expressions can be expensive
0360             if (KDbField::isFPNumericType(type)) {
0361                 return KDbField::BigInteger;
0362             }
0363             switch (type) {
0364             case KDbField::Byte: return KDbField::ShortInteger;
0365             case KDbField::ShortInteger: return KDbField::Integer;
0366             case KDbField::Integer: return KDbField::BigInteger;
0367             case KDbField::Null: return KDbField::Null;
0368             case KDbField::InvalidType: return KDbField::InvalidType;
0369             default:;
0370             }
0371         }
0372         return KDbField::InvalidType;
0373     }
0374 private:
0375     Q_DISABLE_COPY(CeilingFloorFunctionDeclaration)
0376 };
0377 
0378 //! A map of built-in SQL functions
0379 //! See https://community.kde.org/Kexi/Plugins/Queries/SQL_Functions for the status.
0380 class BuiltInFunctions
0381 {
0382 public:
0383     BuiltInFunctions();
0384     ~BuiltInFunctions()
0385     {
0386         qDeleteAll(m_functions);
0387     }
0388 
0389     //! @return function declaration's structure for name @a name
0390     //! If @a name is alias of the function, e.g. "MIN" for "LEAST", the original
0391     //! function's declaration is returned.
0392     BuiltInFunctionDeclaration* value(const QString &name) const;
0393 
0394     //! @return a list of function aliases.
0395     QStringList aliases() const;
0396 
0397     static int multipleArgs[];
0398 private:
0399     QHash<QString, BuiltInFunctionDeclaration*> m_functions;
0400     QHash<QString, BuiltInFunctionDeclaration*> m_aliases;
0401     Q_DISABLE_COPY(BuiltInFunctions)
0402 };
0403 
0404 int BuiltInFunctions::multipleArgs[] = { 0 };
0405 
0406 BuiltInFunctions::BuiltInFunctions()
0407 {
0408     BuiltInFunctionDeclaration *decl;
0409 #define _TYPES(name, ...) static int name[] = { __VA_ARGS__, KDbField::InvalidType }
0410     _TYPES(argAnyTextOrNull, AnyText, KDbField::Null);
0411     _TYPES(argAnyIntOrNull, AnyInt, KDbField::Null);
0412     _TYPES(argAnyNumberOrNull, AnyNumber, KDbField::Null);
0413     _TYPES(argAnyFloatOrNull, AnyFloat, KDbField::Null);
0414     Q_UNUSED(argAnyFloatOrNull);
0415     _TYPES(argAnyOrNull, Any, KDbField::Null);
0416     _TYPES(argBLOBOrNull, KDbField::BLOB, KDbField::Null);
0417     Q_UNUSED(argBLOBOrNull);
0418     _TYPES(argAnyTextBLOBOrNull, AnyText, KDbField::BLOB, KDbField::Null);
0419 #undef _TYPES
0420 
0421 //! Adds a signature named @a name with specified arguments to declaration decl
0422 #define _SIG(name, ...) \
0423     static int* name[] = { __VA_ARGS__, nullptr }; \
0424     decl->signatures.push_back(name)
0425 
0426 //! Adds a signature with no arguments to declaration decl
0427 #define _SIG0 \
0428     decl->signatures.push_back(sig0)
0429 
0430     static int* sig0[] = { nullptr };
0431 
0432     m_functions.insert(QLatin1String("ABS"), decl = new BuiltInFunctionDeclaration);
0433     // From https://www.sqlite.org/lang_corefunc.html
0434     /* The abs(X) function returns the absolute value of the numeric argument X.
0435      Abs(X) returns NULL if X is NULL. Abs(X) returns 0.0 if X is a string or blob that
0436      cannot be converted to a numeric value. If X is the integer -9223372036854775808
0437      then abs(X) throws an integer overflow error since there is no equivalent positive
0438      64-bit two complement value. */
0439     // example: SELECT ABS(-27), ABS(-3.1415), ABS(NULL + 1)
0440     // result: 27, 3.1415, NULL
0441     decl->copyReturnTypeFromArg = 0;
0442     _SIG(abs_1, argAnyNumberOrNull);
0443 
0444     m_functions.insert(QLatin1String("CEILING"), decl = new CeilingFloorFunctionDeclaration);
0445     /* ceiling(X) returns the largest integer value not less than X. */
0446     // See also https://dev.mysql.com/doc/refman/5.1/en/mathematical-functions.html#function_ceiling
0447     // See also https://www.postgresql.org/docs/9.5/static/functions-math.html#FUNCTIONS-MATH-FUNC-TABLE
0448     // SQLite has no equivalent of ceiling() so this is used:
0449     // (CASE WHEN X = CAST(X AS INT) THEN CAST(X AS INT) WHEN X >= 0 THEN CAST(X AS INT) + 1 ELSE CAST(X AS INT) END)
0450     //! @todo add a custom function to SQLite to optimize/simplify things
0451     // example: SELECT CEILING(3.14), CEILING(-99.001)
0452     // result: 4, -99
0453     _SIG(ceiling, argAnyNumberOrNull);
0454 
0455     m_functions.insert(QLatin1String("CHAR"), decl = new BuiltInFunctionDeclaration);
0456     // From https://www.sqlite.org/lang_corefunc.html
0457     /* The char(X1,X2,...,XN) function returns a string composed of characters having
0458      the unicode code point values of integers X1 through XN, respectively. */
0459     // example: SELECT CHAR(75,69,88,73), CHAR()
0460     // result: "KEXI" ""
0461     decl->defaultReturnType = KDbField::LongText;
0462     static int char_min_args[] = { 0 };
0463     _SIG(char_N, argAnyIntOrNull, multipleArgs, char_min_args);
0464 
0465     m_functions.insert(QLatin1String("COALESCE"), decl = new CoalesceFunctionDeclaration);
0466     // From https://www.sqlite.org/lang_corefunc.html
0467     /* The coalesce() function returns a copy of its first non-NULL argument, or NULL if
0468      all arguments are NULL. Coalesce() must have at least 2 arguments. */
0469     // example: SELECT COALESCE(NULL, 17, NULL, "A")
0470     // result: 17
0471     static int coalesce_min_args[] = { 2 };
0472     _SIG(coalesce_N, argAnyOrNull, multipleArgs, coalesce_min_args);
0473 
0474     m_functions.insert(QLatin1String("FLOOR"), decl = new CeilingFloorFunctionDeclaration);
0475     /* floor(X) returns the largest integer value not greater than X. */
0476     // See also https://dev.mysql.com/doc/refman/5.1/en/mathematical-functions.html#function_floor
0477     // See also https://www.postgresql.org/docs/9.5/static/functions-math.html#FUNCTIONS-MATH-FUNC-TABLE
0478     // SQLite has no equivalent of floor() so this is used:
0479     // (CASE WHEN X >= 0 OR X = CAST(X AS INT) THEN CAST(X AS INT) ELSE CAST(X AS INT) - 1 END)
0480     //! @todo add a custom function to SQLite to optimize/simplify things
0481     // example: SELECT FLOOR(3.14), FLOOR(-99.001)
0482     // result: 3, -100
0483     _SIG(floor, argAnyNumberOrNull);
0484 
0485     m_functions.insert(QLatin1String("GREATEST"), decl = new MinMaxFunctionDeclaration);
0486     m_aliases.insert(QLatin1String("MAX"), decl);
0487     // From https://www.sqlite.org/lang_corefunc.html
0488     // For SQLite MAX() is used.
0489     // If arguments are of text type, to each argument default (unicode) collation
0490     // is assigned that is configured for SQLite by KDb.
0491     // Example: SELECT MAX('ą' COLLATE '', 'z' COLLATE '').
0492     // Example: SELECT MAX('ą' COLLATE '', 'z' COLLATE '').
0493     /* The multi-argument max() function returns the argument with the maximum value, or
0494     return NULL if any argument is NULL. The multi-argument max() function searches its
0495     arguments from left to right for an argument that defines a collating function and
0496     uses that collating function for all string comparisons. If none of the arguments to
0497     max() define a collating function, then the BINARY collating function is used. Note
0498     that max() is a simple function when it has 2 or more arguments but operates as an
0499     aggregate function if given only a single argument. */
0500     // For pgsql GREATEST() function ignores NULL values, it only returns NULL
0501     // if all the expressions evaluate to NULL. So this is used for MAX(v0,..,vN):
0502     // (CASE WHEN (v0) IS NULL OR .. OR (vN) IS NULL THEN NULL ELSE GREATEST(v0,..,vN) END)
0503     // See also https://www.postgresql.org/docs/9.5/static/functions-conditional.html#FUNCTIONS-GREATEST-LEAST
0504     //! @todo for pgsql CREATE FUNCTION can be used to speed up and simplify things
0505     // For mysql GREATEST() is used.
0506     // See https://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#function_greatest
0507     // Note: Before MySQL 5.0.13, GREATEST() returns NULL only if all arguments are NULL
0508     // (like pgsql). As of 5.0.13, it returns NULL if any argument is NULL (like sqlite's MAX()).
0509     // See also https://bugs.mysql.com/bug.php?id=15610
0510     //! @todo MySQL: check for server version and don't use the pgsql's approach for ver >= 5.0.13
0511     //!       We cannot do that now because we only have access to driver, not the connection.
0512     // example: SELECT GREATEST("Z", "ą", "AA"), MAX(0.1, 7.1, 7), GREATEST(9, NULL, -1)
0513     // result: "Z", 7.1, NULL
0514     static int greatest_min_args[] = { 2 };
0515     _SIG(greatest_N, argAnyOrNull, multipleArgs, greatest_min_args);
0516 
0517     m_functions.insert(QLatin1String("HEX"), decl = new BuiltInFunctionDeclaration);
0518     // From https://www.sqlite.org/lang_corefunc.html
0519     // See also https://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_hex
0520     /* The hex() function interprets its argument as a BLOB and returns a string which is
0521     the upper-case hexadecimal rendering of the content of that blob. */
0522     /* For pgsql UPPER(ENCODE(val, 'hex')) is used,
0523        See https://www.postgresql.org/docs/9.5/static/functions-string.html#FUNCTIONS-STRING-OTHER */
0524     // example: SELECT HEX(X'BEEF'), HEX('DEAD')
0525     // result: "BEEF", "44454144"
0526     //! @todo HEX(int) for SQLite is not the same as HEX(int) for MySQL so we disable it
0527     //!       -- maybe can be wrapped?
0528     decl->defaultReturnType = KDbField::LongText;
0529     _SIG(hex_1, argAnyTextBLOBOrNull);
0530 
0531     m_functions.insert(QLatin1String("IFNULL"), decl = new CoalesceFunctionDeclaration);
0532     // From https://www.sqlite.org/lang_corefunc.html
0533     /* The ifnull() function returns a copy of its first non-NULL argument, or NULL if
0534      both arguments are NULL. Ifnull() must have exactly 2 arguments. The ifnull() function
0535      is equivalent to coalesce() with two arguments. */
0536     // For postgresql coalesce() is used.
0537     // example: SELECT IFNULL(NULL, 17), IFNULL(NULL, NULL)
0538     // result: 17, NULL
0539     _SIG(ifnull_2, argAnyOrNull, argAnyOrNull);
0540 
0541     m_functions.insert(QLatin1String("INSTR"), decl = new BuiltInFunctionDeclaration);
0542     // From https://www.sqlite.org/lang_corefunc.html
0543     /* The instr(X,Y) function finds the first occurrence of string Y within string X and
0544      returns the number of prior characters plus 1, or 0 if Y is nowhere found within X.
0545      If both arguments X and Y to instr(X,Y) are non-NULL and are not BLOBs then both are
0546      interpreted as strings. If either X or Y are NULL in instr(X,Y) then the result
0547      is NULL. */
0548     //! @todo PostgreSQL does not have instr() but CREATE FUNCTION can be used,
0549     //!       see https://www.postgresql.org/docs/9.5/static/plpgsql-porting.html
0550     //! @todo support (BLOB, BLOB)?
0551     /* From the same docs:
0552      Or, if X and Y are both BLOBs, then instr(X,Y) returns one more than the number bytes
0553      prior to the first occurrence of Y, or 0 if Y does not occur anywhere within X. */
0554     // example: SELECT INSTR("KEXI", "X"), INSTR("KEXI", "ZZ")
0555     // result: 3, 0
0556     decl->defaultReturnType = KDbField::Integer;
0557     _SIG(instr_2, argAnyTextOrNull, argAnyTextOrNull);
0558 
0559     m_functions.insert(QLatin1String("LEAST"), decl = new MinMaxFunctionDeclaration);
0560     m_aliases.insert(QLatin1String("MIN"), decl);
0561     // From https://www.sqlite.org/lang_corefunc.html
0562     // For SQLite uses MIN().
0563     /* The multi-argument min() function returns the argument with the minimum value, or
0564     return NULL if any argument is NULL. The multi-argument min() function searches its
0565     arguments from left to right for an argument that defines a collating function and
0566     uses that collating function for all string comparisons. If none of the arguments to
0567     max() define a collating function, then the BINARY collating function is used. Note
0568     that max() is a simple function when it has 2 or more arguments but operates as an
0569     aggregate function if given only a single argument. */
0570     // For pgsql LEAST() function ignores NULL values, it only returns NULL
0571     // if all the expressions evaluate to NULL. So this is used for MAX(v0,..,vN):
0572     // (CASE WHEN (v0) IS NULL OR .. OR (vN) IS NULL THEN NULL ELSE LEAST(v0,..,vN) END)
0573     // See also https://www.postgresql.org/docs/9.5/static/functions-conditional.html#FUNCTIONS-GREATEST-LEAST
0574     //! @todo for pgsql CREATE FUNCTION can be used to speed up and simplify things
0575     // For mysql LEAST() is used.
0576     // See https://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#function_least
0577     // Note: Before MySQL 5.0.13, LEAST() returns NULL only if all arguments are NULL
0578     // (like pgsql). As of 5.0.13, it returns NULL if any argument is NULL (like sqlite's MIN()).
0579     //! @todo MySQL: check for server version and don't use the pgsql's approach for ver >= 5.0.13
0580     //!       We cannot do that now because we only have access to driver, not the connection.
0581     // See also https://bugs.mysql.com/bug.php?id=15610
0582     // example: SELECT LEAST("Z", "ą", "AA"), MIN(0.1, 7.1, 7), LEAST(9, NULL, -1)
0583     // result: "ą", 0.1, NULL
0584     static int least_min_args[] = { 2 };
0585     _SIG(least_N, argAnyOrNull, multipleArgs, least_min_args);
0586 
0587     m_functions.insert(QLatin1String("LENGTH"), decl = new BuiltInFunctionDeclaration);
0588     // From https://www.sqlite.org/lang_corefunc.html
0589     // See also https://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_length
0590     /* For a string value X, the length(X) function returns the number of characters (not
0591     bytes) in X prior to the first NUL character. Since SQLite strings do not normally
0592     contain NUL characters, the length(X) function will usually return the total number
0593     of characters in the string X. For a blob value X, length(X) returns the number of
0594     bytes in the blob. If X is NULL then length(X) is NULL. If X is numeric then
0595     length(X) returns the length of a string representation of X. */
0596     /* For postgres octet_length(val) is used if val is a of BLOB type.
0597        length(val) for BLOB cannot be used because it returns number of bits. */
0598     /* For mysql char_length(val) is used.
0599        This is because length(val) in mysql returns number of bytes, what is not right for
0600        multibyte (unicode) encodings. */
0601     // example: SELECT LENGTH('Straße'), LENGTH(X'12FE')
0602     // result: 6, 2
0603     decl->defaultReturnType = KDbField::Integer;
0604     _SIG(length_1, argAnyTextBLOBOrNull);
0605 
0606     m_functions.insert(QLatin1String("LOWER"), decl = new BuiltInFunctionDeclaration);
0607     // From https://www.sqlite.org/lang_corefunc.html
0608     /* The lower(X) function returns a copy of string X with all characters converted
0609      to lower case. */
0610     // Note: SQLite such as 3.8 without ICU extension does not convert non-latin1 characters
0611     // too well; Kexi uses ICU extension by default so the results are very good.
0612     // See also https://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_lower
0613     // See also https://www.postgresql.org/docs/9.5/static/functions-string.html#FUNCTIONS-STRING-SQL
0614     // example: SELECT LOWER("MEGSZENTSÉGTELENÍTHETETLENSÉGESKEDÉSEITEKÉRT")
0615     // result: "megszentségteleníthetetlenségeskedéseitekért"
0616     decl->defaultReturnType = KDbField::LongText;
0617     _SIG(lower_1, argAnyTextOrNull);
0618 
0619     m_functions.insert(QLatin1String("LTRIM"), decl = new BuiltInFunctionDeclaration);
0620     // From https://www.sqlite.org/lang_corefunc.html
0621     /* The ltrim(X,Y) function returns a string formed by removing any and all characters
0622      that appear in Y from the left side of X. If the Y argument is omitted, ltrim(X)
0623      removes spaces from the left side of X.*/
0624     // See also https://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_ltrim
0625     //! @todo MySQL's LTRIM only supports one arg. TRIM() does not work too
0626     //! https://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_trim
0627     // See also https://www.postgresql.org/docs/9.5/static/functions-string.html#FUNCTIONS-STRING-SQL
0628     // example: SELECT LTRIM("  John Smith")
0629     // result: "John Smith"
0630     // example: SELECT LTRIM("a b or c", "ab ")
0631     // result: "or c"
0632     decl->defaultReturnType = KDbField::LongText;
0633     _SIG(ltrim_1, argAnyTextOrNull);
0634     _SIG(ltrim_2, argAnyTextOrNull, argAnyTextOrNull);
0635 
0636     m_functions.insert(QLatin1String("NULLIF"), decl = new BuiltInFunctionDeclaration);
0637     // From https://www.sqlite.org/lang_corefunc.html
0638     /* The nullif(X,Y) function returns its first argument if the arguments are different
0639      and NULL if the arguments are the same. The nullif(X,Y) function searches its
0640      arguments from left to right for an argument that defines a collating function and
0641      uses that collating function for all string comparisons. If neither argument to
0642      nullif() defines a collating function then the BINARY is used. */
0643     // See also https://dev.mysql.com/doc/refman/5.1/en/control-flow-functions.html#function_nullif
0644     // See also https://www.postgresql.org/docs/9.5/static/functions-conditional.html#FUNCTIONS-NULLIF
0645     // example: SELECT NULLIF("John", "Smith"), NULLIF(177, 177)
0646     // result: "John", NULL
0647     decl->copyReturnTypeFromArg = 0;
0648     _SIG(nullif_2, argAnyOrNull, argAnyOrNull);
0649 
0650     m_functions.insert(QLatin1String("RANDOM"), decl = new RandomFunctionDeclaration);
0651     /* RANDOM() returns a random floating-point value v in the range 0 <= v < 1.0.
0652      RANDOM(X,Y) - returns returns a random integer that is equal or greater than X
0653      and less than Y. */
0654     // For MySQL RANDOM() is equal to RAND().
0655     // For MySQL RANDOM(X,Y) is equal to (X + FLOOR(RAND() * (Y - X))
0656     // For PostreSQL RANDOM() is equal to RANDOM().
0657     // For PostreSQL RANDOM(X,Y) is equal to (X + FLOOR(RANDOM() * (Y - X))
0658     // Because SQLite returns integer between -9223372036854775808 and +9223372036854775807,
0659     // so RANDOM() for SQLite is equal to (RANDOM()+9223372036854775807)/18446744073709551615.
0660     // Similarly, RANDOM(X,Y) for SQLite is equal
0661     // to (X + CAST((Y - X) * (RANDOM()+9223372036854775807)/18446744073709551615 AS INT)).
0662     // See also https://dev.mysql.com/doc/refman/5.1/en/mathematical-functions.html#function_rand
0663     // See also https://www.postgresql.org/docs/9.5/static/functions-math.html#FUNCTIONS-MATH-RANDOM-TABLE
0664     //! @note rand(X) (where X is a seed value to set) isn't portable between MySQL and PostgreSQL,
0665     //! and does not exist in SQLite, so we don't support it.
0666     // example: SELECT RANDOM(), RANDOM(2, 5)
0667     // result: (some random floating-point value v where 0 <= v < 1.0)
0668     // example: SELECT RANDOM(2, 5)
0669     // result: (some random integer value v where 2 <= v < 5)
0670     decl->defaultReturnType = KDbField::Double;
0671     _SIG0;
0672     _SIG(random_2, argAnyIntOrNull, argAnyIntOrNull);
0673 
0674     m_functions.insert(QLatin1String("ROUND"), decl = new BuiltInFunctionDeclaration);
0675     // From https://www.sqlite.org/lang_corefunc.html
0676     /* The round(X,Y) function returns a floating-point value X rounded to Y digits to the
0677      right of the decimal point. If the Y argument is omitted, it is assumed to be 0. */
0678     // See also https://dev.mysql.com/doc/refman/5.1/en/mathematical-functions.html#function_round
0679     // See also https://www.postgresql.org/docs/9.5/static/functions-math.html#FUNCTIONS-MATH-FUNC-TABLE
0680     //! @note round(X,Y) where Y < 0 is supported only by MySQL so we ignore this case
0681     // example: SELECT ROUND(-1.13), ROUND(-5.51), ROUND(5.51), ROUND(1.298, 1), ROUND(1.298, 0), ROUND(7)
0682     // result: -1, -6, 6, 1.3, 1, 7
0683     decl->copyReturnTypeFromArg = 0;
0684     _SIG(round_1, argAnyNumberOrNull);
0685     _SIG(round_2, argAnyNumberOrNull, argAnyIntOrNull);
0686 
0687     m_functions.insert(QLatin1String("RTRIM"), decl = new BuiltInFunctionDeclaration);
0688     // From https://www.sqlite.org/lang_corefunc.html
0689     /* The rtrim(X,Y) function returns a string formed by removing any and all characters
0690      that appear in Y from the right side of X. If the Y argument is omitted, rtrim(X)
0691      removes spaces from the right side of X. */
0692     // See also https://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_ltrim
0693     //! @todo MySQL's RTRIM only supports one arg. TRIM() does not work too
0694     //! https://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_trim
0695     // See also https://www.postgresql.org/docs/9.5/static/functions-string.html#FUNCTIONS-STRING-SQL
0696     // example: SELECT RTRIM("John Smith   ")
0697     // result: "John Smith"
0698     // example: SELECT RTRIM("a b or c", "orc ")
0699     // result: "a b"
0700     decl->defaultReturnType = KDbField::LongText;
0701     _SIG(rtrim_1, argAnyTextOrNull);
0702     _SIG(rtrim_2, argAnyTextOrNull, argAnyTextOrNull);
0703 
0704     m_functions.insert(QLatin1String("SOUNDEX"), decl = new BuiltInFunctionDeclaration);
0705     // From https://www.sqlite.org/lang_corefunc.html
0706     /* The soundex(X) function returns a string that is the soundex encoding of the string
0707      X. The string "?000" is returned if the argument is NULL or contains non-ASCII
0708      alphabetic characters. */
0709     // See also https://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_soundex
0710     // See also https://www.postgresql.org/docs/9.5/static/fuzzystrmatch.html#AEN165853
0711     //! @todo we call drv_executeSql("CREATE EXTENSION IF NOT EXISTS fuzzystrmatch") on connection,
0712     //!       do that on first use of SOUNDEX()
0713     // example: SELECT SOUNDEX("John")
0714     // result: "J500"
0715     decl->defaultReturnType = KDbField::Text;
0716     _SIG(soundex, argAnyTextOrNull);
0717 
0718     m_functions.insert(QLatin1String("SUBSTR"), decl = new BuiltInFunctionDeclaration);
0719     // From https://www.sqlite.org/lang_corefunc.html
0720     /* The substr(X,Y) returns all characters through the end of the string X beginning with
0721     the Y-th. The left-most character of X is number 1. If Y is negative then the
0722     first character of the substring is found by counting from the right rather than
0723     the left. If Z is negative then the abs(Z) characters preceding the Y-th
0724     character are returned. If X is a string then characters indices refer to actual
0725     UTF-8 characters. If X is a BLOB then the indices refer to bytes. */
0726     _SIG(substr_2, argAnyTextOrNull, argAnyIntOrNull);
0727     /* The substr(X,Y,Z) function returns a substring of input string X that begins
0728     with the Y-th character and which is Z characters long. */
0729     _SIG(substr_3, argAnyTextOrNull, argAnyIntOrNull, argAnyIntOrNull);
0730     decl->copyReturnTypeFromArg = 0;
0731 
0732      m_functions.insert(QLatin1String("TRIM"), decl = new BuiltInFunctionDeclaration);
0733      // From https://www.sqlite.org/lang_corefunc.html
0734      /* The trim(X,Y) function returns a string formed by removing any and all characters
0735       that appear in Y from both ends of X. If the Y argument is omitted, trim(X) removes
0736       spaces from both ends of X. */
0737      // See also https://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_trim
0738      //! @todo MySQL's TRIM only supports one arg. TRIM() does not work too
0739      //! https://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_trim
0740      // See also https://www.postgresql.org/docs/9.5/static/functions-string.html#FUNCTIONS-STRING-SQL
0741      // example: SELECT TRIM("  John Smith   ")
0742      // result: "John Smith"
0743      // example: SELECT TRIM("a b or c", "orca ")
0744      // result: "b"
0745      decl->defaultReturnType = KDbField::LongText;
0746      _SIG(trim_1, argAnyTextOrNull);
0747      _SIG(trim_2, argAnyTextOrNull, argAnyTextOrNull);
0748 
0749      m_functions.insert(QLatin1String("UNICODE"), decl = new BuiltInFunctionDeclaration);
0750      // From https://www.sqlite.org/lang_corefunc.html
0751      /* The unicode(X) function returns the numeric unicode code point corresponding to
0752       the first character of the string X. If the argument to unicode(X) is not a string
0753       then the result is undefined. */
0754      // For MySQL ORD(CONVERT(X USING UTF16)) is used (ORD(X) returns a UTF-16 number)
0755      // For PostreSQL ASCII(X) is used.
0756      // example: SELECT UNICODE('A'), UNICODE('ą'), UNICODE('Δ'), UNICODE('葉')
0757      // result: 65, 261, 916, 33865
0758      decl->defaultReturnType = KDbField::Integer;
0759      _SIG(unicode_1, argAnyTextOrNull);
0760 
0761      m_functions.insert(QLatin1String("UPPER"), decl = new BuiltInFunctionDeclaration);
0762      // From https://www.sqlite.org/lang_corefunc.html
0763      /* The upper(X) function returns a copy of string X with all characters converted
0764       to upper case. */
0765      // Note: SQLite such as 3.8 without ICU extension does not convert non-latin1 characters
0766      // too well; Kexi uses ICU extension by default so the results are very good.
0767      // See also https://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_upper
0768      // See also https://www.postgresql.org/docs/9.5/static/functions-string.html#FUNCTIONS-STRING-SQL
0769      // example: SELECT UPPER("megszentségteleníthetetlenségeskedéseitekért")
0770      // result: "MEGSZENTSÉGTELENÍTHETETLENSÉGESKEDÉSEITEKÉRT"
0771      decl->defaultReturnType = KDbField::LongText;
0772      _SIG(upper_1, argAnyTextOrNull);
0773 
0774 #ifdef KDB_ENABLE_SQLITE_SPECIFIC_FUNCTIONS
0775     m_functions.insert(QLatin1String("GLOB"), decl = new BuiltInFunctionDeclaration);
0776     //! @todo GLOB(X,Y) is SQLite-specific and is not present in MySQL so we don't expose it; use GLOB operator instead.
0777     //! We may want to address it in raw SQL generation time.
0778     // From https://www.sqlite.org/lang_corefunc.html
0779     /* The glob(X,Y) function is equivalent to the expression "Y GLOB X". Note that the
0780      X and Y arguments are reversed in the glob() function relative to the infix GLOB
0781      operator. */
0782     // example: SELECT GLOB("Foo*", "FooBar"), GLOB("Foo*", "foobar")
0783     // result: TRUE, FALSE
0784     decl->defaultReturnType = KDbField::Boolean;
0785     _SIG(glob_2, argAnyTextOrNull, argAnyOrNull /* will be casted to text */);
0786 
0787     m_functions.insert(QLatin1String("LIKE"), decl = new BuiltInFunctionDeclaration);
0788     //! @todo LIKE(X,Y,[Z]) not present in MySQL so we don't expose it; use LIKE operator instead.
0789     //! We may want to address it in raw SQL generation time.
0790     // From https://www.sqlite.org/lang_corefunc.html
0791     /* The like() function is used to implement the "Y LIKE X [ESCAPE Z]" expression. If the
0792     optional ESCAPE clause is present, then the like() function is invoked with three
0793     arguments. Otherwise, it is invoked with two arguments only. Note that the X and Y
0794     parameters are reversed in the like() function relative to the infix LIKE operator.*/
0795     decl->defaultReturnType = KDbField::Boolean;
0796     _SIG(like_2, argAnyTextOrNull, argAnyTextOrNull);
0797     _SIG(like_3, argAnyTextOrNull, argAnyTextOrNull, argAnyTextOrNull);
0798 #endif
0799 }
0800 
0801 BuiltInFunctionDeclaration* BuiltInFunctions::value(const QString &name) const
0802 {
0803     BuiltInFunctionDeclaration* f = m_functions.value(name);
0804     if (!f) {
0805         f = m_aliases.value(name);
0806     }
0807     return f;
0808 }
0809 
0810 QStringList BuiltInFunctions::aliases() const
0811 {
0812     return m_aliases.keys();
0813 }
0814 
0815 Q_GLOBAL_STATIC(BuiltInFunctions, _builtInFunctions)
0816 
0817 //=========================================
0818 
0819 KDbFunctionExpressionData::KDbFunctionExpressionData()
0820  : KDbExpressionData()
0821 {
0822     ExpressionDebug << "FunctionExpressionData" << ref;
0823     setArguments(ExplicitlySharedExpressionDataPointer());
0824 }
0825 
0826 KDbFunctionExpressionData::KDbFunctionExpressionData(const QString& aName,
0827                                                      ExplicitlySharedExpressionDataPointer arguments)
0828         : KDbExpressionData()
0829         , name(aName)
0830 {
0831     setArguments(arguments);
0832     ExpressionDebug << "FunctionExpressionData" << ref << *args;
0833 }
0834 
0835 KDbFunctionExpressionData::~KDbFunctionExpressionData()
0836 {
0837     ExpressionDebug << "~FunctionExpressionData" << ref;
0838 }
0839 
0840 KDbFunctionExpressionData* KDbFunctionExpressionData::clone()
0841 {
0842     ExpressionDebug << "FunctionExpressionData::clone" << *this;
0843     KDbFunctionExpressionData *cloned = new KDbFunctionExpressionData(*this);
0844     ExpressionDebug << "FunctionExpressionData::clone" << *cloned;
0845     cloned->args = args->clone();
0846     return cloned;
0847 }
0848 
0849 void KDbFunctionExpressionData::debugInternal(QDebug dbg, KDb::ExpressionCallStack* callStack) const
0850 {
0851     dbg.nospace() << "FunctionExp(" << name;
0852     if (args.data()) {
0853         dbg.nospace() << ',';
0854         args.data()->debug(dbg, callStack);
0855     }
0856     dbg.nospace() << QString::fromLatin1(",type=%1)").arg(KDbDriver::defaultSqlTypeName(type()));
0857 }
0858 
0859 static QByteArray greatestOrLeastName(const QByteArray &name)
0860 {
0861     if (name == "MAX") {
0862         return "GREATEST";
0863     }
0864     if (name == "MIN") {
0865         return "LEAST";
0866     }
0867     return name;
0868 }
0869 
0870 KDbEscapedString KDbFunctionExpressionData::toStringInternal(
0871                                         const KDbDriver *driver,
0872                                         KDbQuerySchemaParameterValueListIterator* params,
0873                                         KDb::ExpressionCallStack* callStack) const
0874 {
0875     KDbNArgExpressionData *argsData = args->convert<KDbNArgExpressionData>();
0876     if (name == QLatin1String("HEX")) {
0877         if (driver) {
0878             return driver->hexFunctionToString(KDbNArgExpression(args), params, callStack);
0879         }
0880     }
0881     else if (name == QLatin1String("IFNULL")) {
0882         if (driver) {
0883             return driver->ifnullFunctionToString(KDbNArgExpression(args), params, callStack);
0884         }
0885     }
0886     else if (name == QLatin1String("LENGTH")) {
0887         if (driver) {
0888             return driver->lengthFunctionToString(KDbNArgExpression(args), params, callStack);
0889         }
0890     }
0891     else if (name == QLatin1String("GREATEST") || name == QLatin1String("MAX")
0892              || name == QLatin1String("LEAST") || name == QLatin1String("MIN"))
0893     {
0894         if (driver) {
0895             return driver->greatestOrLeastFunctionToString(
0896                 QString::fromLatin1(greatestOrLeastName(name.toLatin1())), KDbNArgExpression(args), params, callStack);
0897         }
0898         // else: don't change MIN/MAX
0899     }
0900     else if (name == QLatin1String("RANDOM")) {
0901         if (driver) {
0902             return driver->randomFunctionToString(KDbNArgExpression(args), params, callStack);
0903         }
0904     }
0905     else if (name == QLatin1String("CEILING") || name == QLatin1String("FLOOR")) {
0906         if (driver) {
0907             return driver->ceilingOrFloorFunctionToString(name, KDbNArgExpression(args), params, callStack);
0908         }
0909     }
0910     else if (name == QLatin1String("UNICODE")) {
0911         if (driver) {
0912             return driver->unicodeFunctionToString(KDbNArgExpression(args), params, callStack);
0913         }
0914     }
0915     return KDbFunctionExpressionData::toString(name, driver, argsData, params, callStack);
0916 }
0917 
0918 void KDbFunctionExpressionData::getQueryParameters(QList<KDbQuerySchemaParameter>* params)
0919 {
0920     Q_ASSERT(params);
0921     args->getQueryParameters(params);
0922 }
0923 
0924 KDbField::Type KDbFunctionExpressionData::typeInternal(KDb::ExpressionCallStack* callStack) const
0925 {
0926     Q_UNUSED(callStack);
0927     const BuiltInFunctionDeclaration *decl = _builtInFunctions->value(name);
0928     if (decl) {
0929         return decl->returnType(this, nullptr);
0930     }
0931     //! @todo
0932     return KDbField::InvalidType;
0933 }
0934 
0935 static void setIncorrectNumberOfArgumentsErrorMessage(KDbParseInfo *parseInfo, int count,
0936                                                       const std::vector<int> &argCounts,
0937                                                       const QString &name)
0938 {
0939     parseInfo->setErrorMessage(
0940                 KDbFunctionExpressionData::tr("Incorrect number of arguments (%1)").arg(count));
0941     const int maxArgCount = argCounts[argCounts.size() - 1];
0942     const int minArgCount = argCounts[0];
0943     QString firstSentence;
0944     if (count > maxArgCount) {
0945         firstSentence = KDbFunctionExpressionData::tr("Too many arguments.%1", "don't use space before %1")
0946                                           .arg(QLatin1String(" "));
0947     }
0948     if (count < minArgCount) {
0949         firstSentence = KDbFunctionExpressionData::tr("Too few arguments.%1", "don't use space before %1")
0950                                           .arg(QLatin1String(" "));
0951     }
0952     if (argCounts.size() == 1) {
0953         const int c = argCounts[0];
0954         if (c == 0) {
0955             parseInfo->setErrorDescription(
0956                 KDbFunctionExpressionData::tr("%1%2() function does not accept any arguments.")
0957                                               .arg(firstSentence, name));
0958         }
0959         else if (c == 1) {
0960             parseInfo->setErrorDescription(
0961                 KDbFunctionExpressionData::tr("%1%2() function requires 1 argument.")
0962                                              .arg(firstSentence, name));
0963         }
0964         else {
0965             //~ singular %1%2() function requires %3 argument.
0966             //~ plural %1%2() function requires %3 arguments.
0967             parseInfo->setErrorDescription(
0968                 KDbFunctionExpressionData::tr("%1%2() function requires %3 argument(s).", "", c)
0969                                              .arg(firstSentence, name).arg(c));
0970         }
0971     }
0972     else if (argCounts.size() == 2) {
0973         const int c1 = argCounts[0];
0974         const int c2 = argCounts[1];
0975         if (c2 == 1) {
0976             parseInfo->setErrorDescription(
0977                 KDbFunctionExpressionData::tr("%1%2() function requires 0 or 1 argument.",
0978                                   "the function requires zero or one argument")
0979                                               .arg(firstSentence, name));
0980         }
0981         else {
0982             //~ singular %1%2() function requires %3 or %4 argument.
0983             //~ plural %1%2() function requires %3 or %4 arguments.
0984             parseInfo->setErrorDescription(
0985                 KDbFunctionExpressionData::tr("%1%2() function requires %3 or %4 argument(s).", "", c2)
0986                                              .arg(firstSentence, name).arg(c1).arg(c2));
0987         }
0988     }
0989     else if (argCounts.size() == 3) {
0990         //~ singular %1%2() function requires %3 or %4 or %5 argument.
0991         //~ plural %1%2() function requires %3 or %4 or %5 arguments.
0992         parseInfo->setErrorDescription(
0993             KDbFunctionExpressionData::tr("%1%2() function requires %3 or %4 or %5 argument(s).", "", argCounts[2])
0994                                          .arg(firstSentence, name).arg(argCounts[0])
0995                                          .arg(argCounts[1]).arg(argCounts[2]));
0996     }
0997     else {
0998         QString listCounts;
0999         for(std::vector<int>::const_iterator it(argCounts.begin()); it != argCounts.end(); ++it) {
1000             if (listCounts.isEmpty()) {
1001                 listCounts += QString::number(*it);
1002             } else {
1003                 listCounts = KDbFunctionExpressionData::tr("%1 or %2").arg(listCounts).arg(*it);
1004             }
1005         }
1006         parseInfo->setErrorDescription(
1007             KDbFunctionExpressionData::tr("%1%2() function requires %3 argument(s).", "",
1008                               argCounts[argCounts.size() - 1])
1009                               .arg(firstSentence, name, listCounts));
1010     }
1011 }
1012 
1013 static void setIncorrectTypeOfArgumentsErrorMessage(KDbParseInfo *parseInfo, int argNum,
1014                                                     KDbField::Type type,
1015                                                     int *argTypes, const QString &name)
1016 {
1017     QString listTypes;
1018     int *argType = argTypes;
1019     while(*argType != KDbField::InvalidType) {
1020         if (!listTypes.isEmpty()) {
1021             listTypes += KDbFunctionExpressionData::tr(" or ");
1022         }
1023         const KDbField::Type realFieldType = KDb::intToFieldType(*argType);
1024         if (realFieldType != KDbField::InvalidType) {
1025             listTypes += KDbFunctionExpressionData::tr("\"%1\"")
1026                             .arg(KDbField::typeName(realFieldType));
1027         }
1028         else if (*argType == KDbField::Null) {
1029             listTypes += KDbFunctionExpressionData::tr("\"%1\"")
1030                             .arg(KDbField::typeName(KDbField::Null));
1031         }
1032         else if (*argType == AnyText) {
1033             listTypes += KDbFunctionExpressionData::tr("\"%1\"")
1034                             .arg(KDbField::typeName(KDbField::Text));
1035         }
1036         else if (*argType == AnyInt) {
1037             listTypes += KDbFunctionExpressionData::tr("\"%1\"")
1038                             .arg(KDbField::typeName(KDbField::Integer));
1039         }
1040         else if (*argType == AnyFloat) {
1041             listTypes += KDbFunctionExpressionData::tr("\"%1\"")
1042                             .arg(KDbField::typeGroupName(KDbField::FloatGroup));
1043                          // better than typeName() in this case
1044         }
1045         else if (*argType == AnyNumber) {
1046             listTypes += KDbFunctionExpressionData::tr("\"Number\"");
1047         }
1048         else if (*argType == Any) {
1049             listTypes += KDbFunctionExpressionData::tr("\"Any\"", "Any data type");
1050         }
1051         ++argType;
1052     }
1053     parseInfo->setErrorMessage(KDbFunctionExpressionData::tr("Incorrect type of argument"));
1054     QString lastSentence
1055         = KDbFunctionExpressionData::tr("Specified argument is of type \"%1\".")
1056             .arg(KDbField::typeName(type));
1057     if (argNum == 0) {
1058         parseInfo->setErrorDescription(
1059             KDbFunctionExpressionData::tr("%1() function's first argument should be of type %2. %3")
1060                                           .arg(name, listTypes, lastSentence));
1061     }
1062     else if (argNum == 1) {
1063         parseInfo->setErrorDescription(
1064             KDbFunctionExpressionData::tr("%1() function's second argument should be of type %2. %3")
1065                                           .arg(name, listTypes, lastSentence));
1066     }
1067     else if (argNum == 2) {
1068         parseInfo->setErrorDescription(
1069             KDbFunctionExpressionData::tr("%1() function's third argument should be of type %2. %3")
1070                                           .arg(name, listTypes, lastSentence));
1071     }
1072     else if (argNum == 3) {
1073         parseInfo->setErrorDescription(
1074             KDbFunctionExpressionData::tr("%1() function's fourth argument should be of type %2. %3")
1075                                           .arg(name, listTypes, lastSentence));
1076     }
1077     else if (argNum == 4) {
1078         parseInfo->setErrorDescription(
1079             KDbFunctionExpressionData::tr("%1() function's fifth argument should be of type %2. %3")
1080                                           .arg(name, listTypes, lastSentence));
1081     }
1082     else {
1083         parseInfo->setErrorDescription(
1084             KDbFunctionExpressionData::tr("%1() function's %2 argument should be of type %3. %4")
1085                                           .arg(name).arg(argNum + 1).arg(listTypes, lastSentence));
1086     }
1087 }
1088 
1089 //! @return true if type rule @a argType matches concrete type @a actualType
1090 static bool typeMatches(int argType, KDbField::Type actualType)
1091 {
1092     if (argType == AnyText) {
1093         if (KDbField::isTextType(actualType)) {
1094             return true;
1095         }
1096     }
1097     else if (argType == AnyInt) {
1098         if (KDbField::isIntegerType(actualType)) {
1099             return true;
1100         }
1101     }
1102     else if (argType == AnyFloat) {
1103         if (KDbField::isFPNumericType(actualType)) {
1104             return true;
1105         }
1106     }
1107     else if (argType == AnyNumber) {
1108         if (KDbField::isNumericType(actualType)) {
1109             return true;
1110         }
1111     }
1112     else if (argType == Any) {
1113         return true;
1114     }
1115     else {
1116         if (argType == actualType) {
1117             return true;
1118         }
1119     }
1120     return false;
1121 }
1122 
1123 static int findMatchingType(int *argTypePtr, KDbField::Type actualType)
1124 {
1125     for (; *argTypePtr != KDbField::InvalidType; ++argTypePtr) {
1126         if (typeMatches(*argTypePtr, actualType)) {
1127             break;
1128         }
1129     }
1130     return *argTypePtr;
1131 }
1132 
1133 bool KDbFunctionExpressionData::validateInternal(KDbParseInfo *parseInfo,
1134                                                  KDb::ExpressionCallStack* callStack)
1135 {
1136     if (!args->validate(parseInfo, callStack)) {
1137         return false;
1138     }
1139     if (args->token != ',') { // arguments required: NArgExpr with token ','
1140         return false;
1141     }
1142     if (args->children.count() > KDB_MAX_FUNCTION_ARGS) {
1143         parseInfo->setErrorMessage(
1144             tr("Too many arguments for function."));
1145         parseInfo->setErrorDescription(
1146             tr("Maximum number of arguments for function %1() is %2.")
1147                .arg(args->children.count()).arg(KDB_MAX_FUNCTION_ARGS));
1148         return false;
1149     }
1150     if (!args->validate(parseInfo)) {
1151         return false;
1152     }
1153     if (name.isEmpty()) {
1154         return false;
1155     }
1156     const BuiltInFunctionDeclaration *decl = _builtInFunctions->value(name);
1157     if (!decl) {
1158         return false;
1159     }
1160     const KDbNArgExpressionData *argsData = args->convertConst<KDbNArgExpressionData>();
1161     if (argsData->containsInvalidArgument()) {
1162         return false;
1163     }
1164 
1165     // Find matching signature
1166     int **signature = nullptr;
1167     bool multipleArgs = false; // special case, e.g. for CHARS(v1, ... vN)
1168     {
1169         const int count = args->children.count();
1170         bool properArgCount = false;
1171         std::vector<int> argCounts;
1172         int i = 0;
1173         argCounts.resize(decl->signatures.size());
1174         for(std::vector<int**>::const_iterator it(decl->signatures.begin());
1175             it != decl->signatures.end(); ++it, ++i)
1176         {
1177             signature = *it;
1178             int **arg = signature;
1179             int expectedCount = 0;
1180             while(*arg && *arg != BuiltInFunctions::multipleArgs) {
1181                 ++arg;
1182                 ++expectedCount;
1183             }
1184             multipleArgs = *arg == BuiltInFunctions::multipleArgs;
1185             if (multipleArgs) {
1186                 ++arg;
1187                 const int minArgs = arg[0][0];
1188                 properArgCount = count >= minArgs;
1189                 if (!properArgCount) {
1190                     parseInfo->setErrorMessage(
1191                         tr("Incorrect number of arguments (%1)").arg(count));
1192                     if (minArgs == 1) {
1193                         parseInfo->setErrorDescription(
1194                             tr("Too few arguments. %1() function requires "
1195                                "at least one argument.").arg(name));
1196                     }
1197                     else if (minArgs == 2) {
1198                         parseInfo->setErrorDescription(
1199                             tr("Too few arguments. %1() function requires "
1200                                "at least two arguments.").arg(name));
1201                     }
1202                     else if (minArgs == 3) {
1203                         parseInfo->setErrorDescription(
1204                             tr("Too few arguments. %1() function requires "
1205                                "at least three arguments.").arg(name));
1206                     }
1207                     else {
1208                         parseInfo->setErrorDescription(
1209                             tr("Too few arguments. %1() function requires "
1210                                "at least %2 arguments.").arg(name).arg(minArgs));
1211                     }
1212                     return false;
1213                 }
1214                 break;
1215             }
1216             else if (count == expectedCount) { // arg # matches
1217                 properArgCount = true;
1218                 break;
1219             }
1220             else {
1221                 argCounts[i] = expectedCount;
1222             }
1223         }
1224         if (!properArgCount) {
1225             const std::vector<int>::iterator last = std::unique(argCounts.begin(), argCounts.end());
1226             argCounts.erase(last, argCounts.end());
1227             std::sort(argCounts.begin(), argCounts.end()); // sort so we can easier check the case
1228             setIncorrectNumberOfArgumentsErrorMessage(parseInfo, count, argCounts, name);
1229             return false;
1230         }
1231     }
1232 
1233     // Verify types
1234     if (multipleArgs) { // special signature: {typesForAllArgs, [multipleArgs-token], MIN, 0}
1235         int **arg = signature;
1236         int *typesForAllArgs = arg[0];
1237         int i = 0;
1238         foreach(const ExplicitlySharedExpressionDataPointer &expr, args->children) {
1239             const KDbField::Type exprType = expr->type(); // cache: evaluating type of expressions can be expensive
1240             const bool isQueryParameter = expr->convertConst<KDbQueryParameterExpressionData>();
1241             if (!isQueryParameter) { // (query parameter always matches)
1242                 const int matchingType = findMatchingType(typesForAllArgs, exprType);
1243                 if (matchingType == KDbField::InvalidType) {
1244                     setIncorrectTypeOfArgumentsErrorMessage(parseInfo, i, exprType, typesForAllArgs, name);
1245                     return false;
1246                 }
1247             }
1248             ++i;
1249         }
1250     }
1251     else { // typical signature: array of type-lists
1252         int **arg = signature;
1253         int i=0;
1254         foreach(const ExplicitlySharedExpressionDataPointer &expr, args->children) {
1255             const KDbField::Type exprType = expr->type(); // cache: evaluating type of expressions can be expensive
1256             const bool isQueryParameter = expr->convertConst<KDbQueryParameterExpressionData>();
1257             if (!isQueryParameter) { // (query parameter always matches)
1258                 const int matchingType = findMatchingType(arg[0], exprType);
1259                 if (matchingType == KDbField::InvalidType) {
1260                     setIncorrectTypeOfArgumentsErrorMessage(parseInfo, i, exprType, arg[0], name);
1261                     return false;
1262                 }
1263             }
1264             ++arg;
1265             ++i;
1266         }
1267     }
1268 
1269     // Check type just now. If we checked earlier, possible error message would be less informative.
1270     if (decl->returnType(this, parseInfo) == KDbField::InvalidType) {
1271         return false;
1272     }
1273     return true;
1274 }
1275 
1276 void KDbFunctionExpressionData::setArguments(ExplicitlySharedExpressionDataPointer arguments)
1277 {
1278     args = (arguments && arguments->convert<KDbNArgExpressionData>())
1279             ? arguments : ExplicitlySharedExpressionDataPointer(new KDbNArgExpressionData);
1280     children.append(args);
1281     args->parent = this;
1282     args->token = ',';
1283     args->expressionClass = KDb::ArgumentListExpression;
1284 }
1285 
1286 //static
1287 KDbEscapedString KDbFunctionExpressionData::toString(
1288                                         const QString &name,
1289                                         const KDbDriver *driver,
1290                                         const KDbNArgExpressionData *args,
1291                                         KDbQuerySchemaParameterValueListIterator* params,
1292                                         KDb::ExpressionCallStack* callStack)
1293 {
1294     return KDbEscapedString(name + QLatin1Char('('))
1295            + args->toString(driver, params, callStack)
1296            + KDbEscapedString(')');
1297 }
1298 
1299 //=========================================
1300 
1301 inline KDb::ExpressionClass classForFunctionName(const QString& name)
1302 {
1303     if (KDbFunctionExpression::isBuiltInAggregate(name))
1304         return KDb::AggregationExpression;
1305     else
1306         return KDb::FunctionExpression;
1307 }
1308 
1309 KDbFunctionExpression::KDbFunctionExpression()
1310  : KDbExpression(new KDbFunctionExpressionData)
1311 {
1312     ExpressionDebug << "KDbFunctionExpression() ctor" << *this;
1313 }
1314 
1315 KDbFunctionExpression::KDbFunctionExpression(const QString& name)
1316         : KDbExpression(new KDbFunctionExpressionData(name),
1317               classForFunctionName(name), KDbToken()/*undefined*/)
1318 {
1319 }
1320 
1321 KDbFunctionExpression::KDbFunctionExpression(const QString& name,
1322                                              const KDbNArgExpression& arguments)
1323         : KDbExpression(new KDbFunctionExpressionData(name.toUpper(), arguments.d),
1324               classForFunctionName(name), KDbToken()/*undefined*/)
1325 {
1326 }
1327 
1328 KDbFunctionExpression::KDbFunctionExpression(const KDbFunctionExpression& expr)
1329         : KDbExpression(expr)
1330 {
1331 }
1332 
1333 KDbFunctionExpression::KDbFunctionExpression(KDbExpressionData* data)
1334     : KDbExpression(data)
1335 {
1336     ExpressionDebug << "KDbFunctionExpression ctor (KDbExpressionData*)" << *this;
1337 }
1338 
1339 KDbFunctionExpression::KDbFunctionExpression(const ExplicitlySharedExpressionDataPointer &ptr)
1340     : KDbExpression(ptr)
1341 {
1342 }
1343 
1344 KDbFunctionExpression::~KDbFunctionExpression()
1345 {
1346 }
1347 
1348 // static
1349 bool KDbFunctionExpression::isBuiltInAggregate(const QString& function)
1350 {
1351     return _builtInAggregates->data.contains(function.toUpper());
1352 }
1353 
1354 // static
1355 QStringList KDbFunctionExpression::builtInAggregates()
1356 {
1357     return _builtInAggregates->data.values();
1358 }
1359 
1360 //static
1361 KDbEscapedString KDbFunctionExpression::toString(
1362                                         const QString &name,
1363                                         const KDbDriver *driver,
1364                                         const KDbNArgExpression& args,
1365                                         KDbQuerySchemaParameterValueListIterator* params,
1366                                         KDb::ExpressionCallStack* callStack)
1367 {
1368     const KDbNArgExpressionData *argsData = args.d.constData()->convertConst<KDbNArgExpressionData>();
1369     return KDbFunctionExpressionData::toString(name, driver, argsData, params, callStack);
1370 }
1371 
1372 QString KDbFunctionExpression::name() const
1373 {
1374     return d->convert<KDbFunctionExpressionData>()->name;
1375 }
1376 
1377 void KDbFunctionExpression::setName(const QString &name)
1378 {
1379     d->convert<KDbFunctionExpressionData>()->name = name;
1380 }
1381 
1382 KDbNArgExpression KDbFunctionExpression::arguments()
1383 {
1384     return KDbNArgExpression(d->convert<KDbFunctionExpressionData>()->args);
1385 }
1386 
1387 void KDbFunctionExpression::setArguments(const KDbNArgExpression &arguments)
1388 {
1389     d->convert<KDbFunctionExpressionData>()->setArguments(arguments.d);
1390 }
1391 
1392 // static
1393 KDbEscapedString KDbFunctionExpression::greatestOrLeastFunctionUsingCaseToString(
1394                                         const QString &name,
1395                                         const KDbDriver *driver,
1396                                         const KDbNArgExpression &args,
1397                                         KDbQuerySchemaParameterValueListIterator* params,
1398                                         KDb::ExpressionCallStack* callStack)
1399 {
1400     // (CASE WHEN (v0) IS NULL OR .. OR (vN) IS NULL THEN NULL ELSE F(v0,..,vN) END)
1401     if (args.argCount() >= 2) {
1402         KDbEscapedString whenSql;
1403         whenSql.reserve(256);
1404         foreach(const ExplicitlySharedExpressionDataPointer &child, args.d.constData()->children) {
1405             if (!whenSql.isEmpty()) {
1406                 whenSql += " OR ";
1407             }
1408             whenSql += QLatin1Char('(') + child->toString(driver, params, callStack)
1409                     + QLatin1String(") IS NULL");
1410         }
1411         return KDbEscapedString("(CASE WHEN (") + whenSql
1412                + QLatin1String(") THEN NULL ELSE (")
1413                + KDbFunctionExpression::toString(name, driver, args, params, callStack)
1414                + QLatin1String(") END)");
1415     }
1416     return KDbFunctionExpression::toString(name, driver, args, params, callStack);
1417 }