From kde-commits  Mon Feb 29 23:21:25 2016
From: Jaroslaw Staniek <staniek () kde ! org>
Date: Mon, 29 Feb 2016 23:21:25 +0000
To: kde-commits
Subject: [kdb] /: Make <SELECT "a" + "b"> SQL queries work and fix <SELECT "a" || "b">
Message-Id: <E1aaX81-0005Dj-2C () scm ! kde ! org>
X-MARC-Message: https://marc.info/?l=kde-commits&m=145678809529595

Git commit f19ae68c17822b33097119974b2ee01c0b93c534 by Jaroslaw Staniek.
Committed on 29/02/2016 at 23:16.
Pushed by staniek into branch 'master'.

Make <SELECT "a" + "b"> SQL queries work and fix <SELECT "a" || "b">

Summary:
- port from calligra.git
- <SELECT "a" + "b"> parses in Kexi>=3D2.9.7 but results in invalid (boolea=
n) type/value
- for convenience, if either argument is of text type, operator "+" is now =
assumed to be identical to operator ||
- also fix SELECT "a" || "b" for MySQL (using CONCAT() is needed)
- add autotests and parser tests

https://phabricator.kde.org/T677
BUG:358636
FIXED-IN:2.9.11

Test Plan:
    Try test queries from the project attached at https://bugs.kde.org/show=
_bug.cgi?id=3D358636.
    Try other queries that use text columns instead of string constants.

Differential Revision: https://phabricator.kde.org/D867

(from calligra.git)

M  +9    -2    autotests/ExpressionsTest.cpp
M  +11   -0    autotests/parser/data/statements.txt
M  +9    -1    src/KDbDriver.cpp
M  +12   -1    src/KDbDriver.h
M  +9    -1    src/drivers/mysql/MysqlDriver.cpp
M  +7    -1    src/drivers/mysql/MysqlDriver.h
M  +17   -2    src/expression/KDbBinaryExpression.cpp
M  +1    -0    src/expression/KDbExpression.h

http://commits.kde.org/kdb/f19ae68c17822b33097119974b2ee01c0b93c534

diff --git a/autotests/ExpressionsTest.cpp b/autotests/ExpressionsTest.cpp
index 145dab1..aa61e91 100644
--- a/autotests/ExpressionsTest.cpp
+++ b/autotests/ExpressionsTest.cpp
@@ -1,5 +1,5 @@
 /* This file is part of the KDE project
-   Copyright (C) 2011-2015 Jaros=C5=82aw Staniek <staniek@kde.org>
+   Copyright (C) 2011-2016 Jaros=C5=82aw Staniek <staniek@kde.org>
 =

    This library is free software; you can redistribute it and/or
    modify it under the terms of the GNU Library General Public
@@ -700,6 +700,7 @@ void ExpressionsTest::testBinaryExpressionCloning_data()
     T(KDbToken::INTEGER_CONST, 3, KDbToken::AND, KDbToken::INTEGER_CONST, =
4, "3 AND 4");
     T(KDbToken::INTEGER_CONST, 3, KDbToken::XOR, KDbToken::INTEGER_CONST, =
4, "3 XOR 4");
     T(KDbToken::CHARACTER_STRING_LITERAL, "AB", KDbToken::CONCATENATION, K=
DbToken::CHARACTER_STRING_LITERAL, "CD", "'AB' || 'CD'");
+    T(KDbToken::CHARACTER_STRING_LITERAL, "AB", '+', KDbToken::CHARACTER_S=
TRING_LITERAL, "CD", "'AB' + 'CD'");
 #undef T
 }
 =

@@ -1299,12 +1300,18 @@ void ExpressionsTest::testBinaryExpressionValidate_=
data()
     T(KDbToken::REAL_CONST, 30.2, KDbToken::XOR, KDbToken::REAL_CONST, 20,=
 KDbField::InvalidType);
     // string
     T(KDbToken::CHARACTER_STRING_LITERAL, "ab", KDbToken::CONCATENATION, K=
DbToken::CHARACTER_STRING_LITERAL, "cd", KDbField::Text);
+    T(KDbToken::CHARACTER_STRING_LITERAL, "ab", '+', KDbToken::CHARACTER_S=
TRING_LITERAL, "cd", KDbField::Text);
+
     T(KDbToken::SQL_NULL, QVariant(), KDbToken::CONCATENATION, KDbToken::C=
HARACTER_STRING_LITERAL, "cd", KDbField::Null);
+    T(KDbToken::SQL_NULL, QVariant(), '+', KDbToken::CHARACTER_STRING_LITE=
RAL, "cd", KDbField::Null);
+
     T(KDbToken::INTEGER_CONST, 50, KDbToken::CONCATENATION, KDbToken::INTE=
GER_CONST, 20, KDbField::InvalidType);
     T(KDbToken::CHARACTER_STRING_LITERAL, "ab", KDbToken::CONCATENATION, K=
DbToken::INTEGER_CONST, 20, KDbField::InvalidType);
+    T(KDbToken::CHARACTER_STRING_LITERAL, "ab", '+', KDbToken::INTEGER_CON=
ST, 20, KDbField::InvalidType);
+
     T(KDbToken::CHARACTER_STRING_LITERAL, "ab", KDbToken::GREATER_OR_EQUAL=
, KDbToken::CHARACTER_STRING_LITERAL, "cd", KDbField::Boolean);
     T(KDbToken::CHARACTER_STRING_LITERAL, "ab", '<', KDbToken::INTEGER_CON=
ST, 3, KDbField::InvalidType);
-    T(KDbToken::CHARACTER_STRING_LITERAL, "ab", '+', KDbToken::CHARACTER_S=
TRING_LITERAL, "cd", KDbField::InvalidType);
+    T(KDbToken::CHARACTER_STRING_LITERAL, "ab", '+', KDbToken::CHARACTER_S=
TRING_LITERAL, "cd", KDbField::Text);
     T(KDbToken::CHARACTER_STRING_LITERAL, "A", KDbToken::OR, KDbToken::REA=
L_CONST, 20, KDbField::InvalidType);
     T(KDbToken::CHARACTER_STRING_LITERAL, "A", KDbToken::AND, KDbToken::RE=
AL_CONST, 20, KDbField::InvalidType);
     T(KDbToken::CHARACTER_STRING_LITERAL, "A", KDbToken::XOR, KDbToken::RE=
AL_CONST, 20, KDbField::InvalidType);
diff --git a/autotests/parser/data/statements.txt b/autotests/parser/data/s=
tatements.txt
index fc0c3e1..95648c2 100644
--- a/autotests/parser/data/statements.txt
+++ b/autotests/parser/data/statements.txt
@@ -92,6 +92,17 @@ select 'ABC' LIKE 'A%';
 select 3 SIMILAR TO 4;
 select 3 NOT SIMILAR TO 4;
 select 'AB' || 'CD';
+select 'AB' || 'CD' || 'EF';
+select 'AB' + 'CD' || 'EF' + 'GH' || '';
+select NULL || NULL;
+select NULL || 'AB';
+select 'AB' + NULL;
+-- ERROR: ||
+select 'AB' || 1;
+select 'AB' + 1;
+select 7 || 'AB';
+select 7 + 'AB';
+select 7 || 1;
 -- ERROR: **
 select 1**2;
 -- ERROR: * or / are not unary operators
diff --git a/src/KDbDriver.cpp b/src/KDbDriver.cpp
index 8752585..efd460d 100644
--- a/src/KDbDriver.cpp
+++ b/src/KDbDriver.cpp
@@ -1,5 +1,5 @@
 /* This file is part of the KDE project
-   Copyright (C) 2003-2015 Jaros=C5=82aw Staniek <staniek@kde.org>
+   Copyright (C) 2003-2016 Jaros=C5=82aw Staniek <staniek@kde.org>
 =

    This program is free software; you can redistribute it and/or
    modify it under the terms of the GNU Library General Public
@@ -356,6 +356,14 @@ KDbEscapedString KDbDriver::unicodeFunctionToString(
     return KDbFunctionExpression::toString(QLatin1String("UNICODE"), this,=
 args, params, callStack);
 }
 =

+KDbEscapedString KDbDriver::concatenateFunctionToString(const KDbBinaryExp=
ression &args,
+                                               KDbQuerySchemaParameterValu=
eListIterator* params,
+                                               KDb::ExpressionCallStack* c=
allStack) const
+{
+    return args.left().toString(this, params, callStack) + KDbEscapedStrin=
g("||")
+            + args.right().toString(this, params, callStack);
+}
+
 //---------------
 =

 Q_GLOBAL_STATIC_WITH_ARGS(
diff --git a/src/KDbDriver.h b/src/KDbDriver.h
index 540e9cc..5b0b286 100644
--- a/src/KDbDriver.h
+++ b/src/KDbDriver.h
@@ -1,5 +1,5 @@
 /* This file is part of the KDE project
-   Copyright (C) 2003-2015 Jaros=C5=82aw Staniek <staniek@kde.org>
+   Copyright (C) 2003-2016 Jaros=C5=82aw Staniek <staniek@kde.org>
 =

    This program is free software; you can redistribute it and/or
    modify it under the terms of the GNU Library General Public
@@ -40,6 +40,7 @@ class KDbConnectionOptions;
 class KDbDriverManager;
 class KDbDriverBehaviour;
 class KDbDriverMetaData;
+class KDbBinaryExpression;
 class KDbNArgExpression;
 class KDbQuerySchemaParameterValueListIterator;
 class DriverPrivate;
@@ -280,6 +281,16 @@ public:
                                             KDbQuerySchemaParameterValueLi=
stIterator* params,
                                             KDb::ExpressionCallStack* call=
Stack) const;
 =

+    //! Generates native (driver-specific) function call for concatenation=
 of two strings.
+    //! Default implementation USES infix "||" operator.
+    //! Special case is for MYSQL (CONCAT()).
+    //! @todo API supporting KDbNArgExpression would be useful so instead =
of a||b||c can be
+    //!       expressed as CONCAT(a,b,c) instead of CONCAT(CONCAT(a,b),c).
+    //!       This requires changes to the KDbSQL parser.
+    KDbEscapedString concatenateFunctionToString(const KDbBinaryExpression=
 &args,
+                                                 KDbQuerySchemaParameterVa=
lueListIterator* params,
+                                                 KDb::ExpressionCallStack*=
 callStack) const;
+
 protected:
     /*! Used by KDbDriverManager.
      Note for driver developers: Reimplement this.
diff --git a/src/drivers/mysql/MysqlDriver.cpp b/src/drivers/mysql/MysqlDri=
ver.cpp
index 70403a5..c6344ef 100644
--- a/src/drivers/mysql/MysqlDriver.cpp
+++ b/src/drivers/mysql/MysqlDriver.cpp
@@ -2,7 +2,7 @@
    Copyright (C) 2002 Lucijan Busch <lucijan@gmx.at>
    Copyright (C) 2003 Daniel Molkentin <molkentin@kde.org>
    Copyright (C) 2003 Joseph Wenninger<jowenn@kde.org>
-   Copyright (C) 2003-2015 Jaros=C5=82aw Staniek <staniek@kde.org>
+   Copyright (C) 2003-2016 Jaros=C5=82aw Staniek <staniek@kde.org>
 =

    This program is free software; you can redistribute it and/or
    modify it under the terms of the GNU Library General Public
@@ -221,4 +221,12 @@ KDbEscapedString MysqlDriver::unicodeFunctionToString(
                             .arg(args.arg(0).toString(this, params, callSt=
ack));
 }
 =

+KDbEscapedString MysqlDriver::concatenateFunctionToString(const KDbBinaryE=
xpression &args,
+                                                          KDbQuerySchemaPa=
rameterValueListIterator* params,
+                                                          KDb::ExpressionC=
allStack* callStack) const
+{
+    return KDbEscapedString("CONCAT(%1, %2)").arg(args.left().toString(thi=
s, params, callStack))
+                                             .arg(args.right().toString(th=
is, params, callStack));
+}
+
 #include "MysqlDriver.moc"
diff --git a/src/drivers/mysql/MysqlDriver.h b/src/drivers/mysql/MysqlDrive=
r.h
index bf86c72..a5d1530 100644
--- a/src/drivers/mysql/MysqlDriver.h
+++ b/src/drivers/mysql/MysqlDriver.h
@@ -2,7 +2,7 @@
    Copyright (C) 2002 Lucijan Busch <lucijan@gmx.at>
    Copyright (C) 2003 Daniel Molkentin <molkentin@kde.org>
    Copyright (C) 2003 Joseph Wenninger<jowenn@kde.org>
-   Copyright (C) 2003-2015 Jaros=C5=82aw Staniek <staniek@kde.org>
+   Copyright (C) 2003-2016 Jaros=C5=82aw Staniek <staniek@kde.org>
 =

    This program is free software; you can redistribute it and/or
    modify it under the terms of the GNU Library General Public
@@ -77,6 +77,12 @@ public:
                                             KDbQuerySchemaParameterValueLi=
stIterator* params,
                                             KDb::ExpressionCallStack* call=
Stack) const;
 =

+    //! Generates native (driver-specific) function call for concatenation=
 of two strings.
+    //! Uses CONCAT().
+    KDbEscapedString concatenateFunctionToString(const KDbBinaryExpression=
 &args,
+                                                 KDbQuerySchemaParameterVa=
lueListIterator* params,
+                                                 KDb::ExpressionCallStack*=
 callStack) const;
+
 protected:
     virtual QString drv_escapeIdentifier(const QString& str) const;
     virtual QByteArray drv_escapeIdentifier(const QByteArray& str) const;
diff --git a/src/expression/KDbBinaryExpression.cpp b/src/expression/KDbBin=
aryExpression.cpp
index 18ff7e0..5515861 100644
--- a/src/expression/KDbBinaryExpression.cpp
+++ b/src/expression/KDbBinaryExpression.cpp
@@ -1,5 +1,5 @@
 /* This file is part of the KDE project
-   Copyright (C) 2003-2015 Jaros=C5=82aw Staniek <staniek@kde.org>
+   Copyright (C) 2003-2016 Jaros=C5=82aw Staniek <staniek@kde.org>
 =

    Based on nexp.cpp : Parser module of Python-like language
    (C) 2001 Jaros=C5=82aw Staniek, MIMUW (www.mimuw.edu.pl)
@@ -139,6 +139,7 @@ KDbField::Type KDbBinaryExpressionData::typeInternal(KD=
b::ExpressionCallStack* c
             return KDbField::Null;
         }
         return (ltBool && rtBool) ? KDbField::Boolean : KDbField::InvalidT=
ype;
+    case '+':
     case CONCATENATION:
         if (lt =3D=3D KDbField::Text && rt =3D=3D KDbField::Text) {
             return KDbField::Text;
@@ -150,8 +151,10 @@ KDbField::Type KDbBinaryExpressionData::typeInternal(K=
Db::ExpressionCallStack* c
             || (rtText && ltNull))
         {
             return KDbField::Null;
+        } else if (token.value() =3D=3D CONCATENATION) {
+            return KDbField::InvalidType;
         }
-        return KDbField::InvalidType;
+        break; // '+' can still be handled below for non-text types
     default:;
     }
 =

@@ -244,6 +247,18 @@ KDbEscapedString KDbBinaryExpressionData::toStringInte=
rnal(
                                         KDbQuerySchemaParameterValueListIt=
erator* params,
                                         KDb::ExpressionCallStack* callStac=
k) const
 {
+    switch (token.value()) {
+    case '+':
+    case CONCATENATION: {
+        if (driver && KDbField::isTextType(type())) {
+            const KDbBinaryExpression binaryExpr(const_cast<KDbBinaryExpre=
ssionData*>(this));
+            return driver->concatenateFunctionToString(binaryExpr, params,=
 callStack);
+        }
+        break;
+    }
+    default:;
+    }
+
 #define INFIX(a) \
     (left().constData() ? left()->toString(driver, params, callStack) : KD=
bEscapedString("<NULL>")) \
     + " " + a + " " + (right().constData() ? right()->toString(driver, par=
ams, callStack) : KDbEscapedString("<NULL>"))
diff --git a/src/expression/KDbExpression.h b/src/expression/KDbExpression.h
index 11a5e88..9621123 100644
--- a/src/expression/KDbExpression.h
+++ b/src/expression/KDbExpression.h
@@ -369,6 +369,7 @@ protected:
     explicit KDbBinaryExpression(const ExplicitlySharedExpressionDataPoint=
er &ptr);
 =

     friend class KDbExpression;
+    friend class KDbBinaryExpressionData;
 };
 =

 =