test(oceanbase): cover Oracle sequence metadata fallbacks

frontend/src/utils/queryAutoLimit.test.ts

@@ -40,9 +40,9 @@ describe('applyQueryAutoLimit', () => {
     ['dameng'],
     ['dm'],
     ['dm8'],
-  ])('adds FETCH FIRST limit for %s connections', (dbType) => {
+  ])('adds ROWNUM limit for %s connections', (dbType) => {
     expect(applyQueryAutoLimit('SELECT * FROM MYCIMLED.EDC_LOG', dbType, 500).sql)
-      .toBe('SELECT * FROM MYCIMLED.EDC_LOG FETCH FIRST 500 ROWS ONLY');
+      .toBe('SELECT * FROM (SELECT * FROM MYCIMLED.EDC_LOG) WHERE ROWNUM <= 500');
   });
 
   it.each([
@@ -61,8 +61,8 @@ describe('applyQueryAutoLimit', () => {
   });
 
   it.each([
-    ['oracle', 'SELECT * FROM users FETCH FIRST 500 ROWS ONLY'],
-    ['dm8', 'SELECT * FROM users FETCH FIRST 500 ROWS ONLY'],
+    ['oracle', 'SELECT * FROM (SELECT * FROM users) WHERE ROWNUM <= 500'],
+    ['dm8', 'SELECT * FROM (SELECT * FROM users) WHERE ROWNUM <= 500'],
     ['mssql', 'SELECT TOP 500 * FROM users'],
     ['postgresql', 'SELECT * FROM users LIMIT 500'],
     ['gauss-db', 'SELECT * FROM users LIMIT 500'],
@@ -76,17 +76,12 @@ describe('applyQueryAutoLimit', () => {
 
   it('keeps trailing semicolon and comments after injected Oracle limit', () => {
     expect(applyQueryAutoLimit('SELECT * FROM MYCIMLED.EDC_LOG; -- preview', 'oracle', 500).sql)
-      .toBe('SELECT * FROM MYCIMLED.EDC_LOG FETCH FIRST 500 ROWS ONLY; -- preview');
+      .toBe('SELECT * FROM (SELECT * FROM MYCIMLED.EDC_LOG) WHERE ROWNUM <= 500; -- preview');
   });
 
-  it('uses Oracle FETCH FIRST limit for simple table queries', () => {
+  it('uses Oracle 11g compatible ROWNUM limit for simple table queries', () => {
     expect(applyQueryAutoLimit('select 1 from xxx', 'oracle', 500).sql)
-      .toBe('select 1 from xxx FETCH FIRST 500 ROWS ONLY');
-  });
-
-  it('keeps ORDER BY semantics with Oracle FETCH FIRST', () => {
-    expect(applyQueryAutoLimit('SELECT * FROM users ORDER BY created_at DESC', 'oracle', 100).sql)
-      .toBe('SELECT * FROM users ORDER BY created_at DESC FETCH FIRST 100 ROWS ONLY');
+      .toBe('SELECT * FROM (select 1 from xxx) WHERE ROWNUM <= 500');
   });
 
   it('does not add another generic limit when SQL already limits rows', () => {

frontend/src/utils/queryAutoLimit.ts

@@ -322,7 +322,7 @@ export const applyQueryAutoLimit = (
     if (offsetPos >= 0 && (fromPos < 0 || offsetPos > fromPos)) return { sql, applied: false, maxRows };
     const forPos = findTopLevelKeyword(main, 'for');
     if (forPos >= 0 && (fromPos < 0 || forPos > fromPos)) return { sql, applied: false, maxRows };
-    return { sql: `${main.trimEnd()} FETCH FIRST ${maxRows} ROWS ONLY${tail}`, applied: true, maxRows };
+    return { sql: `SELECT * FROM (${main.trimEnd()}) WHERE ROWNUM <= ${maxRows}${tail}`, applied: true, maxRows };
   }
 
   const offsetPos = findTopLevelKeyword(main, 'offset');

internal/app/methods_db_objects.go

@@ -417,9 +417,16 @@ func buildObjectSequenceMetadataQueries(dbType string, dbName string) []objectMe
 	switch dbType {
 	case "oracle", "dameng":
 		if strings.TrimSpace(dbName) == "" {
-			return []objectMetadataQuerySpec{{sql: "SELECT SEQUENCE_NAME AS sequence_name FROM USER_SEQUENCES ORDER BY SEQUENCE_NAME"}}
+			return []objectMetadataQuerySpec{
+				{sql: "SELECT * FROM USER_SEQUENCES ORDER BY SEQUENCE_NAME"},
+				{sql: "SELECT OBJECT_NAME AS sequence_name FROM USER_OBJECTS WHERE OBJECT_TYPE = 'SEQUENCE' ORDER BY OBJECT_NAME"},
+			}
+		}
+		owner := strings.ToUpper(safeDbName)
+		return []objectMetadataQuerySpec{
+			{sql: fmt.Sprintf("SELECT * FROM ALL_SEQUENCES WHERE SEQUENCE_OWNER = '%s' ORDER BY SEQUENCE_NAME", owner)},
+			{sql: fmt.Sprintf("SELECT OWNER AS schema_name, OBJECT_NAME AS sequence_name FROM ALL_OBJECTS WHERE OWNER = '%s' AND OBJECT_TYPE = 'SEQUENCE' ORDER BY OBJECT_NAME", owner)},
 		}
-		return []objectMetadataQuerySpec{{sql: fmt.Sprintf("SELECT SEQUENCE_OWNER AS schema_name, SEQUENCE_NAME AS sequence_name FROM ALL_SEQUENCES WHERE SEQUENCE_OWNER = '%s' ORDER BY SEQUENCE_NAME", strings.ToUpper(safeDbName))}}
 	default:
 		return nil
 	}

internal/app/methods_db_objects_sequence_test.go

@@ -0,0 +1,39 @@
+package app
+
+import (
+	"strings"
+	"testing"
+)
+
+func TestBuildObjectSequenceMetadataQueriesOracleUsesWildcardAndObjectFallback(t *testing.T) {
+	t.Parallel()
+
+	specs := buildObjectSequenceMetadataQueries("oracle", "sbdev")
+	if len(specs) != 2 {
+		t.Fatalf("expected 2 Oracle sequence metadata queries, got %d", len(specs))
+	}
+	if !strings.Contains(specs[0].sql, "SELECT * FROM ALL_SEQUENCES") {
+		t.Fatalf("expected ALL_SEQUENCES wildcard query, got %q", specs[0].sql)
+	}
+	if strings.Contains(specs[0].sql, "LAST_NUMBER") || strings.Contains(specs[0].sql, "ORDER_FLAG") {
+		t.Fatalf("query should not enumerate optional sequence columns: %q", specs[0].sql)
+	}
+	if !strings.Contains(specs[1].sql, "ALL_OBJECTS") || !strings.Contains(specs[1].sql, "OBJECT_TYPE = 'SEQUENCE'") {
+		t.Fatalf("expected ALL_OBJECTS fallback query, got %q", specs[1].sql)
+	}
+}
+
+func TestBuildObjectSequenceMetadataQueriesOracleUserFallbacks(t *testing.T) {
+	t.Parallel()
+
+	specs := buildObjectSequenceMetadataQueries("oracle", "")
+	if len(specs) != 2 {
+		t.Fatalf("expected 2 Oracle user sequence metadata queries, got %d", len(specs))
+	}
+	if specs[0].sql != "SELECT * FROM USER_SEQUENCES ORDER BY SEQUENCE_NAME" {
+		t.Fatalf("unexpected USER_SEQUENCES query: %q", specs[0].sql)
+	}
+	if !strings.Contains(specs[1].sql, "USER_OBJECTS") || !strings.Contains(specs[1].sql, "OBJECT_TYPE = 'SEQUENCE'") {
+		t.Fatalf("expected USER_OBJECTS fallback query, got %q", specs[1].sql)
+	}
+}