Bug Report T994957
Visible to All Users

SqlDataSource connected to a PostgreSql database - SqlExecutionException "operator does not exist: integer = integer[]" is thrown on an attempt to execute a query on the server and obtain the resulting query schema

created 4 years ago

I am using Reporting with Angular connecting to the PostgreSql database. I am getting an error with a custom query. When the query is made by Query Builder, no error occurs. But if I make any changes to the query, the error is displayed.
I am attaching a sample sample.

  1. To reproduce the problem, create the table below in a Postgre database and configure the connection in appsettings.json:
Code
create table scientist (id integer, firstname varchar(100), lastname varchar(100));         insert into scientist (id, firstname, lastname) values (1'albert''einstein');         insert into scientist (id, firstname, lastname) values (2'isaac''newton');         insert into scientist (id, firstname, lastname) values (3'marie''curie');         select * from scientist;
  1. Run the project, open the ReportTestIn report and view (everything should work fine so far);
  2. Now open Query Build and make any changes (eg any line break). When running the report. You should be able to track the error below;
Call Stack
DXReporting: Error: [02/05/2021 23:59:01]: Exception occurred. Message: 'StartBuild error'. Exception Details: System.AggregateException: One or more errors occurred. (Error when trying to populate the datasource. The following exception was thrown: Query scientist failed to execute.) ---> DevExpress.XtraReports.DataRetrievalException: Error when trying to populate the datasource. The following exception was thrown: Query scientist failed to execute. ---> System.AggregateException: One or more queries have failed to execute. (Query scientist failed to execute.) ---> DevExpress.DataAccess.Sql.QueryExecutionException: Query scientist failed to execute. ---> DevExpress.DataAccess.Native.Sql.SqlExecutionException: Error trying to execute an SQL query 'select "scientist"."id","scientist"."firstname","scientist"."lastname" from "scientist" "scientist" where "scientist"."id" in (@ids) ' with parameters: ids(Object) = System.Int32[]. Error: 42883: operator does not exist: integer = integer[] ---> Npgsql.PostgresException (0x80004005): 42883: operator does not exist: integer = integer[] at Npgsql.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|194_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage) at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken) at Npgsql.NpgsqlDataReader.NextResult() at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior) at DevExpress.DataAccess.Native.ColumnarDataLoaderHelper.ExecuteReader(IDbCommand command, CommandBehavior commandBehavior, CancellationToken cancellationToken) at DevExpress.Xpo.Logger.LogManager.Log[T](String category, LogHandler`1 handler, MessageHandler`1 createMessageHandler) at DevExpress.DataAccess.Native.ColumnarDataLoaderHelper.InternalExecuteReader(Object connectionProvider, IDbCommand command, CommandBehavior commandBehavior, CancellationToken cancellationToken) at DevExpress.DataAccess.Native.ColumnarDataLoaderHelper.GetData(Dictionary`2 readers, Object connectionProvider, IDbCommand command, CancellationToken cancellationToken, Func`2 prepareSchema, Boolean nativeSkipSupport, Int32 skipCount, Int32 topCount) at DevExpress.DataAccess.Native.ColumnarDataLoaderHelper.GetData(Dictionary`2 readers, Object connectionProvider, IDbCommand command, CancellationToken cancellationToken, ColumnDescription[] columns, Boolean nativeSkipSupport, Int32 skipCount, Int32 topCount) at DevExpress.DataAccess.Native.ColumnarDataLoaderHelper.GetData(Dictionary`2 readers, Object connectionProvider, IDbCommand command, CancellationToken cancellationToken, ColumnDescription[] columns, Boolean nativeSkipSupport, Int32 skipCount) at DevExpress.DataAccess.Native.Sql.ConnectionProviders.ColumnarDataLoader.<>c__DisplayClass7_0.<SelectData>b__0(IDbCommand command) at DevExpress.DataAccess.Native.Sql.ConnectionProviders.ColumnarDataLoader.CreateAndExecuteQueryCommand[T](IConnectionOptions connectionOptions, Query query, Func`2 func) Exception data: Severity: ERROR SqlState: 42883 MessageText: operator does not exist: integer = integer[] Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: 124 File: parse_oper.c Line: 726 Routine: op_error --- End of inner exception stack trace --- at DevExpress.DataAccess.Native.Sql.ConnectionProviders.ColumnarDataLoader.CreateAndExecuteQueryCommand[T](IConnectionOptions connectionOptions, Query query, Func`2 func) at DevExpress.DataAccess.Native.Sql.ConnectionProviders.ColumnarDataLoader.SelectData(CancellationToken cancellationToken, Query query, ColumnDescription[] columns, IConnectionOptions connectionOptions) at DevExpress.DataAccess.Native.Sql.QueryExecutor.Execute(CustomSqlQuery query, IEnumerable`1 parameters, CancellationToken cancellationToken) at DevExpress.DataAccess.Native.Sql.QueryExecutor.Execute(SqlQuery query, Boolean topThousand, CancellationToken cancellationToken) at DevExpress.DataAccess.Native.Sql.QueryExecutor.Execute(SqlQuery query, CancellationToken cancellationToken) at DevExpress.DataAccess.Sql.SqlDataSource.ExecuteQuery(SqlQuery query, QueryExecutor executor, CancellationToken cancellationToken) --- End of inner exception stack trace --- at DevExpress.DataAccess.Sql.SqlDataSource.ExecuteQuery(SqlQuery query, QueryExecutor executor, CancellationToken cancellationToken) at DevExpress.DataAccess.Sql.SqlDataSource.FillInternal(IEnumerable`1 parameters, CancellationToken cancellationToken, String[] queriesToFill) --- End of inner exception stack trace --- at DevExpress.DataAccess.Sql.SqlDataSource.FillInternal(IEnumerable`1 parameters, CancellationToken cancellationToken, String[] queriesToFill) at DevExpress.DataAccess.Sql.SqlDataSource.FillCore(IEnumerable`1 parameters, CancellationToken cancellationToken, String[] queriesToFill) at DevExpress.DataAccess.Sql.SqlDataSource.DevExpress.Data.IListAdapter2.FillList(IServiceProvider serviceProvider, String[] queriesToFill) at DevExpress.XtraReports.Native.ListAdapterFillerAsync2`1.FillListSynchronously() at DevExpress.XtraReports.Native.ListAdapterFillerAsync`1.ExecuteCore() at DevExpress.XtraReports.Native.DataSourceFiller.Execute() --- End of inner exception stack trace --- at DevExpress.XtraReports.Native.DataSourceFiller.Execute() at DevExpress.XtraReports.UI.XtraReportBase.FillDataSources(Boolean skipIfFilled) at DevExpress.XtraReports.UI.XtraReport.FillDataSources() at DevExpress.XtraReports.UI.XtraReport.DevExpress.XtraReports.IReport.CreateDocumentCore(Single progressRange, Boolean buildForInstantPreview) at DevExpress.XtraReports.Native.DocumentCreator.Create(Single progressRange, Boolean buildForInstantPreview) at DevExpress.XtraReports.UI.XtraReport.CreateDocument(Single progressRange, Boolean buildForInstantPreview) at DevExpress.XtraReports.UI.XtraReport.CreateDocument(Boolean buildForInstantPreview) at DevExpress.XtraReports.UI.XtraReport.CreateDocument() at DevExpress.XtraPrinting.Caching.CachedReportSourceBase.CreateDocumentCore() at DevExpress.XtraReports.Web.WebDocumentViewer.Native.BuildTaskFactoryWeb.<>c__DisplayClass9_0.<CreateTask>b__0() at System.Threading.Tasks.Task.InnerInvoke() at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state) --- End of stack trace from previous location where exception was thrown --- at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread) --- End of inner exception stack trace ---
Comments (1)
DevExpress Support Team 4 years ago

    Hi Sandro,
    Thank you for reporting this issue. I reproduced and forwarded it to the team for further investigation. We'll let you know once we make any progress with it, so stay tuned.

    Thanks

    Answers approved by DevExpress Support

    created 4 years ago

    Hello,
    Thank you for your patience. It turns out that custom query parameters cannot be bound directly to a report's multi-value parameters. You need to manually convert an array of report parameter values to a string and then parse it to an array of values on the server side. You can proceed with option #1 from Pass a Multi-Value Parameter Value to a Stored Procedure.

    Please let me know if you have additional questions.

      Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

      Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.