View returns only 1000 rows. But underlying SQL returns much more.

Valentyn_Vdovyc_1

I use Vertica CE 6.1.2. I've created table "test.TABLE1" and inserted 3123 rows. Afterwards I created view "test2.VIEW1" in another schema - create view TEST2.VIEW1 as select * from test.TABLE1; But "select count(1) from test2.VIEW1" return only 1000 rows. What can it be???

Sajan_Sangraula

Try running the following statement to check how the view statement is being saved: select * from views where table_name='VIEW1'

Valentyn_Vdovyc_1

Hello, Here is the problem. It saved as "SELECT * FROM test.TABLE LIMIT 1000". Could you please advise who can add LIMIT 1000 and possible way to avoid that? Many thanks in advance.

Sajan_Sangraula

My guess would be that the tool you are using is probably returning 100 rows by default, and that may have gotten into your view script. Can you try doing (in vsql or other sql tool): Create or replace view test2.view1 as SELECT * FROM test.TABLE and see the limit clause goes away?

Valentyn_Vdovyc_1

Thanks. It's an issue with my tool. vsql creates views properly.