ORDER BY columns are prioritarily *SELECT* columns

as opposed to *table* columns,
and notwithstanding HSQLDB 1.8 (our embedded database) bugs.
Actually, supporting ORDER BY on non-select (but table) columns is OPTIONAL for DBMSs
(but quite common)

Change-Id: I6725dfda36b09429a78262bff6f3d3e3dd9032b6

3 files changed, 60 insertions, 34 deletions

diff --git a/dbaccess/source/core/api/SingleSelectQueryComposer.cxx b/dbaccess/source/core/api/SingleSelectQueryComposer.cxx
index 1cbb0d16df46..24c207690a46 100644
--- a/dbaccess/source/core/api/SingleSelectQueryComposer.cxx
+++ b/dbaccess/source/core/api/SingleSelectQueryComposer.cxx
@@ -456,7 +456,7 @@ void SAL_CALL OSingleSelectQueryComposer::appendFilterByColumn( const Reference<
     setConditionByColumn(column,andCriteria,F_tmp,filterOperator);
 }
 
-OUString OSingleSelectQueryComposer::impl_getColumnName_throw(const Reference< XPropertySet >& column)
+OUString OSingleSelectQueryComposer::impl_getColumnRealName_throw(const Reference< XPropertySet >& column, bool bGroupBy)
 {
     ::connectivity::checkDisposed(OSubComponent::rBHelper.bDisposed);
 
@@ -471,17 +471,18 @@ OUString OSingleSelectQueryComposer::impl_getColumnName_throw(const Reference< X
             throw SQLException(DBACORE_RESSTRING(RID_STR_COLUMN_NOT_VALID),*this,SQLSTATE_GENERAL,1000,makeAny(aErr) );
         }
 
-    OUString aName,aNewName;
+    OUString aName, aNewName;
     column->getPropertyValue(PROPERTY_NAME)         >>= aName;
 
-    if ( !m_xMetaData->supportsOrderByUnrelated() && m_aCurrentColumns[SelectColumns] && !m_aCurrentColumns[SelectColumns]->hasByName(aName))
+    if ( bGroupBy &&
+         !m_xMetaData->supportsGroupByUnrelated() &&
+         m_aCurrentColumns[SelectColumns] &&
+         !m_aCurrentColumns[SelectColumns]->hasByName(aName) )
     {
         OUString sError(DBACORE_RESSTRING(RID_STR_COLUMN_MUST_VISIBLE));
         throw SQLException(sError.replaceAll("%name", aName),*this,SQLSTATE_GENERAL,1000,Any() );
     }
 
-    // Attach filter
-    // Construct SELECT without WHERE and ORDER BY
     OUString aQuote  = m_xMetaData->getIdentifierQuoteString();
     if ( m_aCurrentColumns[SelectColumns]->hasByName(aName) )
     {
@@ -491,7 +492,7 @@ OUString OSingleSelectQueryComposer::impl_getColumnName_throw(const Reference< X
         OSL_ENSURE(xColumn->getPropertySetInfo()->hasPropertyByName(PROPERTY_TABLENAME),"Property TABLENAME not available!");
         OSL_ENSURE(xColumn->getPropertySetInfo()->hasPropertyByName("Function"),"Property FUNCTION not available!");
 
-        OUString sRealName,sTableName;
+        OUString sRealName, sTableName;
         xColumn->getPropertyValue(PROPERTY_REALNAME)    >>= sRealName;
         xColumn->getPropertyValue(PROPERTY_TABLENAME)   >>= sTableName;
         sal_Bool bFunction = sal_False;
@@ -525,11 +526,43 @@ OUString OSingleSelectQueryComposer::impl_getColumnName_throw(const Reference< X
     return aNewName;
 }
 
+OUString OSingleSelectQueryComposer::impl_getColumnName_throw(const Reference< XPropertySet >& column, bool bOrderBy)
+{
+    ::connectivity::checkDisposed(OSubComponent::rBHelper.bDisposed);
+
+    getColumns();
+    if ( !column.is()
+        || !m_aCurrentColumns[SelectColumns]
+        || !column->getPropertySetInfo()->hasPropertyByName(PROPERTY_NAME)
+        )
+        {
+            OUString sError(DBACORE_RESSTRING(RID_STR_COLUMN_UNKNOWN_PROP));
+            SQLException aErr(sError.replaceAll("%value", OUString(PROPERTY_NAME)),*this,SQLSTATE_GENERAL,1000,Any() );
+            throw SQLException(DBACORE_RESSTRING(RID_STR_COLUMN_NOT_VALID),*this,SQLSTATE_GENERAL,1000,makeAny(aErr) );
+        }
+
+    OUString aName, aNewName;
+    column->getPropertyValue(PROPERTY_NAME)         >>= aName;
+
+    if ( bOrderBy &&
+         !m_xMetaData->supportsOrderByUnrelated() &&
+         m_aCurrentColumns[SelectColumns] &&
+         !m_aCurrentColumns[SelectColumns]->hasByName(aName) )
+    {
+        OUString sError(DBACORE_RESSTRING(RID_STR_COLUMN_MUST_VISIBLE));
+        throw SQLException(sError.replaceAll("%name", aName),*this,SQLSTATE_GENERAL,1000,Any() );
+    }
+
+    const OUString aQuote  = m_xMetaData->getIdentifierQuoteString();
+    aNewName = ::dbtools::quoteName(aQuote,aName);
+    return aNewName;
+}
+
 void SAL_CALL OSingleSelectQueryComposer::appendOrderByColumn( const Reference< XPropertySet >& column, sal_Bool ascending ) throw(SQLException, RuntimeException)
 {
     SAL_INFO("dbaccess", "OSingleSelectQueryComposer::appendOrderByColumn" );
     ::osl::MutexGuard aGuard( m_aMutex );
-    OUString sColumnName( impl_getColumnName_throw(column) );
+    OUString sColumnName( impl_getColumnName_throw(column, true) );
     OUString sOrder = getOrder();
     if ( !(sOrder.isEmpty() || sColumnName.isEmpty()) )
         sOrder += COMMA;
@@ -544,7 +577,7 @@ void SAL_CALL OSingleSelectQueryComposer::appendGroupByColumn( const Reference<
 {
     SAL_INFO("dbaccess", "OSingleSelectQueryComposer::appendGroupByColumn" );
     ::osl::MutexGuard aGuard( m_aMutex );
-    OUString sColumnName( impl_getColumnName_throw(column) );
+    OUString sColumnName( impl_getColumnRealName_throw(column, true) );
     OrderCreator aComposer;
     aComposer.append( getGroup() );
     aComposer.append( sColumnName );
diff --git a/dbaccess/source/core/inc/SingleSelectQueryComposer.hxx b/dbaccess/source/core/inc/SingleSelectQueryComposer.hxx
index 1f9c720d2b74..7031b4d3d0ad 100644
--- a/dbaccess/source/core/inc/SingleSelectQueryComposer.hxx
+++ b/dbaccess/source/core/inc/SingleSelectQueryComposer.hxx
@@ -180,9 +180,25 @@ namespace dbaccess
         */
         OUString composeStatementFromParts( const ::std::vector< OUString >& _rParts );
 
-        /** return the name of the column.
+        /** return the name of the column in the *source* *table*.
+
+            That is, for (SELECT a AS b FROM t), it returns A or "t"."A", as appropriate.
+
+            Use e.g. for WHERE, GROUP BY and HAVING clauses.
+
+            @param bGroupBy: for GROUP BY clause? In that case, throw exception if trying to use an unrelated column and the database does not support that.
+        */
+        OUString impl_getColumnRealName_throw(const ::com::sun::star::uno::Reference< ::com::sun::star::beans::XPropertySet >& column, bool bGroupBy);
+
+        /** return the name of the column in the *query*
+
+            That is, for (SELECT a AS b FROM t), it returns "b"
+
+            Use e.g. for ORDER BY clause.
+
+            @param bOrderBy: for ORDER BY clause? In that case, throw exception if trying to use an unrelated column and the database does not support that.
         */
-        OUString impl_getColumnName_throw(const ::com::sun::star::uno::Reference< ::com::sun::star::beans::XPropertySet >& column);
+        OUString impl_getColumnName_throw(const ::com::sun::star::uno::Reference< ::com::sun::star::beans::XPropertySet >& column, bool bOrderBy);
 
     protected:
         virtual ~OSingleSelectQueryComposer();
diff --git a/dbaccess/source/ui/dlg/queryorder.cxx b/dbaccess/source/ui/dlg/queryorder.cxx
index 60860c32d4cc..c241c77076b6 100644
--- a/dbaccess/source/ui/dlg/queryorder.cxx
+++ b/dbaccess/source/ui/dlg/queryorder.cxx
@@ -234,30 +234,7 @@ OUString DlgOrderCrit::GetOrderList( ) const
                 sOrder += OUString(",");
 
             String sName = m_aColumnList[i]->GetSelectEntry();
-            try
-            {
-                sal_Bool bFunction = sal_False;
-                Reference< XPropertySet > xColumn;
-                if ( xColumns.is() && xColumns->hasByName( sName ) && (xColumns->getByName( sName ) >>= xColumn) && xColumn.is() )
-                {
-                    if ( xColumn->getPropertySetInfo()->hasPropertyByName(PROPERTY_REALNAME) )
-                    {
-                        OUString sRealName;
-                        xColumn->getPropertyValue(PROPERTY_REALNAME)    >>= sRealName;
-                        sName = sRealName;
-                        static OUString sFunction("Function");
-                        if ( xColumn->getPropertySetInfo()->hasPropertyByName(sFunction) )
-                            xColumn->getPropertyValue(sFunction) >>= bFunction;
-                    }
-                }
-                if ( bFunction )
-                    sOrder += sName;
-                else
-                    sOrder += ::dbtools::quoteName(sQuote,sName);
-            }
-            catch(const Exception&)
-            {
-            }
+            sOrder += ::dbtools::quoteName(sQuote,sName);
             if(m_aValueList[i]->GetSelectEntryPos())
                 sOrder += sDESC;
             else