12.0.1.3851 Compare Databases: "Unrecognized sql"

[Breck_Carter]

12.0.1.3851

Sybase Central - Tools - SQL Anywhere 12 - Compare Databases...

An unknown error has occurred.
Unrecognized sql in CREATE TABLE statement: "end)
   ,"canarian_query_elapsed_msec"    bigint NOT NULL COMPUTE (case when ABS(DATEDIFF(year,canarian_query_started_at,canarian_query_finished_at)) >= 4083 then
  cast(DATEDIFF(hour,canarian_query_started_at,canarian_query_finished_at) as bigint)*60*60*1000
when ABS(DATEDIFF(year,canarian_query_started_at,canarian_query_finished_at)) >= 68 then
  cast(DATEDIFF(minute,canarian_query_started_at,canarian_query_finished_at) as bigint)*60*1000
when ABS(DATEDIFF(day,canarian_query_started_at,canarian_query_finished_at)) >= 24 then
  cast(DATEDIFF(second,canarian_query_started_at,canarian_query_finished_at) as bigint)*1000
else DATEDIFF(millisecond,canarian_query_started_at,canarian_query_finished_at)
end)
   ,"sample_started_at"              timestamp NOT NULL DEFAULT '1900-01-01'
   ,"sample_finished_at"             timestamp NOT NULL DEFAULT '1900-01-01'
   ,"canarian_query_started_at"      timestamp NOT NULL DEFAULT '1900-01-01'
   ,"canarian_query_finished_at"     timestamp NOT NULL DEFAULT '1900-01-01'
   ,"datediff_msec_between_target_and_local" bigint NOT NULL DEFAULT 0
   ,PRIMARY KEY ("sample_set_number" ASC) 
)
"
Unrecognized sql in CREATE TABLE statement: "end)
   ,"canarian_query_elapsed_msec"    bigint NOT NULL COMPUTE (case when ABS(DATEDIFF(year,canarian_query_started_at,canarian_query_finished_at)) >= 4083 then
  cast(DATEDIFF(hour,canarian_query_started_at,canarian_query_finished_at) as bigint)*60*60*1000
when ABS(DATEDIFF(year,canarian_query_started_at,canarian_query_finished_at)) >= 68 then
  cast(DATEDIFF(minute,canarian_query_started_at,canarian_query_finished_at) as bigint)*60*1000
when ABS(DATEDIFF(day,canarian_query_started_at,canarian_query_finished_at)) >= 24 then
  cast(DATEDIFF(second,canarian_query_started_at,canarian_query_finished_at) as bigint)*1000
else DATEDIFF(millisecond,canarian_query_started_at,canarian_query_finished_at)
end)
   ,"sample_started_at"              timestamp NOT NULL DEFAULT '1900-01-01'
   ,"sample_finished_at"             timestamp NOT NULL DEFAULT '1900-01-01'
   ,"canarian_query_started_at"      timestamp NOT NULL DEFAULT '1900-01-01'
   ,"canarian_query_finished_at"     timestamp NOT NULL DEFAULT '1900-01-01'
   ,"datediff_msec_between_target_and_local" bigint NOT NULL DEFAULT 0
   ,PRIMARY KEY ("sample_set_number" ASC) 
)
"

Here is the original source code used to create the offending table:

   CREATE TABLE rroad_sample_set (
      sampling_id                              UNSIGNED INTEGER NOT NULL, -- FK relationship not maintained: REFERENCES rroad_sampling_options ( sampling_id ),
      sample_set_number                        UNSIGNED BIGINT NOT NULL DEFAULT AUTOINCREMENT,
      sample_lost                              VARCHAR ( 1 ) NOT NULL DEFAULT 'N',
      connected_ok                             VARCHAR ( 1 ) NOT NULL DEFAULT 'Y', -- set to N for a lost sample
      exception_description                    VARCHAR ( 32767 ) NOT NULL DEFAULT '',
      sample_elapsed_msec                      BIGINT NOT NULL COMPUTE ( 
                                                  CASE 
                                                     WHEN ABS ( DATEDIFF ( YEAR, sample_started_at, sample_finished_at ) ) >= 4083
                                                        THEN CAST ( DATEDIFF ( HOUR, sample_started_at, sample_finished_at ) AS BIGINT ) * 60 * 60 * 1000
                                                     WHEN ABS ( DATEDIFF ( YEAR, sample_started_at, sample_finished_at ) ) >= 68
                                                        THEN CAST ( DATEDIFF ( MINUTE, sample_started_at, sample_finished_at ) AS BIGINT ) * 60 * 1000
                                                     WHEN ABS ( DATEDIFF ( DAY, sample_started_at, sample_finished_at ) ) >= 24
                                                        THEN CAST ( DATEDIFF ( SECOND, sample_started_at, sample_finished_at ) AS BIGINT ) * 1000
                                                     ELSE DATEDIFF ( MILLISECOND, sample_started_at, sample_finished_at )
                                                  END ),
      canarian_query_elapsed_msec              BIGINT NOT NULL COMPUTE ( 
                                                  CASE 
                                                     WHEN ABS ( DATEDIFF ( YEAR, canarian_query_started_at, canarian_query_finished_at ) ) >= 4083
                                                        THEN CAST ( DATEDIFF ( HOUR, canarian_query_started_at, canarian_query_finished_at ) AS BIGINT ) * 60 * 60 * 1000
                                                     WHEN ABS ( DATEDIFF ( YEAR, canarian_query_started_at, canarian_query_finished_at ) ) >= 68
                                                        THEN CAST ( DATEDIFF ( MINUTE, canarian_query_started_at, canarian_query_finished_at ) AS BIGINT ) * 60 * 1000
                                                     WHEN ABS ( DATEDIFF ( DAY, canarian_query_started_at, canarian_query_finished_at ) ) >= 24
                                                        THEN CAST ( DATEDIFF ( SECOND, canarian_query_started_at, canarian_query_finished_at ) AS BIGINT ) * 1000
                                                     ELSE DATEDIFF ( MILLISECOND, canarian_query_started_at, canarian_query_finished_at )
                                                  END ),
      sample_started_at                        TIMESTAMP NOT NULL DEFAULT '1900-01-01',
      sample_finished_at                       TIMESTAMP NOT NULL DEFAULT '1900-01-01',
      canarian_query_started_at                TIMESTAMP NOT NULL DEFAULT '1900-01-01',
      canarian_query_finished_at               TIMESTAMP NOT NULL DEFAULT '1900-01-01',
      datediff_msec_between_target_and_local   BIGINT NOT NULL DEFAULT 0,
      PRIMARY KEY ( sample_set_number ) );

[Former Member · Accepted solution]

The problem here is the multi-line COMPUTE() expression with nested parentheses. The expression is perfectly valid, but the Compare Database window can't parse it. For now, I've added a fix to version 16.0.0 (the fix will appear in the first EBF) to skip such CREATE TABLE statements.

A complete fix to actually parse the statement and compare it with the corresponding CREATE TABLE statement in the other database requires more significant changes, so these changes will come in a later EBF.

[Former Member · Accepted solution]

This is now fully fixed in version 16.0.0. The full fix (which correctly parses COMPUTE expressions containing nested parentheses, rather than ignoring them) will appear in the second EBF.

The bug number is 738312.