René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"utPLSQL JSON test cases fail under Oracle DB 21.3","articleBody":"### Describe the bug\r\n\r\nThe following two JSON related test cases fail when running under Oracle Database 21.3:\r\n\r\n- utplsql.test_user.expectations.test_expectations_json.fail_on_diff_data\r\n- UT3$USER#:utplsql.test_user.expectations.test_expectations_json.long_json_diff\r\n\r\nAll other tests complete successful as under 19c.\r\n\r\n### Provide version info\r\n\r\n```\r\n21.0.0.0.0\r\n21.0.0\r\n\r\n\r\nPL/SQL procedure successfully completed.\r\n\r\n\r\nUT_VERSION                                                  \r\n------------------------------------------------------------\r\nv3.1.11.3475-develop\r\n\r\n\r\nBANNER                                                                           BANNER_FULL                                                                                                                                                      BANNER_LEGACY                                                                        CON_ID\r\n-------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------\r\nOracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production           Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production                                                                                           Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production                    0\r\n                                                                                 Version 21.3.0.0.0                                                                                                                                                                                                                                          \r\n\r\n\r\n\r\nPARAMETER                      VALUE                                                           \r\n------------------------------ ----------------------------------------------------------------\r\nNLS_LANGUAGE                   AMERICAN                                                        \r\nNLS_TERRITORY                  AMERICA                                                         \r\nNLS_CURRENCY                   $                                                               \r\nNLS_ISO_CURRENCY               AMERICA                                                         \r\nNLS_NUMERIC_CHARACTERS         .,                                                              \r\nNLS_CALENDAR                   GREGORIAN                                                       \r\nNLS_DATE_FORMAT                DD.MM.YYYY                                                      \r\nNLS_DATE_LANGUAGE              AMERICAN                                                        \r\nNLS_SORT                       BINARY                                                          \r\nNLS_TIME_FORMAT                HH.MI.SSXFF AM                                                  \r\nNLS_TIMESTAMP_FORMAT           DD.MM.YYYY HH24:MI:SSXFF                                        \r\n\r\nPARAMETER                      VALUE                                                           \r\n------------------------------ ----------------------------------------------------------------\r\nNLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR                                              \r\nNLS_TIMESTAMP_TZ_FORMAT        DD.MM.YYYY HH24:MI:SSXFF TZR                                    \r\nNLS_DUAL_CURRENCY              $                                                               \r\nNLS_COMP                       BINARY                                                          \r\nNLS_LENGTH_SEMANTICS           BYTE                                                            \r\nNLS_NCHAR_CONV_EXCP            FALSE                                                           \r\n\r\n17 rows selected. \r\n\r\n\r\nPORT_STRING                                                 \r\n------------------------------------------------------------\r\nx86_64/Linux 2.4.xx\r\n```\r\n\r\n### Information about client software\r\n\r\nSQL Developer 21.2.1. But this does not really matter. It's reproducible with any client.\r\n\r\n### To Reproduce\r\nSteps to reproduce the behavior:\r\n\r\n1. Clone the current develop branch\r\n2. Install the develop version, I've done the following:\r\n```\r\ncd /.../github/utPLSQL\r\ngit fetch --all\r\ngit pull\r\nchmod +x development/*.sh\r\nchmod +x test/*.sh\r\nchmod +x .travis/*.sh\r\necho \"export CONNECTION_STR=127.0.0.1:1522/opdb1.docker\" \u003e\u003e development/env.sh\r\n. development/env.sh\r\ndevelopment/cleanup.sh\r\ndevelopment/refresh_ut3.sh\r\ndevelopment/refresh_sources.sh\r\ndevelopment/install.sh\r\ntest/install_and_run_tests.sh\r\n```\r\n3. You then see two failed tests as a result of running `install_and_run_tests.sh`.\r\n\r\nYou can also run the following block as user `ut3_tester_helper` in a SQL client:\r\n\r\n```sql\r\nset serveroutput on size unlimited\r\nbegin\r\n   ut.run(\r\n      ut_varchar2_list(\r\n         'UT3$USER#:utplsql.test_user.expectations.test_expectations_json.fail_on_diff_data',\r\n         'UT3$USER#:utplsql.test_user.expectations.test_expectations_json.long_json_diff'\r\n      )\r\n   );\r\nend;\r\n/\r\n````\r\n\r\nThe result is:\r\n\r\n```\r\nutplsql\r\n  test_user\r\n    expectations\r\n      json expectations\r\n        Long complex json differences [.288 sec] (FAILED - 1)\r\n \r\nutplsql\r\n  test_user\r\n    expectations\r\n      json expectations\r\n        Gives failure for different data [.051 sec] (FAILED - 2)\r\n \r\nFailures:\r\n \r\n  1) long_json_diff\r\n      Actual: (varchar2)\r\n          'Actual: json was expected to equal: json\r\n          Diff: 133 differences found, showing first 20\r\n          1 missing properties, 132 unequal values\r\n            Extra   property: object on path: $[5]\r\n            Actual value: \"5ce6ec46cb9977b050f15d97\" was expected to be: \"5ce6ec6660565269b16cf836\" on path: $[0].\"_id\"\r\n            Actual value: \"5ce6ec469ba57bef5c421021\" was expected to be: \"5ce6ec66383ddbf3c400e3ed\" on path: $[1].\"_id\"\r\n            Actual value: \"5ce6ec4632328a654d592cb6\" was expected to be: \"5ce6ec6600fb7aaee2d1243e\" on path: $[3].\"_id\"\r\n            Actual value: \"5ce6ec464e6f8751e75ed29f\" was expected to be: \"5ce6ec660a8b5f95ed543305\" on path: $[2].\"_id\"\r\n            Actual value: \"5ce6ec46d9dbfbf9b184cee7\" was expected to be: \"5ce6ec660585cbb589b34fc8\" on path: $[4].\"_id\"\r\n            Actual value: \"59be5b73-fffe-4a4f-acea-65c5abbdb53c\" was expected to be: \"2e778803-50d3-411f-b34d-47d0f19d03f7\" on path: $[1].\"guid\"\r\n            Actual value: \"9dece65b-6b48-4960-880b-7795ff63c81c\" was expected to be: \"18547241-6fd0-466d-9f79-21aeb0485294\" on path: $[4].\"guid\"\r\n            Actual value: \"42e07b71-b769-4078-b226-f79048b75bd2\" was expected to be: \"bb0eaa88-f7fd-4b72-8538-8c0b4595bcec\" on path: $[2].\"guid\"\r\n            Actual value: \"6b9124a9-fbde-4c60-8dac-e296f5daa3c4\" was expected to be: \"4a4363b5-9d65-4b22-9b58-a5c8c1c5bd5d\" on path: $[3].\"guid\"\r\n            Actual value: \"1acb2b6b-15b5-4747-a62f-db477e18df61\" was expected to be: \"c222eda5-d925-4163-89e3-4b0e50d5e297\" on path: $[0].\"guid\"\r\n            Actual value: FALSE was expected to be: TRUE on path: $[2].\"isActive\"\r\n            Actual value: TRUE was expected to be: FALSE on path: $[3].\"isActive\"\r\n            Actual value: TRUE was expected to be: FALSE on path: $[1].\"isActive\"\r\n            Actual value: \"$3,895.35\" was expected to be: \"$2,299.28\" on path: $[1].\"balance\"\r\n            Actual value: \"$1,443.80\" was expected to be: \"$3,626.25\" on path: $[0].\"balance\"\r\n            Actual value: \"$3,366.81\" was expected to be: \"$3,085.28\" on path: $[2].\"balance\"\r\n            Actual value: \"$2,927.54\" was expected to be: \"$3,853.86\" on path: $[4].\"balance\"\r\n            Actual value: \"$2,374.96\" was expected to be: \"$3,152.70\" on path: $[3].\"balance\"\r\n            Actual value: 23 was expected to be: 36 on path: $[2].\"age\"'\r\n       was expected to be like: \r\n          '%Diff: 133 differences found, showing first 20\r\n          %132 unequal values, 1 missing properties\r\n          %Extra   property: object on path: $[5]\r\n          %Actual value: \"5ce6ec46cb9977b050f15d97\" was expected to be: \"5ce6ec6660565269b16cf836\" on path: $[0].\"_id\"\r\n          %Actual value: \"5ce6ec469ba57bef5c421021\" was expected to be: \"5ce6ec66383ddbf3c400e3ed\" on path: $[1].\"_id\"\r\n          %Actual value: \"5ce6ec4632328a654d592cb6\" was expected to be: \"5ce6ec6600fb7aaee2d1243e\" on path: $[3].\"_id\"\r\n          %Actual value: \"5ce6ec464e6f8751e75ed29f\" was expected to be: \"5ce6ec660a8b5f95ed543305\" on path: $[2].\"_id\"\r\n          %Actual value: \"5ce6ec46d9dbfbf9b184cee7\" was expected to be: \"5ce6ec660585cbb589b34fc8\" on path: $[4].\"_id\"\r\n          %Actual value: \"59be5b73-fffe-4a4f-acea-65c5abbdb53c\" was expected to be: \"2e778803-50d3-411f-b34d-47d0f19d03f7\" on path: $[1].\"guid\"\r\n          %Actual value: \"9dece65b-6b48-4960-880b-7795ff63c81c\" was expected to be: \"18547241-6fd0-466d-9f79-21aeb0485294\" on path: $[4].\"guid\"\r\n          %Actual value: \"42e07b71-b769-4078-b226-f79048b75bd2\" was expected to be: \"bb0eaa88-f7fd-4b72-8538-8c0b4595bcec\" on path: $[2].\"guid\"\r\n          %Actual value: \"6b9124a9-fbde-4c60-8dac-e296f5daa3c4\" was expected to be: \"4a4363b5-9d65-4b22-9b58-a5c8c1c5bd5d\" on path: $[3].\"guid\"\r\n          %Actual value: \"1acb2b6b-15b5-4747-a62f-db477e18df61\" was expected to be: \"c222eda5-d925-4163-89e3-4b0e50d5e297\" on path: $[0].\"guid\"\r\n          %Actual value: FALSE was expected to be: TRUE on path: $[2].\"isActive\"\r\n          %Actual value: TRUE was expected to be: FALSE on path: $[3].\"isActive\"\r\n          %Actual value: TRUE was expected to be: FALSE on path: $[1].\"isActive\"\r\n          %Actual value: \"$3,895.35\" was expected to be: \"$2,299.28\" on path: $[1].\"balance\"\r\n          %Actual value: \"$1,443.80\" was expected to be: \"$3,626.25\" on path: $[0].\"balance\"\r\n          %Actual value: \"$3,366.81\" was expected to be: \"$3,085.28\" on path: $[2].\"balance\"\r\n          %Actual value: \"$2,927.54\" was expected to be: \"$3,853.86\" on path: $[4].\"balance\"\r\n          %Actual value: \"$2,374.96\" was expected to be: \"$3,152.70\" on path: $[3].\"balance\"\r\n          %Actual value: 23 was expected to be: 36 on path: $[2].\"age\"%'\r\n      at \"UT3$USER#.TEST_EXPECTATIONS_JSON.LONG_JSON_DIFF\", line 1584 ut.expect(l_actual_message).to_be_like(l_expected_message);\r\n       \r\n  2) fail_on_diff_data\r\n      Actual: (varchar2)\r\n          'Actual: json was expected to equal: json\r\n          Diff: 20 differences found\r\n          13 missing properties, 3 incorrect types, 4 unequal values\r\n            Missing property: \"Alexander Skarsg?rd\" on path: $\r\n            Extra   property: \"Alexander Skarsgard\" on path: $\r\n            Missing property: \"Alice Farmer\" on path: $\r\n            Extra   property: \"Clarke Peters\" on path: $\r\n            Extra   property: \"one\" on path: $.\"Amy Ryan\"\r\n            Missing property: \"The Sopranos\" on path: $.\"Annie Fitzgerald\"[2]\r\n            Extra   property: \"two\" on path: $.\"Amy Ryan\"\r\n            Missing property: \"Oz\" on path: $.\"Annie Fitzgerald\"[3]\r\n            Missing property: \"otherint\" on path: $.\"Aidan Gillen\"\r\n            Extra   property: \"object1\" on path: $.\"Aidan Gillen\".\"object\"\r\n            Extra   property: \"object2\" on path: $.\"Aidan Gillen\".\"object\"\r\n            Extra   property: \"object3\" on path: $.\"Aidan Gillen\".\"object\"\r\n            Extra   property: \"object4\" on path: $.\"Aidan Gillen\".\"object\"\r\n            Actual  type: 'array' was expected to be: 'object' on path: $.\"Amy Ryan\"\r\n            Actual  type: 'string' was expected to be: 'number' on path: $.\"Aidan Gillen\".\"int\"\r\n            Actual  type: 'string' was expected to be: 'boolean' on path: $.\"Aidan Gillen\".\"aboolean\"\r\n            Actual value: \"True Blood\" was expected to be: \"Big Love\" on path: $.\"Annie Fitzgerald\"[0]\r\n            Actual value: \"Big Love\" was expected to be: \"True Blood\" on path: $.\"Annie Fitzgerald\"[1]\r\n            Actual value: FALSE was expected to be: TRUE on path: $.\"Aidan Gillen\".\"boolean\"\r\n            Actual value: \"Game of Thrones\" was expected to be: \"Game of Thron\\\"es\" on path: $.\"Aidan Gillen\".\"array\"[0]'\r\n       was expected to be like: \r\n          '%Diff: 20 differences found\r\n          %3 incorrect types, 4 unequal values, 13 missing properties\r\n          %Missing property: \"Alexander Skarsg?rd\" on path: $\r\n          %Extra   property: \"Alexander Skarsgard\" on path: $\r\n          %Missing property: \"Alice Farmer\" on path: $\r\n          %Extra   property: \"Clarke Peters\" on path: $\r\n          %Extra   property: \"one\" on path: $.\"Amy Ryan\"\r\n          %Missing property: \"The Sopranos\" on path: $.\"Annie Fitzgerald\"[2]\r\n          %Extra   property: \"two\" on path: $.\"Amy Ryan\"\r\n          %Missing property: \"Oz\" on path: $.\"Annie Fitzgerald\"[3]\r\n          %Missing property: \"otherint\" on path: $.\"Aidan Gillen\"\r\n          %Extra   property: \"object1\" on path: $.\"Aidan Gillen\".\"object\"\r\n          %Extra   property: \"object2\" on path: $.\"Aidan Gillen\".\"object\"\r\n          %Extra   property: \"object3\" on path: $.\"Aidan Gillen\".\"object\"\r\n          %Extra   property: \"object4\" on path: $.\"Aidan Gillen\".\"object\"\r\n          %Actual  type: 'array' was expected to be: 'object' on path: $.\"Amy Ryan\"\r\n          %Actual  type: 'string' was expected to be: 'number' on path: $.\"Aidan Gillen\".\"int\"\r\n          %Actual  type: 'string' was expected to be: 'boolean' on path: $.\"Aidan Gillen\".\"aboolean\"\r\n          %Actual value: \"True Blood\" was expected to be: \"Big Love\" on path: $.\"Annie Fitzgerald\"[0]\r\n          %Actual value: \"Big Love\" was expected to be: \"True Blood\" on path: $.\"Annie Fitzgerald\"[1]\r\n          %Actual value: FALSE was expected to be: TRUE on path: $.\"Aidan Gillen\".\"boolean\"\r\n          %Actual value: \"Game of Thrones\" was expected to be: \"Game of Thron\\\"es\" on path: $.\"Aidan Gillen\".\"array\"[0]%'\r\n      at \"UT3$USER#.TEST_EXPECTATIONS_JSON.FAIL_ON_DIFF_DATA\", line 95 ut.expect(l_actual_message).to_be_like(l_expected_message);\r\n       \r\nFinished in .376903 seconds\r\n2 tests, 2 failed, 0 errored, 0 disabled, 0 warning(s)\r\n \r\n\r\n\r\nPL/SQL procedure successfully completed.\r\n```\r\n\r\n### Expected behavior\r\n\r\nAll enable tests complete successful under Oracle Database 21.3.","author":{"url":"https://github.com/PhilippSalvisberg","@type":"Person","name":"PhilippSalvisberg"},"datePublished":"2021-08-14T12:38:01.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":1},"url":"https://github.com/1151/utPLSQL/issues/1151"}